Configuring Replication in Mysql 5.6

After the release of mysql 5.6 with its GTID (global transaction identifier), replication in mysql ceased to be a system administrator’s nightmare and became a very working tool. There is some information on this subject in the internet, but all of it is rather scattered and not always accessible for understanding. Therefore, I decided to make a little squeeze instruction, more for myself, but maybe someone else will come in handy.


Setting up the wizard
I have pretty greenhouse conditions, and the database is empty, we will inject the dump after setting

my.cnf

binlog-format = ROW

There are three types - STATEMENT, MIXED and ROW
In a nutshell - statement writes to the binlog essentially sql queries. Advantages - the old format, tested, the log is small, you can see the requests. Disadvantages - problems with functions and triggers, requests of the form update user set a = 1 order by rand (), as well as some more, may be incorrectly processed. ROW, if completely simplified, writes modified binary data to the logs. Advantages - all types of requests are perfectly logged. Disadvantages are a huge log. Well and mixed is an intermediate format that statement tries to use when possible, and when not, row. They say that it is buggy on some very complex queries. It was him who I ventured to use

binlog-checksum = crc32 The
new mysql5.6 feature, it seems to speed up the work of the binlog

gtid-mode = on
Actually, it includes the same GTID mode replication

enforce-gtid-consistency = true
Forbids anything that can break transactions.

log-slave-updates = true
In the native documentation it says: tells the slave server to keep records of updates that occur on the slave server in a binary log. By default, this option is disabled. It should be included if you want to organize slave servers in a daisy chain.

server-id = 1
A unique number for each server

and don’t forget to specify what exactly we will replicate -
replicate-do-db = mybase
replicate-do-table = mybase.mytable1
replicate-do-table = mybase.mytable2

After that, you need to create a mysql user with replication rights. For example, GRANT replication slave ON *. * TO "replication" @ '192.168.1.102' IDENTIFIED BY 'password';

This completes the setup of the wizard. We pour the dump into the battle)

Setting up the slave

In the simplest version, you can copy the same config to the slave as on the master, the only thing is that you need to change server_id, for example, 2.

Restart the slave, and start replication

change master to master_host = '192.168.1.1 ", master_auto_position = 1, Master_User = 'replication', master_password = 'password';
start slave;

and enjoy the

show slave status \ G