User Tools

Site Tools


mysqlbackup

This guide uses AutoMySQLBackup ( http://sourceforge.net/projects/automysqlbackup/ ) to handle backing up all your MySQL databases. AutoMySQLBackup uses mysqldump which will lock the databases while the backup is being performed. This can take from less than a second up to a few minutes depending on the size of the database. During this time writes to the database can't occur (as the db is locked). If you're using the InnoDB engine you can modify the script to use the –single-transaction flag which doesn't have the limitation of locking the database.

This script requires BASH to run.

portmaster --packages-build --delete-build-only shells/bash/

A user needs to be created that has the appropriate access for the databases that will be backed up.

grant SELECT, RELOAD, LOCK TABLES ON *.*  to 'backup'@'localhost' IDENTIFIED by 'your_password';
flush privileges;
exit

Place the script in /root ( http://sourceforge.net/projects/automysqlbackup/ ) and make sure it has the correct permissions to be run.

chmod 750 /root/automysqlbackup-2.5.1-01.sh

Now edit the script and fill in the appropriate information. <box 100% round blue|edit the script automysqlbackup-2.5.1-01.sh>

#!/bin/bash

to

#!/usr/local/bin/bash

</box>

Create a directory to store the backups.

mkdir -p /var/backups/mysql
chmod 750 /var/backups/mysql

Now let's setup a crontab to backup MySQL daily.

setenv EDITOR ee

As the root user.

crontab -e

<box 100% round blue|This will run at 2am every day>

PATH=/usr/local/bin:/etc:/bin:/sbin:/usr/bin:/usr/sbin

# MySQL Backup
1      2       *       *       *      /usr/local/bin/bash /root/automysqlbackup-2.5.1-01.sh

</box>

Test the script out to make sure it's working correctly.

cd /root/automysqlbackup-2.5.1-01.sh
./automysqlbackup-2.5.1-01.sh

Now check /var/backups/mysql to ensure everything was backed up correctly.

mysqlbackup.txt · Last modified: 2014/10/02 14:19 by admin