Cloning a slave using Mysql Enterprise Backup on a GTID enab_MySQL

MySQL 5.6 introduced a new feature called GTID (Global Transaction IDentifier) support in Replication. For every transaction that is committed on to the server, a GTID of the format :

server_uuid:transaction_idis written into the master's binary log.

This offers the following advantages:

• Very helpful to set up a slave and create a replication setup.

• User need not worry about fetching the master's binlog filename and position in the “CHANGE MASTER TO” command which is used to synchronise the slave with the master.

• Applying GTIDs on slaves ensures consistency – since GTIDs are unique, it cannot be applied more than once on the server.

For a gtid enabled server, the following properties need to be set on both Master and Slave configuration files as shown below in Master.cnf and Slave.cnf

gtid-mode=on

enforce-gtid-consistency=true

log-bin

log-slave-updates=true

Here are the steps to achieve this:

Have a configuration file for master and slave with the below mentioned properties.

So the configuration file for a Master and Slave looks like:

Master.cnf socket=/tmp/mysql3306.sock log-slave-updates=true enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE master-verify-checksum=1 datadir=/export/gtid-test/master-data innodb-file-per-table=on

Slave.cnf [mysqld] port=3307 socket=/tmp/mysql3307.sock binlog-format=MIXED log-bin log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE slave-parallel-workers=2 binlog-checksum=CRC32 slave-sql-verify-checksum=1 server-id=2 binlog-rows-query-log_events=1 datadir=/export/gtid-test/slave-data report-host=localhost

On the Master:

Start the server with this configuration file:

./mysqld –defaults-file=/export/gtid-test/master.cnf &

Create a user and grant 'replication' privileges to that user:

mysql> create user 'replication'@'localhost' identified by 'reppwd';

mysql> grant super,replication slave on *.* to 'replication'@'localhost';

Now, you can use Mysql Enterprise Backup(MEB), which will simplify this task of setting up a new slave from an existing server (which will serve as “master” in this replication environment),

On MEB:

1. Use Mysql Enterprise Backup to take a backup of the master.

./mysqlbackup -uroot --socket=/tmp/mysql3306.sock –backup-dir=/export/gtid-test/slave-data-to-rest/ backup

Now the meta folder under the backup directory contains a new file named backup_gtid_executed.sql.

(In this case, /export/gtid-test/slave-data-to-rest/meta/backup_gtid_executed.sql)

Here are the contents of backup_gtid_executed.sql:

# On a new slave, issue the following command if GTIDs are enabled:

SET @@GLOBAL.GTID_PURGED='6efe48e8-b63c-11e3-a730-0021cc6bab7c:1-12';

# Use the following command if you want to use the GTID handshake protocol:

# CHANGE MASTER TO MASTER_AUTO_POSITION=1;

MEB will simplify the task of cloning a slave with the help of this file which contains the sql to set the GTID_PURGED on the slave (this is the GTID_EXECUTED value of the Master)

On the Slave:

2. Now to setup a new slave server, restore the backed up contents using “copy-back-and-apply-log” operation:

./mysqlbackup --defaults-file=/export/gtid-test/slave.cnf --backup-dir=/export/gtid-test/slave-data-to-rest/ copy-back-and-apply-log

slave.cnf contains the “datadir” where the backup contents will be restored. So no need to pass 'datadir' in the command line.

An image backup is much more efficient, as the image can be streamed onto the slave machine and restored directly. You can check outRestore directly on the remote Machine from backup streamfor more information.

The new server is ready. Start the slave server.

./mysqld –defaults-file=/export/gtid-test/slave.cnf &

Observe that the restored directory is the datadirectory (as mentioned in slave.cnf) of the slave server

This can be synchronised with the master by doing the following simple steps:

mysql> show slave status;

This is an empty set as the slave is not synchronised still.

mysql> show global variables like '%gtid_executed%';

+---------------+------------------------------------------+

| Variable_name | Value |

+---------------+------------------------------------------+

| gtid_executed | 16446ee3-bad6-11e3-8530-0021cc6bab7c:1-2 |

+---------------+------------------------------------------+

1 row in set (0.00 sec)

3. Now to synchronise with Master – to set the gtid_purged, the gtid_executed on the slave need to be empty. Reset Master to do this.

mysql> reset master;

mysql> show global variables like '%gtid_executed%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_executed | |

+---------------+-------+

4 a. Execute the backup_gtid_executed.sql to set the GTID_PURGED on the slave.

mysql> /. /export/gtid-test/slave-data-to-rest/meta/backup_gtid_executed.sql

4 b. Synchronise slave with Master;

mysql> change master to master_host='', master_port= master_auto_position=1;

Setting master_auto_position=1 will automatically replicate the changes.

5. Start the slave with the username and password to connect to Master.

mysql> start slave USER='replication' PASSWORD='reppwd';

mysql> show slave status /G

Look for:

Retrieved_Gtid_Set: 6efe48e8-b63c-11e3-a730-0021cc6bab7c:13

Executed_Gtid_Set: 6efe48e8-b63c-11e3-a730-0021cc6bab7c:1-13

which shows that the slave has all the transactions from 1-13.

This way, the backup taken with Mysql Enterprise Backup can be used to set any number of slaves for the master in a replication environment.

You can also refer :Using MEB with Replication