Configuring Replication in Mysql 5.6

After the release of mysql 5.6 with its GTID (global transaction identifier) ​​replication in mysql has ceased to be a sysadmin's nightmare and has become quite a working tool. In the internet there is a certain amount of information on this subject, but all of it is rather fragmented and is not always available for understanding. By this, I decided to make a small instruction-squeeze, more for myself, but maybe someone else will come in handy.


Setup Wizard
I have pretty greenhouse conditions, and the base is empty, we will pour in the dump after setting

my.cnf
')
binlog-format = ROW

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

binlog-checksum = crc32
New feature mysql5.6, such as speeding up the work of binlog

gtid-mode = on
Actually includes the very same GTID mode replication

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

log-slave-updates = true
In the native documentation it is written: indicates to the subordinate server so that it will keep records of updates occurring on the subordinate server in a binary log. This option is off by default. It should be enabled if you want to organize slave servers in a daisy chain.

server id = 1
Unique number for each server

Well, do not 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, so GRANT replication slave ON *. * TO "replication" @ '192.168.1.102' IDENTIFIED BY 'password';

This completes the wizard setup. We pour in the dump and fight)

Slave tuning

In the simplest version, the same config as on the master can be copied to the slave, the only thing is to change the server_id, for example, to 2.

Restart the slave, and start the replication

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

and admire

show slave status \ G