A graphical way to MySQL mastery

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.