MySQL

MySQL is installed on i2u2-db and used primarily for CIMA.

Like Postgres, MySQL has its own users and passwords, including a root user, and an interactive shell. When logged into i2u2-db, the shell is accessible with the command

$ mysql -u root -p
Enter password: 

The password is that of the MySQL root user, not your sudo password. If you need this password but don't have it, ask Joel.

MySQL Quick Reference

Command	Function
> SHOW DATABASES;	List the available databases
> SELECT User FROM mysql.user;	List users
> \s	Show details of current connection
> USE database_name;	Connect to database_name
> \u database_name	Connect to database_name
> SHOW TABLES;	Show tables in the current database
> \q	Quit

(More here)

Restarts

From the command line, either of the following equivalent commands will restart MySQL:

$ sudo service mysql restart
$ sudo /etc/init.d/mysql restart

Backups

To backup a MySQL database, use the mysqldump program that's already installed on i2u2-db. From the command line (not the MySQL shell),

$ mysqldump -u root -p --databases {databaseName} > {outputfile}.sql

The password is, again, that of the MySQL root user. You can specify multiple databases, separated by spaces, for {databaseName}; for more comprehensive or fine-grained backups, see the mysqldump reference.

If it doesn't already exist, this will create the file {outputfile}.sql in the directory from which you run the command. This file is a listing of the MySQL commands needed to recreate the database and can be used to restore it.

Other

Joel has notes from 16Feb2017 on creating tables and importing csv files.

-- Main.JoelG - 2017-01-30

Comments