MySQL 5.6 master-slave replication Part 1 [Introduction and configuration]

I wrote MySQL 5.1 master-slave configuration a long time ago. Now MySQL has released 5.6.10 and has made many optimizations in the master-slave replication function, especially the introduction of GTID (Global Transaction ID, it is worth writing another article to introduce how to configure master-slave replication for MySQL 5.6.10.

MySQL 5.6 master-slave replication Part 1 [Introduction and configuration]

MySQL 5.6 master-slave replication Part 2 [restore an slave server]

MySQL 5.6 master-slave replication Part 3 [upgrade slave server to master server]

MySQL 5.6 master-slave replication Part 4 [some ignored Operation Details]

MySQL master-slave Replication Event validation MySQL Replication Event Checksum

Use pt-table-checksum to check whether master-slave replication is normal

---------------------------------------- Split line ----------------------------------------

Use mysql: sandbox for testing.
The master server (alias black) is installed on/home/modify/sandboxes/msb_5_6_10/and port 5610 is used.
Install the slave server (alias blue) on/home/modify/sandboxes/msb_5_6_10_a/and use port 5611.
 
Step 1: modify the configuration file
 
Modify the configuration file my. sandbox. cnf of the master server and add the following lines:

Binlog-format = ROW
Log-slave-updates = true
Gtid-mode = on # GTID only
Enforce-gtid-consistency = true # GTID only
Master-info-repository = TABLE
Relay-log-info-repository = TABLE
Sync-master-info = 1
Slave-parallel-workers = 2
Binlog-checksum = CRC32
Master-verify-checksum = 1
Slave-SQL-verify-checksum = 1
Binlog-rows-query-log-events = 1
Server-id = 1
Report-port = 5610
Port = 5610
Log-bin = black-bin.log
Report-host = black
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

Modify the slave server configuration file and add the following lines:

Binlog-format = ROW
Log-slave-updates = true
Gtid-mode = on # GTID only
Enforce-gtid-consistency = true # GTID only
Master-info-repository = TABLE
Relay-log-info-repository = TABLE
Sync-master-info = 1
Slave-parallel-workers = 2
Binlog-checksum = CRC32
Master-verify-checksum = 1
Slave-SQL-verify-checksum = 1
Binlog-rows-query-log-events = 1
Server-id = 2
Report-port = 5611
Port = 5611
Log-bin = blue-bin.log
Report-host = blue
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

You can see that the master and slave configuration files are basically the same, so that when the master server fails, you can upgrade the slave server to the master without modifying the configuration file.
The following describes the meaning of each line:
 
Binlog-format
When row-based replication is adopted, it is said that 5.6 has optimized row-based replication.
 
Gtid-mode and enforce-gtid-consistency
Decide to use gtid.
In traditional mode, the master and slave nodes are based on the master binary logfile and binary logfile postition,
When the master server crashes and a slave server needs to be promoted to the master server, a lot of work needs to be done,
The main reason is that master-slave replication is actually asynchronous,
As a result, data between multiple slave servers is inconsistent. Some synchronization speeds are fast and some are slow. It is very likely that the Read_Master_Log_Pos on each slave server is different.
First, determine which transactions (evens) in the binlog are missing for each slave server based on Read_Master_Log_Pos,
Only when the data on the slave server is completely consistent can any slave server be promoted to the master.
In this process, it is really painful to do it manually. Fortunately, some open-source third-party programs can automatically do this, such as MHA.

1 2 3 Next Page