MGR手动创建

MGR手动创建

1.环境准备

1.1 部署规划

MySQL数据库节点	IP地址	端口	备注
wl_01	172.31.0.101	3306	MySQL8.0.28
wl_02	172.31.0.102	3306	MySQL8.0.28
wl_03	172.31.0.103	3306	MySQL8.0.28

1.2 所有节点安装MySQL-Server

#安装依赖包
[root@wl_01 opt]# yum install -y gcc gcc-c++ ncurses-devel.x86_64 libaio bison gcc-c++.x86_64 perl perl-devel libssl-dev autoconf openssl-devel openssl numactl vim net-tools sysstat wget

#下载二进制包
[root@wl_01 ~]#  cd /opt/
[root@wl_01 opt]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

#解压二进制包
[root@wl_01 opt]# tar xf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

#将解压后的目录软连接到/usr/local下
[root@wl_01 opt]# ln -s /opt/mysql-8.0.28-linux-glibc2.12-x86_64 /usr/local/mysql_8028

#添加相关环境变量
[root@wl_01 opt]# tail -1 /etc/profile
export PATH=/usr/local/mysql_8028/bin:$PATH
[root@wl_01 opt]# source /etc/profile
[root@wl_01 opt]# mysql -V
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

1.3 创建相关目录

[root@wl_01 opt]# mkdir /mysql_8028/{data,logs,binlog,relaylog,run,tmp} -p
[root@wl_01 opt]# groupadd -g 1100 mysql
[root@wl_01 opt]# useradd -g mysql -u 1100 mysql
[root@wl_01 opt]# passwd mysql
Changing password for user mysql.
New password:
BAD PASSWORD: The password contains less than 2 non-alphanumeric characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@wl_01 opt]# chown -R mysql.mysql /mysql_8028/

1.4 编辑配置文件

[root@wl_01 opt]# vim /mysql_8028/my.cnf
[mysql]
show-warnings
prompt="\u@\h : \d \r:\m:\s> "
default_character_set=utf8mb4
socket=/mysql_8028/run/mysql.sock

[mysqld]
user=mysql
port=3306
server_id=1013306 #根据节点自行设置
report_host=172.31.0.101 #根据节点自行设置
report_port=3306
default_time_zone='+8:00'
basedir=/usr/local/mysql_8028
datadir=/mysql_8028/data
tmpdir=/mysql_8028/tmp
pid_file=/mysql_8028/run/mysqld.pid
socket=/mysql_8028/run/mysql.sock
lower_case_table_names=1
character_set_server=utf8mb4
skip_name_resolve
default_storage_engine=INNODB

##connections
max_connections=2000
max_connect_errors=99999
max_allowed_packet=1G
wait_timeout=172800
interactive_timeout=172800

##buffer
net_buffer_length=8K   #设置每个客户端会话线程相关的连接缓冲区和结果集缓冲区初始大小
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
max_length_for_sort_data=16k
join_buffer_size=4M
binlog_cache_size=2M
max_heap_table_size=10M
tmp_table_size=10M
internal_tmp_mem_storage_engine=MEMORY
open_files_limit=65535
table_open_cache=20000       #控制全局打开表数
table_definition_cache=20000   #缓存表定义文件.frm的数量
thread_cache_size=512

##log
general_log=on
general_log_file=/mysql_8028/logs/general.log
log_error=/mysql_8028/logs/error.log
innodb_print_all_deadlocks= 1
long_query_time= 1
slow_query_log= 1
slow_query_log_file=/mysql_8028/logs/slow.log
log_bin=/mysql_8028/binlog/mysql_bin
binlog_format=ROW
max_binlog_size=1G
binlog_rows_query_log_events=1
binlog_expire_logs_seconds=1209600  #14天
sync_binlog=1
relay_log=/mysql_8028/relaylog/mysql_relay
relay_log_recovery=1
log_slave_updates=1


##replica
gtid_mode=on  #开启gtid模式，全局唯一事务id，方便运维管理
enforce_gtid_consistency=true
slave_parallel_workers=16
slave_parallel_type=LOGICAL_CLOCK
replica_preserve_commit_order=ON

##transaction
transaction_isolation=READ-COMMITTED
innodb_thread_concurrency=64
innodb_lock_wait_timeout=50
innodb_rollback_on_timeout=1
lock_wait_timeout=120

##innodb
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1
innodb_max_dirty_pages_pct=75
innodb_flush_neighbors=0
innodb_data_home_dir=/mysql_8028/data
innodb_data_file_path=ibdata1:2048M:autoextend
innodb_file_per_table
innodb_strict_mode=1
innodb_flush_method=O_DIRECT 
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_io_capacity=2000
#redolog
innodb_log_group_home_dir=/mysql_8028/data
innodb_log_buffer_size=64M
innodb_log_file_size=2G
innodb_log_files_in_group=2  #设置redo log日志组中有多少个redo log文件
innodb_flush_log_at_trx_commit=1
#undo
innodb_undo_directory=/mysql_8028/data   #设置独立与系统表空间的undo logs文件存放路径
innodb_purge_threads= 4

#binlog_checksum=none #8.0.20及以下版本MGR无法使用校验，不支持二进制日志中的校验
binlog_checksum=CRC32 #8.0.21及以上版本可设置为默认值CRC32
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64 #需要使用算法对事务每行的主键进行计算，并记录到writeset，用于检测冲突以及并行复制
plugin_load_add='group_replication.so'
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #对于组复制，数据必须存储在 InnoDB 事务存储引擎中。使用其他存储引擎，可能会导致出错。
loose_group_replication_single_primary_mode=on #多主模式设置为off
loose_group_replication_enforce_update_everywhere_checks=off #多主模式设置为on，开启一致性检测 
loose_group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot=OFF
loose_group_replication_local_address= "172.31.0.101:33061" #根据节点自行设置
loose_group_replication_group_seeds= "172.31.0.101:33061,172.31.0.102:33061,172.31.0.103:33061"
loose_group_replication_bootstrap_group=OFF
loose_group_replication_member_expel_timeout=10  #产生怀疑到驱逐出集群的时间，单位秒,8.0.13及以上版本可配置，减少网络波动造成的成员异常逐出
loose_group_replication_transaction_size_limit=0 #限制事物大小，单位字节，0代表不限制

2.创建MGR集群

2.1 所有节点初始化启动

[root@wl_01 ~]# mysqld --defaults-file=/mysql_8028/my.cnf --initialize-insecure
[root@wl_01 ~]# mysqld_safe --defaults-file=/mysql_8028/my.cnf &

2.2 查看组复制插件状态

[root@wl_01 ~]# mysql -S /mysql_8028/run/mysql.sock

mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like '%group_replication%';
+-------------------+---------------+
| PLUGIN_NAME       | PLUGIN_STATUS |
+-------------------+---------------+
| group_replication | ACTIVE        |
+-------------------+---------------+

2.3 所有节点创建组复制用户

[root@wl_01 ~]# mysql -S /mysql_8028/run/mysql.sock
SET SQL_LOG_BIN=0;
CREATE USER repl_user@'172.31.0.%' IDENTIFIED with mysql_native_password BY '123';
GRANT REPLICATION SLAVE ON *.* TO repl_user@'172.31.0.%';
GRANT BACKUP_ADMIN ON *.* TO repl_user@'172.31.0.%';
SET SQL_LOG_BIN=1;

2.4 开启组复制

'--- 以 wl_01 节点为 primary 引导组复制' 
#开启组复制引导参数
[root@wl_01 ~]# mysql -S /mysql_8028/run/mysql.sock
mysql> SET GLOBAL group_replication_bootstrap_group=ON;

#所有节点设置组复制通道
CHANGE MASTER TO 
MASTER_USER='repl_user', 
MASTER_PASSWORD='123'
FOR CHANNEL 'group_replication_recovery';

#所有节点开启组复制
mysql> START GROUP_REPLICATION;

#wl_01节点关闭组复制引导参数
[root@wl_01 ~]# mysql -S /mysql_8028/run/mysql.sock
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

#查看组复制成员
mysql> SELECT * FROM performance_schema.replication_group_members;

3.常用命令

3.1 切换主节点

select group_replication_set_as_primary('1b3163f8-5a57-11ed-845e-52549e082d61');

3.2 切换为多主模式

SELECT group_replication_switch_to_multi_primary_mode();

3.3 切换为单主模式

SELECT group_replication_switch_to_single_primary_mode();