MySQLのBLACKHOLEストレージエンジンを検証する


## はじめに

MySQLのBLACKHOLEストレージエンジンの動作を検証してみます。

## 環境

* CentOS 6.5
* MySQL 5.6.21

## MySQLのインストール

```shell
$ curl -LO http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum localinstall mysql-community-release-el6-5.noarch.rpm
$ sudo yum clean all
$ yum repolist
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: www.ftp.ne.jp
 * extras: www.ftp.ne.jp
 * updates: www.ftp.ne.jp
repo id                                     repo name                                   status
base                                        CentOS-6 - Base                             6,367
extras                                      CentOS-6 - Extras                              15
mysql-connectors-community                  MySQL Connectors Community                     11
mysql-tools-community                       MySQL Tools Community                          15
mysql56-community                           MySQL 5.6 Community Server                     95
updates                                     CentOS-6 - Updates                          1,549
repolist: 8,052

$ sudo yum install mysql-community-server.x86_64
$ sudo /etc/init.d/mysqld start
$ /usr/bin/mysql_secure_installation
```

## BLACKHOLEストレージエンジンを検証する

### 単体で動作させてみる

```mysql
mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE sample;
Database changed

mysql> CREATE TABLE tbl001 (id int , name varchar(32)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW CREATE TABLE tbl001\G
*************************** 1. row ***************************
       Table: tbl001
Create Table: CREATE TABLE `tbl001` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into tbl001 values (1, 'Java');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl001 values (2, 'Ruby');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl001 values (3, 'Python');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from tbl001;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select * from tbl001 where id = 1;
Empty set (0.00 sec)

mysql> update tbl001 set name = Java8 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> delete from tbl001 where id = 2;
Query OK, 0 rows affected (0.00 sec)
```

### レプリケーション構成で動作させてみる

レプリケーションの設定

* マスタ側

レプリケーションユーザの作成

```mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql-salve' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
```

レプリケーションの設定追加

```ini:/etc/my.cnf
[mysqld]
...(省略)...
log-bin=mysql-bin
server-id=1
```

設定を反映するため、MySQLを再起動します。

```shell
$ sudo /etc/init.d/mysqld restart
```

mysqldumpによるバックアップを取得します。

```mysql
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)  
```

別のセッションで、mysqldumpを実行します。

```shell
$ mysqldump -u root -p --master-data sample > sample.dump
```

テーブルロックを解放します。

```mysql
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
```

* スレーブ側

レプリケーションの設定を追加します。

```ini:/etc/my.cnf
[mysqld]
...(省略)...
server-id=2
```

設定を反映させるため、MySQLを再起動します。

```shell
$ sudo /etc/init.d/mysqld restart
```

マスタで取得したバックアップをスレーブでリストアします。

```mysql
mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)
```

```shell
$ mysql -u root -p sample < sample.dump
```

```mysql
mysql> CHANGE MASTER TO MASTER_HOST = 'mysql-master', MASTER_USER = 'repl', MASTER_PASSWORD = 'slavepass';
Query OK, 0 rows affected, 2 warnings (0.22 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 457
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 121235dc-4726-11e4-9824-52540079f136
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
```

スレーブ側のtbl001テーブルのストレージエンジンをinnodbの変更して動作を確認します。

```mysql
mysql> ALTER TABLE sample.tbl001 ENGINE InnoDB;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE sample.tbl001\G
*************************** 1. row ***************************
       Table: tbl001
Create Table: CREATE TABLE `tbl001` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
```

マスタ側でレコードを登録します。

```mysql
mysql> insert into sample.tbl001 values (4, 'PHP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample.tbl001 values (5, 'Perl');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sample.tbl001 values (6, 'C+');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from sample.tbl001;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
```

スレーブ側でテーブルの状態を確認します。

```mysql
mysql> select count(*) from sample.tbl001;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select * from sample.tbl001;
+------+------+
| id   | name |
+------+------+
|    4 | PHP  |
|    5 | Perl |
|    6 | C+   |
+------+------+
3 rows in set (0.00 sec)
```

マスタ側でレコードの更新・削除を行います。

```mysql
mysql> update sample.tbl001 set name = 'PHP5' where name = 'PHP';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> delete from sample.tbl001 where id = 5;
Query OK, 0 rows affected (0.00 sec)
```

スレーブ側でテーブルの状態を確認します。

```mysql
mysql> select count(*) from sample.tbl001;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select * from sample.tbl001;
+------+------+
| id   | name |
+------+------+
|    4 | PHP5 |
|    6 | C+   |
+------+------+
2 rows in set (0.00 sec)

```

## 参考

* [15.6 The BLACKHOLE Storage Engine](http://dev.mysql.com/doc/refman/5.6/en/blackhole-storage-engine.html)
* [Chapter 17 Replication](http://dev.mysql.com/doc/refman/5.6/en/replication.html)
* [13.4.2.1 CHANGE MASTER TO Syntax](http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html)