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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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.

 

  1. 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. 
    MiguelHernandez_0-1602885742267.png
  2. 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
       MiguelHernandez_1-1602885742270.png
    •  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.                        MiguelHernandez_2-1602885742314.png
    • After enabling the VNET rule it should look like the following screenshot MiguelHernandez_3-1602885742289.png
  3. 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. MiguelHernandez_4-1602885742293.png

    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. 
    MiguelHernandez_5-1602885742317.png

  4. 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.

    MiguelHernandez_6-1602885742297.png 
    MiguelHernandez_7-1602885742298.png
  5. 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. 
    MiguelHernandez_8-1602885742300.png

  6. 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 the sections in red below 
    MiguelHernandez_9-1602885742301.png

    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) 

     

     

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

    MiguelHernandez_10-1602885742302.png

    Copy the updated script and run it in the terminal.  
    MiguelHernandez_11-1602885742304.png

  8. 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
  9. Install and login to the azure CLI to shutdown vm after backup runs. 

    curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash  az login​
  10. 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 DBNAME="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 $pathmysqldump --databases $DBNAMES -hyourservername.mysql.database.azure.com -u username@servername -p$MYPASSWORD > back$date.bak az vm deallocate -g MyResourceGroup -n MyVm

     

     

     

  11. 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 * * 6 sh /home/miguel/backup.sh >> /home/miguel/backup.log 

     

     

    MiguelHernandez_12-1602885742305.png

  12. 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.  

    MiguelHernandez_13-1602885742307.pngMiguelHernandez_14-1602885742308.png

    When it is created, it will be under Logic Apps Designer, choose Recurrence.  MiguelHernandez_15-1602885742319.png

    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.  
    MiguelHernandez_16-1602885742310.png

    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. 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.