MySQL Replication principle

MySQL provides replication functionality starting from 3.23, which means that the DDL and DML operations of the main library are routed through the Binlog file to be executed from the library, keeping the main library and synchronizing the data from the library. MySQL supports one master library for multiple copies from the library, as well as other libraries from the library, enabling cascading replication capabilities. The MySQL replication function is equivalent to the logical DG functionality of the Oracle database.

The MySQL replication principle is as follows:

1) When the MySQL main library transaction commits, the data changes will be recorded as event in the Binlog file, and the Sync_binlog parameter of the MySQL Master library controls Binlog log flushing to disk.

2) The main library pushes the event in the Binlog to the trunk log from the library, relay log, and then redo the data change operation from the library according to the trunk log, which is achieved through logical replication to synchronize with the main library.

MySQL completes the replication function with 3 threads: where the Binlog dump thread runs on the main library, and the IO thread and SQL thread run from the library. When starting replication from the library (start slave), first create the IO thread connection main Library, the main library then create the Binlog dump thread to read the event sent to the IO thread, the IO thread gets to the event after the update to the log from the library trunk logs relay, The SQL thread from the library then operates from the library based on the relay log content. This completes the MySQL replication function. MySQL Replication structure

The main library can see the Binlog dump process through the show Processlist command, as follows:

Mysql> show Processlist \g;

1. Row ***************************

id:125

User:root

Host:localhost

Db:test

Command:query

time:0

State:init

Info:show processlist

2. Row ***************************

id:127

User:rep1

host:192.168.80.136:44889

Db:null

Command:binlog Dump

time:16991

State:master have sent all binlog to slave; Waiting for Binlog to be updated

Info:null

2 rows in Set (0.00 sec)

ERROR:

No query specified

Mysql>

The IO thread and SQL thread can be seen from the library through the show Processlist command, as follows:

Mysql> show Processlist \g;

1. Row ***************************

Id:4

User:root

Host:localhost

Db:test

Command:query

time:0

State:init

Info:show processlist

2. Row ***************************

Id:7

User:system User

Host:

Db:null

Command:connect

time:17079

State:waiting for Master to send event

Info:null

3. Row ***************************

Id:8

User:system User

Host:

Db:null

Command:connect

time:17107

State:slave have read all relay log; Waiting for the slave I/O thread to update it

Info:null

3 Rows in Set (0.00 sec)

ERROR:

No query specified

MySQL replication involves two types of files: Binlog and relay log files. According to the different settings of Binlog, MySQL replication is divided into 3 mode.

Common architectures in Replication 3: one master multi-slave, cascade replication, dual-master architecture

1) MySQL one master many from the architecture: solve the main library read request pressure solution.

2) MySQL cascading architecture: solves a master multi-slave architecture in the main library IO and network pressure, the disadvantage of additional increased the main library to the application delay from the library, where master2 can be set to Blackhold (black hole) mode to mitigate replication latency.

3) Dual master replication/dual Master Architecture:

4) Dual main cascade replication architecture:

To view the state of the main library through show Master status:

Mysql> Show master Status \g;

1. Row ***************************

file:dbking-bin.000004

position:1729

binlog_do_db:

binlog_ignore_db:

Executed_gtid_set:

1 row in Set (0.00 sec)

ERROR:

No query specified

View from the library status by show slave state:

Mysql> show slave status \g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.80.133

Master_user:rep1

master_port:3306

Connect_retry:60

master_log_file:dbking-bin.000004

read_master_log_pos:1729

relay_log_file:chavinking-relay-bin.000010

relay_log_pos:714

relay_master_log_file:dbking-bin.000004

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:1729

relay_log_space:1056

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

Master_server_id:1

Master_uuid:9b92b2a8-b7e0-11e6-81e4-000c29fa5a95

Master_info_file:/usr/local/software/mysql-5.6.24-linux-glibc2.5-x86_64/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

MySQL Replication principle