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
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 $] 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 email@example.com 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
Data Directory Size
20 mins 40 secs
18 mins 20 secs
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.