Azure Database for MySQL allows you retain your backup up to 35 days. Often, for audit purposes and compliance requirements, you may need to retain the backups longer than 35 days. In this scenario, you can perform logical backups of your database from Azure Database for MySQL to azure storage for long term retention at optimized. You can follow the steps below to automate full backup of your database on a weekly schedule using an Ubuntu server.
sudo mkdir /home/yourusername/mysqlbackups
if [ ! -d "/etc/smbcredentials" ]; then
sudo mkdir /etc/smbcredentials
fi
if [ ! -f "/etc/smbcredentials/mysqlpaasbackup.cred" ]; then
sudo bash -c 'echo "username=mysqlpaasbackup" >> /etc/smbcredentials/mysqlpaasbackup.cred' sudo bash -c 'echo "password=thiswillbedifferent" >> /etc/smbcredentials/mysqlpaasbackup.cred'
fi
sudo chmod 600 /etc/smbcredentials/mysqlpaasbackup.cred
sudo bash -c 'echo "//mysqlpaasbackup.file.core.windows.net/mysqlbackups /home/yourusername/mysqlbackups cifs nofail,vers=3.0,credentials=/etc/smbcredentials/mysqlpaasbackup.cred,dir_mode=0777,file_mode=0777,serverino" >> /etc/fstab'
sudo mount -t cifs //mysqlpaasbackup.file.core.windows.net/mysqlbackups /home/yourusername/mysqlbackups -o vers=3.0,credentials=/etc/smbcredentials/mysqlpaasbackup.cred,dir_mode=0777,file_mode=0777,serverino
#(Make sure you don’t leave the path under /mnt/ since this disk is erased everytime the vm is shutdown and since we create the directory it will no longer be available to mount it)
Verify connectivity to the file share from Azure VM - Connect to the Azure VM created in Step 1. Verify connectivity to the storage account using the following command. (change the storage account) This will not check if you have access to the file share but will make sure that if the storage account is configured correctly then the connection to the file share will succeed.
nc -zvw3 mysqlpaasbackup.file.core.windows.net
You should see the following screen.
Copy the updated script and run it in the terminal.
Install MySQL Client on Azure VM - Install the required packages to do the dump. In this case we will be using mysqldump which is installed as part of mysql client installation.
sudo apt install mysql-client
curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash
az login
#!bin/bash
cd /home/miguel
export DBNAMES="testconn newdb"
export MYPASSWORD="yourpassword"
date=$(date +%s)
year=$(date +%Y)
month=$(date +%m)
day=$(date +%d)
hour=$(date +%H)
path=$year/$month/$day/$hour
echo $date
cd /home/miguel/mysqlbackups/
mkdir -p $path
cd $path
mysqldump --databases $DBNAMES -hyourservername.mysql.database.azure.com -u username@servername -p$MYPASSWORD > back$date.bak
az vm deallocate -g MyResourceGroup -n MyVm
Setup a cron job to schedule backup - Run crontab -e and edit it with your favorite editor. This time we will use nano.
At the end of the file enter the time you want the vm to create the backup. We will setup to run on Sundays, at 2AM. The server is on UTC, you can either change it to your timezone or just make sure it is 2AM in the morning for you. In our case we have change the time in the server to our region. You can check the backup history with cat /home/miguel/backup.log
0 2 * * 0 sh /home/miguel/backup.sh >> /home/miguel/backup.log
Schedule VM Start to save cost - Schedule the VM to start before the selected backup time using Logic Apps, we will do it 30 mins before our backup runs. Click on Add and enter the required information and create it. Shutdown will be executed after backup finish in the script using azure cli to deallocate vm.
When it is created, it will be under Logic Apps Designer, choose Recurrence.
Enter the interval you want this action to be run. We choose 1 every week, at 1 am, 30 min, Sunday, and select the timezone. Click on new step, and search for azure vm. Under azure vm search for start VM. Select your subscription, the resource group, and the vm. Hit on save and close it.
If you have any questions with Logic Apps, please follow the next link.
https://docs.microsoft.com/en-us/azure/logic-apps/
Hope this step by step guide allows you to automate backups of your Azure DB for MySQL server to azure storage for long term retention and archival.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.