MySQL command line interface

Connecting to MySQL

You can connect to MySQL from ‘stulinux.cms.gre.ac.uk’ details on access this server 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@stulinux 118 % mysql -h mysql.cms.gre.ac.uk -u wug01 -p mdb_wug01
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1876
Server version: 10.5.12-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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.