This Note gives information about accessing MySQL on Linux
with
the mysql terminal 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.
MySQL can be accessed from applications and programs on Linux. This Note
describes accessing MySQL on Linux
with
the mysql command interface
and from the MySQL Workbench application.
The mysql command is a terminal-based SQL command interface to MySQL.
On Linux, start mysql with the mysql command in a terminal window.
The command connects to the School's sample MySQL database on csmysql.
Note the command and its arguments are of the form:
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 | Linux | Jane |
| brown | Linux | Arthur |
+-------+---------+--------+
4 rows in set (0.03 sec)
mysql>
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 Linux Laboratory workstations.
You can start MySQL Workbench from either the Mate Graphical User Interface menu, or from the Linux terminal.
MySQL Workbench can be started from the Mate menu. Click on the Mate menu button in the bottom edge panel to open the Mate menu panel.
If the panel is showing Favourites, click on All applications to
see categories of applications.
Scroll down to Programming and click over
MySQL Workbench
MySQL Workbench can be invoked from the command line in a Linux terminal as well.
mysql-workbench
The terminal must be associated with a graphical display (ie the X11 DISPLAY variable must be set), like it would be if you are logged in at a workstation, or MySQL Workbench will give an error message and fail to run.
When MySQL Workbench starts, the initial window is divided into three panes.
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
.