MySQL-5.6 replication based on GTID and multithreading

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

To use replication in MySQL 5.6, the following options should be defined at least in the service configuration segment [mysqld]:

Binlog-format: binary log format, available as row, statement, and mixed

Log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port and report-host: used to launch GTID and meet other ancillary needs

Master-info-repository and relay-log-info-repository: enable these two items, which can be used to secure binary and slave servers in the event of a crash

Sync-master-info: enable to ensure that no information is lost

Slave-paralles-workers: set the number of SQL threads from the server; 0 means to turn off multithreaded replication

Binlog-checksum, master-verify-checksum, and slave-sql-verify-checksum: enable all validation features related to replication

Binlog-rows-query-log-events: enabled to record event-related information in binary logs, reducing the complexity of troubleshooting

Log-bin: enable binary logging, which is a basic prerequisite for ensuring replication

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 service profile of the master-slave node 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, Configure 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, create replication user mysql > GRANT REPLICATION SLAVE ON *. * TO repluser@172.16.100.7 IDENTIFIED BY 'replpass' Description: 172.16.100.7 is a slave node server If you want to authorize more nodes at one time, you can modify them as needed; 3. Provide the slave node with the initial dataset to lock the master table, back up the data on the master node, and restore it to the slave node; if GTID is not enabled, you need to use the show master status command on master to check the binary log file name and event location so that it can be used later when starting the slave node. 4. Start the replication thread from the node. If the GTID function 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 GTID is not enabled, 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