mysql 主从复制

最新推荐文章于 2024-10-24 02:45:00 发布
最新推荐文章于 2024-10-24 02:45:00 发布
阅读量109 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/supahero/article/details/109608295
版权
原文链接：https://www.cpweb.top/1072

一、简介

  主从复制可以将一台主MySQL数据库服务器的数据实时自动复制到一台或多台从MySQL数据库服务器，实际上默认情况下主从复制是异步的。根据配置，可以复制数据库中的所有数据库、选定的数据库甚至选定的表。
  MySQL 5.7支持不同的复制方法。传统方法基于复制主库二进制日志中的事件，从库需要主库的二进制日志文件及其中的位置点。而新方法基于全局事务标识符(GTID)是事务性的，从库并不需要去找主库的二进制日志文件及其中的位置点，从而大大简化了许多常见的复制任务。使用GTID进行复制可以确保源和副本之间的一致性，在主库上提交的所有事务都会自动应用到从库上。
  官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication.html
  以下以传统方法为主，关于基于GTID的主从复制会在后面文章介绍到。以下内容基于MySQL5.7.28。
 

二、主从复制配置

我们先来简单配置一下主从复制，再来细说原理，这样更加容易理解明白。
主库操作：

1、开启binlog日志
[root@db01 ~]# vim/etc/my.cnf       # 在mysqld下添加以下配置
server_id=1
log_bin=/service/data/binlog/mysql-bin

2、主库创建专门用于复制的用户
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '000000'

3、获取主库当前的binlog文件和位置点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      414 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

不建议从主数据库的当前状态开始同步，因为可能会造成主从数据不一致，从而导致主从复制错误。
可以从File：mysql-bin.000001；Position：1，即从最开始开始复制。首先得保证binlog日志文件保存着数据库从头到尾的事件，如果数据量大同步耗时久。
建议采用：备份主库恢复至从库，再从备份文件中获取binlog日志文件和位置点。
这里简单演示下，注意简单，因为数据库并没有什么数据：

[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 >/backup/full.sql

从库操作：主从时间要同步

1、修改配置文件
[root@db02 ~]# vim/etc/my.cnf       # 在mysqld下添加以下配置
server_id=2         // 注意主库和从库不能相同，还有server_uuid也不能相同

2、恢复数据，获取主库的binlog日志文件和位置点
mysql> source /backup/full.sql;

[root@db02 ~]# grep '\-\- CHANGE MASTER TO' full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=414;

3、配置从库
mysql> change master to
       master_host='10.0.0.51',
       master_user='repl',
       master_password='000000',
       master_port=3306,
       master_log_file='mysql-bin.000002',
       master_log_pos=414;

4、启动从库的复制功能
mysql> start slave;

5、检查从库复制功能状态
mysql> show slave status \G
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

 

三、主从复制原理

  简单的说，主从复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中。
  其他数据库作为slave通过一个IO线程与master的binlog_dump线程保持通信，binlog_dump线程一直监控着binlog日志文件的状态，有新的日志就返回给从库的IO线程。
  从库的IO线程接收到master binlog日志文件发生的变化，则会把变化复制到自己的中继日志relay log中。
  然后slave的一个SQL线程会从relay log里面读取相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
  具体流程如下：

  1、从库执行 change master to , 所有信息会被保存到master.info文件中。
  2、从库执行 start slave，启动IO和SQL线程。
  3、从库IO线程工作，获取master.info文件信息，生成指针（mi）。
  4、从库IO线程，连接主库。
  5、主库连接层，接收请求，验证用户、权限，并生成binlog_dump线程。
  6、从库IO线程和主库binlog_dump线程交互，验证server_id、server_uuid、时间等，从库正式注册到主库中。
  7、主库binlog_dump线程一直监控着binlog状态，有新的日志就返回给从库IO线程。
  8、从库IO线程通过MI指针中的binlog位置点，向binlog_dump线程请求最新日志。
  9、从库IO线程接收主库binlog_dump线程发送的新的日志，mi指针自动更新，并写入master.info中。
  10、从库IO线程线程最终会将接收到的binlog信息，写入到relay log中继日志中。
  11、从库SQL线程，读取relay-log.info信息，获取到上次已经应用过的relay log的位置点信息，生成一个rli指针，与relay log中继日志中的pos进行对比。
  12、如果有新的中继日志生成，就执行到数据库中，执行完成更新rli指针，并写入到relay-log.info中。

  注：
    master.info：用来存储主库相关的信息，默认位于数据目录内。
    relay-log.info：用来记录上次已经应用过的relay log的位置点信息。
    relay log：存储接收到的binlog日志，默认位于数据目录内，默认文件名一般为：主机名称-relay-bin.xxxxxx。

 

四、主从复制状态信息查看

主库：

1、查看线程
mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host            | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 10 | root | localhost       | NULL | Query       |    0 | starting                                                      | show processlist |
| 11 | repl | 10.0.0.52:36712 | NULL | Binlog Dump | 1307 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+

2、查看已经注册的从库
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 846602a3-232d-11eb-ad14-000c29e0d787 |
+-----------+------+------+-----------+--------------------------------------+

从库：

mysql> show slave status \G
 
1、主库相关信息（来自于master_info）
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 704

2、从库relay-log相关信息(relay_info)
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 610

3、relaylog和binlog的对应关系
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 704

4、线程状态有关的信息 				  
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 

5、过滤复制相关信息
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

6、主从复制的时延（从库同步主库数据的延时）
Seconds_Behind_Master: 0

7、延时从库状态信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL
		  	  
8、GTID复制相关	  
Retrieved_Gtid_Set: 
Executed_Gtid_Set:

 

五、主从复制常见故障

有时候我们会遇到IO线程或者SQL线程状态异常，下面来介绍一下遇到这些异常状态的原因和解决办法。
一般通过关注以下信息去获知线程状态有关的信息。

mysql> show slave status \G;
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
......

1、IO线程

（1）connecting（建立连接）状态
外部原因： 网络不通、防火墙
内部原因： 用户、密码错误、port、IP错误、主库连接数满了

对于change master to时主库信息填写有误可以使用以下方法：
mysql> stop slave;       // 停从库
mysql> reset slave all;  // 重置从库配置信息，会忘记已经记录的主库binlog相关信息，还会删除任何现有的relay log并开始一个新的relay log。
mysql> change master to ...... //重新构建主从
mysql> start slave;      // 启动从库

（2）NO状态
可能原因：主从之间的server_id和server_uuid重复、搭建时位置点写错了、主库的日志损坏。
解决方案：
  第一个：修改server_id和server_uuid使之不重复即可。
  第二个：重置从库配置，重新构建主从。
  第三个：修复主库日志，重新构建主从。

2、SQL线程

NO状态，可能原因：
（1）relay log损坏
（2）接收到SQL语句无法执行，导致原因：
   • 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
   • 要创建的数据库对象,已经存在
   • 要删除或修改的对象不存在  
   • DML语句不符合表定义及约束时.  

 解决方案：
   对于第一点，把硬件配置、版本、参数和SQL_MODE配置一致即可。
   因为对象的存在性或者约束冲突导致SQL语句无法执行，原因在于，从库发生写入了，或者经历过宕机导致主从数据不一致。
   防范方案： 
     • 从库设置只读，例如只读只会影响到普通用户，对管理员用户无效，所以不影响主从复制。
       方法：set global read_only=1;
     • 使用读写分离中间件。 
     • 高可用结构、半同步、MGR等。

 当出现此类问题的解决思路： 
  1) PT工具校验主从一致性（pt-table-checksum）
  2) 通过校验信息进行同步数据 (pt-table-sync)
  3) 跳过错误

 跳过错误方法：
  mysql> stop slave; 
  mysql> set global sql_slave_skip_counter = 1;   // 将同步指针向下移动一个，如果多次不同步，可以重复操作。
  mysql> start slave;

 
参考文章：https://www.jianshu.com/p/6ed2cc292077

啊哈？？
关注
专栏目录
docker-compose关于mysql主从复制一键搭建脚本
09-18
关于博客: https://blog.csdn.net/qq_42413011/article/details/126914080 的搭建脚本
SpringBoot第 12 讲：SpringBoot+MySQL主从复制、读写分离
10-10
在本讲中，我们将深入探讨如何使用SpringBoot与MySQL实现主从复制以及读写分离的架构设计。这一技术方案在大型分布式系统中尤为常见，它能够有效地提高数据库系统的可用性和性能。 首先，让我们理解主从复制的核心...
Mysql主从复制
xiaogengtongxu的博客
07-04 827
这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的。read_only的含义是只可以读，不能修改数据库，主要目的是，避免出现从被修改，主的数据不能同步，造成主从复制失败。
MySQL主从复制
Lxn2zh的博客
01-27 1599
由于数据的大量增加，访问量越来越复杂，有时候需要来部署多个数据库，通过数据库的复制策略，可以将一台mysql数据库服务器中的数据复制到其他的mysql数据库服务器之上，当各台数据库服务器上都包含相同数据的时候，前端应用通过访问mysql集群中任意一台服务器，都能够读取到相同的数据，这样，每台mysql服务器所需要承担的负载就会大大降低，从而提高整个系统的承载能力，达到系统扩展的目的。然后封锁并等待主服务器通知新的更新。所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…
Docker部署MySQL主从复制
最新发布
ddf128的博客
10-24 2503
Docker 镜像是用于创建容器的蓝图，包含了运行应用的所有依赖，如代码、运行时、库和配置文件。它们是轻量级、可移植的，确保应用在任何环境中都能一致运行。每个镜像由多个层组成，每层对应Dockerfile中的一个指令，这种分层帮助实现资源共享，优化存储和传输效率。Docker 镜像可以通过docker build创建，也可以从公共或私有仓库如Docker Hub拉取。使用docker pull命令获取镜像，Docker部署mysql数据库，主从复制，双主双从，主主互备，实现数据同步复制
MySQL 主从复制原理与问题
Dablelv 的博客专栏。
10-19 1086
对应的，主库会启动一个 log dump 线程，根据传过来的（file，pos）在本地的binlog中查找，并把剩下的 binlog 发送给slave。主节点会为每一个从节点创建一个 dump 线程。因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication ，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 log dump 线程，而每个从节点都有自己的 I/O 线程和 SQL 线程。
mysql主从复制
m0_73695023的博客
05-12 2249
数据库的主从复制工作中必须要用到一定要会！！！！
MySQL主从复制配置
码云仓库地址：https://gitee.com/lance-gyq
04-30 2969
MySQL主从复制及读写分离 一、MySQL复制的应用常见场景 读写分离，提高查询访问性能，有效减少主数据库访问压力。 实时灾备，主数据库出现故障时，可快速切换到从数据库。 数据汇总，可将多个主数据库同步汇总到一个数据库中，方便数据统计分析。 二、MySQL主从复制原理介绍 1、 MySQL异步和半同步复制 传统的MySQL复制提供了一种简单的主-从复制方法。有一个主，以及一个或多个从。 主节点执行和提交事务，然后将它们（异步地）发送到从节点，以重新...
MySQL主从复制详解
qq_52645134的博客
12-18 7352
在实际应用中，合理配置和持续监控主从复制的状态，有助于保障数据的安全和稳定性，提高数据库整体的可用性。MySQL主从复制基于主从模型工作，其中主服务器（Master）负责处理写操作（INSERT、UPDATE、DELETE等），而从服务器（Slave）则复制主服务器的数据变更。：主服务器将二进制日志发送给从服务器，从服务器接收并应用这些日志，以重放主服务器上的写操作，保持数据同步。：连接到主服务器，并从其获取二进制日志文件，然后将这些日志应用到自身的数据上，保持与主服务器数据的同步。
MYSQL主从复制
07-30 2807
MYSQL主从复制
搭建Mysql主从复制
sloveb123的博客
06-20 1116
MySQL主从复制是一种常用的数据库高可用性解决方案，可以提高数据库的可用性和性能。本教程将介绍如何搭建MySQL主从复制。基于语句的复制（STATEMENT）：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。基于行的复制（ROW）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。混合类型的复制（MIXED）：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
MySQL主从复制与读写分离.docx
04-13
MySQL 主从复制与读写分离 MySQL 主从复制是指将一个 MySQL 服务器的数据实时同步到另一个 MySQL 服务器中，以实现数据的高可用性和读写分离。下面是 MySQL 主从复制与读写分离的详细知识点： MySQL 主从复制 ...
mysql 日期类型转换
supahero的博客
11-02 6989
原文链接：https://www.cpweb.top/965 1、使用cast() 和 convert() 函数实现日期格式的转换   语法格式：    convert( 表达式,数据类型 )    cast( 表达式 as 数据类型 )   可转换的类型有 字符串char、日期date、时间time、日期时间datetime、浮点型decimal、整数signed、无符号整数unsigned。 mysql> select cast(now() as signed); +--------...
mysql binlog日志中的GTID的应用
supahero的博客
11-04 2950
原文链接：https://www.cpweb.top/1000 一、简介   GTID (Global Transaction ID) ，全局事务ID。是一个对于已提交事务的全局唯一的编号，随着事务记录到binlog中，用来标识事务，每一个事务都会有一个。   GTID 实际上是由 server_uuid + transaction_id 组成的：   **server_uuid：**一个mysql实例的唯一标识，每一台mysql实例的server_uuid都是不同的。在mysql第一次启动时...
mysql版本降级
supahero的博客
10-15 2808
原文链接：https://www.cpweb.top/908 一、降级注意事项 - 仅支持GA版本之间升级。 - 从MySQL 5.7降级到5.6只能使用logical降级方法，不能使用In-place方式降级。 - 不支持跳版本降级。例如，不支持直接从5.7降级到5.5。 - 对于同一个发行版系列中，如5.7.y → 5.7.x 进行降级时，才支持In-place就地降级。   接下来我们来进行降级演示，因为是模拟环境，数据库中没有数据，所以不做备份，以下只演示升级...
mysql ANSI连接查询
supahero的博客
11-02 1426
原文链接：https://www.cpweb.top/974 一、简介   在SQL中，可以使用两种语法形式连接表：一种是前面文章介绍的 FROM 子句，连接条件写在 WHERE 子句的逻辑表达式中，从而实现表的连接。另一种是 ANSI（美国国家标准学会） 连接语法形式，在 FROM 子句中使用 join…on 关键字，连接条件写在on之后，从而实现表的连接。   语法格式：     from { 表名 1 [连接类型] join 表名 2 on 连接条件} [,…n]   from…join...
使用systemd管理mysql
supahero的博客
10-14 754
1、方法1 [root@db01 data]# cp /data/app/mysql/support-files/mysql.server /etc/init.d/mysqld [root@db01 data]# chkconfig --add mysqld //会从init.d文件夹中寻找mysqld文件 [root@db01 ~]# systemctl start mysqld //注意关闭之前启动的数据库   2、方法2 [root@db01 ~]# cd /usr/lib
MySQL主从复制配置教程
"mysql主从复制安装配置进阶.pptx" MySQL主从复制是一种常见的数据库高可用性和负载均衡方案，它允许数据从一个主服务器（Master）实时同步到一个或多个从服务器（Slave）。这种配置有助于实现数据冗余，提高系统的...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值