Database JUNKY

MySQL,MariaDBを中心としたブログです

トップ > BLACKHOLE > MySQL レプリケーションとブラックホール化

MySQL レプリケーションとブラックホール化

BLACKHOLE Engines MySQL

MySQLレプリケーション。。すっごく簡単なのはわかっているのですが、いざやろうとすると、あれ？どうやるんだっけ？ といつも悩んでしまうので、他サイトにいっぱい掲載されてることを知りつつも、こちらにも証跡として残しておこうと思います。 あと、先日、某BLOGサイトの方が、MySQLエンジンのBLACKHOLE(ブラックホールエンジン）の活用の方法をつぶやいており おぉこれは、いける使い方だ！！なんてものも発見できたので、それもあわせて証跡に残しました。

証跡がすっごく長いのでBACKHOLEエンジンを利用した、レプリケーションの設定だけを見たい方は、ずーーーーっと下のほうまでブラウザをスクロールしてみてください。（ちょこっとしか書いていませんが）

<検証環境>

以下の環境で、構築、および実装しました。

マスター os : CentOS release 5.5 (Final) ipaddr: 192.168.101.81 mysqlのバージョン： 5.1.52-log

スレーブ os : CentOS release 5.5 (Final) ipaddr: 192.168.101.61 mysqlのバージョン： 5.1.52-log

その他、補足事項 ・データベースは、mydbというサンプルデータベースを作成し、検証しました。

<レプリケーション環境の構築>

<<マスター側>> ・MySQL 停止 /etc/init.d/mysqld stop

・/etc/my.cnf に以下を追記

replication config

server-id=1 log-bin=mysql-bin log-bin-index=mysql-bin relay-log=relay-bin relay-log-index=relay-bin log-slave-updates

・MySQL 開始 /etc/init.d/mysqld start

<<スレーブ側>> ・MySQL 停止 /etc/init.d/mysqld stop

・/etc/my.cnf に以下を追記

replication config

server-id=2 log-bin=mysql-bin log-bin-index=mysql-bin relay-log=relay-bin relay-log-index=relay-bin log-slave-updates ★レプリケーション環境構築作業

<<マスター側>>

mysql -u root -p

・レプリケーション専用ユーザーの作成を行う

mysql> GRANT REPLICATION SLAVE ON . TO repl@'%' IDENTIFIED BY 'repl';

・他から書き込みがないようテーブルをすべてロックする

mysql> FLUSH TABLES WITH READ LOCK;

mysql> exit

・データディレクトリを丸ごと圧縮コピーする

cd /var/lib/mysql/

tar zcvf /tmp/db01_mysql.tar.gz ./*

・圧縮したデータを、スレーブに、scpで転送する

scp -p /tmp/db01_mysql.tar.gz 192.168.101.62:/tmp

・一旦ログをフラッシュする

mysql -u root -p

mysql> FLUSH LOGS; Query OK, 0 rows affected (0.02 sec)

・マスターサーバのステータスを確認する、この際に、File および ポジションをメモする

mysql> SHOW MASTER STATUS; + + -+ + + | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | + + -+ + + | mysql-bin.000003 |      106 |              |                  | + + -+ + + 1 row in set (0.00 sec)

<<スレーブ側>>

・データディレクトリを念のため、すべて削除する（ここの環境での話しなのでスレーブ側で環境が違う人は、注意）

cd /var/lib/mysql/

ll

合計 20540 -rw-rw - 1 mysql mysql  5242880 11月 12 01:43 ib_logfile0 -rw-rw - 1 mysql mysql  5242880 11月  9 17:57 ib_logfile1 -rw-rw - 1 mysql mysql 10485760 11月 12 01:43 ibdata1 drwx   2 mysql mysql     4096 11月  9 17:57 mysql

rm ./* -rf

ll

合計 0

・上記で、転送されてきたデータを解凍する

tar zxvf /tmp/db01_mysql.tar.gz

/etc/init.d/mysqld start

・レプリケーションの設定、下記にて、MASTER_LOG_FILEとMASTER_LOG_POSに関して、メモをとっておいたものを記載する

mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST='192.168.101.81', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=106;

<<マスター側>>

・上記でロックしたテーブル群をアンロックする

mysql -u root -p

mysql> UNLOCK TABLES; mysql> SHOW MASTER STATUS; + + -+ + + | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | + + -+ + + | mysql-bin.000003 |      106 |              |                  | + + -+ + + 1 row in set (0.00 sec)

mysql> exit

<<スレーブ側>>

・スレーブサーバを開始する。

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

mysql> SHOW SLAVE STATUS\G; 1. row Slave_IO_State: Waiting for master to send event Master_Host: 192.168.101.81 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 106 Relay_Log_File: relay-binrelay-log-index=relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes →ここが、yesになっていることを確認 Slave_SQL_Running: Yes →ここが、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: 106 Relay_Log_Space: 425 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: 1 row in set (0.01 sec)

ERROR: No query specified

★動作検証 <<マスター側>> mysql> use mydb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> show tables; + + | Tables_in_mydb   | + + | test             | | zip              | + + 6 rows in set (0.00 sec)

mysql> select * from test; + + -+ | id   | value | + + -+ |    1 | test1 | + + -+ 1 row in set (0.00 sec)

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

mysql> select * from test; + + -+ | id   | value | + + -+ |    1 | test1 | |    2 | test2 | + + -+ 2 rows in set (0.00 sec)

→ id=2 が登録されたことを確認

<<スレーブ側>>

mysql> use mydb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select * from test; + + -+ | id   | value | + + -+ |    1 | test1 | |    2 | test2 | + + -+ 2 rows in set (0.00 sec)

→id=2 が追加されているのを確認 以上で、レプリケーション環境が構築できたとし、終了

<testテーブルをブラックホール化>

こんなイメージで、上記DB内に格納されているtestテーブルを、マスタのみBLACKHOLE化します。 手順を間違えちゃうと、マスタ側の物理データが消えちゃうので注意してくださいね。

上記とおり、BLACKHOLEエンジンを利用したテーブルは、物理的にデータは何ももっておりません。当然SELECT分を発行しても、データの戻りはありません。じゃあ、どこに活用できるの？に関しては後述します。

以下作業の証跡です

<<マスター側>>

mysql mydb -u root -p

mysql> alter table test rename to test_saved; Query OK, 0 rows affected (0.00 sec)

mysql> create table test like test_saved; Query OK, 0 rows affected (0.01 sec)

mysql> alter table test engine=BlackHole; Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables; + + | Tables_in_mydb   | + + | test             | | test_saved       | | zip              | + + 7 rows in set (0.00 sec)

mysql> show create table test; + -+ -+ | Table | Create Table                                                                                                               | + -+ -+ | test  | CREATE TABLE test ( id int(11) DEFAULT NULL, value char(10) DEFAULT NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 | + -+ -+ 1 row in set (0.00 sec) →エンジンが、BLACKHOLEになっていることを確認

<<スレーブ側>>

mysql mydb -u root -p

mysql> show tables; + + | Tables_in_mydb   | + + | test             | | test_saved       | | zip              | + + 7 rows in set (0.00 sec)

mysql> show create table test; + -+ -+ | Table | Create Table                                                                                                               | + -+ -+ | test  | CREATE TABLE test ( id int(11) DEFAULT NULL, value char(10) DEFAULT NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 | + -+ -+ 1 row in set (0.00 sec)

mysql> drop table test; Query OK, 0 rows affected (0.00 sec)

mysql> alter table test_saved rename to test; Query OK, 0 rows affected (0.00 sec)

mysql> show tables; + + | Tables_in_mydb   | + + | test             | | zip              | + + 6 rows in set (0.00 sec)

mysql> show create table test; + -+ -+ | Table | Create Table                                                                                                            | + -+ -+ | test  | CREATE TABLE test ( id int(11) DEFAULT NULL, value char(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | + -+ -+ 1 row in set (0.00 sec) →エンジンが、MyISAMになっていることを確認

mysql> select * from test; + + -+ | id   | value | + + -+ |    1 | test1 | |    2 | test2 | + + -+ 2 rows in set (0.00 sec)

<<マスター側>>

mysql mydb -u root -p

mysql> select * from test; Empty set (0.00 sec) →BLACKHOLEエンジンのため、登録は反映されないことを確認

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

mysql> select * from test; Empty set (0.00 sec) →BLACKHOLEエンジンのため、登録は反映されないことを確認

<<スレーブ側>> mysql> select * from test; + + -+ | id   | value | + + -+ |    1 | test1 | |    2 | test2 | |    3 | test3 | + + -+ 3 rows in set (0.00 sec) →設定が反映されていることを確認

無事、無事ブラックホールテーブルのレプリケーションは完了しました、ブラックホール化する利点については、つまるところ 更新処理がものすごーく早くなるってところですかね？ 事実上エンジンにBLACKHOLEエンジンを利用しているテーブルには なーんにもデータは書き込まれない（/dev/null）、ただ、ログは書くので、スレーブには、ちゃんとレプリケーションされるみたいな 感じです。あわせて、ログをベースに、レプリケーションされるので、実際のデータ登録に関しては、非同期で行われることによって マスタ側の負荷が、大幅に軽減されるということだと思います。BLACKHOLEエンジン自体では何もできませんが、レプリケーションを かますことで、更新処理のキューイングが行えます。

MySQLの負荷分散って、参照に関しては、KeepAlivedと、レプリケーションの組み合わせで、実績を作っている企業さんが 多いのですが、こと、更新系の負荷分散については、あまり進んでいないのではないかというのが現状です。実際更新を負荷分散 させるためには、MySQL-Clusterとか、最近では、SPIDERエンジンを利用してなんて方法があると思いますが、単に更新の負荷を 軽減するのが目的であれば、クラスタリングせずに、マスタデータベースの、更新スループットを改善せるっていう手もこのBLACKHOLEエンジンで 実現できちゃったりするのです。次回、どれくらい、大量更新の処理時間が軽減されるのか？を検証したいと思います。

とまあ、いいことばかり書きましたが、たとえば、マスタデータベースがダウンした場合、どうリカバリすれば良いの？（データないし。。）とか、リカバリ設計はちゃんと使うのであれば、ちゃんと考えなければいけないし、また、データの超即時反映を考える場合は、レプリケーションと、BLACKHOLEだけでは、足りないですね。数秒のデータの誤差では運用上問題ないケースであれば必要ないかと思いますが。

そこのあたりの情報元については、前述した、某ブログサイト様が、掲載しております。そもそお、このネタ元も、その某ブログサイト様です。 http://tech.dclog.jp/2010/11/decologmysql-blackhole.html

このBLACKHOLEエンジン、ECサイトのような、ミッションクリティカルなシステムは向かないとは思うのですがつぶやくとか、 イイネするとか、コメントをするとか、そのような用途には、ばっちりマッチすると思います。決済トランザクションとか、頻繁に発生する処理は向かないだろ？とかが心配な方は、それだけ儲かっている証拠でもありますので、商用DB(Oracleとか、DB2とか）をちゃんと買ってやったほうが得策だと思いますよ！！