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.
Be sure duplicity is installed:
# 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 firstname.lastname@example.org:.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 email@example.com '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) <firstname.lastname@example.org>
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://email@example.com/backups
- Restore your backup:
# duplicity --encrypt-key="1AAB123A" scp://firstname.lastname@example.org/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 stopI'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.
# tar -xvf ~/backup/backup_restore.tar /var/lib/mysql/
# /etc/init.d/mysql start
Check out my duplicity post to see what more can you do with duplicity.
Feedback and suggestions appreciated!