BLACKHOLE安装

使用cmake源码安装时指定-DWITH_BLACKHOLE_STORAGE_ENGINE 选项 可以启用 BLACKHOLE 存储引擎，使用默认编译项可编译BLACKHOLE，但是没有安装插件，需要手工安装。

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

mysql> show variables like 'plugin_dir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| plugin_dir    | /db/mysql/lib/plugin/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> system ls /db/mysql/lib/plugin/
adt_null.so    auth_test_plugin.so  ha_archive.so    ha_federated.so        qa_auth_client.so      semisync_master.so
auth.so        daemon_example.ini   ha_blackhole.so  libdaemon_example.so  qa_auth_interface.so  semisync_slave.so
auth_socket.so    debug             ha_example.so    mypluglib.so        qa_auth_server.so

mysql> INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

使用BLACKHOLE

创建BLACKHOLE表只会在数据库生成.frm表定义结构。

mysql> use test;
Database changed

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
Empty set (0.00 sec)

mysql> system ls /db/mysql/data/test
db.opt    test.frm

BLACKHOLE总结

• BLACKHOLE支持所有类型的索引
• BLACKHOLE 表不存储数据，如果复制基于SBR，语句可以记录并在从库执行；如果复制为RBR、MBR，UPDATE及DELETE操作将会跳过，不会记录也从库不执行。
• Insert触发器可以正常使用，Update、Delete触发器因为不存储数据不能触发，FOR EACH ROW 也不能触发。
• BLACKHOLE 表Auto Increment字段不会自动递增,也不保留自增字段的状态
• 结合复制replicate-do和replicate-ignore规则，可使用BLACKHOLE当做一个分发主服务器
• 可用来验证转储文件语法
• 测试binlog的开销量，通过对比 BLACKHOLE 与 不启动 binlog的性能
• 可能被用来查找与存储引擎自身不相关的性能瓶颈

参考：
http://dev.mysql.com/doc/refman/5.5/en/blackhole-storage-engine.html

整理自网络