新手教程 | MySQL主从复制安装指南，看完就上手
MGR 是MySQL 官方推出的新高可用集群解决方案，但MGR 集群不太适合跨城进行容灾部署，特别是跨城带宽小延迟较高的情况下。因此，需要额外设计跨城容灾部署方案。MGR 集群可以外接读库或MGR集群（异地容灾库）形成高可用容灾方案，本文针对该架构进行安装部署。
注：本文中使用的是mysql 8.0 版本进行部署安装。
集群名称 |
成员IP |
所在城市 |
主机名 |
mgr_cluster1 |
172.16.20.187 |
A |
node187 |
|
172.16.20.188 |
A |
node188 |
|
|
172.16.20.233 |
A |
node233 |
|
mgr_cluster2 |
172.16.20.234 |
B |
node234 |
|
172.16.20.237 |
B |
node237 |
|
|
172.16.20.240 |
B |
node240 |
注：mgr_cluster1 集群为主库，mgr_cluster2作为从库与mgr_cluster1进行数据同步
1. 推荐使用8.0.19 版本；mysql软件包可从mysql官网下载。
2. 上传MySQL 软件安装包至服务器/opt 目录中。
#vim /etc/hosts
172.16.20.187 node187
172.16.20.188 node188
172.16.20.233 node233
4. 解压mysql 8.0 软件包。
#cd /opt
#useradd mysql
#mkdir –p /opt/dbaas
#tar -xf mysql-commercial-8.0.19-linux-glibc2.12-x86_64.tar.xz –C /opt/dbaas
#mv mysql-commercial-8.0.19-linux-glibc2.12-x86_64 mysql-8.0
#ln –s mysql-8.0 mysql
5. 编辑生成mysql配置文件。
#mkdir –p /opt/dbaas/mysql/etc
#touch /opt/dbaas/mysql/etc/myextra_3306.cnf
#chown –R mysql:mysql /opt/dbaas/mysql
#vi /opt/dbaas/mysql/etc/my_mgrcluster1_3306.cnf
[instance]
instance_name=mgrcluster1
[mysqld]
#采用传统密码认证加密方式，否则搭建主从复制时需使用证书认证访问
default_authentication_plugin=mysql_native_password
#加载mysql_clone、group_replication插件
#mysql_clone插件用于从机快速向主机拉起恢复数据
plugin_load = "mysql_clone.so;group_replication.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#admin service
admin_address=127.0.0.1
admin_port=33062
disconnect_on_expired_password=0
#basic settings
user = mysql
port = 3306
socket = /data/dbaas/mysql/3306/prod/mysql.sock
pid-file = /data/dbaas/mysql/3306/prod/mysql.pid
character_set_server = utf8mb4
collation_server = utf8mb4_bin
basedir = /opt/dbaas/mysql-8.0
datadir = /data/dbaas/mysql/3306/data
log_error = mysqld.err
read_only = 0
super_read_only = 0
transaction_isolation = READ-COMMITTED
default_storage_engine = innodb
sql_mode = "STRICT_TRANS_TABLES"
log_timestamps = SYSTEM
secure_file_priv = ''
#connection settings
back_log = 2048
max_connections = 240 #根据实际情况修改
max_connect_errors = 1000000
interactive_timeout = 1800
wait_timeout = 1800
max_allowed_packet = 64M
skip_name_resolve = on
#session settings
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 2M
#innodb settings
#innodb_buffer_pool_size = 1g
innodb_io_capacity = 200
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_method = O_DIRECT
#innodb_log_file_size = 1G
innodb_purge_threads = 4
innodb_strict_mode = on
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 25
innodb_flush_log_at_trx_commit = 1
innodb_log_files_in_group = 3
innodb_undo_directory = /data/dbaas/mysql/binlog/3306/dblogs/undo
innodb_undo_log_truncate = on
innodb_undo_tablespaces = 3
innodb_purge_rseg_truncate_frequency = 128
#replication settings
server_id = 351294564 #注意保持MGR集群中每个数据库实例id唯一
log-bin = /data/dbaas/mysql/binlog/3306/dblogs/bin/binlog.log
sync_binlog = 1
binlog_format = row
master_info_repository = table
relay_log_info_repository = table
relay_log_recovery = on
log_slave_updates = on
expire_logs_days = 5
slave_net_timeout = 60
relay_log = /data/dbaas/mysql/binlog/3306/dblogs/relay/relay.log
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 50
slave_preserve_commit_order = on
#GTID
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = on
gtid_executed_compression_period = 100000
#slow log
slow_query_log = on
log_slow_admin_statements = on
log_output = table,file
long_query_time = 2
slow_query_log_file = /data/dbaas/mysql/binlog/3306/dblogs/mysql-slow.log
#others
open_files_limit = 65535
lower_case_table_names = 1
max_heap_table_size = 32M
tmp_table_size = 32M
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 16
#mgr
report_host=172.16.20.187
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
6. 创建对应目录，并赋权。
#mkdir -p /data/dbaas/mysql/3306/{prod,data}
#mkdir -p /data/dbaas/mysql/binlog/3306/dblogs/{bin,undo,relay}
#chown –R mysql:mysql /data/dbaas/mysql/3306
#chown –R mysql:mysql /data/dbaas/mysql/binlog/3306
7. 创建mysql 实例。
#cd /opt/dbaas/mysql/bin
# ./bin/mysqld --defaults-file=/opt/dbaas/mysql/etc/my_mgrcluster1_3306.cnf --initialize-insecure
8. 编辑配置文件，增加mgr相关配置参数
#cd /opt/dbaas/mysql-8.0/etc
# vim my_mgrcluster1_3306.cnf
group-replication-ip-whitelist='0.0.0.0/0'
group_replication_bootstrap_group= off
group_replication_start_on_boot=off
group_replication_local_address="172.16.20.187:24901"
group_replication_group_seeds="172.16.20.187:24901,172.16.20.188:24901,172.16.20.233:24901"
#同一MGR集群group_name需配置一致
group_replication_group_name='5f79d556-44dc-498f-bfb6-07bf61930fed'
9. 启动mysql 实例。
# cd /opt/dbaas/mysql/
#./bin/mysqld_safe --defaults-file=/opt/dbaas/mysql/etc/my_ mgrcluster1_3306.cnf &
10. 登录mysql 数据库，修改密码（默认密码为空）。
#/opt/dbaas/mysql/bin/mysql -uroot –h localhost –p
Mysql>SET sql_log_bin=0;
Mysql> install plugin clone soname ‘mysql_clone.so’;
#修改root用户密码
Mysql> alter user 'root'@'localhost' identified by '123456';
#创建mgr复制用户，主从复制用户也可以用该用户
Mysql>create user repl@'%'identified by ‘repl_password’;
#赋予repl用户权限，BACKUP_ADMIN 权限用于clone 数据
Mysql>grant REPLICATION SLAVE,REPLICATION CLIENT,BACKUP_ADMIN on *.* to repl@ '%';
Mysql>exit;
11.重复1~9 步骤完成两外两台数据库实例初始化安装。
12. 使用克隆插件从MGR 主库（第一台安装的MySQL数据库实例）同步数据（由于是新安装环境，每台机器上数据都是完全一样的，所以该步骤可不操作；操作了也没有影响）。
#/opt/dbaas/mysql/bin/mysql -uroot –h localhost –p
Mysql>set global clone_valid_donor_list=’172.16.20.187:3306’
# clone instance from repl@’主机IP’:主机mysql端口 identified by ‘repl用户密码’;
Mysql>clone instance from [email protected]:3306 identified by 'repl_password';
Mysql>exit;
这part结束啦~
1. 登录第一台mysql数据库实例，执行命令启动MGR。
#/opt/dbaas/mysql/bin/mysql -uroot –h localhost –p
Mysql>set global group_replication_bootstrap_group=on;
Mysql>change master to master_user='repl',master_password='repl_password' for channel 'group_replication_recovery';
Mysql>start group_replication;
Mysql>set global group_replication_bootstrap_group=off;
Mysql>exit;
2. 依次登录剩余两台mysql数据库实例，启动MGR。
#/opt/dbaas/mysql/bin/mysql -uroot –h localhost –p
Mysql>change master to master_user='repl',master_password='repl_password' for channel 'group_replication_recovery';'repl_password';
Mysql>start group_replication;
Mysql>exit;
操作步骤参见C,D章节。
注意：
1.第二套集群的service_id、group_replication_group_name 等相关参数需要进行修改，避免与mgr_cluster1集群相同。
2.在使用clone 插件进行数据同步时，第二套集群的各个示例节点直接与mgr_cluster1集群主节点进行数据复制同步。
建立两套集群主从复制关系
1.登录mgr_cluster2主节点，执行命令创建主从复制关系。
#/opt/dbaas/mysql/bin/mysql -uroot –h localhost –p
Mysql>change master to master_user=’repl’,master_host=’172.16.20.187’,master_port=’3306’,master_password=’repl_password’,master_auto_position=1;
Mysql>start slave;
Mysql>show slave status\G;
2.验证主从复制关系，在mgr_cluster1集群主节点上写入数据。查看mgr_cluster2集群上个节点数据是否已同步。
本文中使用了mgr单主集群模式，有同学可能会有疑问：若mgr_cluster1集群master切换了，mgr_cluster2 与其的主从复制关系不是断开了吗或是mgr_cluster2 集群切换了对应主从复制关系也会断开？
这些遗留就需要同学们自行开发相关脚本程序对mgr集群切换后的主从进行自动维护了
Head picture by Ilya Pavlov on Unsplash