Slice MySQL Backups
From Metro Studios Knowledgebase
Instructions for generating the database backup on a slice. This will dump all of the database information to a bzipped file which will then be included in the rsync round-robin backup.
 Create Backup MySQL User Account
Backups will be automated, and therefore need to be run as a user with limited privileges. As the root MySQL user, create this limited user account:
GRANT RELOAD,SELECT,LOCK TABLES ON *.* TO [email protected] IDENTIFIED BY 'password';
Replace password with a reasonably secure password, and store this account information in the KeePass database.
Flush database privileges so the backup user will have access:
 Create MySQL Option File
To have the backups be automated, the backup account username and password will need to be stored in an option file. For security purposes, store this file in the /root directory with 600 permissions:
sudo touch /root/.mybup.cnf sudo chmod 600 /root/.mybup.cnf sudo nano /root/.mybup.cnf
You are now editing the option file in nano. Use the following for the contents of this file:
[client] user="bup_user" password="password"
Change the value in "password" to the password you set for the backup user account.
 Create Directory to Store Dumps
The entire contents of the /var/www/ directory will be included in the rsync round-robin backup, so we will create a new sub-directory to store the database dumps. Create that directory as the root user and with limited permissions for security reasons:
sudo mkdir /var/www/db sudo chmod 700 /var/www/db
 Grab the Database Backup Script
A custom PHP script has been created to handle generating and managing the database dumps. That script is setup in a Git repository on the dev1 server, which you can checkout using this command:
git clone ssh://USERNAME@dev1.metro-studios.com:11200/var/www/git/misc/server-tools.git
- Replace USERNAME with your account username on the dev1 slice
After this command completes, you will have a directory named server-tools containing a file named db-backup.php (among others). Move this file to the /root directory and set it's permissions/ownership:
sudo mv server-tools/db-backup.php /root sudo chmod 600 /root/db-backup.php sudo chown root:root /root/db-backup.php
 Configure the Database Backup Script
In most cases you won't need to make any configuration changes to the backup script, but you can if needed. All of the configuration options are at the top of the file and are documented inline with PHP comments.
 Setup Cron Job
The final step is to setup a Cron Job to run the database backup daily. The database backup should be run at 1am central time, and will be followed at 2am central time by the rsync backup. The cron job must be run as the root user, so you will need to add the command to the root user's crontab:
sudo crontab -e
This will open an editor where you can configure the root user's crontab. Use the following crontab entry:
0 1 * * * /usr/bin/php /root/db-backup.php
Also at the top of the crontab file set the USER environment variable: