MySQL备份与还原手册

备份对于数据库而言是至关重要的。当数据文件发生损坏、MySQL服务出现错误、系统内核崩溃、计算机硬件损坏或者数据被误删等事件时，使用一种有效的数据备份方案，就可以快速解决以上所有的问题。MySQL提供了多种备份方案，包括：逻辑备份、物理备份、全备份以及增量备份，你可以选择最适合自己使用的方式备份数据。

下面章节主要描述了关于mysql逻辑备份的几种方案：

• 全库备份与恢复
• 单表备份与恢复
• 多表备份与恢复
• 增量备份与恢复

Mysql 备份概述

备份和冗余有什么区别？

• 备份：能够防止由于机械故障以及人为操作带来的数据丢失，例如将数据库文件保存在了其它地方。
• 冗余：数据有多份冗余，但不等于备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。
   

备份什么？

• 备份数据库： database
• 日志文件：binlog
• 配置文件：my.cnf
• 数据目录：/data/xxx

备份过程中必须考虑的因素：

1、必须制定详细的备份计划（备份频率、时间点、周期）

根据当前的业务情况，需要考虑备份的时间和备份数据的大小。数据量太大的话就使用冗灾。

2、备份数据应该放在非数据库本地，并建议有多份副本

3、必须做好数据恢复的演练

每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据，保证数据的可用性。备份完成后进行断电模拟演练。测试数据库是否可以正常启动，数据能否正常进行恢复。

4、根据数据应用的场合、特点选择正确的备份工具。

5、数据的一致性。

6、数据的可用性。

备份类型

• 逻辑备份

逻辑备份就是在不停业务的情况下进行备份。

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

常用的工具如：mysqldump、mydumper、 into outfile（表的导出导入）等

• 物理备份

物理备份就是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

常用的工具如：tar、cp、xtrabackup、lvm snapshot等

• 在线热备

在线热备：AB复制（在线实时），指的是对数据的冗余

备份工具介绍

使用tar包

备份

tar Jcvf  /work/data/backup/mysql-$(date +%F).tar.xz  /work/data/mysql_data

恢复

tar Jxvf /work/data/backup/mysql-2021-04-15.tar.xz

使用mysqldump

msyqldump是属于逻辑备份，备份sql语句，简单，但是由于恢复时都是通过insert进行插入，所有恢复速度慢，mysqldump备份myisam表时因为要加--lock-all-tables，这时要备份的数据库全部被上锁，可读不可写，所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction，会自动将隔离级别设置为repeatable read并开启一个事务，这时mysqldump将获取dump执行前一刻的行版本，并处于一个长事务中直到dump结束。所以不影响目标数据库的使用，可读也可写，即实现的是热备.

库备份与恢复

全库备份

bin/mysqldump  -p   --lock-tables --all-databases > /work/data/backup/alldb.sql

/work/data/backup/alldb.sql为备份的目录，根据项目情况进行指定。

全库恢复

 	mysql> source  /work/data/backup/alldb.sql
 	bin/mysql -p </work/data/backup/alldb.sql

 单库备份

bin/mysqldump  -p --databases  school > /work/data/backup/school.sql

 单库恢复

 	mysql> source /work/data/backup/school.sql
 	bin/mysql -p < /work/data/backup/school.sql

 多库备份

bin/mysqldump  -p --databases  school  student > /work/data/backup/school_student.sql

 多库恢复

 	mysql> source /work/data/backup/school_student.sql
 	bin/mysql -p < /work/data/backup/school_student.sql  

表备份与恢复

单表备份

bin/mysqldump  -p  school class >  /work/data/backup/school-class.sql

单表恢复

 	bin/mysql -p school  < /work/data/backup/school-class.sql
 	mysql> source  /work/data/backup/school-class.sql

 多表备份

bin/mysqldump  -p  school class info >  /work/data/backup/school-class_info.sql

 多表恢复

 	source /work/data/backup/school-class_info.sql
 	bin/mysql -p school  < /work/data/backup/school-class_info.sql

 增量备份及恢复

增量备份的优点是没有重复数据，备份量不大，时间短。但缺点也很明显，需要建立在上次完全备份及完全备份之后所有的增量才能恢复。

 MySQL没有提供直接的增量备份方法，但是可以通过mysql二进制日志间接实现增量备份。二进制日志对备份的意义如下：

（1）二进制日志保存了所有更新或者可能更新数据库的操作。

（2）二进制日志在启动MySQL服务器后开始记录，并在文件达到所设大小或者收到flush logs 命令后重新创建新的日志文件。

（3）只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些文件保存到一个安全的地方即完成了一个时间段的增量备份。

Binlog: 二进制日志 , 默认会记录下所有对数据库变化的操作

二进制日志文件中会记录某个操作的详细SQL语句，还有执行的时候环境，时间，以及该记录在二进制日志文件的起始和结束点pos值

配置二进制日志

首先查看二进制日志是否开启：show variables like '%log_bin%';

ON 代表开启；OFF 代表没有开启；如果未开启，那么就修改配置文件my.cnf

在配置文件对应位置添加红框内容即可。

配置文件修改完毕后，重启mysql数据库，再次确认二进制日志是否开启。

查看二进制日志

show binary logs

二进制日志切换方法

使用命令flush logs切换二进制日志

说明：

每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个mysql-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

查看当期二进制日志的状态：

二进制文件内容

二进制日志用记事本、编辑器、vi或vim等等打开都是乱码的，必须用mysqlbinlog才能正确打开。

红色圈出的内容对后期恢复数据有重要的依据

更直观的展示方式

执行命令： SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

show binlog events in 'mysql-bin.000004';

利用二进制文件恢复数据

mysql二进制日志文件恢复数据有两种方式：使用事件时间的时间点恢复和使用事件位置点恢复

使用事件时间点恢复

要指明恢复的开始和结束时间，两个选项参数是： --start-datetime和 --stop-datetime；

执行插入的sql：

mysqlbinlog --start-datetime="2021-04-16 7:51:09" --stop-datetime="2021-04-16 7:51:10" /work/data/mysql_data/mysql-bin.000004 | mysql -uroot -p

执行删除的sql：

mysqlbinlog --start-datetime="2021-04-17 14:43:19" --stop-datetime="2021-04-17 14:43:20" /work/data/mysql_data/mysql-bin.000004 | mysql -uroot -p

 使用事件位置点恢复

要指明恢复的开始和结束位置，两个选项参数是：--start-position 和--stop-position

执行插入的sql：

mysqlbinlog --stop-position=469 /work/data/mysql_data/mysql-bin.000004 | mysql -u root -p

执行删除的sql：

mysqlbinlog --start-position=622 --stop-position=773 /work/data/mysql_data/mysql-bin.000004 | mysql -uroot -p