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]>

replication_filter

フィルターの設定とマルチスレッドスレーブが反映されているか確認

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]> 

マルチスレッドスレーブ
slave_para
フィルター
do_db

■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]> 

master_Slave

■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]> 

Show_full_Process_List

参考:
13.4.2.2 CHANGE REPLICATION FILTER Syntax

MySQLのセミナーなどで、デモも含めてご紹介させて頂いているので参加して頂くのも良いかもしれません。
セミナーサイト
http://events.oracle.com/search/search?start=1&pageHitCount=10&group=Events&keyword=japan=

Comments are closed.

Post Navigation