MySQL command line interface

Connecting to MySQL

You can connect to MySQL from both ‘student.cms.gre.ac.uk’ and ‘stulinux.cms.gre.ac.uk’ details on access these servers via ssh, which can be found here. Once you have successfully logged in you will need to use the ‘mysql’ command to connect to the database server:

% mysql -h mysql.cms.gre.ac.uk -u username -p mdb_username

Replace both ‘username’ instances (in red) with your username. E.g.:

% mysql -h mysql.cms.gre.ac.uk -u wug01 -p mdb_wug01

Once connected you may create and drop tables; insert, delete and select records.

To disconnect and logout of MySQL use either the ‘exit’ or ‘quit’ command.

The MySQL 5.5 Reference Manual provides in-depth details regarding all aspects of MySQL.

MySQL command line session (example)

The following is a sequence of events relating to the MySQL command line. Please note that you will not actually see this on your screen.

  1. Connect to database (mdb_wug01) on the host ‘mysql.cms.gre.ac.uk’
  2. Show all available tables (if any) that are available within their database (none to start with)
  3. Creates a table called ‘test’, with one column of chars, called ‘name’
  4. Show all available tables again
  5. Insert the entry “foo” into the name column.
  6. Insert the entry “bar” into the name column.
  7. Select all the entry in the table test.
  8. Delete the table.
  9. Show all available tables once more.
  10. Quit
wug01@student 118 % mysql -h mysql.cms.gre.ac.uk -u wug01 -p mdb_wug01
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1013679
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create TABLE test (name CHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------------+
| Tables_in_mdb_wug01 |
+---------------------+
| test                | 
+---------------------+
1 rows in set (0.00 sec)

mysql> INSERT INTO test (name) VALUES ("foo");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (name) VALUES ("bar");
Query OK, 1 row affected (0.01 sec)

mysql>  SELECT * FROM test;
+------+
| name |
+------+
| foo  | 
| bar  | 
+------+
2 rows in set (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> quit
Bye

How to use ‘mysqldump’ to backup a database

Log into Unix and run ‘mysqldump’ in the following format:

mysqldump -a -h [hostname] -p -u [userid] [database] > [output filename]

Example:

mysqldump -a -h mysql.cms.gre.ac.uk -p -u wug01 mdb_wug01 > mdb_wug01.sql

Comments are closed.