Accessing MySQL Databases from Linux Cardiff School of Computer Science & Informatics Documentation & Facilities Toggle navigation Home Documentation Software Services Contact Accessing MySQL Databases from Linux 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. Linux Applications for accessing MySQL 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 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 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. Starting MySQL Workbench on Linux 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. Using MySQL Workbench 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 . Overview Accounts and Databases on the MySQL Server Linux Applications for accessing MySQL The mysql command MySQL Workbench Starting MySQL Workbench on Linux Using MySQL Workbench Back to top Tags database linux mysql sql Tags database linux mysql sql Cardiff University is a member of the Russell Group of Universities Cardiff University is a registered charity No. 1136855 Copyright © Cardiff University Accessibility Feedback Cookies Privacy Policy Terms of Use Welsh Language Scheme
