限制MySQL Binlog的传输速率

最近一台核心库备库完成恢复后打开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 */