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

mySQL Replication

To create a replicated or mirrored mySQL database environment on another server, first create a user in both environments. In this case we'll use a user called replicaslave.

mysql> CREATE USER 'replicaslave'@'%';
mysql> grant replication slave on *.* to 'replicaslave';

Add the following lines to the /etc/mysql/my.cnf files of each server.

log-bin=mysql-bin
binlog_format=mixed
port=3306
server-id	= <Your number here>

The master server should have server-id 1, all slaves should have a unique server-id.

Restart the mysql service on each machine. To test the changes, log into each instance, and issue the following command:

mysql> show global variables like 'server_id';

On the master, it should show that the server_id is 1, and on each slave, make sure that the server id is not 1 or 0, and that no two servers have the same id.

If all is well, on the Master, run the following commands, and note the value of the File and Position returned.

mysql> Flush tables with read lock;
mysql> show master status;

On each slave run the following commands:

mysql> CHANGE MASTER TO MASTER_HOST=' master_host_name ', MASTER_USER='replicaslave', MASTER_PASSWORD=' memorable password ', MASTER_LOG_FILE=' recorded_log_file_name ', MASTER_LOG_POS=recorded_log_position;

Note the lack of quotes in the final command. If all is well, start the slave instance.

mysql> start slave;