MySQL Replication Template
This template deploys a MySQL replication environment with one master and one slave servers. It has the following capabilities:
- Supports CentOS 6 and MySQL 5.6
- Supports GTID based replication
- Deploys 2 VMs in an Azure VNet, each has 2 data disks striped into Raid0
- Deploys a load balancer in front of the 2 VMs, so that the VMs are not directly exposed to the internet. MySQL and SSH ports are exposed through the load balancer using Network Security Group rules
- Configures a http based health probe for each MySQL instance that can be used to monitor MySQL health
- Installs LIS4 driver on each VM. Note that the VMs are not automatically rebooted, so LIS4 will not take effect until the next time a VM reboots
How to Deploy
You can deploy the template with Azure Portal, or PowerShell, or Azure cross platform command line tools. The example here uses PowerShell to deploy.
- Open Azure Powershell console, and log in by running Login-AzureRmAccount command.
- Next, create a resource group:
> New-AzureRMResourceGroup -Name "mysqlrg"-Location "East US"
- Create a deployment:
> New-AzureRMResourceGroupDeployment -ResourceGroupName mysqlrg -TemplateFile .\azuredeploy.json -TemplateParameterFile .\azuredeploy.parameters.json
- Take a look at AzureDeploy.json to see if you need to make any customization that's not exposed through the template parameters, for example, disk configurations. If you do, download the template and make modifications locally.
- Take a look at AzureMysql.sh. This script configures network and disks within the VM, as well as installs and configures MySQL. You can customize it to meet your own requirements.
- Take a look at my.cnf.template. This is the template for /etc/my.cnf. Empty parameters will be automatically filled during deployment. You can also add your own parameters.
- If you make changes to AzureMysql.sh or my.cnf.template, you need to put the modified file in your own GitHub repo or Azure storage account, and modify the parameters "customScriptFilePath" and "mysqlConfigFilePath" to point to your file location.
- Once you are done with customization, deploy using the same command as default deployment.
How to Access MySQL
- Access MySQL using the public DNS name. By default, the master server can be accessed at port 3306, and the slave server 3307. By default, a user "admin" is created with all privileges to access from remote hosts. For example, access the master with the following command:
> mysql -h mysqldns.eastus.cloudapp.azure.com -u admin -p
- You can access the VMs through ssh. By default, public ssh ports are 64001 and 64002 for the 2 VMs. Within the VM you can check MySQL health probe by running, for example, the following command, and it should return 200 to indicate MySQL is healthy.
> wget http://10.0.1.4:9200 > wget http://10.0.1.5:9200
- Ensure replication topology is properly configured, assuming master is 10.0.1.4:
> mysqlrplshow --master=admin:email@example.com --discover-slaves-login=admin:secret
How to Monitor MySQL Health
- MySQL health can be checked by issuing HTTP query to the MySQL probes and verify that the query returns 200 status code. Replace the following command with your own dns name and location.
> wget http://mysqldns.eastus.cloudapp.azure.com:9200 > wget http://mysqldns.eastus.cloudapp.azure.com:9201
How to Failover
High availability and failover are no different from other GTID based MySQL replication. What's specific to Azure is that in order for the applications to access the current master server without changing their configurations, the NAT rules of the load balancer must be updated in the case of failover:
- Remove the NAT rule for the old master from the load balancer so that applications can't access the failed master, assuming master has $mysqlrg-nic0. For full powershell script, please see switchMySQLNatRule.ps1.
> $nic0=Get-AzureNetworkInterface -Name mysqldns-nic0 -ResourceGroupName mysqlrg > $nic1=Get-AzureNetworkInterface -Name mysqldns-nic1 -ResourceGroupName mysqlrg ... # $i is the index of the target nat rule ... > $rule0=$nic0.IpConfigurations.LoadBalancerInboundNatRules[$i] > $nic0.IpConfigurations.LoadBalancerInboundNatRules.removeRange($i,1) > Set-AzureNetworkInterface -NetworkInterface $nic0
- Fail over MySQL from the old master to the new master. On the slave, run the following, assuming slave 10.0.1.5 is to become the new master:
mysql> stop slave; mysql> change master to master_host='10.0.1.5', master_user='admin', master_password='secret', master_auto_position=1;
- Switch the old master's NAT rule with the new master
... # $j is the index of the target nat rule ... > $rule1=$nic1.IpConfigurations.LoadBalancerInboundNatRules[$j] > $nic1.IpConfigurations.LoadBalancerInboundNatRules.removeRange($j,1) > $nic1.IpConfigurations.LoadBalancerInboundNatRules.add($rule0) > Set-AzureNetworkInterface -NetworkInterface $nic1 > $nic0.IpConfigurations.LoadBalancerInboundNatRules.add($rule1) > Set-AzureNetworkInterface -NetworkInterface $nic0
- Add the old master back to replication as a slave, on the old master, run the following, assuming the new master is 10.0.1.5:
mysql> stop slave; mysql> change master to master_host='10.0.1.5', master_user='admin', master_password='secret', master_auto_position=1; mysql> start slave;
- Verify replication is properly restored by running the following command and make sure there is no error, assuming the new master is 10.0.1.5:
> mysqlrplshow --master=admin:firstname.lastname@example.org --discover-slaves-login=admin:secret
on the master:
mysql> show master status\G;
on the slave:
mysql> show slave status\G;
How to backup databases to Azure blob storage
- There are several ways to take mysql backups as shown at Mysql Backup and Recovery. The example below shows mysql dump from the slave.
# Create backups directory if not already created (modify folder as required) >mkdir /home/admin/backups/ # Install npm and azure-cli # For latest instructions for installing azure cli see https://azure.microsoft.com/en-in/documentation/articles/xplat-cli-install/. (sample commands below) > sudo yum update -y > sudo yum upgrade -y > sudo yum install epel-release -y > sudo yum install nodejs -y > sudo yum install npm -y > sudo npm install -g azure-cli # Login to azure account using azure cli > azure login # Environment settings for your system > export AZURE_STORAGE_ACCOUNT=mysqlbkp > export AZURE_STORAGE_ACCESS_KEY=<your access key> > export image_to_upload=/home/admin/backups/db_bkp.sql.gz > export container_name=<your azure container name> > export blob_name=db-backup-$(date +%m-%d-%Y-%H%M%S).sql.gz > export destination_folder=<your azure destination folder name> > cd /home/admin/backups/ # Stop replication to slave > mysqladmin stop-slave -u admin -p # Take mysql backup for all databases > mysqldump --all-databases > alldbs.sql -u admin -p # Compress the mysql backup > gzip alldbs.sql # Start slave replication > mysqladmin start-slave -u admin -p # Remove previous backup file > rm -Rf $image_to_upload # Move compressed mysql backup to the to be uploaded file > mv alldbs.sql.gz $image_to_upload # Move the backup to Azure Blob storage > azure storage blob upload $image_to_upload $container_name $blob_name