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
整理自网络
Svoid
2015-04-24
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29733787/viewspace-1604393/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29733787/viewspace-1604393/