mysql数据复制详解(两种方法：基于二进制日志+基于gtid 三种模式：异步+半同步+全同步)

最新推荐文章于 2021-06-29 10:44:21 发布
最新推荐文章于 2021-06-29 10:44:21 发布
阅读量331 收藏 2
点赞数 1
分类专栏： linux 文章标签： 二进制日志 gtid 异步复制 半同步 全同步
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42195382/article/details/97627722
版权

安装mysql: redhat7.5

##mysql安装包：官网上可以下到
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

##安装：
yum install -y *.rpm

初始化：

systemctl start mysqld		//开启mysql
cat /var/log/mysqld.log | grep password		//查看初始化密码
mysql_secure_installation	//使用初始化密码登陆进行初始化

一、异步复制

环境：

serevr1172.25.60.1mysql：master(主)
server2172.25.60.2mysql：slave(从)

1.基于二进制文件的复制

(1)server1/2修改配置文件：/etc/my.cnf

##添加下面的内容
log-bin=mysql-bin		//主需要这个参数，从不需要
server-id=1		//主为1，从设为2

systemctl restart mysqld

在这里插入图片描述 在这里插入图片描述

(2)登陆主数据库(server1)，做以下设定：

mysql -uroot -p
CREATE USER 'repl'@'172.25.60.%' IDENTIFIED BY 'YLZ123+a';	//创建新用户，并指定密码
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.60.%';		//授予此用户复制所需的权限
flush privileges;	//刷新授权表
show master status;	//查看当前的二进制日志文件名称和位置

在这里插入图片描述

(3)登陆从数据库(server2)，做以下设定：

mysql -uroot -p
mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.60.1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='YLZ123+a',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=1011;
start slave;		//开启复制
show slave status\G		//查看slave信息

在这里插入图片描述
在这里插入图片描述
(4)测试：

create database westos;		//建立库
use westos;					//使用(进入)库
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(20) not null);		//创建表
desc usertb;				//查看表的结构
insert into usertb values ('user1','123');	//插入数据
select * from usertb;		//查看数据

在server1(master)上创建库，创建表，插入数据，都可以在server2(slave)查看到。
在这里插入图片描述
在这里插入图片描述

2.基于gtid的复制

(1)server1/2修改配置文件：/etc/my.cnf

##添加下面的内容
gtid_mode=ON
enforce-gtid-consistency=true

systemctl restart mysqld

在这里插入图片描述
在这里插入图片描述
(2)上面基于二进制的复制已经做好了主从，接下来只需要修改server2(slave)复制模式即可。

stop slave		//停止复制再修改
change master to master_host='172.25.60.1',master_user='repl',master_password='YLZ123+a',master_auto_position=1;
start slave		//打开
show slave status\G	//查看--两个yes

注： 如果是全新的环境，做上面基于二进制文件的(2)(3)步即可。
在这里插入图片描述
在这里插入图片描述

(3)测试：

在server1(master)创建一个用户表，插入数据，然后在server2(slave)上查看。

在这里插入图片描述在这里插入图片描述
注： 复制完成之后，可以看到记录。

show slave status\G
产生新数据前：空；复制后：如下（gtid号）

在这里插入图片描述
还可以查看gtid号：

select * from mysql.gtid_executed;

在这里插入图片描述

二、半同步

做完基于gtid的复制之后，只需要做以下修改即可

(1)server1：

##下载master端插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
##查看插件
SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
##设置自启动
SET GLOBAL rpl_semi_sync_master_enabled=1;
##查看：
show variables like 'rpl_semi_sync%';
show status like '%rpl%';

在这里插入图片描述
在这里插入图片描述
(2)server2：

##下载slave端插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
##查看插件
SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';	
##自启动
SET GLOBAL rpl_semi_sync_slave_enabled=1;
##关闭线程，重新开启
STOP slave IO_THREAD;
START slave IO_THREAD;
##查看
show variables like 'rpl_semi_sync%';

在这里插入图片描述
(3)测试：
和前面一样，server1生成数据，server2查看即可。

注： 与异步不同的在于下面
slave–>stop后,master无法半同步，默认等待10秒后，会自动转化为异步，下一次就不会等待了。当再次开启slave后，数据会接着之前的同步，也会再次恢复半同步。

servre2关闭复制：

stop slave

server1：
在这里插入图片描述

三、全同步

环境： 开启三台虚拟机,都安装mysql

server1172.25.60.1
server2172.25.60.2
server3172.25.60.3

(1).配置三台虚拟机mysql环境
a. 全新环境(刚装上mysql，没做过实验的)：安全初始化即可(server3)
b. 之前做过mysql实验的，做以下设定即可：

systemctl stop mysqld		//关闭mysql
cd /var/lib/mysql			//进入mysql的数据目录
rm -fr *					//删除所有数据
systemctl start mysqld		//开启mysql
##从新设置超户密码即可(也可从新初始化)
cat /var/log/mysqld.log | grep password
mysql -uroot -p
alter user root@localhost identified by 'YLZ123+a';

(2)修改server1/2/3的配置文件，两处不同我在下面已经说明了。

server_id=1		//server1=1，server2=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="98979348-b04a-11e9-aab5-525400356eac"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.60.1:33061"	//本地ip，是什么就写什么
loose-group_replication_group_seeds= "172.25.60.1:33061,172.25.60.2:33061,172.25.60.3:33061"		
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.60.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

重启mysql

systemctl restart mysqld

(3)配置server1/2/3的mysql数据库：

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'YLZ123+a';	//创建复制用户
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';		//给复制权限
FLUSH PRIVILEGES;			//刷新授权表
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='YLZ123+a' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';	//下载组复制插件
SHOW PLUGINS;			//查看

##三台mysql只需要有一台(server1)发起组复制即可，其他两台只需要开启组复制即可！！
SET GLOBAL group_replication_bootstrap_group=ON;	//允许发起组复制(server2/3不需要)
START GROUP_REPLICATION;		//开启组复制
SET GLOBAL group_replication_bootstrap_group=OFF;	//关闭(server2/3不需要)

(3)测试： 组复制设置ok的现象，使用下面的命令查看

SELECT * FROM performance_schema.replication_group_members;

在这里插入图片描述

总结： 正常情况做完上面的步骤就可以了，但由于环境问题，也会出现报错。下面是我遇到的报错，以及我的解决办法。
(1)报错一： server2/3加入组复制是报错，解决办法如下

set global group_replication_allow_local_disjoint_gtids_join=on;

在这里插入图片描述
(2)报错二： 加入后状态不对(不是ONLINE，而是RECOVERING)
在这里插入图片描述
解决办法：

cd /var/lib/mysql	//备份server1(发起组复制端)的数据
mysqldump --all-databases --single-transaction --routines --events \
--host=127.0.0.1 --port=3306 --user=root --password=YLZ123+a > dbdump.db

##发送给server2/3
scp dbdump.db 172.25.60.2:/root
scp dbdump.db 172.25.60.3:/root

##server2/3
mysql -p < dbdump.db		//导入数据库，如果出错，请阅读报错三

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='YLZ123+a' \
FOR CHANNEL 'group_replication_recovery';
start group_replication;
SELECT * FROM performance_schema.replication_group_members;

(3)报错三： 导入数据时报错

a. ERROR 1290 (HY000) at line 31: The MySQL server is running with the --super-read-only option so it cannot execute this statement.
(MySQL服务器使用–super-read-only选项运行，因此无法执行此语句) 解决办法：关闭mysql再开启就ok

b. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. (@@
GLOBAL.GTID_PURGED只能在@@ GLOBAL.GTID_EXECUTED为空时设置。)
解决办法：登陆数据库，执行reset master即可

在这里插入图片描述

枝子
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL的复制方式（基于二进制日志文件位置的主从复制、基于GTID的主从复制、半同步复制、组复制）
Aimee_c的博客
05-12 945
文章目录1.MySQL1.1 MySQL的事务2.MySQL的三种复制方式2.1 MySQL的异步复制——基于二进制日志文件位置的复制 1.MySQL Mysql是一种关系型数据库，特点是将这些数据保存在不同的二维表中，并将这些表放入数据库，这样可以增加它的读取速度。 1.1 MySQL的事务 事务就是一组原子性的SQL查询，或者说一个独立的工作单元 如果数据库引擎能够成功地对数据库进行该组查询的全部语句，那么就执行该组查询，如果其中有任何一条语句因为崩溃或者其他原因无法执行，那么所有的语句都不会执行，也就
mysql主从复制的二进制日志_MySQL主从复制 - 基于二进制日志(理论篇)
weixin_34378978的博客
01-20 258
mysql日志类型1二进制日志2事务日志3一般查询日志4中继日志5慢查询日志二进制日志二进制日志通常记录的是可能潜在引起数据库发生改变的操作，每一个操作我们称为一个event。二进制日志记录一个event的时候，通常还会记录timestamp,position(偏移量offset)，server-id，event本身。二进制日志的数据存储形式，形如mysql...
mysql复制（二）- 基于二进制日志的复制的搭建
line_on_database的博客
06-29 191
复制相关总结 第一章 复制的概念及用途 第二章 基于二进制日志的复制的搭建 第三章 基于二进制日志的复制的问题及处理 第四章 基于GTID的复制的搭建 第五章 基于GTID的复制的问题及处理 第六章 复制所涉及的相关参数 文章目录复制相关总结一、概述二、复制的搭建1.配置主库（源）2.迁移数据3.配置从库（副本）4.开启复制总结 一、概述 作为源（数据库更改的源）运行的 MySQL 实例将更新和更改作为“事件”写入二进制日志。根据记录的数据库更改，二进制日志中的信息以不同的日志格式存储。副本配置为从源
MySQL 二进制日志复制、GTID 复制与半同步复制
黑白影的博客
01-06 580
一、日志格式 1.1 二进制日志格式 MySQL 二进制日志是进行主从复制的基础，它记录了所有对 MySQL 数据库的修改事件，包括增删改查和表结构修改。当前 MySQL 一共支持三种二进制日志格式，可以通过 binlog-format 参数来进行控制，其可选值如下： STATEMENT：段格式。是 MySQL 最早支持的二进制日志格式。其记录的是实际执行修改的 SQL 语句，因此在进行批量修改...
mysql 主从二进制日志_MySQL基于二进制日志的主从复制
weixin_34396941的博客
01-19 191
一、什么是MySQL的主从复制？MySQL可以将一个数据库设置为主库，另一个数据库设置为该主库的从库，当主库发生了变更，会同步到从库中。MySQL的主从架构，可以是星型的，也可以是线型的。星型架构：线型架构：由于主从复制的延迟问题，星型架构比线型架构更常用。下文中的应用主要是以星型架构为例。二、MySQL主从复制的方式MySQL的复制可以分为基于二进制日志的复制和基于事务(GTID)的复制，基于二...
mysql-binlog-connector-java：MySQL二进制日志连接器
02-26
mysql-binlog-connector-java 注意：该存储库不再维护。 我建议迁移到 。 MySQL Binary Log连接器。 最初，项目是作为的分支开始的，但最终作为完整的重写而结束。 主要差异/特征： 自动binlog文件名/位置| GTID...
基于MYSQLBINLOG日志实现的单向网络数据同步
01-27
MySQL5.6开始主从复制有两种方式：基于日志（binlog）、基于GTID（全局事务标示符）。 根据《High-LevelBinaryLogStructure andContents》所述，不同版本的Binlog格式不一定一样，所以也没有一个定性。在我写这篇...
详解MySQL主从复制实战 – 基于GTID的复制
12-16
在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致. 而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未...
基于keepalived和GTID的半同步主从复制的高可用MySQL集群
最新发布
03-02
基于keepalived和GTID的半同步主从复制的高可用MySQL集群
mysql binlog二进制日志详解
01-21
基本概念 定义： 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。 作用： 1。二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。 2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。 不良影响： 运行服务器时若启用二进制日志则性能大约慢1%。 如何启动： 通过 –log-bin=file选项可以启用 (更改my.ini文件) 日志位置 >>如果没有指定文件名,则Mysql使用hostname-bin文件. >>如果指定了相对路径,则假定该路径相对于数据目录 >>Mysql在
MySQL二进制日志(binlog)开启、查看、截取
12-14
mysql二进制日志记录了数据库所有变更类的操作日志主要有2个作用： MySQL主从配置 数据恢复 参数介绍 server_id=3 log_bin=/data/binlog/mysql-bin： /data/binlog:提前定制好的目录,而且要有mysql.mysql的权限 mysql-bin:二进制日志文件名的前缀 binlog_format=(row、statement、mixed) statement：SBR,语句模式记录日志,做什么命令,记录什么命令. 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况：insert into xx values (1,'sa
mysql 主从复制 基于gtid的同步复制，并行复制，半同步复制
cute
09-28 416
一、mysql 主从复制 1.主从形式 mysql主从复制 灵活 一主一从 主主复制 一主多从---扩展系统读取的性能，因为读是在从库读取的； 多主一从---5.7开始支持 联级复制--- 2.主从复制的用途及部署条件 mysql主从复制用途 实时灾备，用于故障切换 读写分离，提供查询服务 备份，避免影响业务 主从部署必要条件： 主库开启binlog日志（设置log-bin...
mysql主从复制和基于gtid的主从、半同步复制
qq_42711214的博客
10-18 482
一、了解主从复制 1.主从复制的原理： 影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，...
mysql的主从复制、GTID配置、读写分离、半同步复制
weixin_45426401的博客
10-25 228
mysql的主从同步 tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar yum install 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.x...
Mysql的主从复制，基于gtid的主从复制与半同步复制
windowsworld的博客
07-27 144
mysql的主从复制 mysqldump --all-databases --master-data > dbdump.db 主机名（ip） 用途 server1（172.25.42.1） master（主机） server2（172.25.42.2） slave（从机） server1端的配置（master端） 安装包解压并删除不需要的安装后安装 [roo...
MySQL的一些记录
jacksonary的博客
08-21 3837
MySQL学习记录 1. 权限 MySQL权限管理的两个阶段：连接检查和权限检查，前者就是指定哪些用户可以进行连接(具体可以理解为哪些IP可以连接)，后者是用户对哪些库或者表有具体的操作权限。 配置好环境变量，将MySQL的服务添加到Windows服务中，基本登陆命令 mysql -h localhost -u root -p 关于权限的配置，一般是给指定的ip用户提供能满足需求...
MySQL二进制日志
BushRo
06-13 1211
文章目录mysql复制功能解决了什么问题mysql二进制日志二进制日志二进制日志的格式（段）二进制日志的格式（行）二进制日志的格式（混合）mysql二进制日志格式对复制的影响基于SQL语名的复制( SBR )优点缺点基于行的复制( RBR )优点缺点混合模式mysql复制工作方式基于日志点的复制基于日志点的复制配置步骤实际操作(yum安装的mysql 5.7)优点缺点基于GTID的复制基于GTID的配置步骤案例操作优点缺点选择复制模式要考虑的问题复制架构一主多从的架构拥有备库的主-主复制拓扑Mysql复制常
semi-sync插件崩溃导致MySQL重启故障详析
老叶茶馆
09-22 1534
导读 semi-sync插件崩溃导致MySQL重启的故障分析全过程~ 作者：刘安 现为爱可生高级测试工程师，拥有丰富的自动测试开发经验；曾先后在文思海辉、Splunk担任测试工程师。 一、起因： 在公司测试MySQL高可用组件时发现一个异常。如下： 停止从库，高可用组件将从库自动启动后，主库发生了重启。然而，正常情况下，主库不应发生重启。 二、
mysql高可用方案，MySql主从复制原理及配置，binLog和GTID
仅做个人笔记
06-14 2717
目录 一、mysql高可用方案 1. aliyun高可用mysql(内部集成:扩容/主从/集群/故障切换) + 分表 2. 分库 + 自搭mysql主从 + 分表 + 手动或脚本主从切换 3. 更多集群架构 二、单讲binLog和GTID binLog GTID 三、MySql主从复制原理及配置 什么是主从复制？ 主从模式优点 主从复制的原理 主实例搭建 从实例搭建 将主从数据库进行连接 主从复制测试 一、mysql高可用方案 1. aliyun高可用mysql..
mysql binlog gtid_binlog的GTID模式基础及基于GITD的数据恢复
05-27
在MySQL中，有两种类型的GTID：基于二进制日志的GTID（gtid_binlog）和基于事务的GTID（gtid_current_pos）。其中，基于二进制日志的GTID是默认启用的。它由两部分组成：server_uuid和transaction_id。其中，server_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值