- 1.每个节点设置hostname，并添加/etc/hosts
- 2.下载 gperftools-devel 和 mysql 安装包
yum install --downloadonly --downloaddir=/root gperftools-devel
https://downloads.mysql.com/archives/community
- 3.安装步骤
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
rpm -ivh mysql-community-client-plugins-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-common-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-debuginfo-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.36-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-server-debug-8.0.36-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-test-8.0.36-1.el7.x86_64.rpm --force --nodeps
rpm -ivh /root/gperftools-libs-2.6.1-1.el7.x86_64.rpm
rpm -ivh /root/gperftools-devel-2.6.1-1.el7.x86_64.rpm
echo 'LD_PRELOAD=/usr/lib64/libtcmalloc.so' >> /etc/sysconfig/mysql
- 4.替换my.cnf配置文件如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
skip-grant-tables
#server-id必须是唯一的，每个节点不能相同
server-id = 53
#默认134217728,单位byte(也就是128MB)，建议修改为机器总内存的70%
innodb_buffer_pool_size = 6442450944
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql #数据目录
log-error = /var/log/mysqld.log #错误日志
skip_name_resolve = 1 #跳过DNS解析
max_connections = 8192 #最大连接数
lower_case_table_names = 1 #表名大小写不敏感
master_info_repository = TABLE #MGR要求把主从复制信息记录到表中
relay_log_info_repository = TABLE #MGR要求把主从复制信息记录到表中
relay_log_purge = 1 #开启中继日志自动清理
transaction_write_set_extraction = XXHASH6 #必须为每个事务收集写集合，使用XXHASH算法将其编码为散列
authentication_policy = mysql_native_password #设置默认密码验证插件
transaction_isolation = READ-COMMITTED #隔离级别是RC,减少锁粒度
log_bin = /var/lib/mysql/mysql-bin #启用binlog功能，并指定路径名称
log_replica_updates = 1 #记录已经执行过的binlog
binlog_expire_logs_seconds = 259200 #binlog日志的过期时间(单位秒)
binlog_format = row #binlog格式,MGR要求必须是ROW,不过就算不是MGR,也最好用row
replica_preserve_commit_order = ON #开启备库提交事务顺序
replica_parallel_type = LOGICAL_CLOCK #多线程并行复制
replica_parallel_workers = 4 #工作线程数
gtid_mode = ON #开启GTID
enforce_gtid_consistency = ON #强制GTID的一致性
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用的存储引擎
binlog_transaction_dependency_tracking = WRITESET_SESSION #保证同一个session内的事务不可并行
plugin_load_add = 'group_replication.so' #加载group_replication插件
plugin-load-add = 'mysql_clone.so' #加载mysql_clone插件
loose-group_replication_ip_whitelist = '127.0.0.1/8,192.168.7.0/24' #IP地址白名单
loose-group_replication_group_name = "290da651-da91-11ed-be70-000c29b15814" #组复制中的UUID
loose-group_replication_start_on_boot = off #组复制配置完后再改成on，重启后自动加入组复制
loose-group_replication_bootstrap_group = off #启动不会引导创建一个新的组
super_read_only = off #组复制配置完后再改成on，重启后从库的root用户就会自动进入只读状态
read_only = on #重启后从库的普通用户自动进入只读状态
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_redo_log_capacity = 2g
innodb_flush_method = O_DIRECT
#组复制的本机地址
loose-group_replication_local_address = "192.168.7.53:33061"
#组复制的集群地址
loose-group_replication_group_seeds = "192.168.7.52:33061,192.168.7.53:33061,192.168.7.54:33061"
- 5.初始化数据库
systemctl start mysqld
mysql -e"UPDATE mysql.user SET authentication_string='*14CF315C27425870B6B841EC78E21378C8BEE3CA' WHERE user='root';"
mysql -e"flush privileges;"
sed -i '/skip-grant-tables/d' /etc/my.cnf
systemctl restart mysqld
mysql -uroot -pAa.123456 --connect-expired-password -e"alter user 'root'@'localhost' identified with mysql_native_password by 'Aa.1234567';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"set persist validate_password.policy=0;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"set persist validate_password.length=4;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"update mysql.user set host='%' where user='root';flush privileges;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"set global super_read_only=0;SET SQL_LOG_BIN=0;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa.1234567';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';FLUSH PRIVILEGES;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"set global super_read_only=1;SET SQL_LOG_BIN=1;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"reset master;"
sed -i 's/super_read_only = off/super_read_only = on/g' /etc/my.cnf
sed -i 's/loose-group_replication_start_on_boot = off/loose-group_replication_start_on_boot = on/g' /etc/my.cnf
- 6.在其中一个节点上执行：
mysql -uroot -pAa.1234567 --connect-expired-password -e"CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Aa.1234567' FOR CHANNEL 'group_replication_recovery';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"SET GLOBAL group_replication_bootstrap_group=ON;"
mysql -uroot -pAa.1234567 --connect-expired-password -e"START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa.1234567';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"SET GLOBAL group_replication_bootstrap_group=OFF;"
# 如果报错：Unknown system variable 'group_replication_bootstrap_group'，就要执行下面的命令添加MGR插件
# mysql -uroot -pAa.1234567 --connect-expired-password -e"INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
- 7.在另外两个节点执行：
mysql -uroot -pAa.1234567 --connect-expired-password -e"CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Aa.1234567' FOR CHANNEL 'group_replication_recovery';"
mysql -uroot -pAa.1234567 --connect-expired-password -e"START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa.1234567';"
- 8.查看集群状态
mysql -uroot -pAa.1234567 --connect-expired-password -e"SELECT * FROM performance_schema.replication_group_members;"
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 332573b8-da8d-11ed-8d65-00505689861b | 192.168.7.53 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | 3a99e57b-da8d-11ed-960d-005056896304 | 192.168.7.54 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | 418d32d0-da8c-11ed-a069-000c29b15814 | 192.168.7.52 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
- 9.查看主从延迟情况
mysql -uroot -pAa.1234567 -e"SELECT m.member_host as 节点IP,m.member_role as 角色,m.member_state as 状态,@@global.gtid_executed as GTID集合,s.COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE as 延迟事务数,s.VIEW_ID as 视图ID FROM performance_schema.replication_group_members m LEFT JOIN performance_schema.replication_group_member_stats s ON m.member_id = s.member_id ORDER BY CASE m.member_role WHEN 'PRIMARY' THEN 1 WHEN 'SECONDARY' THEN 2 ELSE 3 END,m.member_host;"
+--------------+-----------+--------+-------------------------------------------+-----------------+---------------------+
| 节点IP | 角色 | 状态 | GTID集合 | 延迟事务数 | 视图ID |
+--------------+-----------+--------+-------------------------------------------+-----------------+---------------------+
| 192.168.7.53 | PRIMARY | ONLINE | 11dbbdea-c8b2-4069-9ecc-61700d6c89d3:1-20 | 0 | 17739841252336808:7 |
| 192.168.7.54 | SECONDARY | ONLINE | 11dbbdea-c8b2-4069-9ecc-61700d6c89d3:1-20 | 0 | 17739841252336808:7 |
| 192.168.7.52 | SECONDARY | ONLINE | 11dbbdea-c8b2-4069-9ecc-61700d6c89d3:1-20 | 0 | 17739841252336808:7 |
+--------------+-----------+--------+-------------------------------------------+-----------------+---------------------+
- 10.克隆（在有问题的节点上执行）
#查询是否有安装克隆插件
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS;
#如果克隆插件没有安装，安装克隆插件
#mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
#用root账号创建克隆用账号，设置clone_valid_donor_list变量，192.168.7.52为任意正常节点的ip或者hostname
mysql> CREATE USER 'recipient_clone_user'@'%' IDENTIFIED BY 'Aa123456';
mysql> GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'%';
mysql> SET GLOBAL clone_valid_donor_list = '192.168.7.52:3306';
#设置网络带宽上限为5M.根据实际情况调整
#注克隆期间正常节点不能执行ddl操作,否则会克隆失败
mysql> set global clone_max_network_bandwidth=5;
mysql> exit;
#切换到克隆账号开始克隆
mysql -urecipient_clone_user -pAa123456
#rpl_user应当有BACKUP_ADMIN权限。没的话加一下 192.168.7.52改成上面clone_valid_donor_list里的
mysql> CLONE INSTANCE FROM 'rpl_user'@'192.168.7.52':3306 IDENTIFIED BY 'Aa.1234567';
#克隆成功后接收方mysql会自动重启
#查看克隆进度
mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
#查看克隆状态
select
state,
cast(begin_time as datetime) as "start time",
case
when end_time is null then lpad(sys.format_time(power(10, 12) * (unix_timestamp(now()) - unix_timestamp(begin_time))), 10, ' ')
else lpad(sys.format_time(power(10, 12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ')
end as duration,
source,
destination,
binlog_file,
binlog_position,
gtid_executed
from
performance_schema.clone_status G;
#克隆结束后切换root账号登录可以直接重新加入集群
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa.1234567';