![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL5.7にてレプリケーションに新たなオプションが加わったので、
ここで軽くご紹介させて頂きます。
今回は、以下の2点のみ検証してみました。
1. マルチスレッドスレーブ(同一スキーマ）
– 全体的なパフォーマンス次第ですが、トランザクションを並列して実行可能。
レプリケーションのパフォーマンスが向上します。
2. 動的フィルタリング
– オンライン処理増えると、メンテンナンス時間の制限が減らせてますね。
検証、MySQL Version
root@localhost [REPLI]> select @@version; +--------------+ | @@version | +--------------+ | 5.7.7-rc-log | +--------------+
以下は、また時間見てご紹介させて頂きます。
■ SEMI-SYNC（準同期)に関しては、Loss-Lessが選択できるようになりました。
AFTER_SYNCかAFTER_COMMITを選択
SET rpl_semi_sync_master_wait_point= [AFTER_SYNC|AFTER_COMMIT]
MySQL5.7.2で準同期が改良され、データ同期時にマスター障害が発生しても
データ損失が発生しない、 Loss-less 準同期レプリケーションに改良されました。
[AFTER_SYNC = Loss Less]
■ ACK待ちするスレーブの数(Default = 1)の変更
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N [Default = 1]
■ GTIDのローリングアップグレード
MySQL 5.7.6でGTIDのローリング有効化ができるようになったので、システム全体を一度にシャットダウンしなくてもOK
GTIDのローリングアップグレードに関しては、こちらのサイトが参考になります。
http://yoku0825.blogspot.jp/2015/03/mysql-576gtidok.html
1) My.cnfの変更（マスター＆スレーブ）
こちらは、従来通りserver_id,log_bin,gtid-mode, enforce-gtid-consistency,
log-slave-updates,master_info_repository,relay_log_info_repository,relay_log_recoveryなどを設定しています。
2) アカウント作成
root@localhost [(none)]> CREATE USER 'repl_user'@'192.168.56.109' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.56.109'; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]>
3）バックアップとコピー
今回は、検証用のREPLI DBのみ対象なのでREPLIのみダンプしてコピー&リストアしてます。
[root@misc01 admin]# /usr/local/mysql/bin/mysqldump -uroot -p --databases REPLI --single-transaction --triggers --routines --events > GTID_REPLI.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@misc01 admin]# scp GTID_REPLI.sql admin@192.168.56.109:/home/admin/ The authenticity of host '192.168.56.109 (192.168.56.109)' can't be established. ECDSA key fingerprint is 34:a2:3f:a8:a5:16:6a:35:1c:d6:9c:5f:30:1e:c8:79. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.109' (ECDSA) to the list of known hosts. admin@192.168.56.109's password: GTID_REPLI.sql 100% 2705 2.6KB/s 00:00 [root@misc01 admin]#
4）スレーブにてデータリストア
5）スレーブの設定
※スレーブをスタートする前に、
ここでは、REPLICATION FILTER,SLAVE_PARALLEL_TYPE,SLAVE_PARALLEL_WORKERS
などを設定変更してスレーブをスタートしています。
root@localhost [REPLI]> change master to
-> master_host = '192.168.56.113',
-> master_port=3306,
-> master_user='repl_user',
-> master_password='password',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
root@localhost [REPLI]> CHANGE REPLICATION FILTER REPLICATE_DO_DB=(REPLI);
Query OK, 0 rows affected (0.00 sec)
root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)
root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_WORKERS=5;
Query OK, 0 rows affected (0.01 sec)
root@localhost [REPLI]> start slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost [REPLI]>
フィルターの設定とマルチスレッドスレーブが反映されているか確認
root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.113
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 822
Relay_Log_File: misc02-relay-bin.000002
Relay_Log_Pos: 1035
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: REPLI
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: 822
Relay_Log_Space: 1243
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: 2dde009f-d4dc-11e4-b437-0800279cea3c
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:1-3
Executed_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
1 row in set (0.01 sec)
root@localhost [REPLI]> show variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name | Value |
+------------------------------+-----------------------+
| init_slave | |
| log_slave_updates | ON |
| log_slow_slave_statements | OFF |
| pseudo_slave_mode | OFF |
| rpl_stop_slave_timeout | 31536000 |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 5 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+------------------------------+-----------------------+
23 rows in set (0.01 sec)
root@localhost [REPLI]>
※ 動的に設定した、replicate-do-db, slave_parallel_type, slave_parallel_workersは,
Instanceの再起動で消えてしまうので必要に応じてオプションファイルに追加設定する必要があります。
root@localhost [REPLI]> system cat /etc/my.cnf | grep replicate-do-db replicate-do-db=REPLI root@localhost [REPLI]>
マルチスレッドスレーブ
フィルター
■INSERTの確認
マスター
root@localhost [REPLI]> select @@hostname; +------------+ | @@hostname | +------------+ | misc01 | +------------+ 1 row in set (0.00 sec) root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5; +-------+-------------------------+----------------------------+ | id | n_time | s_time | +-------+-------------------------+----------------------------+ | 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 | | 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 | | 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 | | 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 | | 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 | +-------+-------------------------+----------------------------+ 5 rows in set (0.01 sec) root@localhost [REPLI]> INSERT INTO T_REPLI01(n_time,s_time) values(now(3),sysdate(6)); Query OK, 1 row affected (0.00 sec) root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5; +-------+-------------------------+----------------------------+ | id | n_time | s_time | +-------+-------------------------+----------------------------+ | 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 | | 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 | | 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 | | 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 | | 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 | +-------+-------------------------+----------------------------+ 5 rows in set (0.00 sec) root@localhost [REPLI]>
Slave
root@localhost [REPLI]> select @@hostname; +------------+ | @@hostname | +------------+ | misc02 | +------------+ 1 row in set (0.00 sec) root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5; +-------+-------------------------+----------------------------+ | id | n_time | s_time | +-------+-------------------------+----------------------------+ | 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 | | 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 | | 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 | | 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 | | 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 | +-------+-------------------------+----------------------------+ 5 rows in set (0.00 sec) root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5; +-------+-------------------------+----------------------------+ | id | n_time | s_time | +-------+-------------------------+----------------------------+ | 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 | | 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 | | 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 | | 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 | | 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 | +-------+-------------------------+----------------------------+ 5 rows in set (0.00 sec) root@localhost [REPLI]>
■Processlistでスレッドの確認
root@localhost [REPLI]> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 3 | root | localhost | REPLI | Query | 0 | starting | show processlist | | 9 | system user | | NULL | Connect | 3775 | Waiting for master to send event | NULL | | 10 | system user | | NULL | Connect | 115 | Slave has read all relay log; waiting for more updates | NULL | | 11 | system user | | NULL | Connect | 115 | Waiting for an event from Coordinator | NULL | | 12 | system user | | NULL | Connect | 416 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 416 | Waiting for an event from Coordinator | NULL | | 14 | system user | | NULL | Connect | 416 | Waiting for an event from Coordinator | NULL | | 15 | system user | | NULL | Connect | 416 | Waiting for an event from Coordinator | NULL | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ 8 rows in set (0.00 sec) root@localhost [REPLI]> select thd_id,conn_id,user,db,command,current_statement from sys.processlist where user = 'sql/slave_worker'; +--------+---------+------------------+------+---------+-------------------+ | thd_id | conn_id | user | db | command | current_statement | +--------+---------+------------------+------+---------+-------------------+ | 34 | 12 | sql/slave_worker | NULL | Connect | NULL | | 35 | 13 | sql/slave_worker | NULL | Connect | NULL | | 36 | 14 | sql/slave_worker | NULL | Connect | NULL | | 37 | 15 | sql/slave_worker | NULL | Connect | NULL | | 33 | 11 | sql/slave_worker | NULL | Connect | NULL | +--------+---------+------------------+------+---------+-------------------+ 5 rows in set (0.09 sec) root@localhost [REPLI]>
参考：
13.4.2.2 CHANGE REPLICATION FILTER Syntax
MySQLのセミナーなどで、デモも含めてご紹介させて頂いているので参加して頂くのも良いかもしれません。
セミナーサイト
http://events.oracle.com/search/search?start=1&pageHitCount=10&group=Events&keyword=japan=