MySQL I : GUI Tools

1. There are a number of excellent graphical MySQL clients that make development and administration of MySQL much easier than if using the MySQL Monitor command line client. The best so far is “MyCC”, though it is still in development. Another, equally functional, but not so well designed is “MySQLGUI”. Both are free open source packages available for Windows and Linux, or for compilation on the platform of your choice. At this point, it is recommended that you use the “MyCC” client, which seems the most evolved.
2. [Note: Your instructor may give you different directions for this step.] To obtain a MySQL graphical client, go to http://www.mysql.com/downloads , and scroll down to “Graphical Clients” section. Click on the “MyCC” link, and download from the mirror site nearest to you. To install, extract the zipped archive to the directory where you downloaded it. Double click “Setup.exe” and follow the onscreen instructions, installing to the default directory c:\program files\MyCC” directory.
3. Go to “Start > Programs > MyCC” to launch the MyCC client.
4. To connect to the local MySQL server, right click in the “MySQL Servers” pane, and select new. Set properties like this:
Name: localhost
Host Name: localhost
User Name: Root
Password: mypass
Port: 3306

Note that a port is like a door to a computer that can be open or closed to bar access. The standard port number for MySQL is 3306. Check with your systems administrator to confirm this.
5. Click test to see if the connection is working. If so, you get a “Connection Successful” message. Click “Add” to close the dialog and add the server connection.
6. Right click on “localhost” and select “Connect”. Your MySQL server will open showing three areas: Databases, Server Administration, and User Administration.
7. Open the database folder by clicking the “+”. The default MySQL databases are shown (mysql, test) and the databases that you created are shown (accounting, accounting2, account_management, alpacas, catalog).
8. Double click the “accounting” database. The tables object is shown. Click on the “+” to see the database tables.
9. Click on each table to see its properties. Properties are: Field, Type, Null, Key, Default, Extra.
10. Click to select the “Clients” table. Double click the “client_id” field to get a detailed report about how that field is set up. When done, close the dialog by clicking on the “x” button for that window.
11. SQL Queries can be performed through the MyCC client. To do so, click to select the accounting > tables icon, and from the toolbar click the “SQL” button. A query dialog will pop up. Type:
SELECT * FROM clients WHERE client_id >= 2

Click the exclamation point button in the toolbar to execute a query.

The recordset for your query is returned. All SQL database and table operations can be performed this way. Repetitive queries can be saved by selecting “File > Save” and naming a text file.
12. Roll your mouse over the toolbar and find the “Query Type” button. This provides a very basic wizard to help you formulate your queries, providing the basic query syntax, that you then modify.

Security, User and Connection Management Using the GUI

1. Find a partner to work with.
2. Identify the IP network address of your machine and your partner’s machine. Open a DOS shell prompt and type: ipconfig

You are looking for the “IP address” information. Write both you and your partners IP down, and confirm these with your instructor.
3. Open a DOS shell and type:
ping <remote_ip_address>

This confirms that you have an internet connection to your partners machine.
4. Launch MyCC.
5. Right click on the “localhost” object you created in the last exercise, and select “Connect”.
6. Double click the “User Administration” icon and view the users for your server.
7. Add a new user by right clicking “User Administration” and selecting “New User”.
8. Set properties like this:
Allow access to “Accounting” should be checked.
User Name: webuser
Host: your partner’s IP address
Password: letmein
Privileges: Only “Select” should be highlighted. Note the way this widget works is funky.

Click “Add”, the “Close”. The new user appears in the user list.

Note that MyCC flushes privileges automatically. Now your partner can attempt to connect to your MySQL server.