はじめに
MySQLのBLACKHOLEストレージエンジンの動作を検証してみます。
環境
- CentOS 6.5
- MySQL 5.6.21
MySQLのインストール
$ 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> 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> 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)
レプリケーションの設定追加
/etc/my.cnf
[mysqld]
...(省略)...
log-bin=mysql-bin
server-id=1
設定を反映するため、MySQLを再起動します。
$ sudo /etc/init.d/mysqld restart
mysqldumpによるバックアップを取得します。
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
別のセッションで、mysqldumpを実行します。
$ mysqldump -u root -p --master-data sample > sample.dump
テーブルロックを解放します。
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
- スレーブ側
レプリケーションの設定を追加します。
/etc/my.cnf
[mysqld]
...(省略)...
server-id=2
設定を反映させるため、MySQLを再起動します。
$ sudo /etc/init.d/mysqld restart
マスタで取得したバックアップをスレーブでリストアします。
mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)
$ mysql -u root -p sample < sample.dump
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> 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> 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> 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> 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> 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)