本文详细介绍了MySQL的主从复制，包括异步、半同步和组复制三种模式。在异步复制中，主从节点间无须实时同步；半同步复制确保至少有一个从节点接收到更新；组复制则通过多节点一致性保证数据同步。文章通过实例配置和测试，展示了各种复制模式的设置和数据同步验证。
一.MySQL主从复制的概念
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
二.MySQL主从复制的原理
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:
主节点 binary log dump 线程
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。
从节点I/O线程
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。
从节点SQL线程
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。
要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。
三.异步模式（默认模式）
MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay
实验环境：两台rhel7.3的虚拟机,server1作为主,server2作为从数据库
-
获取安装包,解压安装
server1:tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
选择性安装
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-devel-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-minimal-debuginfo-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.24-1.el7.x86_64.rpm
mysql-community-test-5.7.24-1.el7.x86_64.rpm
安装
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
- 编写配置文件，配置mysql
vim /etc/my.cnf
添加
log-bin=mysql-bin
server-id=1
systemctl start mysqld ##启动mysql
cat /var/log/mysqld.log | grep password ##获取初始密码
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) : No
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
... skipping.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :
... skipping.
All done!
-
登陆数据库
mysql -p mysql> SHOW PLUGINS; #查看插件 mysql> SHOW MASTER STATUS; #查看从需要的信息
mysql> grant replication slave on *.* to repl@'172.25.61.%' identified by 'Westos+001';
-
配置从数据库
server2mysql安装及初始化和server1相同vim /etc/my.cnf 添加 server-id=1 mysql -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> change master to master_host='172.25.61.1',master_user='repl',master_password='Westos+001',master_log_file='mysql-bin.000007',master_log_pos=154; ##要和在server1上查看到的信息保持一致 Query OK, 0 rows affected, 2 warnings (1.00 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.25 sec) mysql> SHOW SLAVE STATUS/G; ##查看slave状态，IO和SQL进程都开 Slave_IO_Running: Yes Slave_SQL_Running: Yes -
在mastr上写入信息，查看slave从库是否同步
主库上添加mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database redhat; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | redhat | | sys | +--------------------+ 5 rows in set (0.00 sec)
从库上查看
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redhat |
| sys |
+--------------------+
5 rows in set (0.00 sec)
同步成功
四.异步复制（基于gtid）
master 配置文件修改 vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
slave 配置文件修改 vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true
-
在master上给从库授权
mysql> SHOW PLUGINS; ##查看规则 mysql> SHOW MASTER STATUS; ##查看从库需要的信息 mysql> grant replication slave on *.* to repl@'172.25.61.%' identified by 'Westos+123'; ##授权，写上数据库密码和要授权的ip -
slave接收
mysql> change master to, master_host='172.25.61.1', ##master的ip master_user='repl', ##授权时的id master_password='Westos+123', ##数据库密码 master_auto_position=1; ##开启gtid mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes
都为yes表示成功
Retrieved_Gtid_Set: 9cc2ec23-728c-11e9-b5c8-525400e16b55:1-4
Executed_Gtid_Set: 9cc2ec23-728c-11e9-b5c8-525400e16b55:1-4
Auto_Position: 1 ##可以看到 Auto_Position为1
- 测试数据是否同步
在master上创建表，添加用户信息，看slave上能否同步
mysql> create database redhat;
Query OK, 1 row affected (0.26 sec)
mysql> use redhat
mysql> create table usertb(
-> username varchar(10) not null,
-> password varchar(15) not null);
mysql> insert into usertb values('user1','123');
Query OK, 1 row affected (0.33 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
在从库上查看
mysql> use redhat;
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
数据同步成功
查看从库gtid信息
五. 半同步复制
异步复制的缺点在于主库只负责将数据库的增，改，查操作记录到 binary log中，而从库在通过日志对操作进行回放同步之后，主库对从库的同步内容不检测，不知道是否同步，为了解决这个问题，下面来说半同步复制。
-
master和slave上安装模块
mastermysql> 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; 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)
slave
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;
mysql> start slave io_thread;
mysql> SHOW STATUS LIKE '%rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
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 |
+---------------------------------+----------+
- 测试
先将slave上的io_thread关掉，在master上写入信息，看slave能否同步
mysql> stop slave io_thread;
master上可以看到连接状态为OFF
mysql> SHOW STATUS LIKE '%rpl%';
| Rpl_semi_sync_master_status | OFF |
写入数据
mysql> insert into usertb values ('user2','123');
mysql> insert into usertb values ('user3','123');
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
从库上查看不到，将io_thread打开，数据才能同步
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
mysql> start slave io_thread;
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
异步复制成功
六. 组复制
server1
systemctl stop mysqld
cat auto.cnf
[auto]
server-uuid=a795633a-728c-11e9-b37b-52540015e123
-
修改server1 mysql配置文件
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="a795633a-728c-11e9-b37b-52540015e123" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.25.61.1:24901" loose-group_replication_group_seeds= "172.25.61.1:24901,172.25.61.2:24901,172.25.61.3:24901" loose-group_replication_bootstrap_group=off loose-group_replication_ip_whitelist="127.0.0.1,172.25.61.0/24" loose-group_replication_enforce_update_everywhere_checks=ON loose-group_replication_single_primary_mode=OFF -
清空mysql目录，用于初始化(如果做过其他同步建议初始化
cd /var/lib/mysql rm -fr * -
开启服务
systemctl start mysqld
- 查看密码
grep password /var/log/mysqld.log
- 安全初始化
[root@server1 mysql]# mysql_secure_installation
密码：Westos+123
-
配置组复制模块
mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+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='Westos+123' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (1.26 sec) mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.60 sec) 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 (3.51 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.14 sec) mysql> USE test; Database changed mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); Query OK, 0 rows affected (0.87 sec) mysql> INSERT INTO t1 VALUES (1, 'Luis'); Query OK, 1 row affected (0.18 sec) mysql> ELECT * FROM t1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELECT * FROM t1' at line 1 mysql> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+
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="a795633a-728c-11e9-b37b-52540015e123" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.25.61.2:24901" loose-group_replication_group_seeds= "172.25.61.1:24901,172.25.61.2:24901,172.25.61.3:24901" loose-group_replication_bootstrap_group=off loose-group_replication_ip_whitelist="127.0.0.1,172.25.61.0/24" loose-group_replication_enforce_update_everywhere_checks=ON loose-group_replication_single_primary_mode=OFF -
开启mysqld服务
[root@server2 mysql]# systemctl start mysqld -
获取密码
[root@server2 mysql]# grep password /var/log/mysqld.log -
安全初始化
[root@server2 mysql]# mysql_secure_installation 密码： Westos+123 -
配置组复制
[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 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 'Westos+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='Westos+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:
-
修改配置文件
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="a795633a-728c-11e9-b37b-52540015e123" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.25.61.3:24901" loose-group_replication_group_seeds= "172.25.61.1:24901,172.25.61.2:24901,172.25.61.3:24901" loose-group_replication_bootstrap_group=off loose-group_replication_ip_whitelist="127.0.0.1,172.25.61.0/24" loose-group_replication_enforce_update_everywhere_checks=ON loose-group_replication_single_primary_mode=OFF -
开启mysqld服务
[root@server2 mysql]# systemctl start mysqld -
获取密码
[root@server2 mysql]# grep password /var/log/mysqld.log -
安全初始化
[root@server2 mysql]# mysql_secure_installation 密码：Westos+123 -
配置组复制
[root@server3 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 'Westos+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)
测试
在server1上
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 0658fbc7-72a6-11e9-91db-5254007b15ae | server1 | 3306 | ONLINE |
| group_replication_applier | 674a452e-72a7-11e9-a122-52540015e123 | server2 | 3306 | ONLINE |
| group_replication_applier | a7efcdb7-72a8-11e9-a496-5254008aeb0a | server3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
组中的服务器mysql都在线代表成功，可以在master上添加数据在slave上测试同步
扫一扫
3222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
