9 steps to Implement Offline Backup at Azure IaaS: Backup a MySQL to Azure Storage

A good backup is probably something you will thank for when s%$t hits the fan.
If you chose MySQL as your data infrastructure and Microsoft Azure as your cloud infrastructure, you will probably thank this procedure (that I actually turned into a a script).

Chosen Products
Our first task is choosing the backup and automation products. I selected the following two:

1. Percona XtraBackup: a leading backup product by Percona. The product creates an hot backup of the database that is equivalent to disk copy. This method is much faster to backup and recover than mysqldump. It also support increment backup.
2. Azure SDK Tools Xplat: a node.js based SDK that enables command line interface to the various Azure services including Azure Storage.

Backup Implementation Guide

1. Install Percona XtraBackup
   sudo wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.2.3/binary/debian/precise/x86_64/percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   sudo dpkg -i percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   sudo apt-get update
   sudo apt-get install -f
   sudo dpkg -i percona-xtrabackup_2.2.3-4982-1.precise_amd64.deb
   
   The CentOS
   sudo rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
   sudo yum -y install percona-xtrabackup.x86_64
2. Install Azure SDK Tools Xplat
   sudo apt-get updatesudo apt-get -y install nodejs python-software-propertiessudo add-apt-repository ppa:chris-lea/node.jssudo wget https://npmjs.org/install.sh --no-check-certificate | sudo shsudo apt-get install npmsudo npm config set registry http://registry.npmjs.org/sudo npm install -g azure-clisudo ln -s /usr/bin/nodejs /usr/bin/node
3. Install a backup procedure
1. Get a publish settings file from Azure (can be done from the console).
2. Get account name and the matching base64 key from the Azure console.
3. Import the publish setting filesudo azure account import /opt/mysqlbackup/mysqlbackup.publishsettings
4. Create a Storage Container
   sudo azure storage container create --container container_name -a account_name -k base64_account_key
4. Run the backup
1. The xtrabackup way
1. Run a full backup and prepare it twice to make it ready for recovery
   sudo xtrabackup --backup
   sudo xtrabackup --prepare
   sudo xtrabackup --prepare
2. Add the frm files and mysql database to your backup
   sudo chmod -R +r /var/lib/mysql/
   sudo cp -R /var/lib/mysql/myql/* /mnt/backup/mysql/
   sudo cp -R /var/lib/mysql/yourdb/*.frm /mnt/backup/yourdb/
2. The innobackupex way:
1. Run full backup and prepare (adjust the memory usage for your case):
   sudo rm -rf /mnt/datadrive/mysqlbackup/*
   sudo innobackupex --user=DBUSER --password=DBUSERPASS /mnt/datadrive/mysqlbackup/
   sudo innobackupex --apply-log /mnt/datadrive/mysqlbackup/ --use-memory=500M
5. Tar the files into a unique daily name_now=$(date +"%Y_%m_%d")
   _file="/mnt/backup/mysqlbackup$_now.tar.gz"
   tar cvzf "$_file" /mnt/datadrive/mysqlbackup/
6. Copy the folder to Azure Storage using
7. azure storage blob upload -f "$_file" -q --container container_name -a account_name -k base64_account_key
8. Create a cron that will run it daily:
   > sudo cron -e
   * 0 * * * /opt/mysqlbackup/daily.backup.sh >/dev/null 2>&1

Recovery Guide

1. Bring back the files from the Azure storage to /mnt/backup/
   sudo cd /mnt/backup/ sudo azure storage blob download --container container_name -a account_name -k base64_account_key -b $file_name
2. Uncompress the files
   sudo tar xvfz $file_name
3. Copy the files to your data folder (/var/lib/mysql) after shutting down the MySQL
1. The xtrabackup waysudo service mysql stopsudo rsync -avrP /mnt/backup/ /var/lib/mysql/
2. The innobackupex way:
   sudo service mysql stopsudo rm -rf /var/lib/mysql/*sudo innobackupex --copy-back /mnt/datadrive/mysqlbackup/sudo chown -R mysql:mysql /var/lib/mysql
4. Verify the folder permissions
   sudo chown -R mysql:mysql /var/lib/mysql
5. Restart the MySQL and verify everything is working.
   sudo service mysql start

Bottom Line 

It may be tough. It may be resource and time consuming. However, you must have good recovery process to keep your a$$...

Keep Performing,

Moshe Kaplan

Azure Production Issues, Not a nice thing to share with your friends...

Last night I got a call from a close friend.
"Our production SQL Server VM at Azure is down, and we cannot provide service to clients".
A short analysis issued that we are in a deep trouble:

1. The server status was: Stopped (Failed to start).
2. Repeated tries to start the server resulted in Starting... and back to the fail message.
3. Changing instance configuration as proposed in various forums and blogs was resulted in the same fail message.
4. Microsoft claims that everything is Okay with its data centers.
5. Checking the Azure storage container found out that the specific VHD disk was not updated since the server failure.

Since getting back to cold backup would cause losing too much data, we had to restore somehow the failed server.

The chances were against us. Yet, lucky us, we could do that by restoring the database files from the VHD (VM disk) file that was available at Azure storage. 

How to Recover from the Stopped (Failed to start) VM Machine?

1. Start a new instance in the same availability set (that way you can continue using the same DNS name, instead of also deploying a new version of the app servers).
2. Attach a new large disk to the instance (the failed server disk was 127GB, make sure the allocated disk is larger).
3. Start the new machine.
4. Format the disk as a new drive.
5. Get to your Azure account and download the VHD file from Azure storage. Make sure you download it to the right disk. We found out that the download process takes several hours even when the blob storage is in the same data center as the VM.
6. Mount the VHD file you downloaded as a new disk.
7. Extract the database and log files from the new disk and attach them to the new SQL Server instance.

Other recommendations:

1. Keep your backup files updated and in a safe place.
2. Keep your database data and log files out of the system disk, so you could easily attach them to other servers.

Bottom Line

When the going gets tough, the tough get going

Keep Performing,

Moshe Kaplan