Data Management

Managing MySQL with MySQL Workbench

Vincent Danen gives an overview of a graphical tool for working with MySQL databases, available for Linux, Mac, and Windows.

MySQL is still the most popular open source SQL database available. And while many MySQL administrators (myself included) are strict adherents of using the mysql command-line client, those who prefer GUI tools have several choices available. One popular choice is phpMyAdmin, a MySQL administration application written in PHP. The advantages of phpMyAdmin are that it is available on the server, can be used from anywhere with any browser, and does not require you to open up MySQL access directly to the network; using the existing web server with PHP support, you can directly manipulate MySQL via the Web.

Oracle provides another tool that allows you to graphically work with MySQL databases. This tool is the MySQL Workbench, which is a cross-platform GUI tool available for Linux, Mac, and Windows. Most likely you will not find this tool among available packages for your chosen distribution, so you will need to download it from the MySQL Web site. Here you can download it to build from source, or download the binary packages (pre-built packages are available for Fedora, Ubuntu, and SUSE). Registration on the site is optional.

Once the package for your distribution is downloaded, it is easy to install. For Fedora, you can download an rpm package to install. Unfortunately, if you want the stable 5.1 version, you will need to be running Fedora 11. On Fedora 12, only the 5.2 beta packages are available. So, on a Fedora 12 system, use:

# yum install mysql-workbench-oss-5.2.17-1fc12.x86_64.rpm —nogpgcheck

This will automatically grab any additional required dependencies. Because the MySQL Workbench rpm is not signed, you must use the —nogpgcheck option or yum will refuse to install it.

Once it is installed, you can find it in the Applications | Programming menu. When you start it up, you are presented with a "home page" screen that allows you to manage a server, create a new server instance, and also manipulate data on a server (Figure A below). To begin, click the New Server Instance link. Select the required options. If the SQL database is local, use the Localhost option; otherwise, provide the IP address for the remote host to set up.

You can choose how you want to connect to the database: standard TCP/IP (requires having MySQL listening on the network with the firewall allowing packets through), local socket/pipe (best choice for a local database), or TCP/IP over SSH (only requires the remote database to be listening to the local interface for TCP/IP). Once you are finished setting up the connection, it will be listed on the home screen in two places: under SQL Development and Server Administration.

When you double-click on the defined database connection under Server Administration, you will be prompted for the password to connect with (typically, this will be the root user). The Administration interface will open in a new tab, and from here you can start or stop the server, edit the configuration of the server, manager user accounts and privileges, show server status and variables, export or import database dumps, and view logs (if they are stored in the database and not in a file). The interface also provides simple explanations for each option, so this allows you to really tweak your server.

Figure A

Click to enlarge.

In the SQL Development section, you can write SQL commands to be executed on the server. It shows a list of what tables and columns are available. You can look at the output of your statements; it saves command history; and with all of this, you can see how long it took to execute a query. This allows you to test queries before writing them into code, so that you can play around with queries to get the information you need as quickly as possible.

In the object browser, right-clicking on a table presents a number of options allowing you to quickly perform various commands: You can do a quick row select, the output of which will show up in the Results tab, you can also manipulate the table as well by using the Alter, Create, and Drop commands. Table data can be directly manipulated using the Edit Table Data command; this lets you edit the contents of columns and rows in the table, just as if you were editing a spreadsheet.

MySQL Workbench is a great tool, useful for any MySQL administrator or developer. It is an evolution of the disparate MySQL GUI Tools that used to be available. With the Workbench, you can use one tool to fully explore and configure the MySQL server, rather than using separate tools (one for administration, another for manipulation of data). There is a lot more that the Workbench can do; it can be used to create and run scripts on data, it can do data modeling, and more. It is really a very powerful and versatile tool.

Get the PDF version of his tip here.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks

Free Newsletters, In your Inbox