mysql全同步（组复制）

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 

加入正确的解析

重新进行上面的步骤

 

 

 

 

 

 

 

 

 

 

 

 

 

最后

以上就是神勇电脑为你收集整理的mysql全同步（组复制）的全部内容，希望文章能够帮你解决mysql全同步（组复制）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。