MySQL 5.7 MGR单主集群搭建

大约 8 分钟约 2261 字

MySQL 5.7 MGR单主集群搭建

节点规划

节点名称	MySQL版本	节点IP地址
tsigndb01	5.7.38	10.130.31.22
tsigndb02	5.7.38	10.130.31.23
tsigndb03	5.7.38	10.130.31.24

节点MySQL 5.7安装（三个节点都操作）

# 在CentOS 7上安装MySQL 5.7数据库执行如下操作
curl -s http://download-soft.lead.cn/MySQL/linux/mysql57-general-binary-install.sh | bash
# 以上脚本方式部署MySQL 5.7数据库实例，会自动完成所有MySQL 5.7安装依赖，关闭系统SELINUX及防火墙配置，将MySQL安装在 /usr/local/mysql目录下，数据目录为/data/mysql,初始化数据名录 服务端口默认为3306。

操作系统基础及优化配置（三个节点都操作）

# 1. 配置时钟同步作业
cat >> /var/spool/cron/root <<EOF 
'*/5 * * * * /usr/sbin/ntpdate 10.130.255.1'
EOF
# 2. mysql 用户资源限制修改及内核参数调整
# limits conf
cat >> /etc/security/limits.conf <<EOF
mysql soft nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql hard nproc 10240
root soft nproc unlimited
EOF
cat >> /etc/sysctl.conf <<EOF
vm.swappiness=0
fs.file-max = 50000
kernel.shmmax = 4294967295
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.ip_local_port_range = 10240 65000
net.core.somaxconn = 65535
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_intvl = 3
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_tw_buckets = 55000
net.core.wmem_default = 8388608
net.core.wmem_max = 16777216
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
EOF
# 3. 文件系统挂载参数优化
# xfs 挂载参数  (rw,noatime,nodiratime,nobarrier)
# 4. 磁盘调度策略
# echo deadline >/sys/block/{DEV-NAME}/queue/scheduler

三个节点都配置主机别名

cat >> /etc/hosts <<EOF
10.130.31.22  tsigndb01
10.130.31.23  tsigndb02
10.130.31.24  tsigndb03
10.130.31.25  tsigndb.lead.cn
EOF

MGR集群第一个节点操作

# 1. 准备MySQL 5.7的配置
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
explicit_defaults_for_timestamp
socket=/data/mysql/mysql3309.sock
port=3309
event_scheduler=0
tmpdir=/data/mysql/tmp

symbolic-links=0

# timeout
interactive_timeout=300
wait_timeout=300

# character set
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci

open_files_limit=65535
max_connections=1200
max_connect_errors=1000000
lower_case_table_names=1

# logs
log-output=file
slow_query_log=1
slow_query_log_file=slow.log
log-error=error.log
#log_warnings=2
log_error_verbosity=2
pid-file=mysql.pid
long_query_time=2
log-slow-admin-statements=1
log-queries-not-using-indexes=1
log-slow-slave-statements=1

# binlog
log-bin=/data/mysql/logs/mysql-bin
log-slave-updates=1
binlog_format=row
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=512M
sync_binlog=1
sync-master-info=1
expire_logs_days=10

# procedure
log_bin_trust_function_creators=1

# gtid
server-id=22
gtid-mode=on
enforce-gtid-consistency=on

# relay log
# skip_slave_start=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6130"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.130.31.22:24901"
loose-group_replication_group_seeds="10.130.31.22:24901,10.130.31.23:24901,10.130.31.24:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on    # 多主时，设置on，单主时，设置off
loose-group_replication_enforce_update_everywhere_checks=off   # 多主时，设置on，单主时，设置off
loose-gorup_replication_ip_whitelist='10.30.162.0/24,127.0.0.1/8"
#max_relay_log_size=128M
#relay_log_purge=1
#relay_log_recovery=1
#relay-log=relay-bin
#relay-log-index=relay-bin.index
#log_slave_updates
#slave-skip-errors=1032,1053,1062
#skip-grant-tables

master_info_repository=table
relay_log_info_repository=table
binlog_checksum=NONE

# buffers & cache
table_open_cache=2048
table_definition_cache=4096
max_heap_table_size=96M
sort_buffer_size=32M
join_buffer_size=8M
thread_cache_size=200
query_cache_size=0
query_cache_type=0
query_cache_limit=256K
query_cache_min_res_unit=512
thread_stack=192K
tmp_table_size=96M
key_buffer_size=400M
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M

# innodb
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=9830M
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
innodb_print_all_deadlocks=1
innodb_log_buffer_size=20M
innodb_log_file_size=500M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=50
innodb_file_per_table=1
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=100
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so

[client]
port=3309
socket=/data/mysql/mysql3309.sock

[mysql]
prompt="\u@\h [\d]>" 
pager="less -i -n -S"
no-auto-rehash
default-character-set=utf8mb4
EOF

# 2. 启动MySQL 5.7实例
mysqld_safe --user=mysql & 
# 或
systemctl start mysqld

# 3. 在MySQL命令下操作，新增复制专用账号、启用复制插件
# 第一个节点配置 -- 开始
SET SQL_LOG_BIN=0;  
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Qwerty.123456';
FLUSH PRIVILEGES;
reset master;

set SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
SET GLOBAL group_replication_bootstrap_group=ON; 

# 开始同步操作
START GROUP_REPLICATION;
# 查看复制组成员节点
SELECT * FROM performance_schema.replication_group_members;

# 创建测试数据库
CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
# 添加测试数据
use kevin;
create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
insert into kevin.haha values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui");   
select * from kevin.haha;
# 第一个节点配置 -- 结束

MGR集群第二个节点操作

# 1. 准备MySQL 5.7的配置文件
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
explicit_defaults_for_timestamp
socket=/data/mysql/mysql3309.sock
port=3309
event_scheduler=0
tmpdir=/data/mysql/tmp


symbolic-links=0

# timeout
interactive_timeout=300
wait_timeout=300

# character set
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci

open_files_limit=65535
max_connections=1200
max_connect_errors=1000000
lower_case_table_names=1

# logs
log-output=file
slow_query_log=1
slow_query_log_file=slow.log
log-error=error.log
#log_warnings=2
log_error_verbosity=2
pid-file=mysql.pid
long_query_time=2
log-slow-admin-statements=1
log-queries-not-using-indexes=1
log-slow-slave-statements=1

# binlog
log-bin=/data/mysql/logs/mysql-bin
log-slave-updates=1
binlog_format=row
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=512M
sync_binlog=1
sync-master-info=1
expire_logs_days=10

# procedure
log_bin_trust_function_creators=1

# gtid
server-id=23
gtid-mode=on
enforce-gtid-consistency=on

# relay log
# skip_slave_start=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6130"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.130.31.23:24901"
loose-group_replication_group_seeds="10.130.31.22:24901,10.130.31.23:24901,10.130.31.24:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on    # 多主时，设置on，单主时，设置off
loose-group_replication_enforce_update_everywhere_checks=off   # 多主时，设置on，单主时，设置off
loose-gorup_replication_ip_whitelist='10.30.162.0/24,127.0.0.1/8"
#max_relay_log_size=128M
#relay_log_purge=1
#relay_log_recovery=1
#relay-log=relay-bin
#relay-log-index=relay-bin.index
#log_slave_updates
#slave-skip-errors=1032,1053,1062
#skip-grant-tables

master_info_repository=table
relay_log_info_repository=table
binlog_checksum=NONE

# buffers & cache
table_open_cache=2048
table_definition_cache=4096
max_heap_table_size=96M
sort_buffer_size=32M
join_buffer_size=8M
thread_cache_size=200
query_cache_size=0
query_cache_type=0
query_cache_limit=256K
query_cache_min_res_unit=512
thread_stack=192K
tmp_table_size=96M
key_buffer_size=400M
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M

# innodb
#innodb_data_home_dir=/data/mysql
#innodb_log_group_home_dir=/data/mysql 
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=9830M
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
innodb_print_all_deadlocks=1
innodb_log_buffer_size=20M
innodb_log_file_size=500M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=50
innodb_file_per_table=1
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=100
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so

[client]
port=3309
socket=/data/mysql/mysql3309.sock

[mysql]
prompt="\u@\h [\d]>" 
pager="less -i -n -S"
no-auto-rehash
default-character-set=utf8mb4
EOF

# 2. 启动MySQL 5.7实例
systemctl start mysqld
# 3. 以下复制组配置操作在MySQL命令行执行
# 第二个节点配置 -- 开始
SET SQL_LOG_BIN=0;  
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Qwerty.123456';
FLUSH PRIVILEGES;
reset master;

set SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
# 第二个节点配置 -- 结束

MGR集群第三个节点操作

# 1. 配置文件准备
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
explicit_defaults_for_timestamp
socket=/data/mysql/mysql3309.sock
port=3309
event_scheduler=0
tmpdir=/data/mysql/tmp

symbolic-links=0

# timeout
interactive_timeout=300
wait_timeout=300

# character set
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci

open_files_limit=65535
max_connections=1200
max_connect_errors=1000000
lower_case_table_names=1

# logs
log-output=file
slow_query_log=1
slow_query_log_file=slow.log
log-error=error.log
#log_warnings=2
log_error_verbosity=2
pid-file=mysql.pid
long_query_time=2
log-slow-admin-statements=1
log-queries-not-using-indexes=1
log-slow-slave-statements=1

# binlog
log-bin=/data/mysql/logs/mysql-bin
log-slave-updates=1
binlog_format=row
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=512M
sync_binlog=1
sync-master-info=1
expire_logs_days=10

# procedure
log_bin_trust_function_creators=1

# gtid
server-id=24
gtid-mode=on
enforce-gtid-consistency=on

# relay log
# skip_slave_start=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6130"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.130.31.24:24901"
loose-group_replication_group_seeds="10.130.31.22:24901,10.130.31.23:24901,10.130.31.24:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on    # 多主时，设置on，单主时，设置off
loose-group_replication_enforce_update_everywhere_checks=off   # 多主时，设置on，单主时，设置off
loose-gorup_replication_ip_whitelist='10.30.162.0/24,127.0.0.1/8"
#max_relay_log_size=128M
#relay_log_purge=1
#relay_log_recovery=1
#relay-log=relay-bin
#relay-log-index=relay-bin.index
#log_slave_updates
#slave-skip-errors=1032,1053,1062
#skip-grant-tables

master_info_repository=table
relay_log_info_repository=table
binlog_checksum=NONE

# buffers & cache
table_open_cache=2048
table_definition_cache=4096
max_heap_table_size=96M
sort_buffer_size=32M
join_buffer_size=8M
thread_cache_size=200
query_cache_size=0
query_cache_type=0
query_cache_limit=256K
query_cache_min_res_unit=512
thread_stack=192K
tmp_table_size=96M
key_buffer_size=400M
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M

# innodb
#innodb_data_home_dir=/data/mysql
#innodb_log_group_home_dir=/data/mysql 
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=9830M
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
innodb_print_all_deadlocks=1
innodb_log_buffer_size=20M
innodb_log_file_size=500M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=50
innodb_file_per_table=1
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=100
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so

[client]
port=3309
socket=/data/mysql/mysql3309.sock

[mysql]
prompt="\u@\h [\d]>" 
#pager="less -i -n -S"
no-auto-rehash
default-character-set=utf8mb4
EOF

# 2. 启动MySQL 5.7实例
systemctl start mysqld
# 3. 以下复制组配置操作在MySQL命令行执行
# 第三个节点配置 -- 开始
SET SQL_LOG_BIN=0;  
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'Qwerty.123456';
FLUSH PRIVILEGES;
reset master;

set SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
# 第三个节点配置 -- 结束

创建应用账号

# 在主节点上执行
alter user 'root'@'localhost' identified by 'Qwerty.123456';
# 创建应用账号
GRANT REPLICATION SLAVE ON *.* TO 'tsign'@'10.130.31.%' IDENTIFIED BY 'Tsign.2022';
CREATE DATABASE tsign DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
GRANT ALL ON tsign.* TO 'tsign'@'10.130.31.%';
FLUSH PRIVILEGES;

MGR集群所有节点全挂掉故障处理

## 故障处理
# 要是三个节点都发生故障的话，在节点的故障都恢复后，需要手动重新做组复制，操作流程如下：
# 第一个节点
mysql> reset master;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
  
# 第二个节点
mysql> reset master;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
  
# 第三个节点
mysql> reset master;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Qwerty.123456' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;

MGR集群单主切多主模式

######## 单主切多主 -- 开始
## 集群所有节点都执行，先将所有节点都停止组复制，并把多主复制参数设置off
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=ON;
set global group_replication_single_primary_mode=OFF;

## 随便在一个想要设置为主节点的节点上执行
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

## 在其它节点上执行组复制
start group_replication;
######## 单主切多主 -- 结束

MGR集群多主切单主模式

######## 多主切单主 -- 开始
## 集群所有节点都执行，先将所有节点都停止组复制，并把多主复制参数设置off
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;

## 随便在一个想要设置为主节点的节点上执行
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

## 在其它节点上执行组复制
start group_replication;
######## 多主切单主 -- 结束

如何确定当前节点是否为主节点？

-- 方法一、查看当前复制组的主节点的uuid及当前节点的uuid，如果两个uuid一致，则当前节点为复制组主节点
SHOW STATUS LIKE 'group_replication_primary_member';show variables like 'server_uuid';
select @@server_uuid AS this_server_uuid,t.* from performance_schema.global_status t where t.variable_name = 'group_replication_primary_member';
-- 方法二、使用proxysql官方视图查看
-- 如果read_only为YES，则当前节点为从节点，如果viable_candidate值YES，而read_only的值均为NO，则为主节点
select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)

MySQL 5.7 MGR单主集群搭建

# MySQL 5.7 MGR单主集群搭建

# 节点规划

# 节点MySQL 5.7安装（三个节点都操作）

# 操作系统基础及优化配置（三个节点都操作）

# 三个节点都配置主机别名

# MGR集群第一个节点操作

# MGR集群第二个节点操作

# MGR集群第三个节点操作

# 创建应用账号

# MGR集群所有节点全挂掉故障处理

# MGR集群单主切多主模式

# MGR集群多主切单主模式

# 如何确定当前节点是否为主节点？

MySQL 5.7 MGR单主集群搭建

节点规划

节点MySQL 5.7安装（三个节点都操作）

操作系统基础及优化配置（三个节点都操作）

三个节点都配置主机别名

MGR集群第一个节点操作

MGR集群第二个节点操作

MGR集群第三个节点操作

创建应用账号

MGR集群所有节点全挂掉故障处理

MGR集群单主切多主模式

MGR集群多主切单主模式

如何确定当前节点是否为主节点？