Backing up your databases is a little bit different than backing up your files. You don't need to backup the entire database directory; rather you just need to backup a dump file. MySQL provides a great way to to this: mysqldump
To begin, you'll want to create a directory to put the backup files into:
sudo mkdir /backup/db
Producing a backup file from MySQL is very easy. You just need to use the mysqldump utility.
(The following command needs to be entered on a single line)
sudo mysqldump -u dbuser -h dbserver -p dbpassword --all-databases > /backup/db/all-db.sql
You can also create backups for specific databases:
sudo mysqldump -u dbuser -h dbserver -p dbpassword --database db01 > /backup/db/db01.sql
sudo mysqldump -u dbuser -h dbserver -p dbpassword --database db02 > /backup/db/db02.sql
Essentially, these commands will place both the SQL commands to re-create your database tables, views, stored procedures, etc. as well as the data contained within the database in the destination file.
You can also separate the CREATE logic from the data with mysqlbackup
(Note that you'll probably want to make a directory for each database):
sudo mysqlbackup -u dbuser -h dbserver -p dbpassword --tab=/backup/db01 db01
sudo mysqlbackup -u dbuser -h dbserver -p dbpassword --tab=/backup/db02 db02
Using --tab will create separate data (tab delimited .txt files) and CREATE logic (.sql files) for each table in your database. If the tables are dumped into separate files, then using --tab will allow rsync or rsnapshot to make incremental backups within your databases .
For example, suppose you have one table corrupt in your database. If you have backed up with --tab, then you can restore just the corrupted table without having to revert the entire database back to the backup point.
If you only have a database or two that you need to back up, you'll probably just want to write a short shell script to do the backup for you. All that you need to do is:
sudo chmod +x /backup/mysql-backups.sh
If you have several databases that change frequently, then you'll probably want to take a look at using AutoMySQLBackup. Essentially, AutoMySQLBackup is a shell script for mysqldump written by a professional who has been kind enough to share.
Not only does it handle daily backups, it also keeps weekly and monthly backups too! To use AutoMySQLBackup:
The remainder of the default options should suit most backup schemes just fine.
sudo chmod +x /backup/automysqlbackup.sh
Once you have your backup script created and saved, you need only to add a cron job to automate it. To automate a cron job for your script:
sudo crontab -e
For example, to run automysqlbackup.sh every night at 11:42 PM, you would enter:
42 23 * * * /backup/automysqlbackup.sh
The fields in crontab (separated by s p a c e s or tabs) are:
[minute] [hour] [day of month] [month] [weekday] [command] [command args]
Should you have questions or need help: firstname.lastname@example.org