This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Tools & Utilities

A graphical way to MySQL mastery

By Amit Kumar Saha on November 21, 2008 (9:00:00 AM)

Share    Print    Comments   

MySQL GUI Tools is a suite of graphical desktop applications for working with and administering MySQL servers. The suite consists of three tools: MySQL Query Browser, MySQL Administrator, and MySQL Migration Assistant (available only on Windows). We'll look at the first two to see how well they let us manage MySQL without using the command line.

You can install MySQL GUI Tools via the package management systems on Ubuntu, Debian, or Fedora Linux. On Ubuntu and Debian the package name is mysql-admin; on Fedora, it's mysql-gui-tools. Pre-built binaries and the source code are all available on the project's site.

The installation process adds new menu items to GNOME's Applications-> Programming menu. You can also invoke the tools from a terminal with the commands mysql-query-browser and mysql-administrator. Both MySQL Query Browser and MySQL Administrator allow you to work with more than one MySQL servers at a time.

MySQL Query Browser

When you start the MySQL Query Browser, a dialog box asks you all the basic information about the MySQL Server that you want to connect to. After the connection has been established you are presented with a workspace window that lets you do things like create a new schema, add tables to it, enter and query data, export the query results, run SQL scripts.

The Query Browser window is divided into three distinct areas. A sidebar on the right contains several tabs in two rows that let you view things like schemas, tables, bookmarks, and history, and provide a reference section for SQL syntax. The Query Window at the top is where you enter your SQL queries. The major part of the screen is occupied by an area where you open tabs for new queries, new SQL scripts, and new result sets.

To create a new MySQL database, right-click on the Sidebar in the Schemata tab. Click Create Schemata and fill in the schema name -- we'll use mysqlguidemo. Then right-click again and select Refresh Schemata. The new schema should show up.

You can set a default schema to use with subsequent commands either by going to File -> Select Schema or right-clicking on the schema in the sidebar and selecting Set as Default Schema. The default schema then appears in bold type in the sidebar.

To add tables to the schema, right-click on the new mysqlguidemo schema and click on Create Table to bring up the Table Editor, with which you enter the details of the new table to be created. When you're happy with the fields you've added, click on Apply Changes to perform the table creation. A Confirm Table Action dialog box shows you the SQL query that the program will execute against your schema. Click on the Execute button to do it. Once the tables have been added you will see them in a tree-like display on the side bar, as shown below:

You can modify tables by right-clicking on the table name and choosing Edit from the pop-menu.

You can also use MySQL Query Browser to execute SQL statements. Choose File -> New Script Tab to create a new SQL script, or you may load an existing SQL script using File -> Open Script, then click on the Execute button to invoke the script. You may also save the new SQL script for your future use.

To retrieve records from a table, double-click on the table name in the sidebar to create a Resultset in the Resultset tab. The Resultset has options for easy navigation, searching, editing of existing records, and addition of new ones. To add a new record or edit an existing one, click on Start Editing toward the bottom of the Resultset window. You can also export Resultsets into CSV, HTML, XML, and Excel files.

Other aspects of the MySQL Query Browser include a history feature and bookmarks. The history feature, visible in the sidebar, lets you view and replay past queries. You can bookmark the current SQL query in the Query tab via Query -> Bookmark. Bookmarked queries are visible in the sidebar's Bookmarks tab.

The Table Options and Advanced Settings tabs allow you to set properties to use for storage engine, character set, row storage format, RAID setup, and many more options.

From the Query menu you can create transactions using Start, Commit, and Rollback statements. You can also add stored procedures and functions to your schema by right-clicking on your schema and selecting New Stored Routine.

MySQL Administrator

The MySQL Query Browser does a good job of helping you create your schemas and tables, feeding data into them, and writing stored objects for them. However, as your tables grow, and complex user access scenario arise, you will also need to administer your MySQL servers efficiently and easily.

The command line utility mysqladmin does a good job of letting you administer MySQL servers. However, an intuitive graphical interface with graphical performance indicators makes MySQL Administrator a great tool for administration of running MySQL servers.

Similar to MySQL Query Browser, on startup, you are required to fill in the MySQL server information you want to connect to. After you are logged in, you get a summary of server and client information; in this case the client is the MySQL Administrator.

The left sidebar of the MySQL Administrator window displays options for administering the various functionalities of the MySQL server. On clicking a specific functional category in the sidebar, the relevant information appears on the right side of the screen in a tabbed interface. If your access privileges permit, you can also modify the information and save the changes.

The Service Control window allows you to stop the running MySQL server to which the client is connected. However, once you stop it, you will have to manually start the server again from outside the tool.

You can configure a MySQL server instance with various options or parameters via option files, which are also called configuration files in MySQL lingo. The Startup Parameters window is a graphical way to add or modify various startup options for the MySQL server. You can modify the user, data, or temp directory; MyISAM, InnoDB, and replication parameters; Security Settings; and other options.

The User Administration window allows you add new users to the MySQL server and modify or delete existing ones. In addition to the basic tasks, you can also set per-user schema privileges and resource limits for individual users.

The Health window shows current client connection statistics such as connection usage, traffic, and number of current SQL queries being executed. You can view the query cache hit rate in the Memory Health tab. The Status Variables and Server Variables tabs show the values of the current status and Server variables and let you modify the values in place. Click Refresh and you will see the new values.

The Backup and Restore Backup windows lets you backup and restore one or all of your schemas. If you have replication enabled on your databases, the Replication Status window allows you to monitor the process.

The two graphical tools in MySQL GUI Tools are helpful for two kinds of users. The MySQL Query Browser makes MySQL more user-friendly for students and database-driven application developers, while the MySQL Administrator improves the life of database administrators with its graphical interface.

Amit Kumar Saha is a student of computer science and engineering from India. He writes about Linux, open source software, and technical topics mainly for beginners, and is also a contributor to a couple of open source documentation projects.

Share    Print    Comments   

Comments

on A graphical way to MySQL mastery

Note: Comments are owned by the poster. We are not responsible for their content.

Another tool.

Posted by: Anonymous [ip: 68.238.158.178] on November 21, 2008 02:20 PM
I've become a big fan of SQLYog myself.
-EllisGL

#

Re: Another tool.

Posted by: Anonymous [ip: 68.238.158.178] on November 21, 2008 02:22 PM
Oh - the only draw back is that you'll have to pull out Wine to run it...

#

Re: Another tool.

Posted by: Anonymous [ip: 59.164.186.188] on November 21, 2008 05:13 PM
I haven't tried SQL Yog, but its Windows-only availability doesn't help.

-Amit

#

A graphical way to MySQL mastery

Posted by: DavidChipman on November 21, 2008 07:30 PM
Neither of these tools are exactly new. They've been around for years (late '90s, certainly).

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 67.176.118.242] on November 22, 2008 04:03 AM
How about a nice article about "Notepad"

#

Re: A graphical way to MySQL mastery

Posted by: Anonymous [ip: 59.164.100.18] on November 22, 2008 03:21 PM
You really want it?

#

Re(1): A graphical way to MySQL mastery

Posted by: Anonymous [ip: 207.171.180.101] on November 26, 2008 09:35 PM
yes

#

Re: A graphical way to MySQL mastery

Posted by: Anonymous [ip: 76.203.253.240] on November 23, 2008 11:14 AM
There are people who were unaware of this app.

No need for the retort.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 72.81.24.169] on November 22, 2008 04:36 AM
The icons look kinda Vista-ish in the MySQL Administrator window. An article about Notepad would have to be about how much it sucks.

#

For those of us who abhor Winders...

Posted by: Anonymous [ip: 75.67.216.99] on November 22, 2008 05:30 AM
Check out mysql-navigator. Great little GUI for perusing your database; works like a charm.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 220.233.66.76] on November 22, 2008 09:07 AM
The way Query Browser handles editing of Stored Procedures is quite poor, it really has left a bad taste in my mouth.
Hopefully the Linux version of MySQL Workbench will be out soon, that is quite a nice tool.

Unfortunately none of these programs are anywhere near as good as MS SQL Server Management Studio though :/

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 85.98.13.196] on November 22, 2008 10:44 AM
i am using myql query browser,
but it has a bug,
when i issue multiple query in one query, with semicolon separated, like:
insert into .... ;
insert into .....;

it says query error.. same query wroks fine in mysql console or phpmyadmin..
ehcp@ehcp.net

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 200.38.120.1] on November 22, 2008 10:23 PM
This article would've been helpful about ten years ago.

Really, MySQL GUI Tools is not a way to MySQL mastery.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 68.124.78.20] on November 23, 2008 07:09 AM
A lot people DO NOT know these tools exist! Esp newbies... so leave the guy alone.

#

Re: A graphical way to MySQL mastery

Posted by: Anonymous [ip: 192.18.192.21] on November 24, 2008 09:20 AM
You introduce MySQL to school kids and ask them to work with the command line. The kids like it and say WOW and they stop using other RDBMS which give them a nice GUI to work with. That is fictitious. The graphical tools give the MySQL newbies more breathing space.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 200.38.123.252] on November 24, 2008 06:59 PM
I have to admit the article is good for mysql newcomers, but the title is a bad joke. ¿MySQL Mastery... using GUI Tools? C'mon.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 72.62.144.138] on November 25, 2008 07:07 PM
Thank you Amit, The rude posters are just jealous because you can write and attempt to help the community. Something at which they are incapable of. I look forward to reading more of your articles.

#

Re: A graphical way to MySQL mastery

Posted by: Anonymous [ip: 192.18.192.76] on November 26, 2008 12:13 PM
Thanks :-)

#

A graphical way to MySQL mastery

Posted by: ForLinux on November 27, 2008 12:27 PM
Great Tool!

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 96.253.120.229] on November 28, 2008 06:42 PM
Good article.

I've become a big fan of Squirrel Sql (http://www.squirrelsql.org/) as a query tool and object browser. It uses JDBC so it can be used on any database that's JDBC compliant.

#

A graphical way to MySQL mastery

Posted by: Anonymous [ip: 24.16.32.94] on December 01, 2008 05:14 AM
I'm still waiting for all the features of SQL server to be duplicated in MySQL. Gui tools? Wow, is that only 10 years too late.

When I really need to get something done, then I pull out SQL Server :)

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya