Slave_IO_Running: Yes
1 IO错误 ：
1slave用户出错
2防火墙
3日志问题
Slave_SQL_Running: Yes
SQL错误：
回放数据与master端冲突
dev/mysql.com/doc/refman/5.7/en/
use mysql;
select * from gtid_executed;
SHOW STATUS LIKE 'Rpl_semi_sync%';
set global rpl_semi_sync_master_timeout=100;
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-monitoring.html
procotol 协议
mysql> show processlist;
+----+------+---------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| 2 | root | localhost | mysql | Query | 0 | starting | show processlist |
| 5 | repl | server5:41764 | NULL | Binlog Dump | 7375 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+---------------+-------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql 5.7并行复制
slave端修改即可
vim /etc/my.cnf
#relay中继
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=10
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
show processlist;
延迟复制(Delayed replication)
master端
CHANGE MASTER TO MASTER_DELAY = N; #N单位s
好处
防止误操作
测试多少延迟对业务的影响
www.percona.com
全同步（也叫组复制 Group replication）
server1
vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="4dec836e-5f04-40b0-b3da-e28b641707e4"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.11.4:24901"
loose-group_replication_group_seeds="172.25.11.4:24901,172.25.11.5:24901,172.25.11.6:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.11.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
#loose- 表示如果不识别仍然初始化
########uuidgen 生成一个组名
grep password /var/log/mysqld.log
mysql -p'Yakexi_007'
alter user root@localhost identified by 'Yakexi_007';
登陆后
SET SQL_LOG_BIN=0;
create user rpl_user@'%' identified by 'Yakexi_007';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yakexi_007'
FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
SET GLOBAL group_replication_bootstrap_group=ON; #第一台需要
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;#第一台需要
select * from group_replication;
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
server2
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="4dec836e-5f04-40b0-b3da-e28b641707e4"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.11.5:24901"
loose-group_replication_group_seeds="172.25.11.4:24901,172.25.11.5:24901,172.25.11.6:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.11.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
systemctl start mysql
alter user root@localhost identified by 'Yakexi_007';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Yakexi_007';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yakexi+007' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
reset master; #没有数据才可以 或者 reset slave; 看那一个冲突
START GROUP_REPLICATION;
server3 和2一样
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="4dec836e-5f04-40b0-b3da-e28b641707e4"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.11.6:24901"
loose-group_replication_group_seeds="172.25.11.4:24901,172.25.11.5:24901,172.25.11.6:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.11.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
systemctl start mysqld
alter user root@localhost identified by 'Yakexi_007';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Yakexi+007';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yakexi+007' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
reset master;
START GROUP_REPLICATION;
测试
INSERT INTO t1 VALUES (2, 'wxh');
server1
select * from performance_schema.replication_group_members;
三台上层加负载均衡器就可以不用读写分离层
出现问题
节点状态出现recovering
vim /etc/hosts
加入正确的解析
重新进行上面的步骤
