一个参数引起的MySQL从库宕机血案

Part1:max_binlog_cache_size

max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小,为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。

当我们执行多语句事务的时候,所有session的使用的内存超过max_binlog_cache_size的值时,就会报错:

Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage

mysql-yi-ge-can-shu-yin-qi-de-dang-ji-xue-an-01

登录MySQL,查询 max_binlog_cache_size 值:

MariaDB [mimvp]> show variables like '%cache%';
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold   | 300                  |
| aria_pagecache_buffer_size     | 134217728            |
| aria_pagecache_division_limit  | 100                  |
| aria_pagecache_file_hash_size  | 512                  |
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 705                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| join_cache_level               | 2                    |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| key_cache_file_hash_size       | 512                  |
| key_cache_segments             | 0                    |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 1048576              |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 1048576              |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | OFF                  |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 400                  |
| table_open_cache               | 2000                 |
| thread_cache_size              | 0                    |
+--------------------------------+----------------------+
31 rows in set (0.09 sec)

 

Part2:为什么它能引起宕机

Warning警告1

max_binlog_cache_size 在主从设置不一致的情况下,主库参数值大于从库参数值,在主库进行大事务操作时,主库顺利进行,从库因max_binlog_cache_size值低于该事物所需,从库会抛出“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”错误号为1197。

Warning警告2

max_binlog_cache_size在主从参数设置一样的情况下,主库执行大事务操作,如主库提示需提高该参数以顺利执行SQL,但DBA只调整了主库的max_binlog_cache_size而忘记调整从库的max_binlog_cache_size,则同样从库会爆出1197错误,导致主从不同步。

 

Part3该设置值为多少

具体值设置为多少,不能纸上谈兵,还需要看公司的具体业务以及硬件内存大小

这里除了不设置该值,使用默认值(默认值很大, max_binlog_cache_size = 18446744073709547520)以外,个人推荐值为4G,基本已经足够应付大部分场合,但无论是否指定该值,在对大表进行操作时,都需注意上述的警告内容,避免该值设置不合理引起从库无法执行报1197的问题。具体命令如下:

set global max_binlog_cache_size =4294967296;

mysql-yi-ge-can-shu-yin-qi-de-dang-ji-xue-an-02

 

总结

max_binlog_cache_size 值为动态参数,可以随时利用上述命令进行调整,

所以别忘记将该参数加入到my.cnf,以防止重启数据库后失效。