尚云网站建设,如何注册营业执照,做网站前端开发的必备软件,天门市基础建设网站1. exec_time 到底表示什么时间#xff1f;
MySQL binlog日志解析后#xff0c;我们能看到会有 exec_time #xff0c;从字面意思理解这个记录的是执行时间#xff0c;那这个记录的到底是单条sql的执行时间#xff1f;还是事务的执行时间#xff1f;下面通过测试来解读一…1. exec_time 到底表示什么时间
MySQL binlog日志解析后我们能看到会有 exec_time 从字面意思理解这个记录的是执行时间那这个记录的到底是单条sql的执行时间还是事务的执行时间下面通过测试来解读一下 2.创建测试库表
mysql create database test_shao;
Query OK, 1 row affected (0.03 sec)
mysql use test_shao;
Database changed
mysql create table test_1(id int not null auto_increment,primary key(id)) engineinnodb default charsetutf8mb4;
Query OK, 0 rows affected (0.03 sec)
3.RC隔离级别下 事务测试
show variables like transaction_isolation;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| transaction_isolation | READ-COMMITTED |
---------------------------------------flush logs;
begin;
select count(*) from cmp_sys_1.message;
insert into test_1 select sleep(5);
select count(*) from cmp_sys_1.message;
insert into test_1 select sleep(10);
commit;mysql flush logs;
Query OK, 0 rows affected (0.02 sec)mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select count(*) from cmp_sys_1.message;----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (45.24 sec)mysql
mysql insert into test_1 select sleep(5);
Query OK, 1 row affected (5.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (35.73 sec)mysql insert into test_1 select sleep(10);
Query OK, 1 row affected (10.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql commit;
Query OK, 0 rows affected (0.00 sec)
4.解析第3步生成的主库binlog
show master status;
----------------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
----------------------------------------------------------------------------------------------------------
| 3306-bin.000781 | 364155470 | | | bc129ea5-5f01-11ed-ae48-fa163efcbfd5:1-17199 |
----------------------------------------------------------------------------------------------------------
show variables like log_bin_basename;
-----------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------
| log_bin_basename | /app/mysql/mysql3306/log/3306-bin |
-----------------------------------------------------mysqlbinlog --base64-outputdecode-rows -vvv 3306-bin.000781 3306-bin.000781.txt
5.解析第3步生成的从库binlog 6.RR 隔离级别下statment binlog格式下事务测试(从库还是row格式binlog)
#RC隔离级别下binlog_format不允许使用statment格式 mysql flush logs;
Query OK, 0 rows affected (0.01 sec)mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (38.37 sec)mysql insert into test_1 select sleep(10);
Query OK, 1 row affected, 1 warning (10.05 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (26.10 sec)mysql insert into test_1 select sleep(15);
Query OK, 1 row affected, 1 warning (15.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql commit;
Query OK, 0 rows affected (0.00 sec)
7.解析第6步主库binlog日志(binlog_formatstatment)
8.解析第6步从库binlog(binlog_formatrow)
9.主库从库隔离级别都为RRbinlog_formatrow时事务测试
mysql use test_shao;
Database changed
mysql set transaction_isolationREPEATABLE-READ;
Query OK, 0 rows affected (0.00 sec)mysql set binlog_formatStatement;
Query OK, 0 rows affected (0.00 sec)mysql select * from test_1;
----
| id |
----
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
----
6 rows in set (0.00 sec)mysql flush logs;
Query OK, 0 rows affected (0.01 sec)mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select count(*) from cmp_sys_1.message;----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (29.95 sec)mysql
mysql insert into test_1 select sleep(4);
Query OK, 1 row affected, 1 warning (4.02 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (24.32 sec)mysql insert into test_1 select sleep(8);
Query OK, 1 row affected, 1 warning (8.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql commit;
Query OK, 0 rows affected (0.00 sec)10.解析第9步中主库binlog日志(隔离级别RRbinlog_formatstatment)
11.解析第9步中从库binlog日志(隔离级别RRbinlog_formatstatment) #对应的relaylog中记录的是statment格式的binlog
##从库记录的binlog格式依然为row格式是因为我只是设置了从库全局的隔离级别为RRbinlog_formatstatment但是我并没有重启从库io和sql线程。如果只是设置了从库的binlog_formatstatment主要为row格式的话从库复制会报如下错误 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction bc129ea5-5f01-11ed-ae48-fa163efcbfd5:17204 at master log 3306-bin.000785, end_log_pos 488; Error executing row event: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT STATEMENT.设置主库从库隔离级别为RRbinlog_format格式为statment重启复制线程后测试结果如下
mysql use test_shao;
Database changed
mysql set transaction_isolationREPEATABLE-READ;
Query OK, 0 rows affected (0.00 sec)mysql set binlog_formatStatement;
Query OK, 0 rows affected (0.00 sec)mysql flush logs;
Query OK, 0 rows affected (0.01 sec)mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (25.33 sec)mysql insert into test_1 select sleep(2);
Query OK, 1 row affected, 1 warning (2.03 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql select count(*) from cmp_sys_1.message;
----------
| count(*) |
----------
| 62882460 |
----------
1 row in set (20.08 sec)mysql insert into test_1 select sleep(4);
Query OK, 1 row affected, 1 warning (4.01 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql commit;
Query OK, 0 rows affected (0.00 sec)主库binlog日志
从库binlog日志 #为什么从库binlog中第二个语句的exec_time时间为10s从库statment binlog格式下exec_time 到底如何记录时间有待继续研究
