Mysql主从复制方式以及可能出现的问题

大致流程：主库将变更写binlog日志，然后从库连接到主库之后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个中继日志 relay日志中。接着从库中有一个SQL线程会从中继日志读取binlog，然后执行binlog日志中的内容，也就是在自己本地再次执行一遍SQL，这样就可以保证自己跟主库的数据是一样的。

成都创新互联公司是创新、创意、研发型一体的综合型网站建设公司，自成立以来公司不断探索创新，始终坚持为客户提供满意周到的服务，在本地打下了良好的口碑，在过去的十多年时间我们累计服务了上千家以及全国政企客户，如混凝土搅拌罐车等企业单位，完善的项目管理流程，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致称誉。

如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，这时候从库成为了主库，那么有些数据可能就丢失了。

开启半同步复制 semi-sync ，用来解决主库数据丢失问题；

这个所谓半同步复制， semi-sync复制 ，指的就是主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志 写入自己本地的relay log之后 ，接着会 返回一个ack 给主库， 主库接收到至少一个从库的ack之后才会认为写操作完成了。 如果 过程出现失败 ，那么 我们的客户端就可以进行重试了 ；

主从延迟对于读写分离的涉及影响比较大

这里有一个非常重要的一点，就是 从库同步主库数据的过程是串行化的 ，也就是说 主库上并行的操作，在从库上会串行执行 。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行SQL的特点，在 高并发场景下，主库大量的写，那么从库的数据一个个的读，那么就会导致从库同步一定会比主库慢一些，是有延时的 。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。（主库并发写的量级越高，从库积压的同步数据越多，延迟越高）

我们可以用 show status 看看 Seconds_Behind_Master 参数，你可以看到从库复制主库的数据落后了几ms，但是这个也不是完全准确，可以看 Seconds_Behind_Master的

对于解决主从延迟，解决方案可以从以下方面考虑

MySQL 主从，5 分钟带你掌握

MySQL 主从一直是面试常客，里面的知识点虽然基础，但是能回答全的同学不多。

比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象。你之前面试，有遇到过哪些 MySQL 主从的问题呢？

所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库， 主库对外提供读写的操作，从库对外提供读的操作 ，下面是一主一从模式：

对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS， 当遇到一些活动时，查询流量骤然，就需要进行主从分离。

大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式， 主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。

MySQL 主从还能做到服务高可用，当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。

整体场景总结如下：

MySQL 的主从复制是依赖于 binlog 的，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步的完成。

详细流程如下：

当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：

我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。

那么我们改如何解决呢？

可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用， 当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。

主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值， 会导致从库查询的数据，和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：

总结一下主从延迟的主要原因 ：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库。

具体解决方案如下：

在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询从库，其它非核心场景，就不要去查主库了。

两台机器 A 和 B，A 为主库，负责读写，B 为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A。

一台主库多台从库，A 为主库，负责读写，B、C、D为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，C、D负责读，修复故障后，A 也成为从库，主库 B 同步数据到从库 A。

如何在一台windows主机上实现MySQL的主从复制

1、首先要在本地建立两个mysql服务（参考这里），指定不同的端口。我这里一个主（3306），一个从（3307）。

2、然后修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的数据库

#binlog-ignore-db=mysql #不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的

log-bin=mysql-bin #要生成的二进制日记文件名称

修改从配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主库添加一个用户 repl 并指定replication权限

create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --这里我指定数据库（test.*）时报错，而指定全库（*.*）时会成功。

4、保持主从mysql的test数据库初始状态一致。

一般是先将所有的表加读锁，然后copy磁盘上的数据库文件夹。我这里直接停止服务，然后将数据文件拷贝过去。

5、在主数据库里面运行show master status;记下file和position字段对应的参数。

mysql show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

安全最重要！MySQL配置主从复制，主主复制

为了保障数据的安全与稳定性，我们常用数据库的主从复制与主主复制来实现。主从复制为从机实时拷贝一份主机的数据，当主机有数据变化时，从机的数据会跟着变，当从机数据有变化时，主机数据不变；同样地，主主复制就是，多个主机之间，只要有一个主机的数据变化了，其它主机数据也会跟着变化。

添加以下内容

如果你是使用我之前那种方式启动的MySQL，那么你只需要去你相关联的宿主机的配置文件夹里面去建立一个 my.cnf 然后写入上面的类容就好了。

比如：我的启动命令如下（不应该换行的，这里为了方便查看，我给它分行了）

那么我只需要在 /docker/mysql_master/conf 这个目录下创建 my.cnf 文件就好了。

这个命令是需要在容器里面执行的

docker重启mysql会关闭容器，我们需要重启容器。

确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。 如果是启用的，则从站无法与主站通信，并且复制失败。

我的命令如下

在从服务器配置连接到主服务器的相关信息 （在容器里面的mysql执行）

上面代码的xxxxx你需要换成你的IP，docker 查看容器 IP 的命令如下：

启动的那个从服务器的线程

测试的话，你可以在主服务器里面，创建一个数据库，发现从服务器里面也有了，就成功了。

如果你还想要一个从服务器，那么你只需要按照上面配置从服务器再配置一个就行了，新建的从服务器，会自动保存主服务器之前的数据。（测试结果） 如果你上面的主从复制搞定了，那么这个主主复制就很简单了。我们把上面的从服务器也改成主服务器

1）、修改上面的从服务器的my.cnf文件，和主服务器的一样（注意这个server-id不能一样）然后重启服务器 2）、在从服务器里面创建一个复制用户创建命令一样（这里修改一下用户名可以改为 repl2） 3）、在之前的主服务器里面运行下面这个代码

上面主要是教你怎么搭建一个MySQL集群，但是这里面还有很多其它的问题。也是我在学习过程中思考的问题，可能有的小伙伴上来看到文章长篇大论的看不下去，只想去实现这样一直集群功能，所以我就把问题写在下面了。

1）、MySQL的replication和pxc MySQL的集群方案有replication和pxc两种，上面是基于replication实现的。

replication: 异步复制，速度快，无法保证数据的一致性。 pxc: 同步复制，速度慢，多个集群之间是事务提交的数据一致性强。

2）、MySQL的replication数据同步的原理 我们在配置的时候开启了它的二进制日志，每次操作数据库的时候都会更新到这个日志里面去。主从通过同步这个日志来保证数据的一致性。

3）、可否不同步全部的数据 可以配置，同步哪些数据库，甚至是哪些表。

4）、怎么关闭和开始同步

5）、我就我的理解画出了，主从、主从从、主主、复制的图。

往期推荐：

利用Docker仅花1分钟时间安装好MySQL服务

Linux下MySQL 5.7的离线与在线安装（图文）

Linux下安装MySQL8.0（收藏！）

如何配置MySQL数据库主从复制

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维 护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那 时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。

为什么使用主从复制？

1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新，这样可能引起冲突。

3、使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服 务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，从服务器使用此线程读取中继日志并执行日 志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。

默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序 列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为 host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用 mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。

从服务器在数据目录中另外创建两个状态文件--master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

设置主从复制：

1、确保在主服务器和从服务器上安装的MySQL版本相同，并且最好是MySQL的最新稳定版本。

2、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。

mysql GRANT REPLICATION SLAVE ON *.*

- TO 'replication'@'%.yourdomain.com' IDENTIFIED BY 'slavepass';

3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：

mysql FLUSH TABLES WITH READ LOCK；

保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。

shell cd /usr/local/mysql/

shell tar -cvf /tmp/mysql-snapshot.tar ./data

如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。

当FLUSH TABLES WITH READ LOCK所置读锁定有效时（即mysql客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：

mysql SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73 | test | manual,mysql |

+---------------+----------+--------------+------------------+

File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin.003，偏移量为73。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，从服务器应从该点开始从主服务器上进行新的更新。

如果主服务器运行时没有启用--logs-bin，SHOW MASTER STATUS显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4.

取得快照并记录日志名和偏移量后，回到前一中端重新启用写活动：

mysql UNLOCK TABLES；

4、确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到232–1之间的一个正整数值。例如：

[mysqld]

log-bin

server-id=1

如果没有提供那些选项，应添加它们并重启服务器。

5、停止从服务器上的mysqld服务并在其my.cnf文件中添加下面的行：

[mysqld]

server-id=2

slave_id值同Master_id值一样，必须为1到232–1之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。

6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。

Shell chown -R mysql:mysql /usr/local/mysql/data

7、启动从服务器。在从服务器上执行下面的语句，用你的系统的实际值替换选项值：

mysql CHANGE MASTER TO

- MASTER_HOST='master_host_name',

- MASTER_USER='replication_user_name',

- MASTER_PASSWORD='replication_password',

- MASTER_LOG_FILE='recorded_log_file_name',

- MASTER_LOG_POS=recorded_log_position;

8、启动从服务器线程：

mysql START SLAVE；

执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。

9、如果出现复制错误，从服务器的错误日志（HOSTNAME.err）中也会出现错误消息。

10、从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME-relay-log.info。从服务器使用这两个文 件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。

当前文章：mysql怎么主从复制,mysql主从复制的优势
URL分享：http://scgulin.cn/article/hspedh.html