mysql的复制和备份

复制和备份

二进制日志

• 也叫作变更日志（update log），默认开启。记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。

• binlog主要用于：

  • 数据恢复：如果MySQL意外停止，可以通过二进制日志文件来查看执行了哪些操作，对数据库进行了哪些修改，然后根据二进制日志中的记录来恢复数据
  • 数据复制：把主数据库的二进制日志传递给从服务器进行重放

• MySQL服务重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

• 查看二进制日志的参数

   show variables like '%log_bin%';
  

• 因为是二进制文件，所以不能直接查看，查看的方式：

  mysqlbinlog -v
  
  show binlog events
  

使用二进制日志恢复数据

• 在使用二进制日志文件恢复数据的时候，因为恢复操作也会记录二进制日志，为了防止混淆，应该先复制一个新的文件，根据新的文件来恢复，

• 恢复数据的语法为

  mysqlbinlog [option] filename|mysql –uuser -ppass
  

• 参数说明：

  • filename ：是日志文件名。
  • option ：可选项，比较重要的两对option参数是–start-date、–stop-date 和 --start-position、–stop-position。
    • –start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
    • –start-position和–stop-position ：可以指定恢复数据的开始位置和结束位置。

• 使用mysqlbinlog命令进行恢复操作时，必须是编号小的日志先恢复，例如编号001的必须比002先恢复

• 二进制日志可以通过数据库的全量备份和二进制日志中保存的 增量信息 ，完成数据库的 无损失恢复 。但是如果遇到数据量大、数据库和数据表很多的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

• 一个有效的解决办法是 配置主从数据库服务器 ，甚至是 一主多从 的架构，把二进制日志 文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常 等问题。

删除二进制日志：

• MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。
• PURGE MASTER LOGS 只删除指定部分的二进制日志文件
• RESET MASTER 删除所有的二进制日志文件

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

二进制日志的写入机制

• 事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
• write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。
  • 为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binglog 会丢失。
  • 为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。
  • 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync

binlog与redolog对比

• redolog是在InnoDB存储引擎层记录的，而binlog是mysql数据库服务器层记录的

• redo log 是 物理日志 ，记录内容是“在某个数据页上做了什么修改”

• binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。

• 两种日志与记录写入磁盘的时间点不同，二进制日志只在事物提交完成后进行一次写入，而redolog的重做日志在事物的进行过程中不断地被写入。

• binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，但是redolog是循环使用的。

• 两个日志都是保证持久性的， 但是侧重点不一致：

  • redo log 让innodb存储引擎有崩溃恢复的能力
  • binlog 保证了Mysql 集群数据的一致性

MySQL 是如何保证数据不丢失的

• 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后恢复数据
• 在恢复数据时，redolog 状态为commit 则说明 binlog 也成功，直接恢复数据；如果redolog 是prepare(准备)，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

二次写入：

• 因为binlog与redolog 写入时机不一致，有可能会出现 redolog 写完， 但是binlog没写完，数据库在此时宕机了，这时候binlog里面没有对应的修改记录

• 为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案

• 也就是在binlog写入前后，redo log 各有一次写入

• 会有三种情况：

  • 情况一：一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了，此时：

    • 由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

  • 情况二：假设写完 binlog 之后崩溃了，此时：

    • redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

  • 情况三：假设 redolog 处于 commit 状态的时候崩溃了，binlog 肯定已经提交了。

• 所以，两阶段提交能够确保数据的一致性。

中继日志：

• 中继日志只在主从服务器架构的从服务器上存在。
• 从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫 中继日志 。
• 然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。
• 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。
• 中继日志与二进制日志的格式相同，查看方式类似二进制日志

复制解决的主要问题有：

• 可以在不同地理位置分布数据备份，避免单点故障，容灾

• 复制本质上是一种冗余机制，通过数据冗余来提供高可用性，如果服务器宕机可以快速切换到其他服务器

• 读写分离，可以将读操作负载均衡到多个服务器上

  • 复制产生了多个数据副本，由于从库的复制是异步的，所以难点是在如何处理从库上的脏数据
  • 读写分离的实现方式：
    • 最简单的方法就是把所有不能容忍脏数据的sql分配到主库，其他的查询分配到从库，虽然很容易实现，但是很少有查询可以允许脏数据
    • 也可以让应用检查复制的延迟，不需要从库百分百跟上主库，如果数据很老，就从主库获取
    • 也可以基于版本号、时间戳等，来判断数据是否足够新

• 复制的目的是让一台服务器的数据和其他服务器保持同步

• 复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但是可以用于备份和从崩溃中恢复，这点开销是必要的

• 每个备库都会对主库增加一些负载，主要是从主库读取二进制文件的开销

• 对于写操作，在一主多从的情况下，写操作会被执行多次，系统的性能就取决于最慢的部分，而且一主多从，会有很多重复数据，可能会造成浪费

复制的步骤

• 三个线程：二进制日志转储线程、从库 I/O 线程、从库 SQL 线程

• 首先是主库在每次准备提交事务完成数据更新前，会将数据更新的事件按照事务的提交顺序记录到二进制日志中，然后才提交事务

• 从库会启动从库 I/O 线程跟主库建立一个普通的客户端连接，然后在主库上启动一个 二进制转储线程 读取主库上的二进制日志中的事件，然后把主库上的日志复制到自己的中继日志中，如果这个线程追赶上了主库，就会进入休眠状态，直到主库通知有新的事件产生才会唤醒

• 最后 从库SQL线程 会读取中继日志中的事件，在从库执行

• 这种复制架构，实现了获取事件和重放事件的解耦，允许两个过程异步执行，但是也限制了复制的过程，主库并发运行的sql，在从库上只能串行化执行，因为只有一个线程来重放中继日志中的事件

复制的基本原则：

• 一个从库只能有一个主库，一个主库可以有多个从库
• 每个从库必须有一个唯一的服务器id，如果有重复的ID，会相互竞争，把对方从主库上踢出
• 如果打开了log_slave_updates，从库可以成为其他库的主库

mysql复制的方式有：基于行的复制和基于语句的复制以及混合模式

• 基于行的复制（binlog_format=ROW）

  • 这种方式会把实际数据记录在二进制日志中，好处就是能正确复制每一行，没有无法处理的场景，而且不需要重新执行sql语句，可以更加高效的复制数据，特别是对大表的数据汇总到小表
  • 但是如果遇到对全部数据进行更新的sql，基于行的复制，开销会非常大，因为每一行都会被记录到二进制日志
  • 而且因为语句没有记录在日志里，就无法判断执行了那些sql，如果出现问题，会很难排查

• 基于语句的复制（binlog_format=STATEMENT）

  • 主库会记录那些造成数据更改的sql，然后从库读取重放，也就是把主库执行过的sql在执行一遍
  • 好处是实现简单，而且二进制日志里的事件紧凑，不会带来太多网络开销
  • 缺点是sql可能包含不确定的函数，例如时间戳，存储过程和触发器都有可能出现问题
  • 此外，更新必须是串行的，因为sql语句的顺序不能改变

• 混合模式 (binlog_format=MIXED)：就是Statement与Row的结合，两种模式都有优缺点，mysql可以自动在两种模式间切换，默认使用的是基于语句的复制，但是如果发现语句无法被正确复制，就会使用基于行的模式

• 两种方式也都是通过在主库上记录二进制日志，在备库重放日志的方式来实现异步的数据复制，所以同一时间点主库和从库的数据可能不一致，而且无法保证主从之间的延迟

• 可以使用高版本的mysql作为低版本的从库，但是低版本最好不要作为高版本的从库

复制的三种方式：

• 异步复制：主库事务提交后不需要等待从库返回结果，直接返回给客户端，这样的好处是不会影响主库写的效率，但是如果主库宕机，从库数据有可能丢失。效率最高，但是一致性最差
• 半同步复制：5.5版本后主库提交后后，需要等待至少有一个从库接收到了Binlog，并且写入到中继日志中，才能返回给客户端，虽然降低了写的效率，但是一致性更好，5.7版本后可设置应答的从库数量，如果提高参数的值，一致性会进一步增加，但也会增加主库的响应时间
• 组复制：
  • 简称 MGR（MySQL Group Replication），是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。
  • 首先我们将多个节点共同组成一个复制组，在 执行读写（RW）事务 的时候，需要通过一致性协议层 （Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节 点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起 方一个说了算。
  • 而针对 只读（RO）事务 则不需要经过组内同意，直接 COMMIT 即可。
  • 在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消 息和全局有序消息，从而保证组内数据的一致性

故障转移和故障恢复

• 冗余只有在遇到故障需要恢复时才有用，一点也不会增加可用性和减少宕机
• 在故障转移的时候，高可用是建立在冗余的基础上的，当一个组件失效，可以启用备用组件
• 故障恢复指的是，服务器A失效，用服务器B来替代它，然后等服务器A修复后再替换回来
• 如果服务器不能自由的切换，最多只能延缓宕机时间，所以一般推荐对称复制，这样故障转移和故障恢复就是相反方向上的相同操作

复制过滤器：

• 运行值复制服务器上一部分数据，有两种过滤方式，一种是过滤主库到二进制日志的事件，一种是在从库上过滤记录到中继日志的事件
• 通常不要开启，否则会造成数据的永久丢失，并且无法找回，很容易造成主备不同步或者复制出错

复制配置：

• sync_binlog=1，开启之后，提交事务前，会将二进制日志同步到磁盘上，保证服务器崩溃时不会丢失事件，一般只在主库开启，对于从库来说是额外的开销
• log_slave_updates，可以让备库变为其他服务器的主库，设置之后，mysql会把自己执行的事件记录到二进制日志中

常见的配置：

• 一主多备：

  • 和一主一备，配置差不多，因为从库间没有交互，仅仅是连接到同一个主库
  • 少量写大量读时，这种配置非常有效，可以把读分配到多个从库上
  • 可以用作灾难恢复，也可以拿一个从库作为测试环境

• 主动-主动模式

  • 也叫双向复制，两台服务器，互为对方的主库，例如两个不同地理位置的办公室，都需要一份可写的数据拷贝
  • 最大的问题是如何解决冲突，带来的麻烦远大于好处
  • mysql，不支持多主库间的复制

• 主动-被动模式

  • 其中的一台服务器是只读的被动服务器
  • 因为服务器的配置对称，所以故障转移和故障恢复很容易

• 环形复制

  • 三个或以上的服务器，相互复制，形成一个环形链，整个结构非常脆弱，应该尽量避免

• 主库-分发主库-从库

  • 一个主库，一个分发主库，分发主库用来将二进制日志分发多个从库，减小了主库的负担

备份

备份的理由：

• 灾难恢复
• 需要知道过去某个点的数据是什么样的
• 再进行某些操作后，又想恢复
• 对于大数据库来说，物理备份是必须的，逻辑备份慢，而且恢复需要很长时间

关闭系统做离线备份，是最简单也是最安全的，数据损坏和不一致的风险也最小，但是让服务器停机的代价可能会很高，所以必须要使用不停机备份

mysql的备份方式

• 逻辑备份也叫导出

  • 对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空 间小，更灵活。

  • MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现。

  • 优点

    • 逻辑备份导出的文件可以通过工具直接查看内容
    • 恢复数据也很简单，导入就可以了
    • 和存储引擎没有关系，因为是从mysql服务器中提取出来的数据
    • 有助于避免数据损坏，不会因为磁盘损坏导出一份错误的数据

  • 缺点

    • 必须由服务器完成生成逻辑备份的工作，会增加cpu负载
    • 逻辑备份可能比数据库文件更大
    • 无法保证导出后再还原是一样的数据，例如浮点数
    • 从逻辑备份中还原需要mysql加载和解释语句，并且重建索引，会比较漫长

• 物理备份是直接复制原始文件

  • 备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比 较大，

  • MySQL中可以用 xtrabackup 工具来进行物理备份

  • 优点

    • 只需要复制文件，没有其他额外操作
    • 恢复会更快，因为mysql服务器不需要执行任何sql或者构建索引

  • 缺点

    • innodb的原始文件比逻辑备份大很多
    • 物理备份不一定可以跨平台

逻辑备份

-- 备份一个数据库

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名
称.sql

导出所有库的全部数据

mysqldump -uroot -p --all-databases >/usr/local/all.sql

-- 导出`test_sync`库的全部数据
mysqldump -uroot -p --databases test_sync >/usr/local/all.sql

-- 导出db1、db2两个数据库的所有数据
mysqldump -uroot -p --databases db1 db2 >/usr/local/all.sql

导出库中的a1、a2表

mysqldump -uroot -p --databases db1 --tables a1 a2  >/tmp/db1.sql

条件导出，导出db1表a1中id=1的数据，如果多个表的条件相同可以一次性导出多个表

mysqldump -uroot -p --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql