In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces the example analysis of MySQL replication application relay log, the article is very detailed, has a certain reference value, interested friends must read it!
1. Preface
The SQL thread applies the relay log, which resides in the primary key update when the binlog_format is in row format. Here is a diagram to prove it.
Get a picture from a big god. The SQL thread applies the relay log flow. Here's an experiment to verify it: (PS, I personally think that this picture binlog_format is correct in ROW format)
two。 Verify that there is a competition table
Create a table structure in the main library
CREATE TABLE `table_ PK` (
`id`int (11) NOT NULL
`name` varchar (20) NOT NULL
`age`tinyint NOT NULL
`sex` tinyint NOT NULL COMMENT '0menjimanjin1 woman`
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Insert test data
Insert into table_pk (`id`, `name`, `age`, `sex`) values (111)
Insert into table_pk (`id`, `name`, `age`, `sex`) values (222)
Insert into table_pk (`id`, `name`, `age`, `sex`) values (333)
Insert into table_pk (`id`, `name`, `age`, `sex`) values (444)
Insert into table_pk (`id`, `name`, `age`, `sex`) values (555
(dg6) root@localhost [(none)] > use mytest
(dg6) root@localhost [mytest] > select * from table_pk
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
(dg6) root@localhost [mytest] > show global variables like'% binlog_format%'
+-+ +
| | Variable_name | Value |
+-+ +
| | binlog_format | ROW |
+-+ +
Row in set (0.00 sec)
So let's take a look at Congku.
(dg7) root@localhost [mytest] > select * from table_pk
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > show global variables like'% binlog_format%'
+-+ +
| | Variable_name | Value |
+-+ +
| | binlog_format | ROW |
+-+ +
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 4469
Relay_Log_File: dg7-relay-bin.000002
Relay_Log_Pos: 4681
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 4469
Relay_Log_Space: 4883
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: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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: b888e1ea-9739-11e4-a24e-000c29b24887:1-17
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-9
B888e1ea-9739-11e4-a24e-000c29b24887:1-17
Auto_Position: 1
Row in set (0.00 sec)
The data is copied, and the MySQL master-slave replication is normal, so in order to verify that the MySQL replication SQL thread resides in the process of the previous figure, if there is a primary key, press the primary key to update the matching update record.
So we are modifying a row of data from the library, deliberately creating inconsistencies.
(dg7) root@localhost [mytest] > UPDATE `table_ pk`SET `name` = 'laowang' WHERE `id` = 333
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
(dg7) root@localhost [mytest] > select * from table_pk
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | laowang | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
At this time, the master and slave data are inconsistent.
Main library
(dg6) root@localhost [mytest] > select * from table_pk where id=333
+-+
| | id | name | age | sex | |
+-+
| | 333 | wangwu | 22 | 1 | |
+-+
Row in set (0.00 sec)
Slave library
(dg7) root@localhost [mytest] > select * from table_pk where id=333
+-+
| | id | name | age | sex | |
+-+
| | 333 | laowang | 22 | 1 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
So, we update a row of data in the main library.
(dg6) root@localhost [mytest] > UPDATE `table_ pk`SET `name` = 'wangzi' WHERE `id` = 333
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg6) root@localhost [mytest] > select * from table_pk where id=333
+-+
| | id | name | age | sex | |
+-+
| | 333 | wangzi | 22 | 1 | |
+-+
Row in set (0.00 sec)
Let's take a look at the status of the slave library, whether the update of the main library has been copied, and it's time to witness the miracle.
# # #
(dg7) root@localhost [mytest] > select * from table_pk where id=333
+-+
| | id | name | age | sex | |
+-+
| | 333 | laowang | 22 | 1 | |
+-+
Row in set (0.00 sec)
# miraculously, the update of the main library has come #
(dg7) root@localhost [mytest] > select * from table_pk where id=333
+-+
| | id | name | age | sex | |
+-+
| | 333 | wangzi | 22 | 1 | |
+-+
Row in set (0.00 sec)
# well, take a look at the master-slave replication status of MySQL, which is also normal # #
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 5249
Relay_Log_File: dg7-relay-bin.000002
Relay_Log_Pos: 5461
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 5249
Relay_Log_Space: 5663
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: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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: b888e1ea-9739-11e4-a24e-000c29b24887:1-20
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-11
B888e1ea-9739-11e4-a24e-000c29b24887:1-20
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
3. Verify that there is no index
The main library creates tables and inserts records
CREATE TABLE `table_ index` (
`id`int (11) NOT NULL
`name` varchar (20) NOT NULL
`age`tinyint (4) NOT NULL
`sex` tinyint (4) NOT NULL COMMENT '0dymanjin1 woman`
) ENGINE=InnoDB
(dg6) root@localhost [mytest] > select * from table_index
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
(dg6) root@localhost [mytest] >
Look at it from the library
(dg7) root@localhost [mytest] > select * from table_index
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
We are continuing to sabotage the slave library, changing the age from name to lisi to 33, which is no longer consistent with master and slave.
(dg7) root@localhost [mytest] > select * from table_index where name='lisi'
+-+
| | id | name | age | sex | |
+-+
| | 222nd | lisi | 22 | 1 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > update table_index set age=33 where name='lisi'
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg7) root@localhost [mytest] > select * from table_index where name='lisi'
+-+
| | id | name | age | sex | |
+-+
| | 222nd | lisi | 33 | 1 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
So let's update the records in the main library. Modify the age of lisi to 30 to see if it can be updated from the library.
(dg6) root@localhost [mytest] > select * from table_index where name='lisi'
+-+
| | id | name | age | sex | |
+-+
| | 222nd | lisi | 22 | 1 | |
+-+
Row in set (0.00 sec)
(dg6) root@localhost [mytest] > update table_index set age=30 where name='lisi'
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg6) root@localhost [mytest] > select * from table_index where name='lisi'
+-+
| | id | name | age | sex | |
+-+
| | 222nd | lisi | 30 | 1 | |
+-+
Row in set (0.00 sec)
(dg6) root@localhost [mytest] >
Back to the slave database, the data has not been updated, and the age of lisi is still 33. At this time, master-slave replication is also abnormal, prompting a 1032 error (no record found)
(dg7) root@localhost [mytest] > select * from table_index where name='lisi'
+-+
| | id | name | age | sex | |
+-+
| | 222nd | lisi | 33 | 1 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 7376
Relay_Log_File: dg7-relay-bin.000003
Relay_Log_Pos: 724
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table mytest.table_index; Can't find record in 'table_index', Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 7345
Skip_Counter: 0
Exec_Master_Log_Pos: 7112
Relay_Log_Space: 8090
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table mytest.table_index; Can't find record in 'table_index', Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 7345
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 150425 08:30:49
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-28
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-14
B888e1ea-9739-11e4-a24e-000c29b24887:1-27
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
4. Verify that there is a unique index
The test methods are all the same, and I will post the results in the following steps. (the core idea is that the slave database first modifies the records to make the master and slave data inconsistent, and then the master database is updated to see if the slave database has synchronized master database records.)
(dg6) root@localhost [mytest] > select * from table_index
+-+
| | id | sid | name | age | sex | |
+-+
| | 111 | 1 | zhangsan | 20 | 0 | |
| | 222nd | 2 | lisi | 30 | 1 | |
| | 333 | 3 | wangzi | 22 | 1 |
| | 444 | 4 | lilei | 32 | 0 |
| | 555 | 5 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
(dg6) root@localhost [mytest] > select * from table_index where sid=3
+-+
| | id | sid | name | age | sex | |
+-+
| | 333 | 3 | wangzi | 22 | 1 |
+-+
Row in set (0.00 sec)
(dg6) root@localhost [mytest] > update table_index set name='wangwu' where sid=3
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg6) root@localhost [mytest] > select * from table_index where sid=3
+-+
| | id | sid | name | age | sex | |
+-+
| | 333 | 3 | wangwu | 22 | 1 |
+-+
Row in set (0.00 sec)
(dg6) root@localhost [mytest] >
From the library, it can be updated, and the master-slave replication status is normal.
(dg7) root@localhost [mytest] > select * from table_index
+-+
| | id | sid | name | age | sex | |
+-+
| | 111 | 1 | zhangsan | 20 | 0 | |
| | 222nd | 2 | lisi | 30 | 1 | |
| | 333 | 3 | wangzi | 22 | 1 |
| | 444 | 4 | lilei | 32 | 0 |
| | 555 | 5 | hanmeimei | 30 | 1 | |
+-+
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > update table_index set name='laowang' where sid=3
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg7) root@localhost [mytest] > select * from table_index where sid=3
+-+
| | id | sid | name | age | sex | |
+-+
| | 333 | 3 | laowang | 22 | 1 |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 13038
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 5841
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 13038
Relay_Log_Space: 6615
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: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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: b888e1ea-9739-11e4-a24e-000c29b24887:1-52
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-26
B888e1ea-9739-11e4-a24e-000c29b24887:1-52
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > select * from table_index where sid=3
+-+
| | id | sid | name | age | sex | |
+-+
| | 333 | 3 | wangwu | 22 | 1 |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 13302
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 6105
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 13302
Relay_Log_Space: 6879
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: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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: b888e1ea-9739-11e4-a24e-000c29b24887:1-53
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-26
B888e1ea-9739-11e4-a24e-000c29b24887:1-53
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
5. Verify that there is a primary key and a normal index
(dg6) root@localhost [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg6) root@localhost [mytest] > update table_key set name='zhangsir' where age=20
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg6) root@localhost [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsir | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg6) root@localhost [mytest] >
Look at it from the library
(dg7) root@localhost [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > desc update table_key set name='xiaozhang' where age=20
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | range | age_index | age_index | 1 | const | 1 | Using where |
+-- +
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > update table_key set name='xiaozhang' where age=20
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg7) root@localhost [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | xiaozhang | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsir | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 16026
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 8829
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 16026
Relay_Log_Space: 9603
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: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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: b888e1ea-9739-11e4-a24e-000c29b24887:1-63
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-28
B888e1ea-9739-11e4-a24e-000c29b24887:1-63
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
6. Verify that there is only a normal index.
Main library
CREATE TABLE `table_ index` (
`id`int (11) NOT NULL
`name` varchar (20) NOT NULL
`age`tinyint (4) NOT NULL
`sex` tinyint (4) NOT NULL COMMENT '0dymanjin1 woman`
Key age_index (`age`)
) ENGINE=InnoDB
(dg6) root@localhost [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsir | 20 | 0 | |
+-+
Row in set (0.00 sec)
(dg6) root@localhost [mytest] > update table_key set name='zhaoliu' where age=20
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg6) root@localhost [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhaoliu | 20 | 0 | |
+-+
Row in set (0.00 sec)
Slave library
(dg7) root@localhost [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsir | 20 | 0 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > update table_key set name='zhangsan' where age=20
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg7) root@localhost [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
+-+
Row in set (0.00 sec)
(dg7) root@localhost [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
+-+
Row in set (0.00 sec)
# # error 1032 and no record found
(dg7) root@localhost [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 16463
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 8993
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table mytest.table_key; Can't find record in 'table_key', Error_code: 1032; Column' name' cannot be null, Error_code: 1048; Column 'age' cannot be null, Error_code: 1048; Column' sex' cannot be null, Error_code: 1048; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 16432
Skip_Counter: 0
Exec_Master_Log_Pos: 16190
Relay_Log_Space: 10040
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table mytest.table_key; Can't find record in 'table_key', Error_code: 1032; Column' name' cannot be null, Error_code: 1048; Column 'age' cannot be null, Error_code: 1048; Column' sex' cannot be null, Error_code: 1048; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 16432
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: / data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 150425 09:43:27
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-65
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-29
B888e1ea-9739-11e4-a24e-000c29b24887:1-64
Auto_Position: 1
Row in set (0.00 sec)
(dg7) root@localhost [mytest] >
When the 7.binlog format is in sbr,mbr format (PS, because I used GTID, so I found two other machines to test)
Main library
(dg1) root@127.0.0.1 [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > show global variables like'% binlog_format%'
+-+ +
| | Variable_name | Value |
+-+ +
| | binlog_format | MIXED |
+-+ +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > select * from table_key
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
| | 222nd | lisi | 22 | 1 | |
| | 333 | wangwu | 22 | 1 | |
| | 444 | lilei | 32 | 0 | |
| | 555 | hanmeimei | 30 | 1 | |
| | 666 | lucy | 30 | 1 | |
| | 777 | lili | 30 | 1 | |
| | 888 | lintao | 32 | 0 | |
+-+
Rows in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > update table_key set name='zhangzong' where age=20
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
Take a look at the library.
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsan | 20 | 0 | |
+-+
Row in set (0.01sec)
(dg2) root@127.0.0.1 [mytest] > update table_key set name='zhangsir' where age=20
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangsir | 20 | 0 | |
+-+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=20
+-+
| | id | name | age | sex | |
+-+
| | 111 | zhangzong | 20 | 0 | |
+-+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: dg1.000001
Read_Master_Log_Pos: 3340
Relay_Log_File: mysql3307-relay-bin.000002
Relay_Log_Pos: 3355
Relay_Master_Log_File: dg1.000001
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: 3340
Relay_Log_Space: 3532
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: 12
Master_UUID: fbc1bdf1-829b-11e4-9bdf-000c29b24882
Master_Info_File: / data/3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] >
Delete the index and test it again
(dg1) root@127.0.0.1 [mytest] > alter table table_key drop key age_index
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > insert into table_key (id,name,age,sex) values
ERROR 1136 (21S01): Column count doesn't match value count at row 1
(dg1) root@127.0.0.1 [mytest] > insert into table_key (id,name,age,sex) values
Query OK, 1 row affected (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > select * from table_key where age=38
+-+
| | id | name | age | sex | |
+-+
| | 999 | user1 | 38 | 0 | |
+-+
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > update table_key set name='user3' where age=38
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg1) root@127.0.0.1 [mytest] >
Take a look at the library.
(dg2) root@127.0.0.1 [mytest] > show create table table_key
+- -+
| | Table | Create Table |
+- -+
| | table_key | CREATE TABLE `table_ key` (
`id`int (11) NOT NULL
`name` varchar (20) NOT NULL
`age`tinyint (4) NOT NULL
`sex` tinyint (4) NOT NULL COMMENT '0dymanjin1 woman`
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+- -+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=38
+-+
| | id | name | age | sex | |
+-+
| | 999 | user1 | 38 | 0 | |
+-+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > update table_key set name='user2' where age=38
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=38
+-+
| | id | name | age | sex | |
+-+
| | 999 | user2 | 38 | 0 | |
+-+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > select * from table_key where age=38
+-+
| | id | name | age | sex | |
+-+
| | 999 | user3 | 38 | 0 | |
+-+
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: dg1.000001
Read_Master_Log_Pos: 3952
Relay_Log_File: mysql3307-relay-bin.000002
Relay_Log_Pos: 3967
Relay_Master_Log_File: dg1.000001
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: 3952
Relay_Log_Space: 4144
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: 12
Master_UUID: fbc1bdf1-829b-11e4-9bdf-000c29b24882
Master_Info_File: / data/3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has 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
Row in set (0.00 sec)
(dg2) root@127.0.0.1 [mytest] >
Summary
1, the SQL thread application relay log, when the binlog_format is in row format, it is indeed in the primary key update (Innodb table, if the specified primary key is not displayed, if the primary key is not explicitly defined, then InnoDB will select the first unique index that does not contain null values as the primary key index), so this diagram is basically correct in binlog_format=ROW format.
2. Use the self-increasing column (INT/BIGINT type) as the primary key, so that the data distribution is basically in order with the splitting order of the B+ leaf nodes, and the performance is relatively good.
3. It is vividly proved that in RBR mode, when MySQL replication SQL thread applies relay log with primary key and unique key, the lock scope is less than statement mode.
Reference: advantages and disadvantages of the two modes of SBR RBR
The advantages and disadvantages of SBR and RBR: the advantages of SBR:
It has a long history and mature technology.
The binlog file is small.
Binlog contains all the database change information, which can be used to audit the security of the database.
Binlog can be used for real-time restore, not just for replication.
The master-slave version can be different, and the slave version can be higher than the master version.
Disadvantages of SBR:
Not all UPDATE statements can be copied, especially if they contain uncertain actions.
Replication can also be a problem when calling UDF with uncertainties
Statements that use the following function cannot be copied either: * LOAD_FILE () * UUID () * USER () * FOUND_ROWS () * SYSDATE () (unless the-- sysdate-is-now option is enabled at startup)
INSERT... SELECT produces more row-level locks than RBR
When replicating a UPDATE that requires a full table scan (indexes are not used in the WHERE statement), more row-level locks are required than the RBR request
For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements
For some complex statements, the resource consumption on the slave server will be more serious, while in RBR mode, it will only affect that changed record.
When a stored function (not a stored procedure) is called, it also executes the NOW () function once, which is either a bad thing or a good thing.
The identified UDF also needs to be executed on the slave server.
The data table must be almost consistent with the primary server, otherwise it may cause replication errors
If something goes wrong in executing complex statements, it will consume more resources
Advantages of RBR:
Any situation can be replicated, which is the safest and most reliable for replication.
The same as the replication technology of most other database systems
In most cases, replication will be much faster if the table on the slave server has a primary key
There are fewer row locks when copying the following statements: * INSERT... SELECT * INSERT with AUTO_INCREMENT fields * UPDATE or DELETE statements that have no strings attached or have not modified many records
Fewer locks when executing INSERT,UPDATE,DELETE statements
It is possible to use multithreading to perform replication from the server
Disadvantages of RBR:
Binlog is much bigger.
Large amounts of data will be contained in the binlog during a complex rollback
When the UPDATE statement is executed on the primary server, all changed records are written to binlog, while SBR is written only once, which leads to frequent concurrent write problems in binlog.
Large BLOB values produced by UDF cause replication to slow down
You can't see what statements are copied from binlog.
When executing a stacked SQL statement on a non-transactional table, it is best to use SBR mode, otherwise it will easily lead to data inconsistency between the master and slave servers.
In addition, the processing rules for changing the tables in the system library mysql are as follows:
If the table is directly manipulated by INSERT,UPDATE,DELETE, the log format is recorded according to the setting of binlog_format
If you use management statements such as GRANT,REVOKE,SET PASSWORD to do it, then use SBR mode to record anyway
Note: after adopting RBR mode, many previous primary key duplication problems can be solved.
The above is all the contents of the article "sample Analysis of MySQL replication Application Relay Log". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
