Backing up MySQL in B3 Server to rsync.net

In a previous post I talked about how to backup to rsync.net using duplicity.

I will now show how to backup your B3 Server Mysql databases using the same method.

Installation

Be sure duplicity is installed:

1su
2apt-get install duplicity

Generate an ssh key to connect to rsync.net

• Generate the ssh key using the defaults (don’t use a password when asked):

1ssh-keygen -t rsa

• Copy your key to rsync.net (if you don’t have no one there):

1scp ~/.ssh/id_rsa.pub user@server.rsync.net:.ssh/authorized_keys

• If you already have a key from another machine or user in rsyn.net, you need to add a new one:

1cat ~/.ssh/id_rsa.pub | ssh user@server.rsync.net 'dd of=.ssh/authorized_keys oflag=append conv=notrunc'

Generate a gpg public and private key to encrypt your backups

• Generate the gpg keys (get down your private key, example: 123AbcH123BB4321):

1gpg --gen-key

• You can check the keys in your system:

1gpg --list-keys

• Look in the output something like this. Look for the public key (in this example your public key is 1AAB123A)

1pub 1239A/**1AAB123A** 2011-10-09 [expires: 2012-10-08]
2uid Your Name (name) <mail@mail.com>
3sub 54321/1234BBCC 2011-10-09 [expires: 2012-10-08]

Backup/restore your databases

I have MyISAM tables, so I will be using the mysqlhotcopy command. You can also use mysqldump.

• Create a user for backups, like backupuser. This user should have the roles ProcessAdmin and BackupAdmin. I used MySQL Workbench to create the user.

• Backup each database you have:

1mysqlhotcopy -u *backupuser* -p *userpassword* *database1* ~/backup --allowold --keepold
2mysqlhotcopy -u *backupuser* -p *userpassword* *database2* ~/backup --allowold --keepold

• Each database will be dumped in a directorie (example: ~/backup/database1) and an old directorie will be kept with the previous dump (example: ~/backup/database1_old).

• Archive every database, except the old ones:

1tar -cvf ~/backup/backup.tar ~/backup/* --exclude='*_old'

• Use duplicity to backup to rsync.net:

1duplicity full --encrypt-key="1AAB123A" ~/backup/backup.tar scp://user@server.rsync.net/backups

• Restore your backup:

1duplicity --encrypt-key="1AAB123A" scp://user@server.rsync.net/backups ~/backup/backup_restore.tar

• Restore database by extracting the backup to the database location. Be sure MySQL isn’t running:

1/etc/init.d/mysql stop
2tar -xvf ~/backup/backup_restore.tar /var/lib/mysql/
3/etc/init.d/mysql start

I’m backing up and restoring all my databases at once. You could create a different archive for database or extract the restore file to a different location and the copy the database you want to restore.

Check out my duplicity post to see what more can you do with duplicity.

Feedback and suggestions appreciated!