MySQL Basics

If you're just starting with MySQL, it's worth noting that MySQL users are entirely independent of the Linux users - I found this somewhat confusing initially, because MySQL has a "root" user. To start the MySQL command line interface, there are several methods (this works as any user, as you specify what MySQL user you want to be, and then authenticate):

# No root password set yet:
$ mysql -u root
...
mysql>

# root password set, to be prompted for it:
$ mysql -u root -p
Enter password:
...
mysql>

# root password set, put the password on the command line:
$ mysql -u root --password=yourpasswd
...
mysql>

The latter is convenient, and it can be used for automation - but the password appears in your shell's history and in memory. top disguises the password, but I imagine other users could potentially see your password if you use this method.

The first thing to know is how to get out of the MySQL command line: use \q.

A trailing semicolon is required for all SQL commands (but not the backslash commands like \q). Basic commands at the MySQL command line are:

  • "SHOW DATABASES;" (works fine as lower case, but common practice says all caps ...).
  • to make a new database: "CREATE DATABASE <name>;".
  • to get rid of a database: "DROP DATABASE <name>;".
  • to access a database, "USE <name>;"
  • once you're accessing a database, "SHOW tables;"

To do a database backup, get out to the Linux command line:

  • mysqldump for backup: mysqldump -u root --password yourpasswd [dbname] > [dbname].sql
  • it produces a series of SQL statements that will rebuild your database
  • gzip -9 [dbname].sql is only getting a ~2.7:1 compression ratio, I expected better (bzip2 does better, but not hugely so)
  • more likely you want to compress, easiest to do it on the fly: mysqldump -u root -pyourpasswd --all-databases | gzip > filename.sql.gz