1.打补丁
需要打以下补丁,以及安装Visual C++ 2015否则mysqld.exe会报缺失下面两个dll文件,
从其他机器拷贝mysqld.exe会报无法找到程序入口点。
链接:https://pan.baidu.com/s/1OTyGN_122Weqe2T1jZzxqQ
提取码:odt8
vcruntime140.dll
vcruntime140_1.dll
2.解压MySQL安装包
使用的为免安装版本
mysql-8.0.20-winx64.zip
3.编写MySQL my.ini配置文件
[mysqld]
port = 4306
server-id = 430651
socket = D:\Program Files\MySQL80\data\4306\mysql.sock
pid-file = D:\Program Files\MySQL80\data\4306\mysql.pid
basedir = D:\Program Files\MySQL80
datadir = "D:\Program Files\MySQL80\data\4306\mysql\"
explicit_defaults_for_timestamp
character-set-server=utf8mb4
lower_case_table_names=1
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # 由于代码SQL原因,无法使用默认的所有sql mode,只能配置这两个,否则有大量sql执行报错
# Buffer
net_buffer_length = 8K
max_allowed_packet = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
# log
log-bin = "D:\Program Files\MySQL80\data\4306\logbin\mysql_bin"
log-bin-index = "D:\Program Files\MySQL80\data\4306\logbin\mysql_bin.index"
sync_binlog=1
binlog_cache_size = 512M
max_binlog_cache_size = 512M # 这个参数调整过,由于研发使用navicat从5.5库导入一个一千万表,作为一个事务,每次导入三百万时会超过该缓存上限,调整到4096M解决
max_binlog_size= 256M
binlog_format = row
log_output = FILE
log_error = D:\Program Files\MySQL80\data\4306\logs\mysql_error.log
slow_query_log = 1
slow_query_log_file = D:\Program Files\MySQL80\data\4306\logs\slow_query.log
general_log = 0
general_log_file = D:\Program Files\MySQL80\data\4306\logs\general_query.log
binlog_expire_logs_seconds = 2592000
relay_log = "D:\Program Files\MySQL80\data\mysql_relay" # relay_log一开始设置的路径过长,导致启动MGR无法找到mysql_relay.index
relay_log_index = "D:\Program Files\MySQL80\data\mysql_relay.index" # 缩短这两个路径参数重启即解决
max_relay_log_size = 1024M
relay_log_purge=ON
relay_log_recovery=ON
log_timestamps=SYSTEM
# InnoDB
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" # 由于之前有表为MyISAM,导致无法导入新库,去掉该参数,重新启动导入,随后再启用。
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 15360M
innodb_undo_directory = D:\Program Files\MySQL80\data\4306\undo
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_dump_pct=15
innodb_max_dirty_pages_pct=85
innodb_lock_wait_timeout=10
innodb_flush_log_at_trx_commit=2
innodb_fast_shutdown=1
innodb_io_capacity=1000
innodb_io_capacity_max=4000
transaction-isolation="READ-COMMITTED"
default_storage_engine=InnoDB
shared-memory
# gtid
gtid_mode=on
enforce_gtid_consistency=ON
master_verify_checksum=ON
sync_master_info=1
#connections
skip_name_resolve=1
max_heap_table_size=64M
connect_timeout=10
wait_timeout=600
interactive_timeout=600
net_read_timeout=30
net_write_timeout=30
max_connections=300
max_connect_errors=100000
max_user_connections=500
#query
long_query_time=1
log_queries_not_using_indexes=ON
log_throttle_queries_not_using_indexes=10
max_join_size=10000000000 # 由于原有系统代码会join超大量数据,所以在不调整代码前提,只能调整参数
max_execution_time=20000000
# parallel replicate
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
# group commit
binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000
# mysql group replicate
transaction_write_set_extraction=XXHASH64
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
slave-preserve-commit-order=on
loose_group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose_group_replication_start_on_boot=off
loose_group_replication_local_address="xx.xx.xx.xx:5306"
loose_group_replication_group_seeds="xx.xx.xx.xx:5306"
loose_group_replication_ip_whitelist="xx.xx.xx.xx,xx.xx.xx.xx" # 之前Linux 7.6配置MGR均无设置该参数,不报错,Windows Server需要设置该参数
loose_group_replication_bootstrap_group=off
loose_group_replication_recovery_get_public_key=on
4.配置services.msc,初始化mysql实例
# cmd下执行下面命令,配置服务:
"D:\Program Files\mysql80\bin\mysqld.exe" --install mysql80 --defaults-file="D:\Program Files\mysql80\my.ini"
# 由于该机器还有5.5版本MySQL,所以只能使用绝对路径,否则,会无法启动。
# 初始化实例
"D:\Program Files\mysql80\bin\mysqld.exe" --defaults-file="D:\Program Files\mysql80\my.ini" --initialize
# 查看errorlog,查看临时root密码,登陆修改
"D:\Program Files\mysql80\bin\mysql.exe" -uroot -p -P 4306
mysql> ALTER USER 'root'@'localhost' identified by 'password';
5.创建复制用户,创建业务用户
mysql> create user 'repl'@'%' identified by 'password';
# 由于之前业务使用5.5版本MySQL,导致无法使用最新认证插件,只能单独设置插件。
mysql> create user 'user'@'%' identified with mysql_native_password by 'password';
6.导入数据
# 由研发使用navicat导入源库数据,中间有根据导入数据报错调整一些my.ini参数:
max_join_size=10000000000
max_execution_time=20000000
max_binlog_cache_size = 512M
7.mysql备份
# cmd下使用:
mysqldump -uroot -p -P 4306 -A -B --master-data=2 --single-transaction > "D:\Program Files\mysql80\mysqlbak.sql"
# -A 导出所有数据库
# -B 导出一些列数据库,会包括use database语句
# --master-data=2 会将change master包含二进制日至名以及位置作为注释包含在输出。
# --single-transaction 生成一个数据库已执行快照,只支持InnoDB事务引擎,以及不能在备份期间执行ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE
8.MGR第二个节点恢复数据
mysql > tee D:\Program Files\mysql80\tee.log
mysql > source D:\Program Files\mysql80\mysqlbak.sql
mysql > notee
9.主节点启动MGR复制
9.1 修改my.ini,安装mgr插件
# 去掉loose_前缀所有参数。
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
9.2 重启mysql服务,启动第一个节点mgr
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 如果未成功启动MGR,查看error log,查看原因,具体问题具体解决。
mysql> SELECT * FROM performance_schema.replication_group_members;
9.3 第二个节点加入MGR
1)设置my.cnf参数
# 按照必须设置参数设置,启动mysql服务器。
# cmd下执行下面命令,配置服务:
"D:\Program Files\mysql80\bin\mysqld.exe" --install mysql80 --defaults-file="D:\Program Files\mysql80\my.ini"
# 由于该机器还有5.5版本MySQL,所以只能使用绝对路径,否则,会无法启动。
# 初始化实例
"D:\Program Files\mysql80\bin\mysqld.exe" --defaults-file="D:\Program Files\mysql80\my.ini" --initialize
2)安装插件
(root@localhost) [(none)] INSTALL PLUGIN group_replication SONAME 'group_replication.so';
3)创建复制用户
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl@'%';
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
9.4查看MGR状态
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SHOW STATUS LIKE 'group_replication_primary_member';