主从复制结构图
半同步复制结构图
1.创建用户
mysql -uroot -prootroot create user 'repuser'@'%' identified by 'repuser123'; grant replication slave on *.* to 'repuser'@'%'; flush privileges; select user,host from mysql.user;
2.安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so'; mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so'; mysql> show plugins;
plugin_dir=/mysql/app/mysql/lib/plugin/ plugin_load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
3.修改主库参数文件
#master modify parameter bind-address=0.0.0.0 server_id=813306 skip_name_resolve = ON #禁用 DNS 反向解析，将主机名连接转为 IP 地址连接。强烈建议开启 transaction-isolation = READ-COMMITTED innodb_flush_log_at_trx_commit =1 #master add parameter log_bin=/mysql/log/3306/binlog/fgedudb-binlog binlog_rows_query_log_events=on #在ROW格式的binlog中记录原始的SQL语句，便于调试和审计。 binlog_gtid_simple_recovery=1 #控制 MySQL 在启动或重启时如何扫描 binlog 文件来初始化 GTID 集合。 binlog_row_metadata=full #在 ROW 格式的 binlog 中记录完整的表结构元数据信息 binlog_expire_logs_seconds=604800 ##控制是否信任存储过程/函数创建者，允许在主从复制环境中创建存储函数而不要求 SUPER 权限 log_bin_trust_function_creators = 1 sync_binlog = 1 relay-log=/mysql/log/3306/relaylog/fgedudb-relaylog #-------------------------- #replication settings #-------------------------- gtid_mode = ON enforce_gtid_consistency = 1 report-host=fgedu81 report-port=3306 #-------------------------- #slave parameter #-------------------------- #read_only=1 #log-slave-updates = 1 #relay_log_recovery=1 #slave_skip_errors = ddl_exist_errors #------------------------------------ #semi sync replication settings #------------------------------------ plugin_dir=/mysql/app/mysql/lib/plugin/ plugin_load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so" loose_rpl_semi_sync_source_enabled = 1 loose_rpl_semi_sync_replica_enabled = 1 loose_rpl_semi_sync_source_timeout = 5000 rpl_semi_sync_source_wait_point = AFTER_SYNC rpl_semi_sync_source_wait_for_replica_count = 1
4.修改从库参数文件
#slave modify parameter bind-address=0.0.0.0 server_id=823306/833306 skip_name_resolve = ON transaction-isolation = READ-COMMITTED innodb_flush_log_at_trx_commit =1 #slave add parameter log_bin=/mysql/log/3306/binlog/fgedudb-binlog binlog_rows_query_log_events=on binlog_gtid_simple_recovery=1 binlog_row_metadata=full binlog_expire_logs_seconds=604800 log_bin_trust_function_creators = 1 sync_binlog = 1 relay-log=/mysql/log/3306/relaylog/fgedudb-relaylog #-------------------------- #replication settings #-------------------------- gtid_mode = ON enforce_gtid_consistency = 1 report-host=fgedu82/83 report-port=3306 #-------------------------- #slave parameter #-------------------------- log-slave-updates = 1 read_only=1 relay_log_recovery=1 slave_skip_errors = ddl_exist_errors #------------------------------------ #semi sync replication settings #------------------------------------ plugin_dir=/mysql/app/mysql/lib/plugin/ plugin_load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so" loose_rpl_semi_sync_source_enabled = 1 loose_rpl_semi_sync_replica_enabled = 1 loose_rpl_semi_sync_source_timeout = 5000 rpl_semi_sync_source_wait_point = AFTER_SYNC rpl_semi_sync_source_wait_for_replica_count = 1
5.clone主库到从库
#安装插件 install plugin clone soname 'mysql_clone.so'; #从库节点执行克隆任务 show global variables like '%gtid%'; set global super_read_only=off; set global clone_valid_donor_list='192.168.18.81:3306'; clone instance from 'root'@'192.168.18.81':3306 identified by 'rootroot'; set global super_read_only=on; #查看克隆进度 select * from performance_schema.clone_progress; #修改从库uuid SELECT UUID() as new_uuid \G vi auto.cnf SHOW GLOBAL VARIABLES LIKE 'server_uuid';
6.配置主从关系
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.18.81', SOURCE_PORT=3306, SOURCE_USER='repuser', SOURCE_PASSWORD='repuser123', GET_SOURCE_PUBLIC_KEY=1, SOURCE_AUTO_POSITION = 1;
7.常用命令
#8.0.22之后 SHOW REPLICAS; SHOW REPLICA STATUS\G STOP REPLICA; STOP REPLICA IO_THREAD; STOP REPLICA SQL_THREAD; START REPLICA; START REPLICA IO_THREAD; START REPLICA SQL_THREAD; reset REPLICA; #8.0.22之前 start slave; show slave status\G start master; show master status\G reset master; reset slave
