背景：
MySQL是现在普遍使用的数据库，但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性。就要会一些提高可靠性的技术。因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据提高可靠性，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力。
本文主要介绍在RHEL7环境下MySQL的主从复制以及基于gtid的主从复制、半同步复制、组复制，实现数据同步
一、主从复制的概念
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
主要用途：
1、读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2、数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
3、高可用HA
4、架构扩展
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。
二、主从复制的原理
我们先看图：
1、主节点 binary log dump 线程
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。
2、从节点I/O线程
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。
3、从节点SQL线程
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
PS：
对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。
要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。
4、主从复制的主要步骤：
整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：
复制的基本过程如下：
- 从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
- 主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；
- Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行。
三、异步复制（主从复制、以及基于gtid的主从复制）
MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay。
异步模式如下图所示，这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地。
环境：RHEL7.3
- server1：172.25.70.1
- server2：172.25.70.2
- server3：172.25.70.3
mysql主从复制
1.server1、server2、server3安装msyqld
yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm
2.修改配置文件/开启log-bin日志
vim /etc/my.cnf
====================
29 server-id=1
30 log-bin=mysql-bin
3.重启服务，并查看mysqld生成的密码
[root@server1 ~]# systemctl start mysqld
[root@server1 ~]# cat /var/log/mysqld.log | grep passwd
[root@server1 ~]# cat /var/log/mysqld.log | grep password
2019-05-02T00:19:28.847875Z 1 [Note] A temporary password is generated for root@localhost: +VblE1i1kArQ
[root@server1 ~]#
4.安全初始化
[root@server1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
#其余全yes
- server2：
1.修改配置文件
[root@server2 ~]# vim /etc/my.cnf
=================
29 server-id=2
2.启动mysqld，并查看密码
[root@server2 ~]# systemctl start mysqld
[root@server2 ~]# cat /var/log/mysqld.log | grep password
2019-05-02T00:26:31.936671Z 1 [Note] A temporary password is generated for root@localhost: 8NsO+JtgHldb
3.安全初始化同server1
[root@server2 ~]# mysql_secure_installation
4.server1创建用户授权
mysql> mysql> grant replication slave on *.* to rep@'172.25.70.%' identified by 'Mahao+123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
5.server1查看master状态
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 842 | | | f32c0a71-6c6f-11e9-8a9f-52540075b0d5:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
6.server2上设置server1为master
mysql> change master to master_host='172.25.70.1',master_user='rep',master_password='Mahao+123',master_log_file='mysql-bin.000002',master_log_pos=842;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.70.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 842
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #表示成功
测试：
在server1上创建表
mysql> CREATE DATABASE taylor;
Query OK, 1 row affected (0.01 sec)
mysql> USE taylor;
Database changed
mysql> CREATE table usertb (
-> username varchar(10) not null,
-> password varchar(15) not null);
Query OK, 0 rows affected (0.02 sec)
查看表
mysql> DESC usertb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into usertb values('user1','123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
mysql>
在server2：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| taylor |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE taylor;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
mysql>
- server1:
mysql> insert into usertb values('user2','123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
mysql> insert into usertb values('user3','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into usertb values('user4','123');
Query OK, 1 row affected (0.00 sec)
- server2:
mysql> SELECT * FROM usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.70.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1010
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 1223
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1010
Relay_Log_Space: 1638
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f32c0a71-6c6f-11e9-8a9f-52540075b0d5
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f32c0a71-6c6f-11e9-8a9f-52540075b0d5:4-9
Executed_Gtid_Set: f32c0a71-6c6f-11e9-8a9f-52540075b0d5:4-9
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 4 | 4 |
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 5 | 5 |
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 6 | 6 |
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 7 | 7 |
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 8 | 8 |
| f32c0a71-6c6f-11e9-8a9f-52540075b0d5 | 9 | 9 |
+--------------------------------------+----------------+--------------+
6 rows in set (0.00 sec)
mysql>
- server1:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE '%semi%';
mysql>SET GLOBAL rpl_semi_sync_master_enabled =1;
- server2:
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql>SET GLOBAL rpl_semi_sync_slave_enabled =1 ;
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE '%rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
- server1:
mysql> SHOW VARIABLES LIKE '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.02 sec)
- server2:
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
- server1:
mysql> use taylor;
Database changed
mysql> insert into usertb values ('user5','123');
Query OK, 1 row affected (10.00 sec)
mysql> SHOW STATUS LIKE '%rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
mysql> insert into usertb values ('user6','123');
Query OK, 1 row affected (0.00 sec)
- server2:
mysql> use taylor;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
- server1:
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
| user5 | 123 |
| user6 | 123 |
+----------+----------+
6 rows in set (0.00 sec)
- server2:
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
| user5 | 123 |
| user6 | 123 |
+----------+----------+
6 rows in set (0.00 sec)
- server1:
mysql> SHOW PROCESSLIST;
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| 3 | root | localhost | taylor | Query | 0 | starting | SHOW PROCESSLIST |
| 8 | rep | server2:48526 | NULL | Binlog Dump GTID | 93 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+---------------+--------+------------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
组复制
- groupname:(三台主机保持一致即可)
[root@server1 mysql]# cat auto.cnf
[auto]
server-uuid=f32c0a71-6c6f-11e9-8a9f-52540075b0d5
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="a5038e3c-6c85-11e9-a044-52540023f8a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.70.1:24901"
loose-group_replication_group_seeds= "172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
- server1：
1.修改server1 mysql配置文件
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="f32c0a71-6c6f-11e9-8a9f-52540075b0d5"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.70.1:24901"
loose-group_replication_group_seeds= "172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
2.清空mysql目录，用于初始化
cd /var/lib/mysql
rm -fr *
3.开启服务
systemctl start mysqld
4.查看密码
grep password /var/log/mysqld.log
==================================
2019-05-02T07:39:44.587176Z 1 [Note] A temporary password is generated for root@localhost: OK*&ybUdk4Pj
5.安全初始化
[root@server1 mysql]# mysql_secure_installation
密码：Mahao+123
6.配置组复制模块
[root@server1 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Mahao+123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mahao+123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
#bootstrap只在master设置一次
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.07 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 742e05c2-6cad-11e9-a66b-52540075b0d5 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
- server2:
1.修改配置文件
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="f32c0a71-6c6f-11e9-8a9f-52540075b0d5"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.70.2:24901"
loose-group_replication_group_seeds= "172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
2.开启mysqld服务
[root@server2 mysql]# systemctl start mysqld
3.获取密码
[root@server2 mysql]# grep password /var/log/mysqld.log
=========================================================
2019-05-02T09:17:25.258582Z 1 [Note] A temporary password is generated for root@localhost: 1z?dQj0.m/;(
4.安全初始化
[root@server2 mysql]# mysql_secure_installation
密码：Mahao+123
5.配置组复制
[root@server2 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABSESL
-> ^C
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Mahao+123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mahao+123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
#出现报错，查看日志，cat /var/log/mysqld.log
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (4.91 sec)
- server3:
1.修改配置文件
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="f32c0a71-6c6f-11e9-8a9f-52540075b0d5"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.70.2:24901"
loose-group_replication_group_seeds= "172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
2.开启mysqld服务
[root@server2 mysql]# systemctl start mysqld
3.获取密码
[root@server2 mysql]# grep password /var/log/mysqld.log
=========================================================
2019-05-02T09:17:25.258582Z 1 [Note] A temporary password is generated for root@localhost: 1z?dQj0.m/;(
4.安全初始化
[root@server2 mysql]# mysql_secure_installation
密码：Mahao+123
5.配置组复制
[root@server2 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABSESL
-> ^C
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Mahao+123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mahao+123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (4.91 sec)
扫一扫
445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
