MySQL 复制

最新推荐文章于 2021-10-05 12:31:26 发布
最新推荐文章于 2021-10-05 12:31:26 发布
阅读量1.4k 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ManWZD/article/details/104076885
版权

本文对 MySQL 复制功能和用法做了整理和介绍。

文章目录

复制(Replication)解决的问题
  1. 数据分布
  2. 负载均衡和读写分离
  3. 备份
  4. 高可用性和故障切换
  5. 大表 DDL 避免锁表
  6. MySQL 升级测试
复制的工作原理
  1. 主库将数据更改记录到 binlog 中
  2. 备库将主库上的日志复制到自己的 relaylog 中
  3. 备库读取 relaylog 中的事件，将其在备库上重放
  4. 实现方式是：备库会启动一个 I/O 线程，I/O 线程会跟主库建立一个客户端连接，然后在主库上启动一个 binlog dump 线程，该线程不断把新的事件传送给备库
  5. 使用复制，需要在主库上增加一个配库使用的账号，该账号需要有REPLICATION SLAVE,REPLICATION CLIENT权限
复制的使用
  1. 配置 master
# 1) my.cnf 配置文件
[mysqld]
log-bin=mysql-bin
server-id=1
# 2) 获取主库 binlog 位点
mysql> SHOW MASTER STATUS;
File: mysql-bin.000001
Position:98
  1. 配置 slave 并开启复制
# 1) my.cnf 配置文件
[mysqld]
log_bin           = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
read_only         = 1
# 2) 设置主库和位点
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1',
    -> MASTER_PORT=3306
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='p4ssword',
    -> MASTER_LOG_FILE='mysql-bin.000001',
	-> MASTER_LOG_POS=98;
# 3) 检查位点是否设置正确
mysql> SHOW SLAVE STATUS\G
# 4) 开启复制
mysql> START SLAVE ;
  1. SHOW SLAVE STATUS 中各个位点信息的含义
Master_Log_File			# 目前拉取的主库文件
Read_Master_Log_Pos		# 目前拉取的主库文件位置
Relay_Log_File			# 当前执行的本库 relay log 文件
Relay_Log_Pos			# 当前执行的 relay log 文件位点
Relay_Master_Log_File	# 当前执行到的对应的主库文件的位置
  1. 暂时关闭 binlog： set sql_log_bin=0;
  2. 安全删除 binlog
PURGE MASTER LOGS TO 'mysql-bin.007379';		# 按文件名删除
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'; # 按时间删除
复制的类型
  • 基于语句的复制
    1. 复制快，节约 binlog 空间
    2. 复杂场景下（使用函数或存储过程等）存在各种异常和 bug
  • 基于行的复制
    1. 简单灵活适应性强
    2. 数据有冗余，出错方便修复
    3. DDL 还是语句的复制
  • 混合性复制
    1. 适应性最强
    2. DDL 也是基于行的复制，binlog 会超级大
    3. 可以理解为，基于行复制的扩展
  • 基于 GTID 的复制
    1. 简单灵活，不用记录位点，会根据全局事务ID自动寻点
    2. 因为是基于事务的复制（也是基于行的），所以不支持非事务的存储引擎
    3. 可以理解为，基于行赋值的升级
复制异常
  • 写冲突，insert 的主键/唯一键已存在，处理的方式往往是：
    1. 记录下错误发生的表和对应主键
    2. 使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;跳过这次冲突
    3. 检查主备库中对应主键的记录是否一致
  • 写异常，update 前镜像不一致，导致数据更新失败：
    1. 使用基于行的复制时，update 操作在备库上会匹配前镜像，也就是带有 where 条件（见下节 binlog 文件结构）
    2. 当前镜像不匹配时，update 中最新的记录不会更新到备库
    3. 这种情况往往发生在位点错误或 binlog 丢失的情况下
binlog
  • 把 binlog 解析成可读文件
mysqlbinlog binlog.000001 --database=name --start-datetime=name  \
	--stop-datetime=name --start-position 245770410  \
	--stop-position 245793673 -f -v \
	--base64-output=decode-rows > tmpfile
  • binlog 解析后的文件结构(update 操作)
BEGIN
/*!*/;
# at 92105505
# at 92105582
#160525 19:44:50 server id 101717547  end_log_pos 92105582      Table_map: `im_user_data_0005`.`im_user_cfg_tbl` mapped to number 1450
#160525 19:44:50 server id 101717547  end_log_pos 92106296      Update_rows: table id 1450 flags: STMT_END_F
BINLOG '
MpBFVxMrFhAGTQAAAG5rfQUAAKoFAAAAAAEAEWltX3VzZXJfZGF0YV8wMDA1AA9pbV91c2VyX2Nm
Z190YmwACAMP/AMBAQwMA0AAA8Q=
(二进制信息略)
'/*!*/;
### UPDATE im_user_data_0005.im_user_cfg_tbl
### WHERE
###   @1=222 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxxxx' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='xxxxx' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
###   @4=222 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @7=2012-07-12 11:22:13 /* DATETIME meta=0 nullable=1 is_null=0 */
###   @8=2016-05-24 20:23:03 /* DATETIME meta=0 nullable=1 is_null=0 */
### SET
###   @1=1111 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxxxx' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='xxxxx' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
###   @4=1111 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @7=2012-07-12 11:22:13 /* DATETIME meta=0 nullable=1 is_null=0 */
###   @8=2016-05-25 19:44:50 /* DATETIME meta=0 nullable=1 is_null=0 */
# at 92106296
#160525 19:44:50 server id 101717547  end_log_pos 92106323      Xid = 3286612943
COMMIT/*!*/;
常见的拓扑结构

  每一个 MySQL 实例都可以即做主库由做备库，一个主库可以有多个备库，一个备库只能有一个主库。
  理论上 MySQL 可以支持各种复杂（网状）的复制拓扑结构，但是不同的业务场景会限制复制拓扑的设计。
在这里插入图片描述

  • 主主结构
    1. 两个互为主库，但是应用只写其中一个，另一个可以作为热备份，或者作为只读库分担读流量
    2. 使用灵活，运维方便（因为切换方便）
      在这里插入图片描述
  • 一主多备
    1. 在读流量极大的情况下，需要这种一个主库（提供数据更新服务）多个备库（提供数据查询服务）的结构
    2. 这种结构运维的难点是主库 Crash 后，需要重新选择一个数据最完整的备库作为新的主库
主备一致性保证
  • semi-sync （半同步）
    1. 半同步介于异步和全同步(sync_binlog 打开)之间
    2. 主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可
    3. 半同步保证了主备的最终一致性，同时降低了主库事务 commit 的等待时间
参考资料

《高性能MySQL（第3版）》

Mysql复制表结构、表数据的方法
09-10
MySQL是一种广泛使用的开源关系型数据库管理系统，其复制表结构和数据是常见的数据库管理任务，尤其在数据迁移、备份和恢复时。以下是对标题和描述中所述知识点的详细说明： 1. **复制表结构及数据到新表**： 使用...
MySQL复制表的三种方式(小结)
12-14
复制表结构及其数据 下面这个语句会拷贝数据到新表中。 注意：这个语句其实只是把select语句的结果建一个表，所以新表不会有主键，索引。 create table table_name_new as (select * from table_name_old); 只复制...
mysql copy_MySQL复制
weixin_39875516的博客
01-27 198
复制是指将主数据库中的DDL和DML操作通过二进制日志传到复制服务器(也叫从服务器)上，然后从服务器上对这些日志重新执行(也叫重做)，从而使得从服务器和主服务器的数据库的数据保持同步。MySQL支持一台主服务器同时向多台从服务器进行复制，从服务器也可以作为其他服务器的主服务器。复制的优点：如果主服务器出现问题，可以快速切换到从服务器提供服务；可以在从服务器上执行查询操作，降低主服务器的访问压力可以...
mysql copy,MySQL复制
weixin_39899226的博客
03-17 309
(也叫从服务器)上，然后从服务器上对这些日志重新执行(也叫重做)，从而使得从服务器和主服务器的数据库的数据保持同步。MySQL支持一台主服务器同时向多台从服务器进行复制，从服务器也可以作为其他服务器的主服务器。复制的优点：如果主服务器出现问题，可以快速切换到从服务器提供服务；可以在从服务器上执行查询操作，降低主服务器的访问压力可以在从服务器上执行备份，以避免备份期间影响主服务器的服务由于MySQL...
mysql复制
sinat_38831242的博客
04-23 170
mysql复制的基础是基于log日志进行的，对应的是BinLog日志，BinLog日志有三种格式 格式一： Statement:BinLog中储存SQL语句，储存日志量最小 Row：储存event数据，储存日志量大，但是不能直接进行读取 Mixd：介于Row和Statement之间 复制类型： 类型一：二进制日志的复制 类型二：使用GTID完成基于事务的复制 安装虚拟机 1安装v...
MYSQL复制
weixin_30284355的博客
04-14 95
今天我们聊聊复制，复制对于mysql的重要性不言而喻，mysql集群的负载均衡，读写分离和高可用都是基于复制实现。下文主要从4个方面展开，mysql的异步复制，半同步复制和并行复制，最后会简单聊下第三方复制工具。由于生产环境中，innodb存储引擎支持事务，并且行级复制使用广泛，所以下文的讨论都是基于这种假设。 异步复制 异步复制是mysql自带的最原始的复制...
MySQL 复制表详解及实例代码
12-16
MySQL 复制表详解 如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令，是无法实现的。 本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下： ...
MySQL复制表结构
01-19
　有时候我们需要原封不动的复制一张表的表结构来生成一张新表，MYSQL提供了两种便捷的方法。 　例: 　CREATE TABLE tb_base( 　id INT NOT NULL PRIMARY KEY， 　name VARCHAR(10)， 　KEY ix_name (name)) ...
mysql copy_mysql的复制
weixin_39932947的博客
01-27 125
MYSQL的复制复制原理复制原理分为三个步骤：主服务器把数据更新记录到二进制日志中从服务器把主服务器的二进制日志拷贝到自己的中继日志中从服务器重做中继日志中的时间，把更新应用到自己的数据库上从服务器有两个进程：一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志复制的流程1配置master;2:配置slave;3:将slave连接到master...
经常要copy的mysql语句
qq_42508343的博客
08-13 299
1、日期比较大小及日期格式化： DATE_FORMAT(a.PAY_TIME_,'%Y-%m-%d') <= DATE_FORMAT(#{endMonthPay},'%Y-%m-%d') > 2、转义 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END 3、foreach   in ma...
mysql copy复制_MYSQL数据库mysql copy复制表数据与结构的几种方法
weixin_34348050的博客
01-27 1206
《MYSQL数据库mysql copy复制表数据与结构的几种方法》要点：本文介绍了MYSQL数据库mysql copy复制表数据与结构的几种方法，希望对您有用。如果有疑问，可以联系我们。导读：本节内容：mysql复制表结构、mysql复制表数据.本文介绍几种mysql数据库中拷贝表的方式,希望有助于大家掌握mysql拷贝表方面的知识.例,表...本节内容：mysql复制表结构、mysql复制表数据...
mysql copy复制_mysql拷贝表的几种方式
weixin_42515024的博客
02-01 442
场景，工作中有的时候需要 把bak表删除 再把src表拷贝到bak表。如下：DROP TABLE IF EXISTS bak;CREATE TABLE bak LIKE src;INSERT INTO bak SELECT * FROM src;下面我们谈一下常用的表拷贝的方式假如我们有个admin表 表的结构就不列出来了1. 下面这个语句会拷贝表结构到新表newadmin中。 (不会拷贝表中的数...
mysql 复制方式_MySQL数据库复制表的几种方式讲解
weixin_29061425的博客
01-27 3093
一、MySQL复制表的几种方式我们首先创建表stuents_score，利用SQL语句查看创建表的结构与列名字段。之后查看students_score表数据，便于之后观察对比。1、CREATE TABLE table_copy SELECT...FROM table形式如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELEC...
Mysql的主从复制与读写分离
Shadow__Flow的博客
11-01 384
在企业应用中，成熟的业务通常数据量都比较大，单台MySQL在安全性、高可用性和高并发方面都无法满足实际的要求， 配置多台主从数据库服务器以实现读写分离
mysql 拷贝表（复制表）的几种方式
donghaixiaolongwang的博客
08-16 5385
1. 下面这个语句会拷贝表结构到新表newadmin中。 （不会拷贝表中的数据） CREATE TABLE newadmin LIKE admin 2. 下面这个语句会拷贝数据到新表中。 注意：这个语句其实只是把select语句的结果建一个表。所以newadmin这个表不会有主键，索引。 CREATE TABLE newadmin AS   (   SELECT *   FRO
2021-01-26
flywpfwpf的博客
01-26 129
增删改查语句 增删改查的语句命令为 增：insert 删：delete 改：update 查：SELECT或者show 库操作 创建数据库：create database shujukuba; 创建带字符集的数据库：create database mydb2 CHARACTER SET=utf8; 创建带校验的数据库：create database mydb3 CHARACTER SET=utf8 COLLATE utf8_general_ci; 显示数据库：show databases; 删除数据库：DR
MySQL 的自增主键
ManWZD的博客
10-05 2223
自增主键是数据库中最常用的功能之一，这里简单介绍一下 MySQL 自增主键的内部原理、加锁方式以及临界值的处理方式。
MySQL复制原理与实战配置详解
"MySQL复制原理与实践应用详解" MySQL复制是一种数据冗余和负载均衡技术，它允许一个或多个从服务器（slaves）实时同步主服务器（master）上的数据变化。这种技术对于高可用性、故障恢复和扩展数据库性能至关重要。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值