【Mysql】MySQL5.7.17- Group Replication搭建

小亮520cl發表於2016-12-28
MySql
背景：
  1. 環境
  2. host          dbport
  3. 192.168.1.247 3307
    192.168.1.14 24802
    192.168.1.242 3307


1.配置hosts資訊

  1. 192.168.1.247 sh247
  2. 192.168.1.14 interface.test.haodai.com
  3. 192.168.1.242 sh242

2 第一臺資料庫操作

  1. 2.1修改配置檔案

  1. [client]
    port = 3307
    socket = /home/data/mydata/3307/mysql.sock

    [mysqld]
    port = 3307
    socket = /home/data/mydata/3307/mysql.sock
    basedir = /home/data/mysql
    datadir = /home/data/mydata/3307
    pid-file = /home/data/mydata/3307/mysql.pid
    ##group replication####
    server_id = 1
    gtid_mode =ON
    enforce_gtid_consistency = ON
    master_info_repository = TABLE
    relay_log_info_repository=TABLE
    binlog_checksum = NONE
    log_slave_updates = ON
    log_bin=binlog
    binlog_format=ROW


    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name = "77e497e2-c59a-11e6-9a68-525400159185"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address="192.168.1.247:34061"
    loose-group_replication_group_seeds="192.168.1.247:34061,192.168.1.14:34062,192.168.1.242:34063"
    loose-group_replication_bootstrap_group=off
    loose-group_replication_single_primary_mode=false
    loose-group_replication_enforce_update_everywhere_checks=TRUE

2.2 啟動資料庫進行配置

  1. SET SQL_LOG_BIN=0;
  2. CREATE USER rpl_user@'%';
  3. GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
  4. SET SQL_LOG_BIN=1;
  5. CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'
 


2.3 安裝引擎

    1. mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so' 
    2. mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+-------------+
| Name                       | Status   | Type               | Library              | License     |
+----------------------------+----------+--------------------+----------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |

(...)

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |
+----------------------------+----------+--------------------+----------------------+-------------+
      mysql> SET GLOBAL group_replication_bootstrap_group=ON;
      mysql> START GROUP_REPLICATION;
      mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
      mysql> SELECT * FROM performance_schema.replication_group_members;
      +---------------------------+--------------------------------------+-------------+-------------+--------------+
      | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
      +---------------------------+--------------------------------------+-------------+-------------+--------------+
      | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247       |        3307 | ONLINE       |
      +---------------------------+--------------------------------------+-------------+-------------+--------------+
      1 row in set (0.00 sec)


2.4插入一些模擬資料

  1. mysql> CREATE DATABASE test;
  2. Query OK, 1 row affected (0.00 sec)

  4. mysql> use test;
  5. Database changed
  6. mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
  7. Query OK, 0 rows affected (0.00 sec)

  9. mysql> INSERT INTO t1 VALUES (1, 'Luis');
  10. Query OK, 1 row affected (0.01 sec)






3 往叢集裡面新增資料庫例項

  1. 3.1修改第二臺資料庫配置檔案

    1. [client]
    2. port = 24802
    3. socket = /home/data/mydata/3307/mysql.sock


    6. [mysqld]
    7. port = 24802
    8. socket = /home/data/mydata/3307/mysql.sock
    9. basedir = /home/data/mysql
    10. datadir = /home/data/mydata/3307
    11. pid-file = /home/data/mydata/3307/mysql.pid
    12. ##group replication####
    13. server_id = 2
    14. gtid_mode =ON
    15. enforce_gtid_consistency = ON
    16. master_info_repository = TABLE
    17. relay_log_info_repository=TABLE
    18. binlog_checksum = NONE
    19. log_slave_updates = ON
    20. log_bin=binlog
    21. binlog_format=ROW

    23. transaction_write_set_extraction=XXHASH64
    24. loose-group_replication_group_name = "77e497e2-c59a-11e6-9a68-525400159185"
    25. loose-group_replication_start_on_boot=off
    26. loose-group_replication_local_address="192.168.1.14:34062"
    27. loose-group_replication_group_seeds="192.168.1.247:34061,192.168.1.14:34062,192.168.1.242:34063"
    28. loose-group_replication_bootstrap_group=off
    29. loose-group_replication_single_primary_mode=false
    30. loose-group_replication_enforce_update_everywhere_checks=TRUE

  1. 3.2啟動資料庫進行配置
    1. SET SQL_LOG_BIN=0;
    2. CREATE USER rpl_user@'%';
    3. GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
    4. SET SQL_LOG_BIN=1;
    5. CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'

  1. 3.3 安裝引擎，加入gp組
    1. mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    2. mysql> START GROUP_REPLICATION;
    3. mysql> SELECT * FROM performance_schema.replication_group_members;
    4. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
    5. | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
    6. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
    7. | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
    8. | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
    9. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
    10. 注意：與2操作相比少了兩個步驟，千萬別執行那兩個步驟！那兩個步驟是在搭建gp 初始化才需要執行的兩個步驟

  2. 3.4檢驗資料
    1. mysql> show databases;
    2. +--------------------+
    3. | Database |
    4. +--------------------+
    5. | information_schema |
    6. | mysql |
    7. | performance_schema |
    8. | sys |
    9. | test |
    10. +--------------------+
    11. 5 rows in set (0.00 sec)

    13. mysql> select * from test.t1;
    14. +----+------+
    15. | c1 | c2 |
    16. +----+------+
    17. | 1 | Luis |
    18. +----+------+
    19. 1 row in set (0.00 sec)


4 模擬資料庫down機
  1. 關閉247資料庫
  2. mysql> SELECT * FROM performance_schema.replication_group_members;
  3. +---------------------------+-----------+-------------+-------------+--------------+
  4. | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  5. +---------------------------+-----------+-------------+-------------+--------------+
  6. | group_replication_applier | | | NULL | OFFLINE |
  7. +---------------------------+-----------+-------------+-------------+--------------+
  8. 1 row in set (0.00 sec)

  10. mysql> start GROUP_REPLICATION;  ###重新開啟即可
  11. Query OK, 0 rows affected (3.50 sec)

  13. mysql> SELECT * FROM performance_schema.replication_group_members;
  14. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  15. | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  16. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  17. | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
  18. | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
  19. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  20. 2 rows in set (0.00 sec)

  22. mysql> stop GROUP_REPLICATION;
  23. Query OK, 0 rows affected (8.47 sec)

  25. mysql> SELECT * FROM performance_schema.replication_group_members;
  26. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  27. | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  28. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  29. | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | OFFLINE |
  30. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  31. 1 row in set (0.00 sec)

  33. mysql> start GROUP_REPLICATION;
  34. Query OK, 0 rows affected (5.49 sec)

  36. mysql> SELECT * FROM performance_schema.replication_group_members;
  37. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  38. | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  39. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  40. | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
  41. | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
  42. +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
  43. 2 rows in set (0.00 sec)

至此，資料庫叢集組搭建完畢！！！！未完待續！！！！


效能測試參考姜老師部落格：


實現原理與維護可參考acumg部落格
http://mp.weixin.qq.com/s/pBAOXW7Kx_EtdRVe4VaCYg

參考：
http://dev.mysql.com/doc/refman/5.7/en/group-replication-adding-instances.html


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2131537/，如需轉載，請註明出處，否則將追究法律責任。

相關文章