Automate backups of your Azure Database for MySQL server to azure storage for longer term retention

Microsoft

Oct 16, 2020

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.

Provision a VM in Virtual Network to perform secure backups - Create a D4s_v3 VM with ubuntu image and accelerated networking enabled as shown below. You should create the VM in the same region as your Azure Database for MySQL server and add it to a virtual network for secure communication to the MySQL server for backup operation.
Add you Azure DB for MySQL server in VNet Service endpoint - While the VM is being created, you can use the time to add your Azure Database for MySQL server to VNet service endpoints as shown below:
- Navigate to connection security blade in Azure portal for you Azure Database for MySQL server.
- Click on “+ Adding existing virtual network” under VNet rules. After filling the details in the Create virtual network rule blade, you should have something like the screenshot below. Click on enable and click ok.
- After enabling the VNET rule it should look like the following screenshot.
Create an Azure storage account - Create an Azure storage account with the name of your preference to host the backups of the server for longer term retention. In this case we will use a unique name to identify that the storage account is for our backups. On the Azure Portal navigate to “Storage Accounts” blade and click on “Add”.

Select the same resource group and create a new storage account. On performance we will use standard. Create it with a RA-GRS availability, and choose the same region where the vm is located.
Add Storage account to the same Virtual Network - Once the storage account is created and under the newly created storage account navigate to “Firewalls and virtual networks” blade and choose “selected networks” instead of all networks. Click on add existing virtual network. Choose the virtual network, the subnet and click add. If you want to explore the files from the portal, you will need to add your public ip.
Add Fileshare to store backups - Once the Firewall rule is configured, on the Storage Account navigate to “File Shares” under File Service. Click on Add fileshare. Give it a name (this will be a nfs disk which we will mount in our linux vm) Select Hot as the tier. Choose a name and configure the disk size depending on your estimated backup size up to 5 TB. Give it a name and enter how much Gib you want on the disk. Max to 5TB.

Connect to Fileshare - Once the File Share is create, click on it to access it and click Connect. From the sidebar that will show up on the right handside select Linux and click on “Copy to Clipboard”. Please modify accordingly to your path.

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
```

Install and login to the azure CLI to shutdown vm after backup runs.

curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash 
az login

Setup bash script to take backups - Copy the script from here and change it accordingly to what you need. We will be dumping our dbs into one single file.

#!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/