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 > /path/to/backup-${datum}.sql

Make the file executable

$ sudo chmod 775 /usr/bin/backupmysql

You can now back up your entire mySQL database by running this script.

Restoring mySQL

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

$ mysql -u root -p < /path/to/yourbackup.sql