MySQL mgr部署文档
一、环境说明
1.1服务器信息
1.2目录规划
1.2目录规划
二、环境配置
2.1 关闭防火墙和selinux
service iptabls stop /etc/selinux/config SELINUX=disabled
2.2 配置主机名
hostname
2.3 配置ip域名映射
vi /etc/hosts 172.16.104.8 172-16-104-8 172.16.104.9 172-16-104-9 172.16,104.10 172-16-104-10
2.4 操作系统调优
cat>>/etc/sysctl.conf <<EOF vm.swappiness = 0 fs.aio-max-nr = 1048576 fs.file-max = 681574400 kernel.shmmax = 137438953472 kernel.shmmni = 4096 kernel.sem = 250 32000 100 200 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 EOF cat>>/etc/security/limits.conf <<EOF mysql soft nproc 65536 mysql hard nproc 65536 mysql soft nofile 65536 mysql hard nofile 65536 EOF cat>>/etc/profile<<EOF if [ $USER = "mysql" ]; then ulimit -u 16384 -n 65536 fi EOF
2.5 关闭numa
cat>>/etc/sysctl.conf <<EOF vm.swappiness = 0 fs.aio-max-nr = 1048576 fs.file-max = 681574400 kernel.shmmax = 137438953472 kernel.shmmni = 4096 kernel.sem = 250 32000 100 200 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 EOF cat>>/etc/security/limits.conf <<EOF mysql soft nproc 65536 mysql hard nproc 65536 mysql soft nofile 65536 mysql hard nofile 65536 EOF cat>>/etc/profile<<EOF if [ $USER = "mysql" ]; then ulimit -u 16384 -n 65536 fi EOF
三、部署mysql
3.1 创建用户
groupadd mysql useradd -g mysql mysql -s /sbin/nologin id mysql
3.2 配置目录
mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/3.3 解压安装包
mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/3.4 配置环境变量
mkdir -pv /data/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /data/mysql/3.5 修改配置文件
[client] port = 3306 socket = /data/mysql/data/mysql.sock [mysqld] user=mysql port=3306 basedir=/usr/local/mysql datadir=/data/mysql/data tmpdir =/data/mysql/tmp socket=/data/mysql/data/mysql.sock log-error = /data/mysql/logs/error.log symbolic-links=0 server-id=30061 //各节点server-id不要一致 port=3306 gtid_mode=on enforce_gtid_consistency=on log-bin = /data/mysql/binlog/binlog binlog_format=row binlog_checksum=none log_slave_updates=on master_info_repository=TABLE relay_log_info_repository=TABLE relay_log = /data/mysql/binlog/relaylog transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="02eb0070-4419-463f-ac3aece40c0f6ac0" loose-group_replication_start_on_boot=off loose-group_replication_member_weigth = 40 loose-group_replication_local_address="172.16.104.8:30061" //各节点服务器ip:server-id loose-group_replication_group_seeds="172.16.104.8:30061,172.16.104.9:30062,172.16.104.10:30063"
3.6 初始化
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure & //初始化加上--initialize-insecure 选项会设置密码为空
3.7 启动mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
四、配置MGR
4.1启动mgr单主模式
#以下步骤所有节点均执行 mysql #登陆数据库 mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件 mysql> SET SQL_LOG_BIN=0; #关闭binlog mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl'; #创建复制账号 mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; #授权 mysql> FLUSH PRIVILEGES; #刷新权限 mysql> SET SQL_LOG_BIN=1; #开启binlog mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #创建复制 #启动MGR，在172.16.104.8上执行 mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; #将其他节点加入MGR，在从库172.16.104.9、172.16.104.10上执行 mysql> START GROUP_REPLICATION; 查看MGR组信息 root@mysql 02:30: [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 19b47031-c397-11ea-a83b-000c29652de7 | node2 | 3306 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | 627eae86-c38a-11ea-9dcf-000c2989a02a | node1 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 652b5dc9-c397-11ea-a416-000c2936f782 | node3 | 3306 | ONLINE | SECONDARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) #如果在启动过程中报错，可以参考 performance_schema.replication_connection_status或者日志输出解决问题。
4.2切换为MGR多主模式
#以下步骤所有节点均执行 mysql> stop group_replication; #是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了 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; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 172.16.104.8 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 172.16.104.9 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 172.16.104.10|3306| ONLINE | PRIMARY | 8.0.17 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.03 sec)
4.3切换为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.0.118上执行 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; #在其余节点执行 START GROUP_REPLICATION; 查看MGR组信息 mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 | 192.168.0.118 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier | 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 | 3306 | ONLINE | SECONDARY | 8.0.17 | | group_replication_applier | c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 | 3306 | ONLINE | SECONDARY | 8.0.17 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
4.4单主模式下的failover
#mgr查找主节点： SHOW STATUS LIKE 'group_replication_primary_member'; SELECT * FROM performance_schema.replication_group_members; #模拟主节点宕机： [root@172-16-104-8 data]# mysqladmin -uroot -p shutdown Enter password: 2020-10-28T11:54:54.927610Z mysqld_safe mysqld from pid file /data/mysql/data/172-16-104-8.pid ended [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf (wd: /data) (wd now: /data/mysql/data) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 | 172-16-104-10 | 3306 | ONLINE | | group_replication_applier | 79f6ff2c-18e4-11eb-964a-fa566a1b9b00 | 172-16-104-9 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 2 rows in set (0.00 sec) #主节点切换到了节点172.16.104.10 mysql> SHOW STATUS LIKE 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 | +----------------------------------+--------------------------------------+ 1 row in set (0.02 sec) mysql> show variables like '%group_replication_member_weight%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | group_replication_member_weight | 50 | +---------------------------------+-------+ 1 row in set (0.01 sec) 将主节点重启后，重新加入 mysql> select * from performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+-----------+-------------+-------------+--------------+ | group_replication_applier | | | NULL | OFFLINE | +---------------------------+-----------+-------------+-------------+--------------+ 1 row in set (0.00 sec) mysql> set global group_replication_single_primary_mode=on; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start group_replication; Query OK, 0 rows affected (3.01 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 0f2c4453-18e6-11eb-b5c0-faebd3a13700 | 172-16-104-10 | 3306 | ONLINE | | group_replication_applier | 10517159-18cd-11eb-bd67-faa7dd2bc000 | 172-16-104-8 | 3306 | ONLINE | | group_replication_applier | 79f6ff2c-18e4-11eb-964a-fa566a1b9b00 | 172-16-104-9 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 3 rows in set (0.00 sec)
