Accessing MySQL Databases from Windows

This Note gives information about accessing MySQL on Windows with the mysql command interface and from the MySQL Workbench application.

For information about the School's MySQL database server and to learn about your MySQL account and database schema, see

MySQL in the School of Computer Science & Informatics.

Windows Applications for accessing MySQL

MySQL can be accessed from applications and programs on Windows. This Note describes accessing MySQL on Windows with the mysql command interface and from the MySQL Workbench application.

The mysql program

The mysql program is a terminal-based SQL command interface to MySQL.

To start MySQL open a Command Prompt and use the command below to connect to the School's MySQL server.

mysql -h csmysql.cs.cf.ac.uk -u username -p database

The arguments are

  • -h followed by the server host name (csmysql.cs.cf.ac.uk)
  • -u followed by the account user name (use your MySQL username)
  • -p which tells mysql to prompt for a password
  • database the name of the database (use your database name).

Once mysql is running, you can type SQL statements and see the output in the terminal window.

mysql> select * from systems;
+-------+--------+-----------+
| name  | oscode | ownercode |
+-------+--------+-----------+
| blue  |      1 |         2 |
| red   |      2 |         2 |
| green |      3 |         1 |
| brown |      2 |         3 |
+-------+--------+-----------+
4 rows in set (0.00 sec)
mysql> select s.name, o.osname, p.pname from systems s, opsystem o, people p
    -> where s.oscode=o.oscode and s.ownercode = p.pcode;
+-------+---------+--------+
| name  | osname  | pname  |
+-------+---------+--------+
| green | MacOSX  | John   |
| blue  | Windows | Jane   |
| red   | Windows   | Jane   |
| brown | Windows   | Arthur |
+-------+---------+--------+
4 rows in set (0.03 sec)
mysql>

MySQL Workbench

MySQL Workbench is a modeling tool that lets you design and generate MySQL databases graphically. It can also be used as an interface to execute SQL queries.

MySQL Workbench is available on the School's Windows Laboratory workstations.

Using MySQL Workbench

MySQL Workbench is available via the Start menu in the School's Windows Teaching Laboratories.

When MySQL Workbench starts, the initial window is divided into three panes.

  • The top left pane is for connections to databases,
  • the lower left pane is for data models,
  • the right side pane is a list of shortcuts to MySQL functions and information.

There is also a menu bar across the top.

To begin with, we need to configure a new connection to the database we want to use.

In the Connections pane click on the after MySQL Connections.

In the setup window, type a name that you want to give to the connection, for example, use your username and the server name, or a description like My Database.

In the Hostname field, enter the server name csmysql.cs.cf.ac.uk.

Select Standard TCP/IP as the Connection Method.

Enter your MySQL username in the Username field and the name of your database in the Schema field.

If you click on Store in Keychain, you will be prompted for your password. Otheriwse MySQL Workbench will prompt for it when you make the connection.

Click on OK, and the an icon for the new connection appears in the workspace.

To open the connection, double-click on the icon.

After obtaining your password, an SQL editor opens in a new tab and connects to the server.

The example here shows a connection to the sample MySQL database.

The left hand pane shows the database schema, including the tables that had been previously created.

To the right is an SQL query pane where you can type SQL commands. The results of executing the commands appear below in the result pane.

To run commands in the SQL query pane, click the execute icon .