使用mysql5.7.26搭建MGR单主及多主模式(参考链接https://www.jianshu.com/p/ca1af156f656)
一、搭建单主
1.1前言:
MySQL Group Replication(MGR)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供,实现了分布式下数据的最终一致性,总结MGR特点如下:
高一致性:基于分布式paxos协议实现组复制,保证数据一致性;
高容错性:自动检测机制,只要不是大多数节点都宕机就可以继续工作,内置防脑裂保护机制;
高扩展性:节点的增加与移除会自动更新组成员信息,新节点加入后,自动从其他节点同步增量数据,直到与其他节点数据一致;
高灵活性:提供单主模式和多主模式,单主模式在主库宕机后能够自动选主,所有写入都在主节点进行,多主模式支持多节点写入。
1.2网络拓扑
192.168.56.141 centos7.node2 ##从
192.168.56.149 docker.node01 ##主
192.168.56.150 centos7.node1 ##从
1.3安装mysql
1.3.1 从官网下载mysql安装包
1.3.2 安装过程(每台服务器都做一遍)
配置集群中全部节点的免密登录(以其中一个节点为例,其它的节点类似操作即可):
[root@docker .ssh]# ssh-keygen -t rsa
[root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.150
[root@docker .ssh]# ssh-copy-id -i ./id_rsa.pub root@192.168.56.141
测试(所有节点操作)
[root@docker .ssh]# ssh root@192.168.56.150
[root@docker .ssh]# ssh root@192.168.56.141
安装mysql5.7(所有节点操作)
[root@docker local]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@docker local]# cd /usr/local/
[root@docker local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
[root@docker local]# groupadd mysql
[root@docker local]# useradd -r -g mysql mysql ##创建系统用户,并设置组
[root@docker local]# chown -R mysql . ## 设置软件产品目录的属主
[root@docker local]# chgrp -R mysql .
[root@docker local]# vim /etc/profile.d/mysql.sh ##添加环境变量,添加如下内容
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
[root@docker local]# source /etc/profile
[root@docker mysql]# cp support-files/mysql.server /etc/init.d/mysqld ##添加启动uni files/mysql
[root@centos7 mysql_data]# systemctl stop firewalld.service ##关闭防火墙
[root@centos7 mysql_data]# systemctl disable firewalld.service
[root@centos7 mysql_data]# vim /etc/hosts
添加如下内容:
192.168.56.141 centos7.node2
192.168.56.149 docker.node01
192.168.56.150 centos7.node1
[root@centos7 mysql_data]# vim /etc/my.cnf ###添加内容,下面的配置每台机器注意修改server-id、report_host、bind_address、group_replication_local_address都不一样,其它都一样
[client]
user=root
password=wiki123
[mysqld]
########basic settings########
server-id = 11
port = 3306
user = mysql
bind_address = 192.168.56.149
report_host=192.168.56.149
report_port=3306
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
binlog_checksum=NONE
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/
innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 524288 ##512M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;group_replication=group_replication.so"###group_replication.so是mgr插件
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
####MGR SETTING#######
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "docker.node01:33061"
loose-group_replication_group_seeds= "docker.node01:33061,centos7.node1:33061,centos7.node2:33061"
loose-group_replication_bootstrap_group=OFF
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 524288 ##512M
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=XXHASH64
show_compatibility_56=on
创建目录(所有节点操作):
创建my.cnf配置文件中的全部目录,并授权,下面以datadir = /data/mysql_data为例
[root@docker local]# mkdir -pv /data/mysql_data
[root@docker local]# chown -R mysql:mysql /data
[root@docker local]# chmod -R 770 /data
#######################################到这里,安装的准备工作已经完成,下面开始初始化数据库
初始化数据库(所有节点操作)
[root@docker local]# mysqld --initialize --user=mysql
[root@docker local]# mysqld_safe --user=mysql & ##启动数据库
#######################################到这里,所有节点的数据都已经出事化完毕
1.3.4 设置复制账号(所有节点操作)
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
1.3.5 启动MGR单主模式
# 启动MGR,在主库(192.168.56.149)上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
# 其他节点加入MGR,在从库(192.168.56.150,192.168.56.141)上执行
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.
出现这个现象的原因:
MySQL是新装的没问题,但是每次新装MySQL都要修改密码,如果在修改密码的时候就已经把binlog_format=on配在了/etc/my.cnf中,
那么修改密码的记录是存在在binlog日志中的。所以就会提示前文中的日志错误。
解决方案(每一个从节点执行):
mysql> RESET MASTER
mysql> START GROUP_REPLICATION; ##从新启动,问题解决(错误解决参考链接:https://blog.csdn.net/snowhite91/article/details/83791997)
# 查看MGR组信息(在主库(192.168.56.149)上执行)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE |
| group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 | 3306 | ONLINE |
| group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
mysql> show variables like '%read_only%';##192,168.56.149自动为读写
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| read_only | OFF |
| super_read_only | OFF |
其它库,默认启动就为只读
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| read_only | ON |
| super_read_only | ON |
可以看到,3个节点状态为online,并且主节点为192.168.56.149,只有主节点可以写入,其他节点只读,MGR单主模式搭建成功
1.3.6 测试插入数据
mysql> create table t1(a int);
mysql> insert into t1 values(1),(2),(3);
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
错误的原因是在MGR下,表必须要有主键
mysql> create table t1(a int primary key);
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
二、将单主模式切换为多主模式
2.1 要求
MGR切换模式需要重新启动组复制,因些需要在所有节点上先关闭组复制,设置 group_replication_single_primary_mode=OFF 等参数,再启动组复制。
2.2 切换步骤
# 停止组复制(所有节点执行):
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
mysql> START GROUP_REPLICATION;
# 查看组信息,所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 16855150-f223-11e9-9b22-000c294aef44 | 192.168.56.149 | 3306 | ONLINE |
| group_replication_applier | 2d1b48ec-dbd1-11e9-9f76-000c2995d20d | 192.168.56.141 | 3306 | ONLINE |
| group_replication_applier | 6bd815d0-f22b-11e9-b02f-000c29b9031b | 192.168.56.150 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
可以看到所有节点状态都是online,都可以读写,角色都是PRIMARY,MGR多主模式搭建成功。
三、从多主切换为单主
# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
# 主节点(192.168.56.149)执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点(192.168.56.150、192.168.56.149)执行
START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;