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

5 Options to Backup Your MySQL and Keep Your Service Online

An old customer was facing a major issue. They just expanded their service from a local shop to a global "follow the sun" service, providing SaaS to worldwide customers.
Their MySQL server were the finest, and their procedures are the same, including backup procedures. Yet, concepts that matched a local shop my not fit a global firm.

Going from Local to Global
Their current procedures include both binary backup and mysqldump of the server. Although both procedures are aimed to keep the service available while performing the backup, the bottom line is that the CPU is highly utilized and the service is poor.

Backup Methods
There are two major backup methods (you can read more about them and others at Lenz Grimmer's presentation):

1. mysqldump: This method extracts the database content into a flat file. This file includes all the DDL and DML SQL statements. Although this method is considered as a "hot backup", it tends to reduce the SLA and leave users unsatisfied. Moreover, if the database is not locked during the process, you may result an inconsistent backup.
2. Binary files backup: This method requires stopping the MySQL server. On the other hand if you copy the binary file to a disk on the same physical server, it could be a very short downtime. In order to keep the files in a safe place, you should later on backup these copied files over the network. Please notice that this method has a another disadvantage: one of the binary files may be corrupted and you may not notice that till getting to the money time.

Where the Backup Can Be Performed?
There are three possible locations:

1. The Master Server: The safest since we know that data was not "disappeared" during replication. The significant drawback is service downtime...
2. The Existing Slave Server: At the risk losing information that was not replicated correctly, we get a service with no downtime. Yet, notice that if you use this server for other tasks you may affect these tasks SLA.
3. Dedicated Slave: This can be a lightweight machine (yes, even a VPS) that can perform backups once or more times a day without causing downtime and without affecting the service at all.

The Bottom Line or How to Keep Their Service Available?
Well, in this case, I suggested the customer choosing the Dedicated Slave option that in a very small price helped him reduce the service downtime and provide great SLA to their global customers.

Keep Performing,
Moshe Kaplan