Slice MySQL Backups

From Metro Studios Knowledgebase

Jump to: navigation, search

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.


Contents

[edit] 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:

FLUSH PRIVILEGES;


[edit] 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.


[edit] 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


[edit] 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

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


[edit] 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.


[edit] 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:

USER=root
Personal tools
Namespaces
Variants
Actions
Wiki Navigation
Knowledgebase
Toolbox