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.
- Connect to database (mdb_wug01) on the host ‘mysql.cms.gre.ac.uk’
- Show all available tables (if any) that are available within their database (none to start with)
- Creates a table called ‘test’, with one column of chars, called ‘name’
- Show all available tables again
- Insert the entry “foo” into the name column.
- Insert the entry “bar” into the name column.
- Select all the entry in the table test.
- Delete the table.
- Show all available tables once more.
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]
mysqldump -a -h mysql.cms.gre.ac.uk -p -u wug01 mdb_wug01 > mdb_wug01.sql