使用blockhole儲存引擎過濾複製的binlog

如果我們只需要將資料庫中的部分表複製到從資料庫上，那麼可以通過replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table、replicate-wild-do-table複製選項來選擇只需要複製的表或者資料庫。

但是這幾個引數只是將符合條件的表或者資料庫的binlog apply到從資料庫中，不能過濾傳輸的binlog的內容。如果我們需要複製的內容很少，而不需要複製的內容很多的時候，就會無謂在主從伺服器之間傳輸很多不需要的binlog，這種情況下，我們可以通過使用blockhole儲存引擎過濾需要的binlog，而又不會對主資料庫造成太大的額外的壓力。

blockhole，直譯就是黑洞的意思，使用這個儲存引擎的表，不論執行什麼操作，都不會實際像表中插入記錄，但是對這個表的更新操作，卻可以通過binlog記錄下來。

下面我們測試使用blockhole進行binlog過濾得步驟：

首先要建立一個正確的主從複製環境，最好在相同伺服器上建立，這樣才能達到減少網路傳輸的問題。

我們在test使用者下面有兩個表：

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| test_myisam |

| test_myisam1 |

+----------------+

2 rows in set (0.00 sec)

我們對這兩個表的更新，都會記錄到binlog中，並且都會傳輸到從伺服器上執行。

主資料庫執行：

mysql> insert into test_myisam1 values(1,'ttt'),(2,'aaa');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

從資料庫：

mysql> select * from test_myisam1;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

+------+------+

2 rows in set (0.00 sec)

我們在從伺服器上設定replicate-do-table=test.test_myisam，只複製其中的一個表，並且開啟從資料庫的log-slave-updates，確保從資料庫的更新可以寫binlog，然後重啟從資料庫生效。

啟動後，更新兩個表的記錄，只有test_myisam地更新執行到從資料庫中，但是傳輸到從資料庫的binlog包括全部的更新資訊。

主資料庫：

mysql> insert into test_myisam1 values(1,'ttt'),(2,'aaa');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into test_myisam values(1,'ttt'),(2,'aaa');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_myisam;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from test_myisam1;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

| 1 | ttt |

| 2 | aaa |

+------+------+

4 rows in set (0.00 sec)

從資料庫：

mysql> select * from test_myisam;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from test_myisam1;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

+------+------+

2 rows in set (0.00 sec)

下面是傳輸到從資料庫的binlog：

[lisa1@localhost data]$ mysqlbinlog localhost-relay-bin.000007

......

/*!C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;

insert into test_myisam1 values(1,'ttt'),(2,'aaa')/*!*/;

# at 348

#070807 16:15:12 server id 1 end_log_pos 900 Query thread_id=9 exec_time=0 error_code=0

SET TIMESTAMP=1186474512/*!*/;

insert into test_myisam values(1,'ttt'),(2,'aaa')/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

下面是從資料庫執行更新寫的binlog：

[lisa1@localhost data]$ mysqlbinlog mysql-bin.000004

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#070807 16:15:01 server id 2 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 070807 16:15:01 at startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK/*!*/;

# at 98

#070807 16:15:12 server id 1 end_log_pos 210 Query thread_id=9 exec_time=0 error_code=0

use test/*!*/;

SET TIMESTAMP=1186474512/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=0/*!*/;

/*!C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;

insert into test_myisam values(1,'ttt'),(2,'aaa')/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

從兩個binlog可以看出，已經有效的過濾了binlog的內容，只保留了我們關心的test_myisam表的更新資訊。

最後，我們把從資料庫上test_myisam表的儲存引擎修改成blockhole的，這樣對從資料庫的訪問壓力降低到最小，只是簡單的進行binlog的過濾。

從資料庫：

mysql> alter table test_myisam engine=blackhole;

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_myisam;

Empty set (0.00 sec)

然後在主資料庫上進行更新操作：

mysql> insert into test_myisam values(3,'bbb'),(4,'ccc');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_myisam;

+------+------+

| i | c |

+------+------+

| 1 | ttt |

| 2 | aaa |

| 3 | bbb |

| 4 | ccc |

+------+------+

4 rows in set (0.00 sec)

從資料庫：

mysql> select * from test_myisam;

Empty set (0.00 sec)

檢查從資料庫寫的binlog：

[lisa1@localhost data]$ mysqlbinlog mysql-bin.000004

......

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;

insert into test_myisam values(1,'ttt'),(2,'aaa')/*!*/;

# at 210

#070807 16:17:09 server id 2 end_log_pos 313 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1186474629/*!*/;

/*!C latin1 *//*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

alter table test_myisam engine=blackhole/*!*/;

# at 313

#070807 16:17:41 server id 1 end_log_pos 425 Query thread_id=9 exec_time=0 error_code=0

SET TIMESTAMP=1186474661/*!*/;

/*!C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;

insert into test_myisam values(3,'bbb'),(4,'ccc')/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

可以發現，更新操作雖然沒有實際寫到從資料庫的表中，但是更新的操作正確記錄在binlog中。

注意事項：

由於blockhole表中並沒有實際的記錄，所以，對blockhole表的insert觸發器可以觸發，但是update和delete觸發器都不會觸發。

這樣，如果在主資料庫上的表有update和delete觸發器，就不能使用blockhole儲存引擎進行binlog過濾，因為觸發器的操作並沒有寫在binlog中，

而是需要通過主表的更新來觸發從資料庫的觸發器的，如果從資料庫的觸發器不能觸發，那麼資料就不同步了。

mysql> CREATE TABLE IF NOT EXISTS test_father ( id smallint(5) unsigned NOT NULL AUTO_INCREMENT, note varchar(10) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.10 sec)

mysql> create table test_sun(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));

Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$

mysql> CREATE TRIGGER ins_test_father_bef

-> BEFORE INSERT ON test_father FOR EACH ROW BEGIN

-> INSERT INTO test_sun (note) VALUES (new.note);

-> END;

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> insert into test_father values(1,'abcd');

Query OK, 1 row affected (0.11 sec)

mysql> select * from test_sun;

+----+------+

| id | note |

+----+------+

| 1 | abcd |

+----+------+

1 row in set (0.00 sec)

察看傳輸到從資料庫的binlog：

[lisa1@localhost data]$ mysqlbinlog localhost-relay-bin.000013

......

#070807 17:07:25 server id 1 end_log_pos 103 Query thread_id=9 exec_time=0 error_code=0

SET TIMESTAMP=1186477645/*!*/;

insert into test_father values(1,'abcd')/*!*/;

# at 784

#070807 17:07:25 server id 1 end_log_pos 3946 Xid = 82

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

其他blockhole的可能用途可以用來對比是否開啟binlog對資料庫效能的影響程度，或者測試資料庫和儲存引擎不相關的效能瓶頸。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/51862/viewspace-180655/，如需轉載，請註明出處，否則將追究法律責任。