目录
一、数据库的备份分类二、数据库的备份方法三、完全备份、增量备份概述与对比四、完全备份的实例4.1 冷备份与数据恢复4.2 mysqldump备份与恢复五、增量备份的实例（基于完全备份）5.1 一般恢复5.2 时间点恢复5.3 位置点恢复一、数据库的备份分类
从物理与逻辑的角度，备份可分为
1、物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份方法
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
从数据库的备份策略角度，备份可分为
1、完全备份：每次对数据库进行完整的备份
2、差异备份：备份自从上次完全备份之后被修改过的文件
3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
二、数据库的备份方法
物理冷备
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
专业备份工具mysqldump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
三、完全备份、增量备份概述与对比
完全备份：
是对整个数据库、数据库结构和文件结构的备份；
保存的是备份完成时刻的数据库；
是差异备份与增量备份的基础；
每次对数据进行完整的备份。
优点： 备份与恢复操作简单方便
缺点：
数据存在大量的重复；
占用大量的备份空间；
备份与恢复时间长。
增量备份：
MySQL增量备份是自上一次备份后增加/变化的文件或者内容
特点
没有重复数据，备份量不大，时间短；
依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低。
MySQL二进制日志对增量备份有重要的作用
二进制日志保存了所有更新或者可能更新数据库的操作；
二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；
只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
四、完全备份的实例
4.1 冷备份与数据恢复
需要先停止数据库服务，再直接打包压缩数据库文件
数据库所有文件目录：/usr/local/mysql/data
数据库已有的库mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || student || sys|+--------------------+5 rows in set (0.00 sec)#进行备份[root@server1 ~]# systemctl stop mysqld[root@server1 ~]# mkdir /opt/backup[root@server1 ~]# tar zcf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/ #对备份的文件加入备份时间点的命名，获取当时的日期#查看备份文件[root@server1 ~]# cd /opt/backup/[root@server1 backup]# ll总用量 1356-rw-r--r--. 1 root root 1386275 11月 2 10:54 mysql_all--11-02.tar.gz
故障时，停止服务，将故障的数据库文件移走到备份文件夹中，解压刚才备份数据库包到/restore目录下，再移动到/usr/local/mysql/data下，再重启服务
mysql> drop database student; #误删除库Query OK, 3 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|+--------------------+4 rows in set (0.00 sec)#恢复[root@server1 ~]# systemctl stop mysqld[root@server1 ~]# mkdir /badbak #建立损坏数据库的备份[root@server1 ~]# mv /usr/local/mysql/data/ /badbak [root@server1 ~]# mkdir /restore [root@server1 ~]# tar zxf /opt/backup/mysql_all--11-02.tar.gz -C /restore/[root@server1 ~]# mv /restore//usr/local/mysql/data/ /usr/local/mysql/data[root@server1 ~]# systemctl start mysqld#查看恢复结果[root@server1 ~]# mysql -uroot -pxzf729mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || student || sys|+--------------------+5 rows in set (0.00 sec)恢复成功
4.2 mysqldump备份与恢复
备份方法：
mysqldump -u root -p --all-databses > all-data-$(date +%F).sql###备份所有数据库到当前目录下的all-data-$(date +%F).sql 文件mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库mysqldump -u root -p auth > auth-$(date +%F).sql ###备份auth数据库mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构
实例1：
备份单独一个student库
[root@server1 ~]# mysqldump -u root -p student > student-$(date +%F).sqlEnter password:
恢复：
误删除单独一个库，恢复时，需要再建一下这个库
mysql> drop database student;Query OK, 3 rows affected (0.02 sec)mysql> create database test;#命名可根据需要Query OK, 1 row affected (0.00 sec)mysql> exitBye方法一：在数据库外导入[root@server1 ~]# mysql -u root -p test < student--11-02.sql #将刚刚备份的导入testEnter password: 方法二：在数据库内用source#这里必须要use 数据库，否则无法有对应的数据库可以导入mysql> use test;mysql> source /student--11-02.sql;查看恢复结果mysql> show tables;+-------------------+| Tables_in_student |+-------------------+| info || test || zf|+-------------------+3 rows in set (0.00 sec)mysql> select * from info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)
实例2：
备份多个数据库
[root@server1 ~]# mysqldump -uroot -pxzf729 --databases mysql student > mysql-student-bak.sql#备份数据库mysql和student到当前目录下的mysql-student.sql，可查看到，也可加绝对路径保存[root@server1 ~]# ll总用量 48784-rw-------. 1 root root1878 8月 11 04:02 anaconda-ks.cfg-rw-r--r--. 1 root root1926 8月 11 04:49 initial-setup-ks.cfgdrwxr-xr-x. 38 7161 314154096 10月 22 11:11 mysql-5.7.20-rw-r--r--. 1 root root 48833145 10月 22 10:31 mysql-boost-5.7.20.tar.gz ####-rw-r--r--. 1 root root 1101429 11月 2 12:14 mysql-student-bak.sql......
误删除两个库
mysql> drop database mysql;Query OK, 32 rows affected, 2 warnings (0.04 sec)mysql> drop database student;Query OK, 3 rows affected, 2 warnings (0.00 sec)
恢复：无需再创建数据库，可直接恢复
mysql> source /root/mysql-student-bak.sql查看恢复结果mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || student || sys|+--------------------+5 rows in set (0.00 sec)mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)
五、增量备份的实例（基于完全备份）
增量备份分类：
一般恢复
将所有备份的二进制日志内容全部恢复断点恢复
基于位置恢复：
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
基于时间点恢复：
跳过某个发生错误的时间点实现数据恢复
MySQL二进制日志对增量备份有重要的作用
二进制日志保存了所有更新或者可能更新数据库的操作；二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
修改配置文件，开启二进制日志功能
vi /etc/f.....[root@server1 ~]# [mysqld]末尾加log-bin=mysql-bin 前面是功能名称，后面是二进制日志文件名称#重启数据库[root@server1 ~]# systemctl restart mysqld[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll总用量 122924-rw-r-----. 1 mysql mysql 56 10月 22 11:29 f-rw-r-----. 1 mysql mysql917 11月 2 15:45 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibdata1-rw-r-----. 1 mysql mysql 50331648 11月 2 15:45 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibtmp1drwxr-x---. 2 mysql mysql4096 11月 2 12:51 mysql-rw-r-----. 1 mysql mysql154 11月 2 15:45 mysql-bin.000001 #生成二进制日志文件-rw-r-----. 1 mysql mysql 19 11月 2 15:45 mysql-bin.index
以下所有的数据库操作（新增与误删除），都被记录在000001文件里
#原有的记录，已做了完全备份ysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)#先做一些数据库操作mysql> insert into student.info values(4,'yangli','南京');Query OK, 1 row affected (0.02 sec)#误删除了一条记录mysql> delete from student.info where name='lisi';Query OK, 1 row affected (0.01 sec)#又插入了一条数据mysql> insert into student.info values(5,'lili','南京');Query OK, 1 row affected (0.01 sec)mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)
恢复：
首先需要刷新二进制文件，000002里会存储后续的数据库操作，而不会存在000001里面了。
[root@server1 ~]# mysqladmin -uroot -p flush-logsEnter password: [root@server1 ~]# ll /usr/local/mysql/data/总用量 122928-rw-r-----. 1 mysql mysql 56 10月 22 11:29 f-rw-r-----. 1 mysql mysql917 11月 2 15:45 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 11月 2 16:49 ibdata1-rw-r-----. 1 mysql mysql 50331648 11月 2 16:49 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibtmp1drwxr-x---. 2 mysql mysql4096 11月 2 12:51 mysql-rw-r-----. 1 mysql mysql1019 11月 2 16:57 mysql-bin.000001##刷新之前的操作存储在这里-rw-r-----. 1 mysql mysql154 11月 2 16:57 mysql-bin.000002##新增的二进制文件-rw-r-----. 1 mysql mysql 38 11月 2 16:57 mysql-bin.index
查看二进制文件：
[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001使用mysqlbinlog工具查看日志文件
每一个数据库操作，在二进制文件中都是一个事务，以begin开头，commit结尾，以之前插入yangli的数据操作为例，可查看到：
5.1 一般恢复
直接把整个二进制文件的内容进行恢复。
当前的状态：
mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)
误删除表之后，先用完全备份恢复，再用二进制文件恢复表至上述状态
mysql> drop database student;Query OK, 0 rows affected (0.01 sec)#完全备份mysql> source /root/mysql-student-bak.sql;mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+#一般备份[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pxzf729#查看恢复结果mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)
5.2 时间点恢复
从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
想要恢复删除的lisi
查看二进制日志文件：000001
[root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
记录两个时间点：
误操作之前的时间点：02 16：48：37
正确操作开始的时间点：101102 16：49：18
跳过了误操作。
#删除坏表，完全备份恢复表#首先从日志开头截止到某个时间点的恢复，即删除lisi操作之前的时间点[root@server1 data]# mysqlbinlog --no-defaults --stop-datetime='-11-02 16:48:37' mysql-bin.000001 |mysql -uroot -pxzf729#从某个时间点到日志结尾的恢复，即正确操作之后的日志恢复[root@server1 data]# mysqlbinlog --no-defaults --start-datetime='-11-02 16:49:18' mysql-bin.000001 |mysql -uroot -pxzf729#查看恢复结果mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+5 rows in set (0.00 sec)
5.3 位置点恢复
想要恢复删除的lisi
查看二进制文件：
记录两个位置点：
误操作之前的位置点 493
正确操作开始的位置点 700
#删除坏表，完全备份恢复表#首先从日志开头截止到某个位置点的恢复，即删除lisi操作之前的位置点#停止错误操作[root@server1 data]# mysqlbinlog --no-defaults --stop-position='493' mysql-bin.000001 |mysql -uroot -pxzf729#从某个位置点到日志结尾的恢复，即正确操作之后的日志恢复#开始正确操作[root@server1 data]# mysqlbinlog --no-defaults --start-position='700' mysql-bin.000001 |mysql -uroot -pxzf729#查看恢复结果mysql> select * from student.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+5 rows in set (0.00 sec)
