MySQL主从库复制，基于gtid的主从复制，半同步复制和全同步复制

最新推荐文章于 2024-08-16 14:14:13 发布

y_先森

最新推荐文章于 2024-08-16 14:14:13 发布

阅读量709

点赞数

本文链接：https://blog.csdn.net/y_yang666/article/details/87891869

版权

MYsql主从库复制

主从复制简介：

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

环境（RHEL7.3）

主机	ip
server3（主库）	172.25.4.3
server4（从库）	172.25.4.4

1.主库（server3）配配置：
1.下载mysql5.7安装包，并解压

[root@server3 ~]# ls
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server3 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server3 ~]# ls
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server3 ~]# ls
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

在这里插入图片描述
2.安装需要的软件包

[root@server3 ~]# 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

在这里插入图片描述
3.编辑/etc/my.cnf文件

[root@server3 ~]# vim /etc/my.cnf   ##在最后写入
log-bin=mysql-bin    ##启动二进制日至系统
server-id=1

在这里插入图片描述
4.开启数据库，查看原始密码

[root@server3 ~]# systemctl start mysqld
[root@server3 ~]# cat /var/log/mysqld.log |grep password
2019-02-23T05:00:29.496897Z 1 [Note] A temporary password is generated for root@localhost: sg<tSf,k!51t

在这里插入图片描述
5.进行数据库安全初始化(登陆密码为查看到的原始密码)

[root@server3 ~]# mysql_secure_installation
修改密码：我的为Yang+0428     ##注意此处设置新密码时必须是数字+大小写字母+特殊字符，且超过8位，因为该数据库版本开启了密码检测系统简单密码会报错；第一个选项直接回车，后面统一输入y

在这里插入图片描述
6.登陆数据库

[root@server3 ~]# mysql -p
Enter password:  Yang+0428
mysql> show databases;     ##可以查看，说明设置成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

在这里插入图片描述
7.查看主库状态

登陆数据库
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1002 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库（server4）配置：
1.从主库那拷贝需要的安装包，并安装

[root@server3 ~]# scp 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 root@172.25.4.4:/root
[root@server4 ~]# 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.编辑/etc/my.cnf文件

[root@server4 ~]# vim /etc/my.cnf    ##最后一行写入
server-id=2    ##因为主库为1，所以此处要与主库不同

在这里插入图片描述
3.获取数据库初始密码，并进行安全初始化

[root@server4 ~]# grep password /var/log/mysqld.log
2019-02-23T05:13:07.443745Z 1 [Note] A temporary password is generated for root@localhost: %e0EOHl)vsl=
[root@server4 ~]# mysql_secure_installation
我的密码为Yang+0823，修改完密码后，第一个选项直接回车，后面默认都输入y

在这里插入图片描述
4.登陆库查看

[root@server4 ~]# mysql -p
密码为Yang+0823

在这里插入图片描述
5.设定从库，将主库与从库连接起来，并开启从库

[root@server4 ~]# mysql -p
mysql> change master to master_host='172.25.4.3',master_user='yang',master_password='Yang+0428',master_log_file='mysql-bin.000002',master_log_pos=1002;    ##此处的主库用户，主库密码都是之前在主库中设定的，日至文件和pos可以在主库查看：show matser status;
mysql> start slave;

在这里插入图片描述
6.查看从库状态

mysql> show slave status\G;

在这里插入图片描述
如果Slave_IO_Running和Slave_SQL_Running都为yes，则表示正常

主从测试：

1.在主库端建立库westos和表usertb,并插入信息

mysql> create databases westos;   ##创建库
mysql> use westos;   
mysql> create table usertb ( username varchar(10) not null, passord varchar(15) not null);   ##创建表
mysql> desc usertb;    ##查看表
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| passord  | varchar(15) | NO   |     | NULL    |       |

mysql> insert into usertb values ('user1','11111');   ##插入信息
Query OK, 1 row affected (0.01 sec)
mysql> select * from usertb   ##查看
    -> ;
+----------+---------+
| username | passord |
+----------+---------+
| user1    | 11111   |
+----------+---------+

在这里插入图片描述

2.从库端查看是否存在在主库中创建的内容
在这里插入图片描述

基于gtid的主从复制：

server3（主库）：
1.编辑配置文件

[root@server3 mysql]# vim /etc/my.cnf
log-bin=mysql-bin
server-id=1

gtid_mode=ON    ##打开gtid模式
enforce-gtid-consistency=true

2.重起mysql服务

[root@server3 mysql]# systemctl restart mysqld

server4（从库）：
1.编辑配置文件，并重启mysql

[root@server4 ~]# vim /etc/my.cnf
server-id=2
gtid_mode=ON     ##打开gtid模式
enforce-gtid-consistency=true

[root@server4 ~]# systemctl restart mysqld

2.登陆数据库，关掉slave，重新添加连接策略，开启slave查看

[root@server4 ~]# mysql -p
mysql> stop slave;
mysql> change master to master_host='172.25.4.3',master_user='yang',master_password='Yang+0428',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;      ##查看slave状态，以下两个状态为yes就可以了
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在这里插入图片描述

测试：

主库上在表中插入信息
在这里插入图片描述

从库查看同步成功
在这里插入图片描述

半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

server3（主库）
1.登陆数据库，安装半同步复制插件，并打开开关

[root@server3 mysql]# mysql -p
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';    ##安装插件
mysql> set global rpl_semi_sync_slave_enabled=1;     ##打开开关

在这里插入图片描述
2.查看参数：

mysql> show variables like '%rpl%';

在这里插入图片描述

server4（从库）
1.安装半同步slave端插件，开启半同步，关掉io_thread线程在打开，使之生效

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;

在这里插入图片描述

测试：

关掉slave端io线程，在主库插入信息，等待时间10s（默认）才能插入成功
在这里插入图片描述
Rpl_semi_sync_master_no_tx为失败次数，yes为成功次数

在打开slave端io线程，在测试：
在这里插入图片描述

Rpl_semi_sync_master_yes_tx为1，半同步成功

从库查看表信息
在这里插入图片描述

基于组复制的并行复制

并行复制的目的就是要让slave尽可能的多线程跑起来，提高slave的并发连接度，解决延迟问题

1.编辑配置文件

[root@server2 mysql]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK   
slave-parallel-workers=6         ##设置多个worker线程数
master_info_repository=TABLE     ##将信息以表的形式存储
relay_log_info_repository=TABLE  ##将信息以表的形式存储
relay_log_recovery=ON

在这里插入图片描述
2.查看：6个线程等待主线程调用

全同步复制（组复制）

组复制分单主模式和多主模式，mysql 的复制技术仅解决了数据同步的问题，如果 master 宕机，意味着数据库管理员需要介入，应用系统可能需要修改数据库连接地址或者重启才能实现。组复制在数据库层面上做到了，只要集群中大多数主机可用，则服务可用
优点：
1.高一致性
基于原生复制及 paxos 协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
2.高容错性
只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
3.高扩展性
节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
4.高灵活性
有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有 server 都可以同时处理更新操作

server3配置：

1.停掉之前的mysql，清空/var/lib/mysql目录下所有的mysql缓存记录
在这里插入图片描述

2.获取uuid

3.编辑配置文件

[root@server3 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    ##开启二进制日志文件，转变成msater
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="6a900183-b992-458c-b6e7-7470296556b9"    ##三节点保持uuid一致（命令行输入uuidgen获取）
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.4.3:24901"
loose-group_replication_group_seeds="172.25.4.3:24901,172.25.4.4:24901,172.25.4.2:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.4.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

在这里插入图片描述
3.开启mysql，获取初始密码，登陆数据库修改初始密码，安装全同步插件及配置

[root@server3 mysql]# systemctl start mysqld
[root@server3 ~]# cat /var/log/mysqld.log |grep password   ##获取初始密码
[root@server3 ~]# mysql -p
mysql> alter user root@localhost identified by 'Yang+0428';
mysql>SET SQL_LOG_BIN=0;     ##关掉日志同步
mysql>CREATE USER rpl_user@'%' IDENTIFIED BY 'Yang+0428';    ##用户授权
mysql>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';  
mysql>FLUSH PRIVILEGES;    ##刷新授权表
mysql>SET SQL_LOG_BIN=1;   ##开启日志同步
mysql>CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yang+0428' FOR CHANNEL 'group_replication_recovery'; 
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';    ##安装插件
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>START GROUP_REPLICATION;    ##开启组复制
mysql>SET GLOBAL group_replication_bootstrap_group=OFF;

在这里插入图片描述

server4配置：

1.停掉之前的mysql，清空/var/lib/mysql目录下所有的mysql缓存记录
在这里插入图片描述

2.编辑配置文件

[root@server4 mysql]# vim /etc/my.cnf
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="6a900183-b992-458c-b6e7-7470296556b9"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.4.4:24901"
loose-group_replication_group_seeds="172.25.4.3:24901,172.25.4.4:24901,172.25.4.2:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.4.0/24"

在这里插入图片描述

3.开启数据库，获取初始密码，登陆数据库，修改密码，安装全同步插件并配置

[root@server4 mysql]# systemctl start mysqld
[root@server4 mysql]# grep password /var/log/mysqld.log    ##密码在最后一行
[root@server4 ~]# mysql -p
mysql> alter user root@localhost identified by 'Yang+0428';    ##更改密码
mysql> SET SQL_LOG_BIN=0;    ##关掉日志同步
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Yang+0428';    ##用户授权
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;     ##刷新授权表
mysql> SET SQL_LOG_BIN=1;    ##开启日志同步
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yang+0428'FOR CHANNEL 'group_replication_recovery'; 
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';    ##安装插件
mysql>reset master;    ##重制master
mysql>START GROUP_REPLICATION;   ##开启组同步

在这里插入图片描述

server2配置：

1.安装数据库

[root@server2 ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm

在这里插入图片描述

2.编辑配置文件

[root@server2 ~]# vim /etc/my.cnf
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="6a900183-b992-458c-b6e7-7470296556b9"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.4.2:24901"
loose-group_replication_group_seeds="172.25.4.3:24901,172.25.4.4:24901,172.25.4.2:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.4.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

在这里插入图片描述
3.开启mysql，获取初始密码，登陆数据库，修改密码，安装组复制插件并配置

[root@server2 ~]# systemctl start mysqld
[root@server2 ~]# grep password /var/log/mysqld.log    ##获取初始密码
[root@server2 ~]# mysql -p
mysql> alter user root@localhost identified by 'Yang+0428';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Yang+0428';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yang+0428' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> reset master;
mysql> START GROUP_REPLICATION;

在这里插入图片描述

测试：

1.server3端查看组复制状态（三台状态都是online）
在这里插入图片描述
2.server3上登陆数据库，进入westos库，插入信息到t1表中

server4登陆数据库查看是否同步到（server2上也会同步到，此处以server4为例）：

3.server2上登陆数据库，插入信息

server3上查看数据是否同步到