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