MySQL MGR：3 Nodes
开始本文的前提：
- 【3】台，RHEL 7.6的Linux服务器
- 每台服务器，安装好MySQL【5.7.26】；MySQL数据库的安装步骤，在本文中不重复赘述
本文将呈现【3】节点的【MySQL MGR】的技术细节；
涉及架构包括：
- 单主模式
- 多主模式
环境介绍：
操作系统：RHEL 7.6 64bit
防火墙【firewalld】：已停止；禁用开机启动
SELINUX：已禁用
MySQL版本：5.7.26
内存：2G
节点1：
192.168.40.11
节点2：
192.168.40.12
节点3：
192.168.40.13
文件：/etc/hosts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@mysql1 ~]# cat /etc/hosts #127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 #::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # Local 127.0.0.1 localhost # Pub #192.168.40.254 linux # MySQL 192.168.40.11 mysql1 192.168.40.12 mysql2 192.168.40.13 mysql3 # Puppet Server side 192.168.40.252 puppet puppet.adamhuan.com [root@mysql1 ~]# |
修改配置文件【/etc/my.cnf】
节点【1】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 |
[root@mysql1 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 4 09:45 my.cnf.rpmsave-20190804-0945 drwxr-xr-x. 2 root root 6 Aug 4 09:46 my.cnf.d -rw-r--r-- 1 root root 636 Aug 4 19:49 my.cnf_old_20190805_01 -rw-r--r-- 1 root root 1488 Aug 4 20:23 my.cnf [root@mysql1 ~]# [root@mysql1 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815 [root@mysql1 ~]# [root@mysql1 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 4 09:45 my.cnf.rpmsave-20190804-0945 drwxr-xr-x. 2 root root 6 Aug 4 09:46 my.cnf.d -rw-r--r-- 1 root root 636 Aug 4 19:49 my.cnf_old_20190805_01 -rw-r--r-- 1 root root 1488 Aug 4 20:23 my.cnf -rw-r--r-- 1 root root 1488 Aug 14 18:01 my.cnf_orig_20190815 [root@mysql1 ~]# [root@mysql1 ~]# cat /etc/my.cnf # File: MySQL config file: my.cnf # Default Location: /etc/my.cnf # MySQL version: Ver 14.14 Distrib 5.7.26-29 # =================================== # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 # --- [mysqld] # ---> 文件 # 服务进程ID文件 pid-file=/var/run/mysqld/mysqld.pid # 错误日志文件 log-error=/var/log/mysqld.log # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 目录 # 软件所在目录 #basedir = /home/mysql/mysql/ # 数据目录 datadir=/var/lib/mysql #tmpdir= # ---> 参数 symbolic-links=0 # MySQL：服务编号 # -- diff server-id=1 # 用户 user=mysql # 字符集 character_set_server = utf8mb4 # 端口 port=3306 # 存储引擎 default_storage_engine=innodb # !!!--> UNDO #innodb_undo_directory= #innodb_undo_tablespaces= # !!!--> GTID gtid-mode=ON enforce-gtid-consistency=ON # !!!--> Binlog # Binlog模式 log_bin=binlog binlog_format=ROW binlog_checksum=NONE # 文件:binlog log-bin=/var/lib/mysql/mysql-bin log-bin-index=/var/lib/mysql/mysql-bin # !!!--> Master master_info_repository=TABLE # !!!--> Slave log_slave_updates=ON # !!!--> 文件:relaylog relay_log_info_repository=TABLE # !!!--> Transaction - 事务 # 设置Write_Set的哈希算法 # Write_Set用于MGR实例之间的数据校验 transaction_write_set_extraction=XXHASH64 # !!!--> MGR - MySQL Group Replication - 组复制 # 以前缀【loose-】开头，代表：如果组复制的插件没有加载的情况下，也能运行并启动组复制 # 定义一个Group Name，必须是UUID格式，可以自定义 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # 禁止实例启动时，自动开启组复制 loose-group_replication_start_on_boot=OFF # -- diff # 配置当前实例的IP与组复制内部沟通端口，不同组成员之间需要区分开来 loose-group_replication_local_address="192.168.40.11:33061" # 设置组复制的种子成员，需要包含当前实例 loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061" # 关闭引导组复制 loose-group_replication_bootstrap_group=OFF # !!!--> REPLICATION / master-slave - report setting # -- diff report_host=192.168.40.11 # -- diff report_port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 - 安全模式 # --- [mysqld_safe] # ---> 文件 # 错误日志文件 log-error=/var/log/mariadb/mariadb.log # 服务进程ID文件 pid-file=/var/run/mariadb/mariadb.pid # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 客户端进程 # --- [client] # ---> 文件 # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 参数 # 端口 port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 命令行工具 # --- [mysql] # ---> 参数 no-auto-rehash # 提示符 prompt="\u@db \R:\m:\s [\d]>" # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 其他的包含配置文件的目录 # --- # 包含配置文件的目录 !includedir /etc/my.cnf.d # =================================== # Finished [root@mysql1 ~]# |
节点【2】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
[root@mysql2 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 13 17:53 my.cnf.rpmsave-20190813-1753 -rw-r--r-- 1 root root 636 Aug 13 17:54 my.cnf drwxr-xr-x. 2 root root 6 Aug 13 17:54 my.cnf.d [root@mysql2 ~]# [root@mysql2 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815 [root@mysql2 ~]# [root@mysql2 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 13 17:53 my.cnf.rpmsave-20190813-1753 -rw-r--r-- 1 root root 636 Aug 13 17:54 my.cnf drwxr-xr-x. 2 root root 6 Aug 13 17:54 my.cnf.d -rw-r--r-- 1 root root 636 Aug 14 18:01 my.cnf_orig_20190815 [root@mysql2 ~]# [root@mysql2 ~]# cat /etc/my.cnf # File: MySQL config file: my.cnf # Default Location: /etc/my.cnf # MySQL version: Ver 14.14 Distrib 5.7.26-29 # =================================== # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 # --- [mysqld] # ---> 文件 # 服务进程ID文件 pid-file=/var/run/mysqld/mysqld.pid # 错误日志文件 log-error=/var/log/mysqld.log # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 目录 # 软件所在目录 #basedir = /home/mysql/mysql/ # 数据目录 datadir=/var/lib/mysql #tmpdir= # ---> 参数 symbolic-links=0 # MySQL：服务编号 # -- diff server-id=2 # 用户 user=mysql # 字符集 character_set_server = utf8mb4 # 端口 port=3306 # 存储引擎 default_storage_engine=innodb # !!!--> UNDO #innodb_undo_directory= #innodb_undo_tablespaces= # !!!--> GTID gtid-mode=ON enforce-gtid-consistency=ON # !!!--> Binlog # Binlog模式 log_bin=binlog binlog_format=ROW binlog_checksum=NONE # 文件:binlog log-bin=/var/lib/mysql/mysql-bin log-bin-index=/var/lib/mysql/mysql-bin # !!!--> Master master_info_repository=TABLE # !!!--> Slave log_slave_updates=ON # !!!--> 文件:relaylog relay_log_info_repository=TABLE # !!!--> Transaction - 事务 # 设置Write_Set的哈希算法 # Write_Set用于MGR实例之间的数据校验 transaction_write_set_extraction=XXHASH64 # !!!--> MGR - MySQL Group Replication - 组复制 # 以前缀【loose-】开头，代表：如果组复制的插件没有加载的情况下，也能运行并启动组复制 # 定义一个Group Name，必须是UUID格式，可以自定义 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # 禁止实例启动时，自动开启组复制 loose-group_replication_start_on_boot=OFF # -- diff # 配置当前实例的IP与组复制内部沟通端口，不同组成员之间需要区分开来 loose-group_replication_local_address="192.168.40.12:33061" # 设置组复制的种子成员，需要包含当前实例 loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061" # 关闭引导组复制 loose-group_replication_bootstrap_group=OFF # !!!--> REPLICATION / master-slave - report setting # -- diff report_host=192.168.40.12 # -- diff report_port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 - 安全模式 # --- [mysqld_safe] # ---> 文件 # 错误日志文件 log-error=/var/log/mariadb/mariadb.log # 服务进程ID文件 pid-file=/var/run/mariadb/mariadb.pid # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 客户端进程 # --- [client] # ---> 文件 # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 参数 # 端口 port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 命令行工具 # --- [mysql] # ---> 参数 no-auto-rehash # 提示符 prompt="\u@db \R:\m:\s [\d]>" # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 其他的包含配置文件的目录 # --- # 包含配置文件的目录 !includedir /etc/my.cnf.d # =================================== # Finished [root@mysql2 ~]# |
节点【3】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
[root@mysql3 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 13 18:08 my.cnf.rpmsave-20190813-1808 -rw-r--r-- 1 root root 636 Aug 13 18:08 my.cnf drwxr-xr-x. 2 root root 6 Aug 13 18:08 my.cnf.d [root@mysql3 ~]# [root@mysql3 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815 [root@mysql3 ~]# [root@mysql3 ~]# ls -ltr /etc | grep my.cnf -rw-r--r-- 1 root root 570 Aug 13 18:08 my.cnf.rpmsave-20190813-1808 -rw-r--r-- 1 root root 636 Aug 13 18:08 my.cnf drwxr-xr-x. 2 root root 6 Aug 13 18:08 my.cnf.d -rw-r--r-- 1 root root 636 Aug 14 18:01 my.cnf_orig_20190815 [root@mysql3 ~]# [root@mysql3 ~]# cat /etc/my.cnf # File: MySQL config file: my.cnf # Default Location: /etc/my.cnf # MySQL version: Ver 14.14 Distrib 5.7.26-29 # =================================== # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 # --- [mysqld] # ---> 文件 # 服务进程ID文件 pid-file=/var/run/mysqld/mysqld.pid # 错误日志文件 log-error=/var/log/mysqld.log # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 目录 # 软件所在目录 #basedir = /home/mysql/mysql/ # 数据目录 datadir=/var/lib/mysql #tmpdir= # ---> 参数 symbolic-links=0 # MySQL：服务编号 # -- diff server-id=3 # 用户 user=mysql # 字符集 character_set_server = utf8mb4 # 端口 port=3306 # 存储引擎 default_storage_engine=innodb # !!!--> UNDO #innodb_undo_directory= #innodb_undo_tablespaces= # !!!--> GTID gtid-mode=ON enforce-gtid-consistency=ON # !!!--> Binlog # Binlog模式 log_bin=binlog binlog_format=ROW binlog_checksum=NONE # 文件:binlog log-bin=/var/lib/mysql/mysql-bin log-bin-index=/var/lib/mysql/mysql-bin # !!!--> Master master_info_repository=TABLE # !!!--> Slave log_slave_updates=ON # !!!--> 文件:relaylog relay_log_info_repository=TABLE # !!!--> Transaction - 事务 # 设置Write_Set的哈希算法 # Write_Set用于MGR实例之间的数据校验 transaction_write_set_extraction=XXHASH64 # !!!--> MGR - MySQL Group Replication - 组复制 # 以前缀【loose-】开头，代表：如果组复制的插件没有加载的情况下，也能运行并启动组复制 # 定义一个Group Name，必须是UUID格式，可以自定义 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # 禁止实例启动时，自动开启组复制 loose-group_replication_start_on_boot=OFF # -- diff # 配置当前实例的IP与组复制内部沟通端口，不同组成员之间需要区分开来 loose-group_replication_local_address="192.168.40.13:33061" # 设置组复制的种子成员，需要包含当前实例 loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061" # 关闭引导组复制 loose-group_replication_bootstrap_group=OFF # !!!--> REPLICATION / master-slave - report setting # -- diff report_host=192.168.40.13 # -- diff report_port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 服务进程 - 安全模式 # --- [mysqld_safe] # ---> 文件 # 错误日志文件 log-error=/var/log/mariadb/mariadb.log # 服务进程ID文件 pid-file=/var/run/mariadb/mariadb.pid # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 客户端进程 # --- [client] # ---> 文件 # 网络套接字文件 socket=/var/lib/mysql/mysql.sock # ---> 参数 # 端口 port=3306 # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 命令行工具 # --- [mysql] # ---> 参数 no-auto-rehash # 提示符 prompt="\u@db \R:\m:\s [\d]>" # %%%%%%%%%%%%%%%%%%%%%% # --- # MySQL: 其他的包含配置文件的目录 # --- # 包含配置文件的目录 !includedir /etc/my.cnf.d # =================================== # Finished [root@mysql3 ~]# |
在所有节点上，初始化数据库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
[root@mysql1 ~]# service mysql stop Redirecting to /bin/systemctl stop mysql.service [root@mysql1 ~]# [root@mysql1 ~]# service mysql status Redirecting to /bin/systemctl status mysql.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Wed 2019-08-14 18:39:40 PDT; 9min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 7782 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 7254 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 7785 (code=exited, status=0/SUCCESS) Aug 14 17:45:04 mysql1.adamhuan.com systemd[1]: Starting MySQL Server... Aug 14 17:45:30 mysql1.adamhuan.com systemd[1]: Started MySQL Server. Aug 14 18:39:38 mysql1.adamhuan.com systemd[1]: Stopping MySQL Server... Aug 14 18:39:40 mysql1.adamhuan.com systemd[1]: Stopped MySQL Server. [root@mysql1 ~]# [root@mysql1 ~]# cat /etc/my.cnf | grep datadir datadir=/var/lib/mysql [root@mysql1 ~]# [root@mysql1 ~]# ls -ltr /var/lib/mysql total 110672 -rw-r----- 1 mysql mysql 50331648 Aug 4 09:46 ib_logfile1 -rw-r----- 1 mysql mysql 56 Aug 4 09:46 auto.cnf -rw-r--r-- 1 mysql mysql 1120 Aug 4 09:46 ca.pem -rw------- 1 mysql mysql 1676 Aug 4 09:46 ca-key.pem -rw------- 1 mysql mysql 1676 Aug 4 09:46 server-key.pem -rw-r--r-- 1 mysql mysql 1120 Aug 4 09:46 server-cert.pem -rw------- 1 mysql mysql 1680 Aug 4 09:46 client-key.pem -rw-r--r-- 1 mysql mysql 1120 Aug 4 09:46 client-cert.pem -rw-r--r-- 1 mysql mysql 452 Aug 4 09:46 public_key.pem -rw------- 1 mysql mysql 1680 Aug 4 09:46 private_key.pem drwxr-x--- 2 mysql mysql 8192 Aug 4 09:46 performance_schema drwxr-x--- 2 mysql mysql 4096 Aug 4 09:46 mysql drwxr-x--- 2 mysql mysql 8192 Aug 4 09:46 sys -rw-r----- 1 mysql mysql 588 Aug 14 17:45 mysql-bin.000001 -rw-r----- 1 mysql mysql 64 Aug 14 17:45 mysql-bin.index -rw-r----- 1 mysql mysql 217 Aug 14 18:39 mysql-bin.000002 -rw-r----- 1 mysql mysql 326 Aug 14 18:39 ib_buffer_pool -rw-r----- 1 mysql mysql 50331648 Aug 14 18:39 ib_logfile0 -rw-r----- 1 mysql mysql 12582912 Aug 14 18:39 ibdata1 [root@mysql1 ~]# [root@mysql1 ~]# rm -rf /var/lib/mysql/* [root@mysql1 ~]# [root@mysql1 ~]# ls -ltr /var/lib/mysql total 0 [root@mysql1 ~]# [root@mysql1 ~]# which mysqld /usr/sbin/mysqld [root@mysql1 ~]# [root@mysql1 ~]# mysqld --initialize-insecure --datadir=/var/lib/mysql --user=mysql [root@mysql1 ~]# [root@mysql1 ~]# ls -ltr /var/lib/mysql total 110668 -rw-r----- 1 mysql mysql 50331648 Aug 14 18:52 ib_logfile1 -rw-r----- 1 mysql mysql 32 Aug 14 18:52 mysql-bin.index -rw-r----- 1 mysql mysql 56 Aug 14 18:52 auto.cnf -rw------- 1 mysql mysql 1676 Aug 14 18:52 ca-key.pem -rw-r--r-- 1 mysql mysql 1120 Aug 14 18:52 ca.pem -rw------- 1 mysql mysql 1680 Aug 14 18:52 server-key.pem -rw-r--r-- 1 mysql mysql 1120 Aug 14 18:52 server-cert.pem -rw------- 1 mysql mysql 1680 Aug 14 18:52 client-key.pem -rw-r--r-- 1 mysql mysql 1120 Aug 14 18:52 client-cert.pem -rw-r--r-- 1 mysql mysql 452 Aug 14 18:52 public_key.pem -rw------- 1 mysql mysql 1680 Aug 14 18:52 private_key.pem drwxr-x--- 2 mysql mysql 8192 Aug 14 18:52 performance_schema drwxr-x--- 2 mysql mysql 4096 Aug 14 18:52 mysql drwxr-x--- 2 mysql mysql 8192 Aug 14 18:52 sys -rw-r----- 1 mysql mysql 169 Aug 14 18:52 mysql-bin.000001 -rw-r----- 1 mysql mysql 417 Aug 14 18:52 ib_buffer_pool -rw-r----- 1 mysql mysql 50331648 Aug 14 18:52 ib_logfile0 -rw-r----- 1 mysql mysql 12582912 Aug 14 18:52 ibdata1 [root@mysql1 ~]# |
在所有节点上，启动MySQL数据库服务：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[root@mysql1 ~]# service mysql status Redirecting to /bin/systemctl status mysql.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Wed 2019-08-14 18:39:40 PDT; 15min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 7782 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 7254 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 7785 (code=exited, status=0/SUCCESS) Aug 14 17:45:04 mysql1.adamhuan.com systemd[1]: Starting MySQL Server... Aug 14 17:45:30 mysql1.adamhuan.com systemd[1]: Started MySQL Server. Aug 14 18:39:38 mysql1.adamhuan.com systemd[1]: Stopping MySQL Server... Aug 14 18:39:40 mysql1.adamhuan.com systemd[1]: Stopped MySQL Server. [root@mysql1 ~]# [root@mysql1 ~]# service mysql start Redirecting to /bin/systemctl start mysql.service [root@mysql1 ~]# [root@mysql1 ~]# service mysql status Redirecting to /bin/systemctl status mysql.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Wed 2019-08-14 18:55:17 PDT; 5s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 8108 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 8081 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 8111 (mysqld) CGroup: /system.slice/mysqld.service └─8111 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Aug 14 18:55:16 mysql1.adamhuan.com systemd[1]: Starting MySQL Server... Aug 14 18:55:17 mysql1.adamhuan.com systemd[1]: Started MySQL Server. [root@mysql1 ~]# |
这个过程中的日志【/var/log/mysqld.log】：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
[root@mysql2 ~]# tail -f /var/log/mysqld.log 2019-08-15T01:53:27.167546Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2019-08-15T01:53:27.222650Z 0 [Warning] unknown variable 'loose-group_replication_group_name=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' 2019-08-15T01:53:27.222680Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=OFF' 2019-08-15T01:53:27.222683Z 0 [Warning] unknown variable 'loose-group_replication_local_address=192.168.40.12:33061' 2019-08-15T01:53:27.222685Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061' 2019-08-15T01:53:27.222687Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=OFF' 2019-08-15T01:53:27.224401Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 794882d1-beff-11e9-9f51-000c2968d274. 2019-08-15T01:53:27.224743Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2019-08-15T01:53:27.458149Z 0 [Warning] CA certificate ca.pem is self signed. 2019-08-15T01:53:27.948593Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. =================================================== 2019-08-15T01:57:00.003769Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2019-08-15T01:57:00.020396Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-29-log) starting as process 8138 ... 2019-08-15T01:57:00.033205Z 0 [Note] InnoDB: PUNCH HOLE support available 2019-08-15T01:57:00.033325Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019-08-15T01:57:00.033330Z 0 [Note] InnoDB: Uses event mutexes 2019-08-15T01:57:00.033335Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2019-08-15T01:57:00.033338Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7 2019-08-15T01:57:00.033341Z 0 [Note] InnoDB: Using Linux native AIO 2019-08-15T01:57:00.035195Z 0 [Note] InnoDB: Number of pools: 1 2019-08-15T01:57:00.035821Z 0 [Note] InnoDB: Using CPU crc32 instructions 2019-08-15T01:57:00.048070Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2019-08-15T01:57:00.090091Z 0 [Note] InnoDB: Completed initialization of buffer pool 2019-08-15T01:57:00.118514Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2019-08-15T01:57:00.150005Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite 2019-08-15T01:57:00.152695Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2019-08-15T01:57:00.175494Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes 2019-08-15T01:57:00.188633Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2019-08-15T01:57:00.188703Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2019-08-15T01:57:00.268318Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2019-08-15T01:57:00.277827Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2019-08-15T01:57:00.277853Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2019-08-15T01:57:00.287581Z 0 [Note] InnoDB: Waiting for purge to start 2019-08-15T01:57:00.337990Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.26-29 started; log sequence number 2530251 2019-08-15T01:57:00.350585Z 0 [Note] Plugin 'FEDERATED' is disabled. 2019-08-15T01:57:00.375030Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2019-08-15T01:57:00.377558Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190814 18:57:00 2019-08-15T01:57:00.414207Z 0 [Warning] unknown variable 'loose-group_replication_group_name=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' 2019-08-15T01:57:00.414245Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=OFF' 2019-08-15T01:57:00.414251Z 0 [Warning] unknown variable 'loose-group_replication_local_address=192.168.40.12:33061' 2019-08-15T01:57:00.414253Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061' 2019-08-15T01:57:00.414256Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=OFF' 2019-08-15T01:57:00.419728Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2019-08-15T01:57:00.419766Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory. 2019-08-15T01:57:00.420359Z 0 [Warning] CA certificate ca.pem is self signed. 2019-08-15T01:57:00.420399Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory. 2019-08-15T01:57:00.420493Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2019-08-15T01:57:00.420534Z 0 [Note] IPv6 is available. 2019-08-15T01:57:00.420543Z 0 [Note] - '::' resolves to '::'; 2019-08-15T01:57:00.420560Z 0 [Note] Server socket created on IP: '::'. 2019-08-15T01:57:00.434472Z 0 [Note] Failed to start slave threads for channel '' 2019-08-15T01:57:00.444518Z 0 [Note] Event Scheduler: Loaded 0 events 2019-08-15T01:57:00.445451Z 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961 ============================================= |
在所有节点上，安装MGR的插件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
[root@mysql2 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, 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. root@db 19:02: [(none)]> root@db 19:03: [(none)]>select @@have_dynamic_loading ; +------------------------+ | @@have_dynamic_loading | +------------------------+ | YES | +------------------------+ 1 row in set (0.00 sec) root@db 19:03: [(none)]> root@db 19:04: [(none)]>show plugins; +-------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | XTRADB_READ_VIEW | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_INTERNAL_HASH_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ZIP_DICT | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ZIP_DICT_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD | | INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | +-------------------------------+----------+--------------------+---------+---------+ 52 rows in set (0.00 sec) root@db 19:04: [(none)]> root@db 19:04: [(none)]>install plugin group_replication soname 'group_replication.so'; Query OK, 0 rows affected (0.40 sec) root@db 19:04: [(none)]> root@db 19:04: [(none)]>show plugins; +-------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | XTRADB_READ_VIEW | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_INTERNAL_HASH_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ZIP_DICT | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ZIP_DICT_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD | | INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +-------------------------------+----------+--------------------+----------------------+---------+ 53 rows in set (0.00 sec) root@db 19:04: [(none)]> |
在所有节点上，设置MGR的复制账号：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
[root@mysql1 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, 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. root@db 19:08: [(none)]> root@db 19:08: [(none)]>show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) root@db 19:08: [(none)]> root@db 19:08: [(none)]>set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) root@db 19:08: [(none)]> root@db 19:08: [(none)]>show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) root@db 19:08: [(none)]> root@db 19:08: [(none)]>select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 3 rows in set (0.00 sec) root@db 19:08: [(none)]> root@db 19:08: [(none)]>create user repl@'%' identified by 'repl1'; Query OK, 0 rows affected (0.00 sec) root@db 19:08: [(none)]>select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | repl | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>show grants for repl@'%'; +----------------------------------+ | Grants for repl@% | +----------------------------------+ | GRANT USAGE ON *.* TO 'repl'@'%' | +----------------------------------+ 1 row in set (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>grant replication slave on *.* to repl@'%'; Query OK, 0 rows affected (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>show grants for repl@'%'; +----------------------------------------------+ | Grants for repl@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>flush privileges; Query OK, 0 rows affected (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) root@db 19:09: [(none)]> root@db 19:09: [(none)]>set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) root@db 19:10: [(none)]> root@db 19:10: [(none)]>show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) root@db 19:10: [(none)]> root@db 19:10: [(none)]>show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 150 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) root@db 19:10: [(none)]> root@db 19:10: [(none)]>show slave status; Empty set (0.00 sec) root@db 19:10: [(none)]> root@db 19:10: [(none)]>change master to master_user='repl',master_password='repl1' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.34 sec) root@db 19:11: [(none)]> root@db 19:11: [(none)]>show slave status; Empty set (0.00 sec) root@db 19:11: [(none)]> |
启动MGR：单主模式 – 主库
节点【1】：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
[root@mysql1 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, 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. root@db 19:15: [(none)]> root@db 19:15: [(none)]>show variables like 'group_replication_bootstrap_group'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | group_replication_bootstrap_group | OFF | +-----------------------------------+-------+ 1 row in set (0.00 sec) root@db 19:16: [(none)]> root@db 19:16: [(none)]>set global group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) root@db 19:16: [(none)]>show variables like 'group_replication_bootstrap_group'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | group_replication_bootstrap_group | ON | +-----------------------------------+-------+ 1 row in set (0.00 sec) root@db 19:16: [(none)]> root@db 19:16: [(none)]>start group_replication; Query OK, 0 rows affected (3.29 sec) root@db 19:16: [(none)]>set global group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) root@db 19:17: [(none)]> root@db 19:17: [(none)]>show variables like 'group_replication_bootstrap_group'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | group_replication_bootstrap_group | OFF | +-----------------------------------+-------+ 1 row in set (0.00 sec) root@db 19:17: [(none)]> |
这个过程的日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
2019-08-15T02:11:23.825503Z 5 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql1-relay-bin' to avoid this problem. 2019-08-15T02:11:24.151318Z 5 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. 2019-08-15T02:16:51.230069Z 6 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 2019-08-15T02:16:51.230202Z 6 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.40.11/24 to the whitelist' 2019-08-15T02:16:51.230346Z 6 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 2019-08-15T02:16:51.230400Z 6 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 2019-08-15T02:16:51.230420Z 6 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.40.11:33061"; group_replication_group_seeds: "192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 2019-08-15T02:16:51.230472Z 6 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0' 2019-08-15T02:16:51.230482Z 6 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds' 2019-08-15T02:16:51.230506Z 6 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "61006e5f-beff-11e9-9d5e-000c2998ef22"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 2019-08-15T02:16:51.552650Z 8 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 2019-08-15T02:16:51.571393Z 11 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql1-relay-bin-group_replication_applier.000001' position: 4 2019-08-15T02:16:51.572767Z 6 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 2019-08-15T02:16:51.572831Z 6 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 2019-08-15T02:16:51.572846Z 6 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1' 2019-08-15T02:16:52.513704Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3' 2019-08-15T02:16:52.513737Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061' 2019-08-15T02:16:53.531054Z 14 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group' 2019-08-15T02:16:53.531459Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306 on view 15658354135168253:1.' 2019-08-15T02:16:53.547169Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group' 2019-08-15T02:16:53.547363Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.11:3306 was elected, enabling conflict detection until the new primary applies all relay logs.' 2019-08-15T02:16:53.547467Z 16 [Note] Plugin group_replication reported: 'This server is working as primary member.' |
查看MGR当前的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
root@db 19:18: [(none)]>select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) root@db 19:18: [(none)]>select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) root@db 19:18: [(none)]> root@db 19:18: [(none)]>desc performance_schema.replication_group_members; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | CHANNEL_NAME | char(64) | NO | | NULL | | | MEMBER_ID | char(36) | NO | | NULL | | | MEMBER_HOST | char(60) | NO | | NULL | | | MEMBER_PORT | int(11) | YES | | NULL | | | MEMBER_STATE | char(64) | NO | | NULL | | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) root@db 19:19: [(none)]> root@db 19:19: [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 1 row in set (0.00 sec) root@db 19:19: [(none)]> |
这样，单主模式下的MGR的主库的配置就完成了。
启动MGR：单主模式 – 成员库
单主模式下，成员库（从库）直接启动MGR，就可以加入MGR：
节点【2 / 3】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@mysql2 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, 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. root@db 19:22: [(none)]> root@db 19:22: [(none)]>start group_replication; Query OK, 0 rows affected (12.25 sec) root@db 19:23: [(none)]> |
MGR启动成功后，去MGR主库上查看当前MGR的状态：
节点【1】：
|
1 2 3 4 5 6 7 8 9 10 |
root@db 19:24: [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | ONLINE | | group_replication_applier | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 2 rows in set (0.00 sec) root@db 19:24: [(none)]> |
这个过程中的日志：
节点二加入MGR时候的日志
节点【1】
|
1 2 3 4 |
2019-08-15T02:22:59.759502Z 0 [Note] Plugin group_replication reported: 'Members joined the group: 192.168.40.12:3306' 2019-08-15T02:22:59.759825Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306 on view 15658354135168253:2.' 2019-08-15T02:22:59.812330Z 19 [Note] Start binlog_dump to master_thread_id(19) slave_server(2), pos(, 4) 2019-08-15T02:22:59.855596Z 0 [Note] Plugin group_replication reported: 'The member with address 192.168.40.12:3306 was declared online within the replication group' |
节点【2】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
2019-08-15T02:22:48.482965Z 8 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 2019-08-15T02:22:48.483068Z 8 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.40.12/24 to the whitelist' 2019-08-15T02:22:48.483215Z 8 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 2019-08-15T02:22:48.492134Z 8 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 2019-08-15T02:22:48.492180Z 8 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.40.12:33061"; group_replication_group_seeds: "192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 2019-08-15T02:22:48.492216Z 8 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0' 2019-08-15T02:22:48.492221Z 8 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds' 2019-08-15T02:22:48.492241Z 8 [Note] Plugin group_replication reported: 'Member configuration: member_id: 2; member_uuid: "794882d1-beff-11e9-9f51-000c2968d274"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 2019-08-15T02:22:48.817450Z 10 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 2019-08-15T02:22:48.825430Z 13 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql2-relay-bin-group_replication_applier.000001' position: 4 2019-08-15T02:22:48.825887Z 8 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 2019-08-15T02:22:48.825909Z 8 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 2019-08-15T02:22:48.825914Z 8 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2' 2019-08-15T02:22:55.067575Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3' 2019-08-15T02:22:55.067622Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061' 2019-08-15T02:22:59.729993Z 8 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.40.11:3306.' 2019-08-15T02:22:59.731361Z 16 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10' 2019-08-15T02:22:59.732093Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306 on view 15658354135168253:2.' 2019-08-15T02:22:59.755980Z 16 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.40.11', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. 2019-08-15T02:22:59.769369Z 16 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 61006e5f-beff-11e9-9d5e-000c2998ef22 at 192.168.40.11 port: 3306.' 2019-08-15T02:22:59.770525Z 18 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2019-08-15T02:22:59.773945Z 18 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@192.168.40.11:3306',replication started in log 'FIRST' at position 4 2019-08-15T02:22:59.774664Z 19 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql2-relay-bin-group_replication_recovery.000001' position: 4 2019-08-15T02:22:59.793166Z 16 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.' 2019-08-15T02:22:59.794135Z 19 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'mysql-bin.000002' at position 758 2019-08-15T02:22:59.795717Z 18 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery' 2019-08-15T02:22:59.795750Z 18 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000002', position 758 2019-08-15T02:22:59.812013Z 16 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='192.168.40.11', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 2019-08-15T02:22:59.825005Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group' |
节点三加入MGR的时候的日志
节点【1】
|
1 2 3 |
2019-08-15T02:27:10.440253Z 0 [Note] Plugin group_replication reported: 'Members joined the group: 192.168.40.13:3306' 2019-08-15T02:27:10.440673Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:3.' 2019-08-15T02:27:10.559377Z 0 [Note] Plugin group_replication reported: 'The member with address 192.168.40.13:3306 was declared online within the replication group' |
节点【2】
|
1 |
节点【3】
|
1 |
最后，再看看MGR当前的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[root@mysql1 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, 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. root@db 19:29: [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | ONLINE | | group_replication_applier | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 | 3306 | ONLINE | | group_replication_applier | 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 3 rows in set (0.00 sec) root@db 19:30: [(none)]> |
可以看到，上面的查询结果中并没有关于MGR成员节点的角色信息；
这一块的信息可以通过以下SQL查询：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, IF(global_status.VARIABLE_NAME IS NOT NULL, 'PRIMARY', 'SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; |
执行效果如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
root@db 19:47: [(none)]>SELECT -> MEMBER_ID, -> MEMBER_HOST, -> MEMBER_PORT, -> MEMBER_STATE, -> IF(global_status.VARIABLE_NAME IS NOT NULL, -> 'PRIMARY', -> 'SECONDARY') AS MEMBER_ROLE -> FROM -> performance_schema.replication_group_members -> LEFT JOIN -> performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' -> AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; +--------------------------------------+---------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | +--------------------------------------+---------------+-------------+--------------+-------------+ | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | ONLINE | PRIMARY | | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 | 3306 | ONLINE | SECONDARY | | 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 | 3306 | ONLINE | SECONDARY | +--------------------------------------+---------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) root@db 19:47: [(none)]> |
这样，就可以清晰地看到MGR的成员节点的角色（ROLE）信息了。
至此，MGR的单主模式，配置完成。
测试以下：
在主库上创建一个库，并建表写入一些数据，看看从库是否能够同步到数据。
节点【1】：创建数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
root@db 19:53: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) root@db 19:54: [(none)]>create database adamhuan; Query OK, 1 row affected (0.01 sec) root@db 19:54: [(none)]> root@db 19:54: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuan | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) root@db 19:54: [(none)]> |
节点【2 / 3】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
root@db 19:53: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) root@db 19:54: [(none)]> root@db 19:54: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuan | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) root@db 19:54: [(none)]> |
可以看到，从库同步到了主库的变化。
这个过程中的日志
节点【1】
|
1 |
2019-08-15T02:54:28.833495Z 8 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection' |
节点【2】
|
1 |
2019-08-15T02:54:28.801646Z 10 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection' |
节点【3】
|
1 |
2019-08-15T02:54:28.833724Z 10 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection' |
将MGR从【单主模式】切换成【多主模式】
在所有节点上， 停MGR：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
root@db 20:05: [(none)]>SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, IF(global_status.VARIABLE_NAME IS NOT NULL, 'PRIMARY', 'SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; +--------------------------------------+---------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | +--------------------------------------+---------------+-------------+--------------+-------------+ | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | ONLINE | PRIMARY | | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 | 3306 | ONLINE | SECONDARY | | 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 | 3306 | ONLINE | SECONDARY | +--------------------------------------+---------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) root@db 20:06: [(none)]> root@db 20:06: [(none)]>stop group_replication; Query OK, 0 rows affected (9.42 sec) root@db 20:06: [(none)]> root@db 20:06: [(none)]>SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, IF(global_status.VARIABLE_NAME IS NOT NULL, 'PRIMARY', 'SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; +--------------------------------------+---------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | +--------------------------------------+---------------+-------------+--------------+-------------+ | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 | 3306 | OFFLINE | SECONDARY | +--------------------------------------+---------------+-------------+--------------+-------------+ 1 row in set (0.00 sec) root@db 20:06: [(none)]> |
这个过程中的日志：
节点【1】
|
1 2 3 4 5 6 7 8 9 |
2019-08-15T03:06:11.923056Z 27 [Note] Plugin group_replication reported: 'Plugin 'group_replication' is stopping.' 2019-08-15T03:06:11.923119Z 27 [Note] Plugin group_replication reported: 'Going to wait for view modification' 2019-08-15T03:06:15.342959Z 0 [Note] Plugin group_replication reported: 'Group membership changed: This member has left the group.' 2019-08-15T03:06:20.347623Z 27 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1' 2019-08-15T03:06:20.347678Z 27 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1' 2019-08-15T03:06:20.347852Z 11 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed 2019-08-15T03:06:20.347863Z 11 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 65 2019-08-15T03:06:20.348695Z 8 [Note] Plugin group_replication reported: 'The group replication applier thread was killed' 2019-08-15T03:06:20.348948Z 27 [Note] Plugin group_replication reported: 'Plugin 'group_replication' has been stopped.' |
节点【2】
|
1 2 3 4 5 |
2019-08-15T03:06:12.404854Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 192.168.40.11:3306' 2019-08-15T03:06:12.404916Z 0 [Note] Plugin group_replication reported: 'Primary server with address 192.168.40.11:3306 left the group. Electing new Primary.' 2019-08-15T03:06:12.405050Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.12:3306 was elected, enabling conflict detection until the new primary applies all relay logs.' 2019-08-15T03:06:12.405128Z 30 [Note] Plugin group_replication reported: 'This server is working as primary member.' 2019-08-15T03:06:12.405187Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:4.' |
节点【3】
|
1 2 3 4 5 |
2019-08-15T03:06:12.436596Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 192.168.40.11:3306' 2019-08-15T03:06:12.436728Z 0 [Note] Plugin group_replication reported: 'Primary server with address 192.168.40.11:3306 left the group. Electing new Primary.' 2019-08-15T03:06:12.436877Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.12:3306 was elected, enabling conflict detection until the new primary applies all relay logs.' 2019-08-15T03:06:12.436966Z 24 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.40.12:3306.' 2019-08-15T03:06:12.437027Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:4.' |
在所有节点上，设置参数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
root@db 20:09: [(none)]>show variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ 1 row in set (0.00 sec) root@db 20:09: [(none)]>set global group_replication_single_primary_mode=OFF; Query OK, 0 rows affected (0.00 sec) root@db 20:09: [(none)]>show variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | OFF | +---------------------------------------+-------+ 1 row in set (0.00 sec) |