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

By -

 

 

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.

Comments

Post a Comment

Popular posts from this blog

How to install MyRocks into mariaDB as a plugin?

By -
Whats is MyRocks? MyRocks is a storage engine that integrates RocksDB into MySQL with most features of InnoDB and the storage efficiency of RocksDB. It has been said that it provides 3-4x compression when compared to uncompressed InnoDB tables and 10x less write amplification when compared to InnoDB What is RocksDB? RocksDB is a embedded key-value data storage(a fork of LevelDB) which stores data in the disk unlike redis(redis stores data in memory). It uses Log-structured merge-tree algorithm that reduces write amplification. https://en.wikipedia.org/wiki/Log-structured_merge-tree Now let's come to the post topic. We are going to compile MyRocks engine as a plugin into mariaDB. Requirements 1) gcc 4.8.1 and above MyRocks document says it could be installed in centos 6.8 too. But it's a bit tedious since a MyRocks requires a gcc 4.8 which is not available through default repositories in Centos 6.8 . So let's choose Centos 7 which has gcc 4.8.5 2) gfl
Read more

Drop Table slow

By -
A few days back we were given a MySQL database hosted in EC2 with around 5TB data to move some of the tables to a new server.  The intention was to share the tables between 2 EC2 instances. Since AWS had the option to take online snapshots, the plan was to take a snapshot, create a new machine with that snapshot and drop the unwanted tables. So everything went as planned until creating a new machine with the snapshot.  The real challenge was dropping the unwanted tables.  It took around 4 minutes to Drop a table whose size is 20GB.  It took 20 minutes to drop a 100GB table. The time kept on increasing for larger tables. MySQL even went to “defunct” when we killed the drop query and at times crashed. To track down this issue we executed drop table in one session and checked the processlist from another session that gave the below output. mysql> show processlist \G *************************** 1. row ***************************      Id: 2352    User: dbuser    Hos
Read more

Why is my MySQL server using a single core while my processor has multiple cores ?

By -
Image
I am writting my first ever blog post after four and a half years since I started my career as a MySQL DBA. I used to read lots of blog posts  especially from mysqlperformceblog , but  never really had the interest of writting one.  But this interesting question from a client made me write this blog post. “Why is my MySQL consuming only “core 0”, while I have 8 cores and there is hardly any usage in the other cores?” This is a question put forward by one of our clients. Started my investigation by looking into the cpu usage stats. It was true that only “core 0” was getting used and the other cores were rarely getting used.  Usage of “core 0” was like below Where as there is hardly any usage in the other cores as shown below But if we look at the overall cpu usage it rarely crossed 30%. But the client questioned “why can’t MySQL share the usage in ‘core 0’ with other cores?” In my first look found that the machine is a dedicated for MySQL with multi-thread
Read more