Thursday, 18 May 2017

MySQL Enterprise backup : How to setup a slave for a standalone server with zero downtime?

 

 

It’s a well know fact that we could take backups with almost zero downtime with the “mysqldump” tool provided with the community edition of MySQL with a magical option --single-transaction on one  condition that all the tables are InnoDB. Myisam tables locked even with single transaction option.

 

Now let us talk about the MySQL Enterprise backup tool that is only available with the Enterprise edition. We are also offered a 30 day trial. It has been said that MEB(let us just call the MySQL Enterprise backup, MEB)  provides hot online backups with zero downtime.

 

So a hot backup is done online and doesn’t disrupt the normal operations of the database and it even captures the database operations happening during the backup process and applies it during the restoration process. This is applicable only to InnoDB tables.

 

When it comes to other engine tables like Myisam, CSV it has to read lock the tables..

So this is pretty much similar to mysqldump with --single-transaction in terms to locking and disruption to database operations, ain’t it?  But mysqldump produces a sql file whereas MEB produces and backup of the data directory.

 

Wait.., This is not the only difference. There are several other features available in MEB like parallelism, incremental backup, differential backup,backup compression backup encryption etc.., Let’s not get into those now.

Let’s first concentrate on creating a slave from a standalone server with very minimal disruption to database in the most simple way possible using MEB.

 

I have a MySQL community edition installed in port 3306 of my test server. I have downloaded the trial version of MEB from the below link

https://www.mysql.com/trials/

There are several packages available. Let's choose the generic linux version.

How to install MEB ?

 

The downloaded package in zip format

Unzip the file and you would receive below files

 

meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz
meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz.asc
meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz.md5

 The .tar.gz file is the one. 

Arav $] tar  -xzvf meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz
Arav $] cd meb-3.12.3-linux-glibc2.5-x86-64bit
Arav $] cd /bin/
Arav $] ls -ltrh
-rwxr-xr-x. 1 7161 31415 8.0M May  5  2016 mysqlbackup
 

 That’s it we have just installed MEB. So easy isn’t it.

Now steps to take backup. We are interested in taking the backup into a single image file

  

 
Arav $] ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/aravinth/ent_backup/my_ba.mbi --backup-dir=/home/aravinth/ent_backup/ backup-to-image
 

 

--backup-image= : location to place the backup image

--backup-dir= : stores temporary files created during backup. It also has the log file that stores the course of the backup process.

 The location would be /home/aravinth/ent_backup/meta/MEB_2017-05-17.12-22-52_image_backup.log.

This file also holds the binary log position to configure the slave.

 

To get the binary log position run the below command

  

 

Arav $] grep 'INFO: MySQL binlog position:'  /home/aravinth/ent_backup/meta/MEB_2017-05-17.12-22-52_image_backup.log

 

170517 12:40:27 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000436, position 55853225

 

Now that backup is complete and we have the exact binlog position, we can configure the slave.

 

Let's start by installing MySQL in the slave server. Login to the slave server and use the below commands to install MySQL

 

 

Arav $] wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Arav $] tar -xzvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

Arav $] mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql

Arav $] cd /usr/local/mysql

Arav $] mkdir /mysqldata/

Arav $] chown -R mysql.mysql /mysqldata/

Arav $] cp support-files/mysql.server /etc/

Arav $] ./scripts/mysql_installdb --datadir=/mysqldata/ --user=mysql

Arav $]./bin/mysqld_safe --datadir=/mysqldata/ --user=mysql &

 

 
Now shutdown the slave server and empty the data directory to start the restoration process. MEB needs a empty data directory for restoration process. You could also use --force option which replaces the files in data directory  
 

 

Arav $ ] ./bin/mysqladmin -uroot -p shutdown

Arav $] rm -rf /mysqldata/*

 

 
 
Now run the below command in the master server to restore the backup in the empty data directory of the slave server.
 

 

Arav $] ./mysqlbackup --user=root --password --port=3310 --host=192.168.1.2 --datadir=/home/aravinth/ent_test/mysql/data/ --backup-image=/home/aravinth/ent_backup/my_ba.mbi --backup-dir=/tmp/ent_backup/ copy-back-and-apply-log

 

 

--host=192.168.1.2 : the ip of the slave server

 

--datadir=/home/aravinth/ent_test/mysql/data/ : the data directory path of the slave server which is empty. If not empty it would through and error, we could also use force option to override it.

 

--backup-dir=/tmp/ent_backup/ : temporary directory to store temp location to store the temporary files created during the restore process

 

copy-back-and-apply-log : This is the option that tells MEB to restore the files to data directory and apply the changes that happened during the backup process

 

Wait for the restoration to complete. It took around 5 minutes to complete for 30G of data directory. Once the restoration process is complete start the mysql using mysqld_safe or "service start mysql" and configure the slave as shown below.

 

Create the replication user in master. Login to the master server and execute the below commands to create the replication user

Arav $]./bin/mysql -u root -p

mysql $] grant replication slave on *.* to repicator@192.168.1.2 identified by 'replica'

 

 

Use the below commands in slave server to configure the slave using the position that we got from the MEB log file. 

 

Arav $]./bin/mysql -u root -p

mysql $] change master to master_host='192.168.1.1',master_user='replicator',master_password='replica',master_log_file='mysql-bin.000436',master_log_pos=55853225;

mysql $] start slave;

 

 

Now run "Show slave status" check if the slave is running perfect. Slave should be behind by a few seconds depending on the hits in the master server. It should start catching up.

 

Once the slave has caught up with master it can be made availave as for read hits from application.

 

Now let's look at the backup and restoration speed

 

Backup Type

mysqldump

MEB

Data Directory Size

30G

30G

Backup Time

20 mins 40 secs

18 mins 20 secs

Restore Time

35 mins 11 secs

5 mins 41 secs

 

Restore time is very less in MEB since it involves only disk level operations. Yet it is a great improvement that too with no downtime.

Also I believe with compression enabled in MEB backup, the time taken would yet go down. Let’s explore that another day.