875
阿裏雲ECS通過docker配置MySQL--MGR
MySQL Group Replication（簡稱MGR）是mysql官方於2016年12月推出的一個全新的高可用與高擴展的解決方案。MySQL組複製提供了高可用、高擴展、高可靠的MySQL集群服務。
-
高一致性，基於原生複製及paxos協議的組複製技術，並以插件的方式提供，提供一致數據安全保證；
-
高容錯性，隻要不是大多數節點壞掉就可以繼續工作，有自動檢測機製，當不同節點產生資源爭用衝突時，不會出現錯誤，按照先到者優先原則進行處理，並且內置了自動化腦裂防護機製；
-
高擴展性，節點的新增和移除都是自動的，新節點加入後，會自動從其他節點上同步狀態，直到新節點和其他節點保持一致，如果某節點被移除了，其他節點自動更新組信息，自動維護新的組信息；
-
高靈活性，有單主模式和多主模式，單主模式下，會自動選主，所有更新操作都在主上進行；多主模式下，所有server都可以同時處理更新操作。
Docker 是一個開源的引擎，可以輕鬆的為任何應用創建一個輕量級的、可移植的、自給自足的容器。開發者在筆記本上編譯測試通過的容器可以批量地在生產環境中部署，包括VMs（虛擬機）、bare metal、OpenStack 集群和其他的基礎應用平台。
1.安裝Docker及配置MGR
從官方的docker store中拉取MySQL鏡像，大約隻需要幾分鍾時間，完畢以後，可以通過docker images命令來查看。
[root@iz2zec57gfl6i9vbtdksl1z ~]# yum install -y docker
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker pull mysql:5.7.17
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker images;
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 5.7.17 9546ca122d3a 4 months ago 406.9 MB
現在我們已經有了docker鏡像，然後我們設計如下的目錄結構，將三個數據庫的數據文件映射到宿主機。
# mkdir /Users/Kamus/mysql_data
# mkdir /Users/Kamus/mysql_data/s1-docker
# mkdir /Users/Kamus/mysql_data/s2-docker
# mkdir /Users/Kamus/mysql_data/s3-docker
由於目標是能運行MGR集群，那麼是有一部分數據庫初始化參數要額外設置的，而docker容器中的my.cnf內容無法改動，所以我們再設計一個專門的目錄用來存儲所有數據庫的my.cnf文件。
# mkdir /Users/Kamus/mysql_data/conf.d
# mkdir /Users/Kamus/mysql_data/conf.d/s1-docker
# mkdir /Users/Kamus/mysql_data/conf.d/s2-docker
# mkdir /Users/Kamus/mysql_data/conf.d/s3-docker
網絡網段的設置，隻能使用用戶指定的IP網段
[root@iz2zec57gfl6i9vbtdksl1z mysql_data]# docker network ls
NETWORK ID NAME DRIVER SCOPE
02cf0f7c8806 bridge bridge local
74ea9186efd3 host host local
d25ca440e9f6 none null local
[root@iz2zec57gfl6i9vbtdksl1z mysql_data]# docker run --detach --memory=500m --memory-swap=1g --hostname=mysql-mgr-server1 --net=bridge --ip=172.18.0.2 --add-host mysql-mgr-server1:172.18.0.3 --add-host mysql-mgr-server3:172.18.0.4 --publish 3307:3306 --volume=/mnt/mysql_data/conf.d/s1-docker/:/etc/mysql/conf.d --volume=/mnt/mysql_data/s1-docker:/var/lib/mysql --name=s1-docker -d docker.io/mysql:5.7.17
33b5e2660b080a74f056ad50a061b56d53aeede8b3cbb3673712cc018046e705
/usr/bin/docker-current: Error response from daemon: User specified IP address is supported on user defined networks only.
[root@iz2zec57gfl6i9vbtdksl1z mysql_data]# docker network create --subnet=172.19.0.0/16 b1
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker network ls
NETWORK ID NAME DRIVER SCOPE
3e9fad4158b4 b1 bridge local
02cf0f7c8806 bridge bridge local
74ea9186efd3 host host local
d25ca440e9f6 none null local
因為我們設計了容器啟動時候會擁有不同的IP地址，因此在容器中運行的MySQL實例的初始化參數中關於MGR的部分也需要指定這些IP地址。以下是my.cnf文件的設置內容。
[mysqld]
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
port=3306
socket=/var/run/mysqld/mysqld.sock
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog-format=ROW
binlog_checksum=NONE
log-slave-updates=1
log_bin=binlog
relay-log=bogon-relay-bin
# Group Replication
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '172.19.0.2:33061'
loose-group_replication_group_seeds ='172.19.0.2:33061,172.19.0.3:33061,172.19.0.4:33061'
loose-group_replication_bootstrap_group = off
對於第一個容器s1-container會使用到的my.cnf文件，這個參數為：
loose-group_replication_local_address="172.19.0.1:33061"
對於第二個容器s2-container會使用到的my.cnf文件，這個參數為：
loose-group_replication_local_address="172.19.0.3:33061"
對於第三個容器s3-container會使用到的my.cnf文件，這個參數為：
loose-group_replication_local_address="172.19.0.4:33061"
2.啟動docker鏡像有關mysql容器：
運行三個docker容器的完整命令如下：
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker run --detach --memory=500m --memory-swap=1g --hostname=mysql-mgr-server3 --net=b1 --ip=172.19.0.4 --add-host mysql-mgr-server1:172.19.0.2 --add-host mysql-mgr-server2:172.19.0.3 --publish 3309:3306 --volume=/mnt/mysql_data/conf.d/s3-docker/:/etc/mysql/conf.d --volume=/mnt/mysql_data/s3-docker:/var/lib/mysql --name=s3-docker -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/mysql:5.7.17
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker run --detach --memory=500m --memory-swap=1g --hostname=mysql-mgr-server2 --net=b1 --ip=172.19.0.3 --add-host mysql-mgr-server1:172.19.0.2 --add-host mysql-mgr-server3:172.19.0.4 --publish 3308:3306 --volume=/mnt/mysql_data/conf.d/s2-docker/:/etc/mysql/conf.d --volume=/mnt/mysql_data/s2-docker:/var/lib/mysql --name=s2-docker -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/mysql:5.7.17
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker run --detach --memory=500m --memory-swap=1g --hostname=mysql-mgr-server1 --net=b1 --ip=172.19.0.2 --add-host mysql-mgr-server1:172.19.0.3 --add-host mysql-mgr-server3:172.19.0.4 --publish 3307:3306 --volume=/mnt/mysql_data/conf.d/s1-docker/:/etc/mysql/conf.d --volume=/mnt/mysql_data/s1-docker:/var/lib/mysql --name=s1-docker -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/mysql:5.7.17
查看是否啟動mysql成功：
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
30396bcd7ffb docker.io/mysql:5.7.17 "docker-entrypoint.sh" 7 hours ago Up 7 hours 0.0.0.0:3309->3306/tcp s3-docker
6644ab2fa9ae docker.io/mysql:5.7.17 "docker-entrypoint.sh" 7 hours ago Up 7 hours 0.0.0.0:3308->3306/tcp s2-docker
7ca53c437c5a docker.io/mysql:5.7.17 "docker-entrypoint.sh" 8 hours ago Up 8 hours 0.0.0.0:3307->3306/tcp s1-docker
報錯時在宿主機查看日誌：
[root@iz2zec57gfl6i9vbtdksl1z conf.d]# docker logs <CONTAINER ID>
通過如下命令登錄到Docker容器的操作係統中，再進入MySQL實例，啟動MGR。我們目前設置的是Single Primary模式的MGR，先啟動第一個Primary實例。
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker exec -it s1-docker bash
root@mysql-mgr-server1:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| group_replication_applier | d79c04bf-87a3-11e7-865f-0242ac130002 | mysql-mgr-server1 | 3306 | ONLINE
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>
啟動第二個隻讀實例：
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker exec -it s2-docker bash
root@mysql-mgr-server2:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.52 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| group_replication_applier | d79c04bf-87a3-11e7-865f-0242ac130002 | mysql-mgr-server1 | 3306 | ONLINE |
| group_replication_applier | f7127f84-87a5-11e7-97af-0242ac130003 | mysql-mgr-server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
2 rows in set (0.00 sec)
啟動第三個隻讀實例：
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker exec -it s3-docker bash
root@mysql-mgr-server3:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
| group_replication_applier | d79c04bf-87a3-11e7-865f-0242ac130002 | mysql-mgr-server1 | 3306 | ONLINE |
| group_replication_applier | f7127f84-87a5-11e7-97af-0242ac130003 | mysql-mgr-server2 | 3306 | ONLINE |
| group_replication_applier | feeee99d-87a5-11e7-97cb-0242ac130004 | mysql-mgr-server3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+
3 rows in set (0.00 sec)
start group_replication可能一些問題
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
解決辦法：回到宿主機中查看錯誤日誌：
[root@iz2zec57gfl6i9vbtdksl1z ~]# docker logs -f -t --tail=10 <CONTAINER ID >
2017-08-23T02:24:51.177878000Z 2017-08-23T02:24:51.175681Z 0 [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 172.19.0.4 refused. Address is not in the IP whitelist.'
2017-08-23T02:24:51.178024000Z 2017-08-23T02:24:51.175721Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
2017-08-23T02:24:51.178176000Z 2017-08-23T02:24:51.175728Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_exit'
2017-08-23T02:24:51.178312000Z 2017-08-23T02:24:51.175798Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2017-08-23T02:24:52.209910000Z 2017-08-23T02:24:52.209376Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2017-08-23T02:25:51.176542000Z 2017-08-23T02:25:51.175539Z 3 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2017-08-23T02:25:51.176898000Z 2017-08-23T02:25:51.175647Z 3 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2017-08-23T02:25:51.177142000Z 2017-08-23T02:25:51.175676Z 3 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2017-08-23T02:25:51.179057000Z 2017-08-23T02:25:51.175896Z 3 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2017-08-23T02:25:51.179354000Z 2017-08-23T02:25:51.175905Z 3 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2017-08-23T02:25:51.179567000Z 2017-08-23T02:25:51.177979Z 15 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2017-08-23T02:25:51.185216000Z 2017-08-23T02:25:51.184778Z 12 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
解決：
mysql> set global group_replication_ip_whitelist='172.19.0.0/24' ;
Query OK, 0 rows affected (0.00 sec)
問題2：
2017-08-23T02:29:34.592161000Z 2017-08-23T02:29:34.590561Z 0 [Note] Plugin group_replication reported: 'state 4277 action xa_complete'
2017-08-23T02:29:34.592311000Z 2017-08-23T02:29:34.590696Z 0 [Note] Plugin group_replication reported: 'new state x_run'
2017-08-23T02:29:35.691341000Z 2017-08-23T02:29:35.689373Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: feeee99d-87a5-11e7-97cb-0242ac130004:1-5 > Group transactions: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-8,
2017-08-23T02:29:35.691504000Z d79c04bf-87a3-11e7-865f-0242ac130002:1-5,
2017-08-23T02:29:35.691652000Z f7127f84-87a5-11e7-97af-0242ac130003:1-5'
2017-08-23T02:29:35.691806000Z 2017-08-23T02:29:35.689397Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2017-08-23T02:29:35.691957000Z 2017-08-23T02:29:35.689401Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
解決：
mysql> show variables like 'group_replication_allow_local_disjoint_gtids_join';
+---------------------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------------------+-------+
| group_replication_allow_local_disjoint_gtids_join | OFF |
+---------------------------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.12 sec)
測試：
在s1-docker創建庫test，表test1
mysql> create database test
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table test1 (id int primary key,name varchar(12));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test1 values (123,'abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+-----+------+
| id | name |
+-----+------+
| 111 | aaa |
+-----+------+
2 rows in set (0.00 sec)
mysql>
在s2-docker查看是否同步：
root@mysql-mgr-server2:/var/lib/mysql# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
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 test1;
+-----+------+
| id | name |
+-----+------+
| 111 | aaa |
+-----+------+
2 rows in set (0.00 sec)
在s3-docker查看是否同步：
root@mysql-mgr-server3:/var/lib/mysql# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 119
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
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 test1;
+-----+------+
| id | name |
+-----+------+
| 111 | aaa |
+-----+------+
2 rows in set (0.00 sec)
主要參考mysql官方文檔：
部分參考文檔來源：
最後更新：2017-08-23 20:02:43