mysql.gtid_executed表的更新机制
来源:互联网 发布:java 日志 编辑:程序博客网 时间:2024/05/16 05:26
mysql.gtid_executed表的更新机制
环境
mysql.gtid_executed在主库上更新机制
1. 主库开启binlog
2. 主库关闭binlog
mysql.gtid_executed在备库上更新机制
1. 备库关闭log_slave_updates
1. 备库打开log_slave_updates
总结
参考资料
环境
mysql.gtid_executed在主库上更新机制
1. 主库开启binlog
2. 主库关闭binlog
mysql.gtid_executed在备库上更新机制
1. 备库关闭log_slave_updates
1. 备库打开log_slave_updates
总结
参考资料
mysql.gtid_executed表的更新机制
环境
- 主库:mysql5.7.18,IP:192.168.1.21
- 备库:mysql5.7.18,IP:192.168.1.128
mysql.gtid_executed在主库上更新机制
1. 主库开启binlog
- 查看binlog是否打开
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/binlog/mysql-bin |
| log_bin_index | /data/mysql/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------------+
6 rows in set (0.01 sec)
- 主库上进行操作,产生事务,观察mysql.gtid_executed表的变化情况
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 2 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> insert into xiyouji values (1,'shawujin');
Query OK, 1 row affected (0.02 sec)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 2 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
#mysql.gtid_executed表没有发生变化
- 刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 4 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
#发现主库在开启binlog的情况下,mysql.gtid_executed并不会实时更新,仅在二进制binlog rotation时发生
2. 主库关闭binlog
- 查看binlog是否关闭
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
- 主库上进行操作,产生事务,观察mysql.gtid_executed表的变化情况
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 5 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> insert into wukong_test.xiyouji values (10,'aaaaa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 5 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
#发现mysql.gtid_executed并不会记录信息
- 刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 5 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)
#mysql.gtid_executed表还是不会进行更新
- 此时备库mysql.gtid_executed表也不会记录信息。因为,主库都关闭了binlog,此时主从复制已经出现了错误。
root@localhost : (none) 03:25:09> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 464
Relay_Log_File: mysql-relay-bin.000016
Relay_Log_Pos: 677
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: No
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: 464
Relay_Log_Space: 971
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12001
Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 170524 03:23:16
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 0a646c88-36e2-11e7-937d-fa163ed7a7b1:3-12
Executed_Gtid_Set: 0a646c88-36e2-11e7-937d-fa163ed7a7b1:1-12,
3a1f3827-4029-11e7-9dc1-fa163ebca1df:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
#解决方法:开启主库binlog,然后stop slave;start slave;
mysql.gtid_executed在备库上更新机制
1. 备库关闭log_slave_updates
- 查看log_slave_updates是否关闭
root@localhost : mysql 11:48:31> show variables like '%log_slave%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | OFF |
+-------------------+-------+
1 row in set (0.00 sec)
- 在主库上产生事务,并观察备库mysql.gtid_executed表的变化情况
root@localhost : mysql 02:55:12> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 1 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 2 | 2 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 3 | 3 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 4 | 4 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 5 | 5 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 6 | 6 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 7 | 7 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 8 | 8 |
| 3a1f3827-4029-11e7-9dc1-fa163ebca1df | 1 | 3 |
+--------------------------------------+----------------+--------------+
9 rows in set (0.00 sec)
#主库上进行insert
mysql> insert into wukong_test.xiyouji values (10,'aaaaa');
Query OK, 1 row affected (0.01 sec)
#备库上的mysql.gtid_executed会每一个事务进行更新
root@localhost : mysql 02:58:28> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 1 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 2 | 2 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 3 | 3 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 4 | 4 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 5 | 5 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 6 | 6 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 7 | 7 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 8 | 8 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 9 | 9 |
| 3a1f3827-4029-11e7-9dc1-fa163ebca1df | 1 | 3 |
+--------------------------------------+----------------+--------------+
10 rows in set (0.00 sec)
1. 备库打开log_slave_updates
- 查看log_slave_updates是否打开
root@localhost : (none) 03:46:54> show variables like '%log_slave%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
- 在主库上产生事务,并观察备库mysql.gtid_executed表的变化情况
root@localhost : (none) 03:47:26> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 1 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 2 | 2 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 3 | 3 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 4 | 4 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 5 | 5 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 6 | 6 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 7 | 7 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 8 | 8 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 9 | 9 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 10 | 10 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 11 | 11 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 12 | 12 |
| 3a1f3827-4029-11e7-9dc1-fa163ebca1df | 1 | 3 |
+--------------------------------------+----------------+--------------+
13 rows in set (0.00 sec)
#主库上操作,产生事务:
mysql> insert into wukong_test.xiyouji values (10,'bbbb');
Query OK, 1 row affected (0.02 sec)
#查看备库上mysql.gtid_executed表并不会实时更新:
root@localhost : (none) 03:48:09> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 1 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 2 | 2 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 3 | 3 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 4 | 4 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 5 | 5 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 6 | 6 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 7 | 7 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 8 | 8 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 9 | 9 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 10 | 10 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 11 | 11 |
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 12 | 12 |
| 3a1f3827-4029-11e7-9dc1-fa163ebca1df | 1 | 3 |
+--------------------------------------+----------------+--------------+
13 rows in set (0.00 sec)
- 备库进行日志刷新
root@localhost : (none) 03:49:40> flush logs;
Query OK, 0 rows affected (0.11 sec)
root@localhost : (none) 03:49:59> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0a646c88-36e2-11e7-937d-fa163ed7a7b1 | 1 | 16 |
| 3a1f3827-4029-11e7-9dc1-fa163ebca1df | 1 | 3 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)
#发现在binary log rotation的时候会去记录并进行压缩。
总结
- 1、主库上mysql.gtid_executed也是会进行更新,开启了binlog后,会在binlog rotation时进行更新。
- 2、备库上mysql.gtid_executed表的更新情况主要取决于log_slave_updates参数。
- log_slave_updates = ON,不会每个事务进行实时更新,而是在binary log rotation的时候会更新并进行压缩。
- log_slave_updates = OFF,备库上的mysql.gtid_executed会每一个事务进行更新。并且,此时mysql.gtid_executed表的压缩取决于系统变量executed_gtids_compression_period,默认为1000个事务进行压缩。
- 3、MySQL 5.6版本开启GTID模式,必须打开参数log_slave_updates,简单来说就是必须在从机上再记录一份二进制日志。这样的无论对性能还是存储的开销,无疑会相应的增大。而从MySQL 5.7.5版本开始无需在GTID模式下启用参数log_slave_updates,而是将GTID值持久化保存在一张InnoDB表中,并与用户事务一起进行提交,从而实现数据的一致性。
- 4、关于log_slave_updates 参数
- Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the –log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the –log-slave-updates option without also starting the server with the –log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) –log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C
Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with –log-bin to enable binary logging, and B with the –log-slave-updates option so that updates received from A are logged by B to its binary log.
- Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the –log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the –log-slave-updates option without also starting the server with the –log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) –log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
参考资料
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html
阅读全文
0 0
- mysql.gtid_executed表的更新机制
- MySQL 5.7中新增的表gtid_executed,看看是否解决了你的痛点
- MySQL复制之gtid_purged与gtid_executed
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析
- mysql查询更新时的锁表机制分析(只介绍了MYISAM)
- mysql查询更新时的锁表机制分析(只介绍了MYISAM)
- mysql 查询与更新时的锁机制
- QGLWidget的更新机制
- Oscache的强行更新机制
- Oscache的强行更新机制
- xmemcached的缓存更新机制
- MYSQL,MSSQL的两表更新语法
- DTD 和XSD 区别
- 笔杆网查重
- java集合类
- shell传参
- 斐波那契数列系列问题,题目描述 一只青蛙一次可以跳上1级台阶,也可以跳上2级。求该青蛙跳上一个n级的台阶总共有多少种跳法。
- mysql.gtid_executed表的更新机制
- tensorflow 测试python
- 用户复购总不见提升?你必须关注这 3 个指标
- 整合springboot+mvc+mybatis(通用mapper)+druid+jsp+bootstrap实现权限管理文件上传下载多数据源切换操作日志记录等功能
- 查看 OGG debug信息 OGG 的日志文件存放路径
- bootstrap14--辅助类
- perl 基本语法
- linux下ftp自动登录的shell脚本
- 福尔摩斯到某古堡探险