マスター、
今回は現在進行形で遅延しているSQL文の調査方法と、
はじめに環境構築
MySQL5.6インストール
今回はマスター／
MySQL Yum Repositoryをインストールします。
wget http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm rpm -i mysql57-community-release-el6-8.noarch.rpm
デフォルトではMySQL5.
vim /etc/yum.repos.d/mysql-community.repo # Enable to use MySQL 5.6 [mysql56-community] name=MySQL 5.6 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/ enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql yum install mysql-community-server
レプリケーションを行うためにMySQLの設定ファイル
- バイナリログを有効にします。
- マスター側はserver-id=1、
スレーブ側はserver-id=2と追記します。
vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1
MySQLを起動します。
/etc/init.d/mysqld start
構成は以下の通りです。
| IP | MySQL PORT | master/ |
|---|---|---|
| 192. | 3306 | master |
| 192. | 3306 | slave |
レプリケーション設定
今回はMySQL5.
マスターの設定
まずはレプリケーション用のユーザと権限を設定します
mysql> CREATE USER 'repl'@'192.168.0.2' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';
そして、
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 439 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
スレーブの設定
スレーブ側でレプリケーションを開始します。
CHANGE MASTER TO MASTER_PORT= 3306, MASTER_HOST='192.168.0.1', MASTER_USER='repl', MASTER_PASSWORD='replication', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=439; mysql> START SLAVE; Query OK, 0 rows affected (0.01 sec)
| オプション値 | 値 | 説明 |
|---|---|---|
| MASTER_ | 3306 | マスターのポート番号 |
| MASTER_ | 192. | マスターのIPアドレス |
| MASTER_ | repl | レプリケーション用のユーザ |
| MASTER_ | replication | レプリケーション用のユーザのパスワード |
| MASTER_ | mysql-bin. | マスターで確認したバイナリログ |
| MASTER_ | 439 | マスターで確認したバイナリログポジション |
正常にレプリケーションが開始されているかは
- Slave_
IO_ Running - Slave_
SQL_ Running
mysql> SHOW SLAVE STATUS\G : : Slave_IO_Running: Yes Slave_SQL_Running: Yes : :
以上でレプリケーションの設定は完了です。
仮データの準備
マスターでデータベースを作成します。ここではreplというデータベースを作成しています。
mysql> create database repl; Query OK, 1 row affected (0.00 sec) mysql> use repl; Database changed
テーブルを作成します。
mysql> create table repl_test (id serial,id2 int ,tx text); Query OK, 0 rows affected (0.26 sec)
仮データを投入します。今回は簡単なプロシージャを作ってデータをインサートします。
delimiter //
DROP PROCEDURE IF EXISTS ins_repl_test//
CREATE PROCEDURE ins_repl_test ( num int)
BEGIN
DECLARE cnt int DEFAULT 1;
START TRANSACTION;
WHILE cnt <= num DO
INSERT INTO repl_test(id2,tx) values (mod(cnt,5),'TEST_TEXT');
SET cnt = cnt + 1;
END WHILE;
COMMIT;
END
//
delimiter ;
プロシージャの内容として、TEST_という文字列を挿入します。
プロシージャを実行して1000000行のデータを登録します。
mysql> CALL ins_repl_test(1000000); Query OK, 0 rows affected (8.96 sec)
CALL句の後に、
スレーブにデータがレプリケーションされていることを確認します。スレープでSELECT文を実行します。
mysql> select count(*) from repl_test; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.03 sec)
これで初期設定は完了です。
レプリケーション遅延調査方法
基本的にMySQLのレプリケーションは非同期のデータ転送のためマスターはスレーブが遅延しているかいないかは意識していません。 よって、
現在進行形で遅延しているSQL文の調査方法
現在進行形で遅延が発生しているか調べる方法として、
では、
mysql> delete from repl_test ; Query OK, 1000000 rows affected (12.92 sec)
上記実行中にスレーブのSeconds_
mysql> show slave status\G
Seconds_Behind_Master: 5
Seconds_
では、
mysql> show processlist; +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------+ | 1 | system user | | NULL | Connect | 3792 | Waiting for master to send event | NULL | | 2 | system user | | repl | Connect | 11 | updating | delete from repl_test | | 5 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------+
ここから、
過去に遅延したSQL文の調査方法
過去遅延していてすでに遅延が解消されていた場合に対象のSQL文を確認する方法をご紹介します。
- スレーブのMySQL設定ファイル
（my. cnf） にlog-slave-updatesを追記して有効化 - mysqlbinlogユーティリティーを使用してバイナリログから調査
という流れになります。スレーブのmy.
vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=2 log-slave-updates
log-slave-updatesを有効にすることで、
次にマスターで遅延を発生させる全件更新のUPDATE文を実行します。先ほどのテーブルは全件DELETEしてしまったので、
mysql> call ins_repl_test(5000000); mysql> update repl_test set id2=100; Query OK, 5000000 rows affected (1 min 22.70 sec) Rows matched: 5000000 Changed: 5000000 Warnings: 0
スレーブのSeconds_
mysql> show slave status\G
Seconds_Behind_Master: 0
ここからmysqlbinlogユーティリティを使用してスレーブのバイナリログから遅延を調査します。mysqlbinlogユーティリティとは、
# mysqlbinlog mysql-bin.000005 : : #160513 8:59:39 server id 1 end_log_pos 301 CRC32 0xd44db1e3 Query thread_id=4 exec_time=467 error_code=0 use `repl`/*!*/; SET TIMESTAMP=1463129979/*!*/; update repl_test set id2=100 /*!*/; # at 301 #160513 8:59:39 server id 1 end_log_pos 332 CRC32 0x4430c7d2 Xid = 7000266 COMMIT/*!*/; DELIMITER ; # End of log file : :
先ほど実行したUPDATE文の表示のある記述を抜き出しています。その中のexec_を確認します。このexec_
上の内容ではexec_となっており、
- 注意
障害などでレプリケーションが停止していた場合
（特にIO_ Thread） は、 もちろんexec_ timeが大きくなるので、 スレーブ遅延の調査時はレプリケーションが正しくされている状態であることは必須となります。 ［例］ IO_ Thread停止していた場合 スレーブでIO_
Thread停止 mysql> stop slave io_
thread; Query OK, 0 rows affected (0. 02 sec) マスターでinsert文
mysql> insert into repl_
test(id2,tx) values (888,'Iothread does not work'); Query OK, 1 row affected (0. 20 sec) 1分後、
スレーブでIO_ Threadを起動しバイナリログを確認すると、 以下のようになります。 #160607 3:21:52 server id 1 end_
log_ pos 774 CRC32 0x57c11014 Query thread_ id=13 exec_ time=87 error_ code=0 SET TIMESTAMP=1465269712/*!*/; insert into repl_ test(id2,tx) values (888,'Iothread does not work') insert自体は0.
02 secで完了していますが、 レプリケーションが停止していた分だけの時間も加算されているため、 exec_ time=87となってしまいます。
まとめ
現在のスレープの遅延は、