锐客网

  1. 首页 > 睿知 > it技术 > >

mysql主从复制怎么做 mysql主从复制的实现

IT技术主从

MySQL的Binlog与主从复制在MySQL中mysql主从复制怎么做，可以使用多种存储引擎 。其中最常用的InnoDB引擎支持事务，Redo Log和Undo Log就是InnoDB里面的工具 ， 用于实现事务 。而Binlog是MySQL层面的东西，用于实现主从复制，与使用的存储引擎无关 。
通过监听并解析Mater的Binlog ， 也可以实现将MySQL中的数据同步到其他应用组件中（比如更新缓存）的效果 。
在不发生宕机的情况下，未提交的事务和已回滚的事务是不写入Binlog日志中的 ， 只有提交成功的事务才写入Binlog日志 。这一点和Redo Log不一样，Redo Log中会记录未提交、已回滚的事务内容 。
Binlog是一种逻辑日志——例如Binlog的statement格式记录原始SQL语句、RAW格式记录某一行修改前后的值——且一个事务的日志在Binlog中是连续排列的，因此要求每个事务都要串行地写入，这意味着每个事务在写Binlog之前都要排他地锁住Binlog，这会导致写的效率很低 。MySQL5.6之后，通过pipline技术异步地批量化将已提交的事务内容写入Binlog 。
一个事务的提交既要写Binlog日志又要写Redo Log日志，如何保证双写的原子性？一个写成功 ， 写另外一个时发生宕机，重启后如何处理？在讨论这个问题之前，先说下Binlog自身写入的原子性问题：Binlog刷盘到一半，出现宕机，这个问题和Redo Log的写入原子性是同样的问题，通过类似于checksum的办法或者Binlog中的结束标记来判断出某个事务的Binlog这是不是不完整的Binlog，从而把不完整的部分截掉 。对于客户端来说，此时宕机，事务肯定是没有提交成功的，所以截掉也没问题 。下面来讲如何保证双写Binlog和Redo Log的原子性 。由于双写Binlog和Redo Log发生在同一台机器上 ， 这其实是一个内部分布式事务 ， 可以使用两阶段提交法来实现双写的原子性 。简单来说就是：
1）第一阶段（准备阶段）：MySQL Server要求innoDB完成将事务内容写入Redo Log中的工作，只等事务提交mysql主从复制怎么做；以及，MySQL Server完成Binlog内容写入内存的工作，只等刷盘 。两个都准备好之后，会向MySQL Server发送OK反?。?MySQL Server紧接着执行第二阶段 。
2）第二阶段（提交阶段）：收到客户端的Commit指令，MySQL Server先将内存中的Binlog刷盘，然后让innoDB执行事务的提交 。两个都完成之后，会向MySQL Server发送OK反馈 ， 两阶段提交结束 。
若双写Binlog和Redo Log的过程中发生宕机，处理思路为：
1）若宕机发生在第一阶段 ， 此时Binlog还在内存中，宕机导致全部消失 。而Redo Log记录了未提交的日志，MySQL Server重启后感知到Binlog中不存在Redo Log中记录的未提交事务，会自行回滚未提交事务的Redo Log日志；
2）若宕机发生在第二阶段，Binlog写了一半，innoDB还未执行提交，MySQL Server重启后会对Binlog做截断，对Redo Log中记录的未提交事务做回滚；
3）若宕机发生在第二阶段，Binlog写入成功 ， innoDB还未执行提交，MySQL Server重启后会通过checksum的办法或者Binlog中的结束标记感知到Binlog写入成功，紧接着对Binlog中存在的、但Redo Log未提交的事务发起提交 。
在MySQL的Master / Slave集群模式中 ， 有三种主从复制模式：
1）同步复制：所有的Slave都收到Master发送的Binlog，并且接收完，Master才认为事务提交成功，再对客户端返回成功 。这种方式最安全，但是性能很差；
2）异步复制：只要Master事务提交成功，就对客户端返回成功 。后台线程异步地将Binlog发送给Slave，然后Slave回放Binlog 。这种方式性能最好，但是可能会导致数据丢失；
3）半同步复制：Master事务提交后 ， 同时把Binlog同步给Slave，只要有部分（数量可以配置）Slave收到了Binlog，就认为事务提交成功 ， 对客户端返回 。
对于半异步复制，如果Slave超时后还未返回，也会退化为异步复制 。所以无论是异步复制还是半异步复制 ， 都无法严格保证主从中的数据完全一致，主从复制的延迟会导致主节点宕机后部分数据未来得及同步到从节点 ， 从而丢失数据 。但是主节点宕机后，还是要立即切换到从节点，保证服务的可用（牺牲一致性保证可用性），数据的丢失可以通过后续的人工干预来补偿 。
Mysql主从复制方式以及可能出现的问题大致流程mysql主从复制怎么做：主库将变更写binlog日志mysql主从复制怎么做，然后从库连接到主库之后mysql主从复制怎么做，从库有一个IO线程，将主库mysql主从复制怎么做的binlog日志拷贝到自己本地，写入一个中继日志 relay日志中 。接着从库中有一个SQL线程会从中继日志读取binlog ， 然后执行binlog日志中的内容，也就是在自己本地再次执行一遍SQL，这样就可以保证自己跟主库的数据是一样的 。
如果主库突然宕机 ， 然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，这时候从库成为了主库 ， 那么有些数据可能就丢失了 。
开启半同步复制semi-sync ， 用来解决主库数据丢失问题；
这个所谓半同步复制， semi-sync复制 ， 指的就是主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志 写入自己本地的relay log之后，接着会 返回一个ack 给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了 。如果 过程出现失败 ，那么 mysql主从复制怎么做我们的客户端就可以进行重试了 ；
主从延迟对于读写分离的涉及影响比较大
这里有一个非常重要的一点，就是 从库同步主库数据的过程是串行化的，也就是说 主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行SQL的特点，在 高并发场景下，主库大量的写 ， 那么从库的数据一个个的读，那么就会导致从库同步一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到 。（主库并发写的量级越高，从库积压的同步数据越多，延迟越高）
我们可以用 show status 看看 Seconds_Behind_Master 参数，你可以看到从库复制主库的数据落后了几ms，但是这个也不是完全准确，可以看 Seconds_Behind_Master的
对于解决主从延迟，解决方案可以从以下方面考虑
如何在一台windows主机上搭建mysql主从复制先在主数据库中创建新数据库rep_test 。
然后编辑主数据库的my.ini文件
在[mysqld]节点中增加如下内容：
server-id=1 #指定唯一的ID，1至32，必须的
log-bin=mysql-log-bin #指定二进制日志存放路径 ， 必须的
binlog-do-db=rep_test #指定要同步的数据库，必须的
#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项
重启主数据库 ， 然后在主数据库中建立一个备份账户
mysqlgrant replication slave on *.* to slave@192.168.1.128 identified by 'slave' ;
mysqlflush privileges;
PS:identified by 指定的slave是账号slave@192.168.1.128 的密码
显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；
mysqlshow master status;
在从数据库中创建新的数据库rep_test 。
然后编辑从数据库的my.ini文件
在[mysqld]节点中增加如下内容：
server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样
replicate-do-db=rep_test #指定要同步的数据库，必须的
#replicate-ignore-db=mysql #指定不要同步的数据库，
重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave
mysqlchange master to master_host='192.168.1.2',master_user='slave',master_password='slave', master_log_file='mysql-bin.000002',master_log_pos=120;
mysqlstart slave;
查看从数据库的信息
mysqlshow slave status \G;
如果出现： Slave_IO_Running: YesSlave_SQL_Running: Yes以上两项都为Yes，那说明没问题了
测试主从复制是否有效果
在主数据库中创建一个新的数据库，然后再切换到从数据库查看是否同样多出通名的数据库
配置旧数据库的主从复制
如果一开始数据库的架构不是主从复制 ， 并且运行一段时间后已经有数据存在，那配置的方式略有不同 。
编辑主数据库的my.ini文件，加上一下内容：
binlog-do-db=landclash
【mysql主从复制怎么做 mysql主从复制的实现】重启主数据库 ， 然后在主数据库中锁定所有的表
mysqlflush tables with read lock；
显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；
mysqlshow master status;
将主数据库data目录下需要做主从复制的数据库的同名目录拷贝到从数据库的data目录下
编辑从数据库的my.ini文件，加上一下内容：
replicate-do-db=landclash
重启从数据库，因为主数据库在重新配置my.ini后 ， 日志文件变成新的文件，所以需要再次设置登录主数据库的账号和密码等信息
mysqlstop slave;
mysqlchange master to master_host='192.168.1.2',master_user='slave',master_password='slave', master_log_file='mysql-bin.000003',master_log_pos=120;
mysqlstart slave;
再次输入查看从数据库状态的命令
mysqlshow slave status \G;
完成上述配置后，回到主数据库，将表解锁
mysqlunlock tables;
之后在主数据库的修改就能同步到从数据库上了 。
如何配置MySQL数据库主从复制MySQL支持单向、异步复制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客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：
mysqlSHOW 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 主从，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 | |
------------------ ---------- -------------- ------------------
1 row in set (0.00 sec)
6、在从库设置它的master：
mysql change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='asdf',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.19 sec)
这里的master_log_file和master_log_pos对应刚才show master status记下的参数 。
7、在从库开启从数据库复制功能 。
slave start;
mysql slave start;
Query OK, 0 rows affected (0.00 sec)
在从库可以通过show slave status来查看一些参数 。
8. 此时在主库创建表或插入数据，在从库就会很快也能看到了 。
-- 主库
mysql create table tianyc_02(b int);
Query OK, 0 rows affected (0.16 sec)
mysql insert into tianyc_02 values(2013);
Query OK, 1 row affected (0.13 sec)
-- 从库
mysql show tables;
----------------
| Tables_in_test |
----------------
| tianyc_01 |
| tianyc_02 |
----------------
2 rows in set (0.00 sec)
mysql select * from tianyc_02;
------
| b |
------
| 2013 |
------
1 row in set (0.00 sec)同理，可以搭建第二、第三个从节点 。
关于mysql主从复制怎么做和mysql主从复制的实现的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：redis集群哨兵模式优点 redis集群加哨兵模式

    下一篇：区块链学堂第一课，区块链的课程