Backup MySQL to Azure Storage in 30 Seconds

Step 1. Disable password prompt for "mysqldump command"

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

1. Run "vi ~/.my.cnf"
2. Add the following lines

[mysqldump]
user=mysqluser
password=secret

3. For Bitnami, you'll need to append the following line in "/opt/bitnami/mysql/my.cnf"

!include ~/.my.cnf

4. Try running to see if the command works.

mysqldump --all-databases > /home/bitnami/backups/db-backup.sql

Step 2(a). Install Azure-CLI

Prerequisites:

Installing npm.

sudo apt-get update
sudo apt-get install nodejs
sudo apt-get install npm

Note: If you facing issue while installing nodejs/npm on Ubuntu 12.04, you can refer to his article for alternative way to install https://rtcamp.com/tutorials/nodejs/node-js-npm-install-ubuntu/, or this http://stackoverflow.com/questions/16302436/install-nodejs-on-ubuntu-12-10#comment32247107_16303380

Install Azure CLI.

npm install azure-cli -g

Potential issues:

nodejs : Depends: rlwrap but it is not installable

Add universe packages to package list:

echo "deb http://archive.ubuntu.com/ubuntu precise universe" >> /etc/apt/sources.list

Source: http://stackoverflow.com/questions/16302436/install-nodejs-on-ubuntu-12-10#comment32247107_16303380

/usr/bin/env: node: No such file or directory

If you facing node not found issue, just run the following command. More info http://stackoverflow.com/a/20058007.

sudo ln -s `which nodejs` /usr/local/bin/node

Step 2(b). Create a backup-db.sh script 

Create the backup script.

vi /home/bitnami/backup-db.sh

Then copy the content from the code snippet below.

	#!/bin/sh
	mkdir /home/bitnami/backups/
	export BACKUP_FILE=/home/bitnami/backups/db-backup.sql.gz
	export DATABASE_SCHEMA_NAME=--all-databases
	export AZURE_CONTAINER=YOUR_VALUE_HERE
	export AZURE_NAME=YOUR_VALUE_HERE
	export AZURE_KEY=YOUR_VALUE_HERE
	export AZURE_BLOB_NAME=db-production-$(date +%Y%m%d%H%M%S).sql.gz
	/opt/bitnami/mysql/bin/mysqldump $DATABASE_SCHEMA_NAME > temp.sql
	gzip temp.sql
	rm -rf $BACKUP_FILE
	mv temp.sql.gz $BACKUP_FILE
	azure storage blob -a $AZURE_NAME -k $AZURE_KEY upload --container $AZURE_CONTAINER -f $BACKUP_FILE -b $AZURE_BLOB_NAME

view raw gistfile1.sh hosted with ❤ by GitHub

Test it

sh /home/bitnami/backup-db.sh

If if works, add to crontab. This will setup cron to backup, at midnight everyday.

(crontab -l ; echo "0 0 * * * /home/bitnami/backup-db.sh") | sort - | uniq - | crontab -

Step 3. Done!

Check your Azure Storage for the backup .gz file.

References:

• http://stackoverflow.com/questions/9293042/mysqldump-without-the-password-prompt
• http://community.bitnami.com/t/support-for-home-my-cnf-in-bitnamis-mysql/8547
• http://fogstack.wordpress.com/2013/05/25/backup-mysql-to-s3-in-30-seconds/
• http://stackoverflow.com/questions/18629632/azure-command-line-tool-for-linux-machine-to-upload-download-data-to-from-azure
• http://azure.microsoft.com/en-us/documentation/articles/xplat-cli/
• http://stackoverflow.com/questions/16302436/install-nodejs-on-ubuntu-12-10#comment32247107_16303380
• http://stackoverflow.com/questions/16302436/install-nodejs-on-ubuntu-12-10#comment32247107_16303380