User Tools

Site Tools


mysqlbackup

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysqlbackup [2014/10/02 14:19] (current)
admin created
Line 1: Line 1:
 +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.
 +<​code>​
 +portmaster --packages-build --delete-build-only shells/​bash/​
 +</​code>​
 +
 +A user needs to be created that has the appropriate access for the databases that will be backed up.
 +<​code>​
 +grant SELECT, RELOAD, LOCK TABLES ON *.*  to '​backup'​@'​localhost'​ IDENTIFIED by '​your_password';​
 +flush privileges;
 +exit
 +</​code>​
 +
 +Place the script in /root ( http://​sourceforge.net/​projects/​automysqlbackup/​ ) and make sure it has the correct permissions to be run.
 +<​code>​
 +chmod 750 /​root/​automysqlbackup-2.5.1-01.sh
 +</​code>​
 +
 +Now edit the script and fill in the appropriate information.
 +<box 100% round blue|edit the script automysqlbackup-2.5.1-01.sh>​
 +<​code>​
 +#!/bin/bash
 +</​code>​
 +to
 +<​code>​
 +#​!/​usr/​local/​bin/​bash
 +</​code>​
 +</​box>​
 +
 +Create a directory to store the backups.
 +<​code>​
 +mkdir -p /​var/​backups/​mysql
 +chmod 750 /​var/​backups/​mysql
 +</​code>​
 +
 +Now let's setup a crontab to backup MySQL daily.
 +<​code>​
 +setenv EDITOR ee
 +</​code>​
 +
 +As the root user.
 +<​code>​
 +crontab -e
 +</​code>​
 +
 +<box 100% round blue|This will run at 2am every day>
 +<​code>​
 +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
 +</​code>​
 +</​box>​
 +
 +Test the script out to make sure it's working correctly. ​
 +<​code>​
 +cd /​root/​automysqlbackup-2.5.1-01.sh
 +./​automysqlbackup-2.5.1-01.sh
 +</​code>​
 +
 +Now check /​var/​backups/​mysql to ensure everything was backed up correctly.
mysqlbackup.txt · Last modified: 2014/10/02 14:19 by admin