MySQL Backup script on Linux
This tutorial will show you how to setup and configure MySQL backup on your Linux server. The tool is free and easy to configure. Most people don’t realise that they cannot restore using the MySQL data directory (/var/lib/mysql/). We will be doing this setup over SSH using Putty client.
- Download the MySQL Backup script from http://sourceforge.net/projects/automysqlbackup/ to a folder of your choice. Typically in a temp directory.
- Then extract the archive so we can execute the installation script: tar xvzf automysqlbackup-v3.0_rc6.tar.gz
- Then type: ./install.sh to execute the script.
- Edit the configuration file so it has the credentials and privileges to access MySQL databases. By default the config file is in /etc/automysqlbackup/automysqlbackup.conf and can be edited using VI, VIM or NANO editors. You need to add the username and password. Typically most will use their MySQL root username and password but feel free to create another user which has privileges over all MySQL databases.
- As we want this backup to take place regularly, we should add it to cron job. So create a new file called /etc/cron.d/automysql and enter the following lines to run the backup job at 23:00 every night: 0 23 * * * root /usr/local/bin/automysqlbackup
- Ensure that the above cron file is owned by root and has a chmod value of 644. You could change the ownership by chown root:root automysql and permission by typing: chmod 644 automysql .
- To kick off the backup just execute /usr/local/bin/automysqlbackup in the command line.
By default the backups are stored in /backups folder and then it has sub folders called daily, weekly and monthly. You can adjust the location,rotation period and frequency within the configuration file.
Let us know if you get stuck with any of the step above or simply have a better way of dealing with this.