MySQL

Installing mySQL

mySQL is a popular open-source database... well, it used to be. It has since been gobbled up by the monster that is Sun/Oracle. While aspects of the database remain open source, many of the open-source community are gradually moving away from mySQL. That said, it is still arguably the most convenient and widely used non-commercial database product in the world.

Chances are mysql has already been emerged on your system as a dependency for some other package. But in case it hasn't

$ sudo emerge -av mysql

After the package has emerged, we'll need to set it up, start it, and add it to startup.

$ sudo /usr/bin/mysql_install_db
$ sudo /etc/init.d/mysql start
$ sudo rc-update add mysql default

Next we'll set a root password for mySQL.

$ sudo /usr/bin/mysqladmin -u root password '<yourpassword>'

To log in to mysql, type

$ mysql -u root -p

and enter the password you supplied above.

To create a database, enter

mysql> CREATE DATABASE <yourdatabase>;

To create a user, enter

mysql> CREATE USER 'yourmysqluser'@'localhost';

To set their password, enter

mysql> set password for <yourmysqluser>@localhost = password('<yourpassword>');

To grant that user privileges full permissions on a database, enter

mysql> grant ALL on <yourdatabase>.* to <yourmysqluser>@localhost;

Finally, to exit mySQL, enter

mysql> quit

Backing up mySQL

You should back up mySQL regularly, perhaps using a cron job or otherwise. Here is an example of a script capable of backing up an entire mySQL database.

$ sudo nano -w /usr/bin/backupmysql

Add the following to the file

#!/bin/sh
datum=`/bin/date +%Y%m%d-%H`
/usr/bin/mysqldump --user=root --password=<yourpassword> --lock-all-tables \
--all-databases | gzip -9 > /path/to/backup-${datum}.sql.gz

Make the file executable

$ sudo chmod 775 /usr/bin/backupmysql

You can now back up your entire mySQL database by running this script. If you run it manually rather than via cron, you should make sure you have write permissions to the /path/to folder.

Restoring mySQL

To restore mySQL based on a backup file you've created, simply run the command

$ gunzip < /path/to/backupfile.sql.gz | mysql -u root -p