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].sqlis 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