MySQL GTID master-slave replication

I. Introduction of Gtid
One of the new features of MySQL 5.6 is the addition of a global transaction ID (GTID) to enhance the database's primary and standby consistency, fault recovery, and fault tolerance. It is a combination of the server ID and the transaction ID. This global transaction ID is unique not only on the original server, but also on all MySQL servers that have a master-slave relationship. It is this feature that makes MySQL's master-slave replication easier and database consistency more reliable. A gtid is executed only once on a server, avoiding duplication of data or the fact that the master is never consistent.

On the traditional slave side, the Binlog is not open, but the binlog in the slave end of the gtid must be turned on to record the Gtid (mandatory) performed. Gtid is used instead of the classic copy method and does not use Binlog+pos to turn on replication. Instead, use Master_auto_postion=1 to automatically match Gtid breakpoints for replication.

MySQL's master-slave replication is a very classic application, but there will always be data consistency between master and slave (consistency) problem, the general situation from the library behind the main library for a few hours, and in the traditional master many from (mysql5.6) model before the master down , we do not just need to be a Slave commission master, but also to the other slave synchronization destination from the previous master to the current master, and bin-log the serial number and offset to see, which is very inconvenient and time-consuming, But this problem was solved after mysql5.6 introduced Gtid.

Second, gtid parameter configuration

1. Master Master:
[Mysqld]
#GTID:
Server_id=1 #服务器id
Gtid_mode=on #开启gtid模式
Enforce_gtid_consistency=on #强制gtid一致性, not supported for specific CREATE table after opening

#binlog
Log_bin=master-binlog
Log-slave-updates=1
Binlog_format=row #强烈建议, other formats may cause inconsistent data

#relay Log
Skip_slave_start=1

2. From slave:
[Mysqld]
#GTID:
Gtid_mode=on
Enforce_gtid_consistency=on
server_id=2

#binlog
Log-bin=slave-binlog
Log-slave-updates=1
Binlog_format=row #强烈建议, other formats may cause inconsistent data

#relay Log
Skip_slave_start=1

In summary: The configuration is not too big difference, just server_id inconsistent.

Third, configure the master-slave

Master

Create and authorize salve remote Access User: GRANT REPLICATION SLAVE on * * to [email protected] identified by ' 123456 ';
Flush privileges;

View authorized Slave User table: Show grants for [email protected];

View binlog information: Show master status;

Slave (the bin and Pos here are replaced according to the actual situation)
Change MASTER to master_host= ' 192.168.50.116 ', master_user= ' root ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000009 ', master_log_pos=194, master_auto_position=0;

Start slave;
show slave status\g;

Example of an error:

2017-10-12t09:59:27.660287z 4 [ERROR] Slave I/O for Channel ': Fatal error:the Slave I/O thread stops because master and Slave has equal MySQL server UUIDs; These uuids must is different for replication to work. error_code:1593
Workaround:
If it is a copy of the data directory may appear this error, the data directory in the Auto.cnf file to change the UUID to be different from master.

2017-10-12t10:09:15.365312z 4 [ERROR] Slave I/O for Channel ": Got fatal ERROR 1236 from master while reading data from BI Nary log: ' Could not "find first log file name in binary log index file ', error_code:1236
Workaround:
is because the master binary file cannot be found, view master's Binlog binaries, POS location is the same as slave, turn off salve and perform change MASTER to master_log_file= in slave mysqld-bin.000011 ', master_log_pos=106; change, then turn on start slave; and see show Slave status\g

The master never synchronizes, but the slave display double Yes, the log no error problem.

This solution is the next worst-case, I do not know what is the reason for the synchronization, if there is known T friend, please comment to inform.

Reset master/slave: Reset Master;reset slave #清除binlog日志信息

Back up the master's full library to Slave:
Mysqldump-h 192.168.50.116-uroot-p123456--all-databases--skip-lock-tables--set-gtid-purged=off > Qk.sql

Then import: mysql-uroot-p123456 <aaa.sql

Prompt when importing: ERROR 1840 (HY000) at line: @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty. The Reset master is executed locally.

After the import is successful, turn on slave sync again.
If the slave needs to be re-mounted on the master side, the command change is ignored when the master_auto_position is executed.

Attention:

After the Gtid master-slave copy is turned on, it is not possible to operate from above, otherwise there will be a hint of slave_sql_running.
When you have deleted from the library or added data, the remedy is to close slave and then create the deleted data back or delete the added data in order to be consistent with master and then turn on slave.

Excellent article sharing:

Http://www.cnblogs.com/luckcs/articles/6295992.html

http://blog.csdn.net/leshami/article/details/50630691

MySQL GTID master-slave replication