Sunday, November 6, 2011

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:
$ su
# apt-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):
# ssh-keygen -t rsa
  • Copy your key to rsync.net (if you don't have no one there):
# scp ~/.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:
# cat ~/.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):
# gpg --gen-key
  • You can check the keys in your system:
# gpg --list-keys
  • Look in the output something like this. Look for the public key (in this example your public key is 1AAB123A)
pub 1239A/1AAB123A 2011-10-09 [expires: 2012-10-08]
uid Your Name (name) <mail@mail.com>
sub 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:
# mysqlhotcopy -u backupuser -p userpassword database1 ~/backup --allowold --keepold
# mysqlhotcopy -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:
# tar -cvf ~/backup/backup.tar ~/backup/* --exclude='*_old'
  • Use duplicity to backup to rsync.net:
# duplicity full --encrypt-key="1AAB123A" ~/backup/backup.tar scp://user@server.rsync.net/backups
  • Restore your backup:
# duplicity --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:
# /etc/init.d/mysql stop
# tar -xvf ~/backup/backup_restore.tar /var/lib/mysql/
# /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!

No comments:

Post a Comment