MySQL GUI Clients

This time we will look at two graphical alternatives to text based clients.

The Feature-Rich MySQLGUI

MySQLGUI graphical client runs on X-Window. Developed by MySQL AB, the same company that developed the MySQL database, it’s quite easy to install. This month’s CD has the statically linked binary in the directory /cdrom/linux/mysql. Mount the CD, copy the file mysqlgui-linux-static-1.7.5-1.tar.gz to (say) /opt directory. Uncompress and untar it as follows:

mount /mnt/cdrom

cd /mnt/cdrom/cdrom/linux/mysql

cp mysqlgui-linux-static-1.7.5-1.tar.gz /opt

cd /opt

tar -zxvf mysqlgui-linux-static-1.7.5-1.tar.gz

The last command makes a directory called mysqlgui-1.7.5-1-linux-static. Start X-Window. Open a terminal window within X and change to this directory (cd /opt/mysqlgui-1.7.5-1-linux-static). Make sure that the MySQL server is running (refer to the article Online Databases with MySQL on page 119, PCQuest October 2001) and then type the command:

./mysqlgui

When you are asked for the password for the first time, press ‘Cancel’. When the GUI shows up, select ‘Options’ menu and then enter the following path in the text field labeled ‘Sql command on connect (or socket)’:

/var/lib/mysql/mysql.sock

mysql.sock file provides a socket interface to the MySQL server and is thus required by MySQLGUI for connecting to the server. You may like to change the username or login name - which is ‘root’ by default - used to connect to the server by clicking on the ‘Client’ tab. When done, select ‘Save’. Now from the ‘Manage’ menu, select ‘Connect’ and when prompted for the password, enter the password you had setup for the corresponding login name (refer to articles in PCQuest October and November 2001).

The next time you start MySQLGUI, instead of pressing ‘Cancel’, you can use this password. Once connected, you can type in a query in the window and select ‘Execute query’ from the menu. If the query returns results like those in the ‘select’ SQL statements, you are presented with a nice interface to look at the results.

The menu item Command >Administration allows you to exercise administrative tasks graphically without the need of remembering the syntax. Apart from that you can create and drop databases, check out the status of the database server, maintain log files, even shutdown the server etc. We leave upon you to further explore this easy-to-use, but comprehensive graphical client.

Squirrel SQL Client: A Java Front-end

A program written in Java can interface with MySQL database using the JDBC (Java DataBase Connectivity) technology. Squirrel SQL Client is a GUI application based on these lines and written in Java. You will need JDK 1.2 or above and the MySQL JDBC drivers to run this client. JDK 1.4 for Linux is on the PCQuest September 2001 CD and MM.MySQL JDBC drivers are on this month’s CD. Let us first install JDK. Install JDK 1.4 and the JDBC drivers. The JDK will be installed in the directory /usr/java/j2sdk1.4.0. For the JDBC drivers, mount this month’s CD, change to cdrom/linux/mysql and copy the file ‘mm.mysql-2.0.7.jar’ to /opt directory. Change to /opt directory and issue the following command:

/usr/java/j2sdk1.4.0/bin/jar -xf mm.mysql-2.0.7.jar

This makes a directory called mm.mysql-2.0.7 within /opt. Change to this directory and copy the file mm.mysql-2.0.7-bin.jar to /usr/java/j2sdk1.4.0/lib directory.

Next we come to installing the Squirrel SQL Client. From this month’s CD–from the same directory as JDBC drivers–copy the file called ‘squirrel-sql-1.0final2-install.jar’ file to /opt directory. Start X Window using ‘startx’ command. From within a terminal window, change to the /opt directory and issue the following command:

/usr/java/j2sdk1.4.0/bin/java -jar squirrel-sql-1.0final2-install.jar

This will start a graphical installation wizard. Accept the license agreement and click ‘Next’. Subsequently keep clicking on ‘Next’ to accept the defaults. Finally click on the ‘Install’ button when shown. The application files are installed in ‘/usr/local/Squirrel SQL Client’ directory. You must set an environment variable named JAVA_HOME containing the path to the JDK directory before running the Squirrel SQL client. To do this, in a terminal window issue the command:

export JAVA_HOME=/usr/java/j2sdk1.4.0

Also append this line to the file named ‘profile’ found in /etc directory or else you must set it each time, before running Squirrel. Run the application by typing in the following command in the same terminal window:

/usr/local/”Squirrel SQL Client”/squirrel-sql.sh

This will pop up the GUI of Squirrel SQL Client. Select Drivers > New Driver from the menu and enter mm.mysql for the name of the driver. Uncheck the check box labeled ‘Load Driver from CLASSPATH’. Click on the button labeled ‘...’. In the presented file system browser, browse to the directory /usr/java/j2sdk1.4.0/lib and select the file

‘mm.mysql-2.0.7-bin.jar’. For the ‘Driver Class Name’ enter org.gjt.mm.mysql.Driver and for the ‘Example URL’ enter ‘jdbc:mysql://127.0.0.1/dbname’. Replace ‘127.0.0.1’ with the IP address of the machine if MySQL database server is running on a different machine. Replace ‘dbname’ with the name of the database you want to access. Finally press ‘Ok’. The name of the driver will be displayed in the window labeled ‘Drivers’.

Next, from the ‘Aliases’ window click the only active button–‘Create new alias’. In the window that appears, enter any name for the alias and for the ‘User Name”, enter the login name you use to connect to the database specified in the ‘Example URL’ above. The new alias will appear in the Aliases window. Select the alias and then click on the button labeled ‘Connect to the selected alias’. A window pops up again where you must specify the password corresponding to the user name you specified while creating the alias. When you click ‘Ok’, a window appears which shows you many JDBC specific information and data types supported by MySQL. Click on the ‘SQL’ tab. In the text field shown, type any SQL query and then click on the icon that corresponds to executing the query (‘lightning’ picture). If the query returns some result, it’s shown in tabular form in the window below. You can select any previously executed SQL queries from the drop down list above the text field. 

We have included two more graphical clients–gmysql and sqlgui– in the CD (cdrom/ linux/ mysql). The instructions to install them can be found in the text file ‘install.txt’ in the same directory.

Next time, we will go through setting up server side scripting languages like Perl, PHP, JSP and look at how they can be used as front-ends for
MySQL.

Shekhar Govindarajan