本文共 4267 字，大约阅读时间需要 14 分钟。
最近一台核心库备库完成恢复后打开slave，导致主库传送binlog，瞬间占满网络，触发故障。
为了做一些限制， 给mysql在发送binlog的函数(mysql_binlog_send)里每隔一段时间sleep一次， 增加了两个参数：
master_send_count 每读master_send_count次事件，sleep一次
master_send_sleep 每次sleep的时间为master_send_sleep ，单位为毫秒ms
以下统计数据，每1秒统计一次。
1.不受限制
set global master_send_count = 0;
set global master_send_sleep = 0;
2.
set global master_send_count = 10;
set global master_send_sleep = 100;
3.
set global master_send_count = 10;
set global master_send_sleep = 200;
4.
set global master_send_count = 1;
set global master_send_sleep = 15;
5.
set global master_send_count = 1;
set global master_send_sleep = 50;
测试的过程中，发现时不时的网卡发送数据彪的很高，暂不清楚原因，似乎mysql本身会做缓存。
Patch 很简单，基于5.1.48
Index: sql/sql_repl.cc===================================================================--- sql/sql_repl.cc (revision 2487)+++ sql/sql_repl.cc (working copy)@@ -360,6 +360,8 @@ DBUG_ENTER("mysql_binlog_send"); DBUG_PRINT("enter",("log_ident: '%s' pos: %ld", log_ident, (long) pos)); + ulong read_count=0;+ bzero((char*) &log,sizeof(log)); #ifndef DBUG_OFF@@ -541,11 +543,18 @@ /* seek to the requested position, to start the requested dump */ my_b_seek(&log, pos); // Seek will done on next read-+ while (!net->error && net->vio != 0 && !thd->killed) { while (!(error = Log_event::read_log_event(&log, packet, log_lock))) {+ if (master_send_count != 0 &&+ master_send_sleep !=0 &&+ ++read_count>master_send_count) {+ my_sleep(master_send_sleep);+ read_count=0;+ }+ #ifndef DBUG_OFF if (max_binlog_dump_events && !left_events--) {Index: sql/mysqld.cc===================================================================--- sql/mysqld.cc (revision 2487)+++ sql/mysqld.cc (working copy)@@ -540,6 +540,9 @@ ulong binlog_cache_use= 0, binlog_cache_disk_use= 0; ulong max_connections, max_connect_errors; uint max_user_connections= 0;+ulong master_send_count = 0;+ulong master_send_sleep = 0;+ /** Limit of the total number of prepared statements in the server. Is necessary to protect the server against out-of-memory attacks.@@ -5737,7 +5740,9 @@ OPT_SLOW_QUERY_LOG_FILE, OPT_IGNORE_BUILTIN_INNODB, OPT_BINLOG_DIRECT_NON_TRANS_UPDATE,- OPT_DEFAULT_CHARACTER_SET_OLD+ OPT_DEFAULT_CHARACTER_SET_OLD,+ OPT_MASTER_SEND_COUNT,+ OPT_MASTER_SEND_SLEEP }; @@ -7128,6 +7133,14 @@ "t_innodb; otherwise, slaves may diverge from the master.", (uchar**) &global_system_variables.binlog_direct_non_trans_update, (uchar**) &max_system_variables.binlog_direct_non_trans_update, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},+ {"master_send_count", OPT_MASTER_SEND_COUNT,+ "every master_send_count binlog send ,will sleep for a while",+ (uchar**) &master_send_count, (uchar**) &master_send_count, 0, GET_ULONG,+ REQUIRED_ARG, 0, 0, ULONG_MAX, 0, 1, 0},+ {"master_send_sleep", OPT_MASTER_SEND_SLEEP,+ "every master_send_count binlog send ,will sleep master_send_sleep ms",+ (uchar**) &master_send_sleep, (uchar**) &master_send_sleep, 0, GET_ULONG,+ REQUIRED_ARG, 0, 0, ULONG_MAX, 0, 1, 0}, {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; Index: sql/mysql_priv.h===================================================================--- sql/mysql_priv.h (revision 2487)+++ sql/mysql_priv.h (working copy)@@ -81,6 +81,9 @@ /* increment query_id and return it. */ inline query_id_t next_query_id() { return global_query_id++; } +extern ulong master_send_count;+extern ulong master_send_sleep;+ /* useful constants */ extern MYSQL_PLUGIN_IMPORT const key_map key_map_empty; extern MYSQL_PLUGIN_IMPORT key_map key_map_full; /* Should be threaded as const */Index: sql/set_var.cc===================================================================--- sql/set_var.cc (revision 2487)+++ sql/set_var.cc (working copy)@@ -436,6 +436,8 @@ &SV::myisam_stats_method, &myisam_stats_method_typelib, NULL);+static sys_var_long_ptr Sys_master_send_count(&vars, "master_send_count", &master_send_count);+static sys_var_long_ptr Sys_master_send_sleep(&vars, "master_send_sleep", &master_send_sleep); #ifdef __NT__ /* purecov: begin inspected */ 转载地址：http://iiymo.baihongyu.com/