MySQL-5.6 based on Gtid and multi-threaded replication

The Gtid (Global Transaction IDs) introduced by MySQL 5.6 makes the configuration, monitoring, and management of its replication capabilities easier to implement and more robust.

To use the replication feature in MySQL 5.6, the following options should be defined in the service configuration segment [Mysqld]:

binlog-format: Binary log format, there are several types of row, statement and mixed;

log-slave-updates, Gtid-mode, enforce-gtid-consistency, Report-port and report-host : Used to start Gtid and meet other ancillary requirements;

master-info-repository and relay-log-info-repository: Enable these two items, which can be used to ensure the security of the binary and slave servers in the crash;

sync-master-info: Enable it to ensure that no information is lost;

slave-paralles-workers: Set the number of SQL threads from the server; 0 to turn off the multithreading replication function;

Binlog-checksum, Master-verify-checksum, and slave-sql-verify-checksum: Enable all verification functions related to replication;

binlog-rows-query-log-events: Enables the use of information related to logging events in binary logging to reduce the complexity of troubleshooting;

log-bin: Enable the binary log, which is the basic premise to ensure the replication function;

Server-id: The ID number of all servers in the same replication topology must be unique;

Simple master-Slave mode configuration steps:

1, configure the master-slave node service profile 1.1, configure the Master node: [Mysqld]binlog-format=rowlog-bin=master-binlog-slave-updates=truegtid-mode=on  enforce-gtid-consistency=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info =1slave-parallel-workers=2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum= 1binlog-rows-query-log_events=1server-id=1report-port=3306port=3306datadir=/mydata/datasocket=/tmp/ mysql.sockreport-host=master.magedu.com1.2, configuring the Slave node: [mysqld]binlog-format=rowlog-slave-updates=truegtid-mode= on enforce-gtid-consistency=truemaster-info-repository=tablerelay-log-info-repository= tablesync-master-info=1slave-parallel-workers=2binlog-checksum=crc32master-verify-checksum= 1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=11report-port=3306port=3306log-bin= Mysql-bin.logdatadir=/mydata/datasocket=/tmp/mysql.sockreport-host=slave.magedu.com2, creating a replication user mysql>  Grant replication slave on *.* to [email protected] IDENTIFIED BY  ' Replpass '; Description: 172.16.100.7 is from the node server; If you want to authorize more nodes at once, you can modify it as needed; 3, to provide the initial data set lock Main table for the standby node. Back up the data on the primary node, restore it to the slave node, or, if Gtid is not enabled, use the Show master status command on master to view the binary log file name and the event location when you back up, so that you can use it later when you start the slave node. 4. Start the replication thread from the node if the Gtid feature is enabled, use the following command: mysql> change master to master_host= ' master.magedu.com ' ,  master_user= ' Repluser ',  master_password= ' Replpass ', master_auto_position=1; did not enable Gtid, You need to use the following command: slave> change master to master_host= ' 172.16.100.6 ',-> master_user= ' Repluser ',-> master_password= ' Replpass ',-> master_log_file= ' master-bin.000003 ',->  master_log_pos=1174;

MySQL-5.6 based on Gtid and multi-threaded replication