安装 MySQL 通用版 MGR集群

版本下载地址：

https://downloads.mysql.com/archives/community/

文档安装版本：

Product Version:    8.0.30
Operating System:   Linux - Generic
OS Version:         Linux - Generic (glibc 2.12) (x86, 64-bit)
3台 虚拟机分别为
192.168.3.110
192.168.3.111
192.168.3.112

下载

[root@test ~]# cd /opt 
[root@test opt]# mkdir packages 
[root@test opt]# cd packages 
[root@test packages]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

1)安装依赖

[root@test packages]# yum -y install ncurses ncurses-devel gcc libtool gcc-c++ make cmake aio bison libaio

2)解压

[root@test packages]# tar -xf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz -C /usr/local && cd /usr/local && mv mysql-8.0.30-linux-glibc2.12-x86_64 mysql-8.0.30

3)创建目录

[root@test local]# mkdir -p /data/mysql-3306
[root@test local]# mkdir -p /usr/local/mysql-3306/etc

4)创建账号

[root@test local]# groupadd -g 1024 mysql && useradd -u 1024 -g 1024 -s /shin/nologin mysql
[root@test local]# chown -R mysql.mysql /data/mysql-3306
[root@test local]# chown -R mysql.mysql /usr/local/mysql-8.0.30

5)修改配置

配置在文档末尾
[root@test local]# vim /usr/local/mysql-3306/etc/my-3306.cnf

6)初始化

[root@test local]# /usr/local/mysql-8.0.30/bin/mysqld  --defaults-file=/usr/local/mysql-8.0.30/etc/my-3306.cnf --initialize --basedir=/usr/local/mysql-8.0.30 --datadir=/data/mysql-3306 --user=mysql

7)启动服务

[root@test local]# /usr/local/mysql-8.0.30/bin/mysqld_safe  --defaults-file=/usr/local/mysql-8.0.30/etc/my-3306.cnf &

8)登录服务

密码查看：
[root@test local]# grep 'temporary password' /data/mysql-3306/error.log
登录数据库：
[root@test local]# /usr/local/mysql-8.0.30/bin/mysql -uroot -p --socket=/data/mysql-3306/mysql.sock  -A
修改root密码：
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

9)安装mgr插件

mysql> install plugin group_replication soname 'group_replication.so';
查看插件
mysql> show plugins;
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |

10)创建数据同步账号

mysql> set sql_log_bin=0;
mysql> create user 'repl'@'%' identified with mysql_native_password by '123456';
mysql> grant replication slave,replication client on *.* to 'repl'@'%';
mysql> flush privileges;
mysql >set sql_log_bin=1;

11)修改配置，重启服务

将 my-3306.cnf 配置文件中 mgr settings 取消注释
[root@test local]# /usr/local/mysql-8.0.30/bin/mysqladmin -uroot -p --socket=/data/mysql-3306/mysql.sock shutdown
[root@test local]# /usr/local/mysql-8.0.30/bin/mysqld_safe  --defaults-file=/usr/local/mysql-8.0.30/etc/my-3306.cnf &

12)修改hosts

[root@test local]# echo "192.168.3.110 test110.localdomain" >/etc/hosts
[root@test local]# echo "192.168.3.111 test111.localdomain" >/etc/hosts
[root@test local]# echo "192.168.3.112 test112.localdomain" >/etc/hosts

13)开始配置mgr，初始化第一个结点

13.1)初始化第一个节点 192.168.3.110
    mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
    mysql> set global group_replication_single_primary_mode=on;
    第一个执行，告诉mgr集群，我是第一个节点（第一个节点会选举为主节点）
    mysql> set global group_replication_bootstrap_group=ON;
    mysql> start group_replication;
    mysql> set global group_replication_bootstrap_group=OFF;

13.2)初始化第二第三节点 192.168.3.111 192.168.3.112
    mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
    mysql> reset master;
    mysql> start group_replication;

配置文件

[client]
port    = 3306
socket  = /data/mysql-3306/mysql.sock

[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash

[mysqld]
user    = mysql
port    = 3306
server_id = 330606
basedir = /usr/local/mysql-8.0.30
datadir = /data/mysql-3306
socket  = /data/mysql-3306/mysql.sock
pid_file = mysqldb.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 333666

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 400
table_definition_cache = 400
thread_stack = 512K
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600  
tmp_table_size = 96M
max_heap_table_size = 96M

#log settings
log_timestamps = SYSTEM
log_error = /data/mysql-3306/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/mysql-3306/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /data/mysql-3306/mybinlog
binlog_format = ROW
sync_binlog = 0
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
relay_log_recovery = 1
log_slave_updates=1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4 
slave_preserve_commit_order = 1
slave_checkpoint_period = 2
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_transaction_dependency_tracking = WRITESET

#首次启动注释这批
#mgr settings
# 第12个注释
#plugin_load_add='group_replication.so'
#transaction_write_set_extraction ='XXHASH64'
#loose-group_replication_group_name = '0a79659e-f0a4-11ed-9df6-000c29c57452'
#loose-group_replication_start_on_boot = off
#loose-group_replication_local_address = '192.168.3.107:33061'
#loose-group_replication_group_seeds ='192.168.3.119:33061,192.168.3.108:33061,192.168.3.107:33061'
#loose-group_replication_bootstrap_group = off
#loose-group_replication_recovery_retry_count=31536000
#loose-group_replication_single_primary_mode=on
#loose-group_replication_enforce_update_everywhere_checks=off
#loose-group_replication_ip_whitelist = '192.168.3.119,192.168.3.107,192.168.3.108'
##loose-group_replication_exit_state_action = READ_ONLY
#loose-group_replication_communication_max_message_size = 10M
#loose-group_replication_unreachable_majority_timeout = 30
#loose-group_replication_member_expel_timeout = 5
#loose-group_replication_autorejoin_tries = 288
#group_replication_consistency = EVENTUAL
#group_replication_flow_control_mode = DISABLED
#首次启动注释END

#loss-less semi-sync replication settings
#skip_slave_start
#semi-sync(master)
#loose-rpl_semi_sync_master_timeout = 99999999
#loose-plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose-rpl_semi_sync_master_enabled = 1
#loose-rpl_semi_sync_master_wait_for_slave_count = 1
#loose-rpl_semi_sync_master_wait_point = AFTER_SYNC
#semi-sync(slave)
#loose-rpl_semi_sync_slave_enabled = 1
#read_only = 1
#super_read_only = 1

#innodb settings
#transaction_isolation = REPEATABLE-READ
#transaction_isolation = REPEATABLE-READ
#innodb_buffer_pool_size = 512M
#innodb_buffer_pool_instances = 1
#innodb_data_file_path = ibdata1:12M:autoextend
#innodb_flush_log_at_trx_commit = 1 
#innodb_log_buffer_size = 32M
#innodb_log_file_size = 1G 
#innodb_log_files_in_group = 3
#innodb_max_undo_log_size = 4G
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
#innodb_open_files = 65535
#innodb_flush_method = O_DIRECT
#innodb_lru_scan_depth = 4000
#innodb_lock_wait_timeout = 10
#innodb_rollback_on_timeout = 1
#innodb_print_all_deadlocks = 1
#innodb_online_alter_log_max_size = 4G
##innodb_print_ddl_logs = 1
#innodb_status_file = 1
#innodb_status_output = 0
#innodb_status_output_locks = 1
#innodb_sort_buffer_size = 67108864
#innodb_adaptive_hash_index = OFF

#innodb monitor settings
#innodb_monitor_enable = "module_innodb"
#innodb_monitor_enable = "module_server"
#innodb_monitor_enable = "module_dml"
#innodb_monitor_enable = "module_ddl"
#innodb_monitor_enable = "module_trx"
#innodb_monitor_enable = "module_os"
#innodb_monitor_enable = "module_purge"
#innodb_monitor_enable = "module_log"
#innodb_monitor_enable = "module_lock"
#innodb_monitor_enable = "module_buffer"
#innodb_monitor_enable = "module_index"
#innodb_monitor_enable = "module_ibuf_system"
#innodb_monitor_enable = "module_buffer_page"
##innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick