Mysql数据库的备份与恢复

一、Mysql数据库备份的相关概述

备份的目的：
用于灾难恢复、测试应用、回滚数据修改、查询历史数据、审计等
数据丢失的原因：
（1）程序错误
（2）人为操作失误
（3）运算错误
（4）磁盘故障
（5）灾难和盗窃等

二、数据库备份的类型

– 从物理和逻辑角度划分

• 物理备份： 对数据库操作系统的物理文件（数据文件、日志文件等）进行的备份（针对出现问题时需要快速恢复大型数据库）
  划分： 冷备份、热备份、温备份
  （1）冷备份： 数据库关闭的状态下进行的备份
  （优点：备份速度快，恢复时简单，通过直接打包数据库文件）
  （2）热备份： 数据库处于运行状态时进行的备份
  （备份依赖于数据库的日志文件）
  （3）温备份： 数据库锁定表格的状态下进行的备份
  （不可往表格中写入但是可以读取的状态）

  
  
  
  
  
  
  
  
  
  
  
  
  
  

• 逻辑备份： 对数据库的逻辑组件进行的备份
  （对create database、create table、insert等语句信息进行的备份，适用于可编辑数据值或者表结构较小的数据量）
  
  

– 备份策略的三种角度

（1）完全备份： 又叫完整备份，对整个数据库进行备份，保存的是备份完成时刻的数据库（是差异备份和增量备份的前提）
特点： 备份恢复简单，占用磁盘空间大，备份时间较长
（2）差异备份： 备份上一次完全备份后被修改过的所有文件
特点： 备份数据量大，恢复时需要恢复上次的完全备份 + 最近一次的差异备份
（3）增量备份： 以上一次完全备份或者上一次增量备份的时间为节点，只备份时间节点数据发生变化的部分（通过日志文件备份）
特点： 备份数据量小，占用空间小，备份速度快,恢复时需要一次完整备份 + 所有增量备份依次恢复

– 数据备份的方法

（1）物理冷备份： （使用tar工具直接打包）
（2）专用备份工具： （mysqldump或者mysqlhotcopy）
（3）通过二进制日志进行增量备份
（4）通过第三方工具进行备份

三、mysql数据库的备份操作

（1）物理冷备份与恢复

（使用tar命令直接打包数据库文件夹）

步骤： 1.systemctl stop mysqld （停止数据库服务） 2.创建备份目录：mkdir /backup 3.备份：tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data (把/usr/local/mysql/data目录打包) 注意：$(date +%F） 是一个环境变量，date显示当前时间，这个是自动写成当前时间 4.模拟故障：mkdir /bak mv /usr/local/mysql/data/ /bak （移走数据库目录，我的数据库目录是这个位置，做的时候要看一下自己的数据库主目录位置在哪里，一般安装的时候会指定主目录） 5.启动数据库：systemctl start mysqld (发现数据库无法正常启动) 6.恢复数据库：mkdir /restore tar zxvf /backup/mysql_all-2020-12-08.tar.gz -C /restore/ （解压备份的数据库文件） mv /restore/usr/local/mysql/data/ /usr/local/mysql/ （恢复数据库到原来位置） systemctl start mysql （发现数据库已经可以正常启动） 

做的时候要注意自己的mysql主目录的存放位置

（2）使用mysqldump实现备份恢复

作用： 可以将指定的库、表或者全部的库导出为SQL脚本

格式1：备份指定库中的某个库

基本语法结构： mysqldump 选项 库名 表名 > /备份路径/文件名
示例：

先创建一个库，库名叫cless，再在库里创建一个表叫aaa mysql> create database cless; Query OK, 1 row affected (0.00 sec) mysql> use cless; （切换到库里） Database changed mysql> create table aaa(姓名 char(12),年龄 int(12),性别 char(4)); Query OK, 0 rows affected (0.10 sec) 然后退出数据库，进行备份 mysql> exit Bye [root@mysql ~]# mkdir /beifen (先创建备份目录) [root@mysql ~]# mysqldump -u root -p cless aaa > /beifen/cless-aaa.sql （一般备份文件都是sql结尾） Enter password: （输入数据库密码） *****把cless库下的aaa表进行备份 [root@mysql ~]# ll /beifen/ （查看备份目录，发现已经有了备份文件） 总用量 4 -rw-r--r-- 1 root root 1807 12月 8 18:25 cless-aaa.sql 

格式2：备份一个或者多个完整的库 （包含库中的所有表）

语法结构： mysqldump 选项 –databases 库名1 库名2… > /备份路径/备份文件名
示例：

之前已经创建了备份目录，这里就不进行创建了 [root@mysql ~]# mysqldump -u root -p --databases cless >/beifen/cless.sql Enter password: （输入数据库密码） *****直接把整个cless库都进行备份 [root@mysql ~]# ll /beifen/ 总用量 8 -rw-r--r-- 1 root root 1807 12月 8 18:31 cless-aaa.sql -rw-r--r-- 1 root root 1947 12月 8 18:30 cless.sql 

格式3：备份所有的库

语法结构： mysqldump 选项 –all-databases > /备份路径/备份文件名
示例：

[root@mysql ~]# mysqldump -u root -p --all-databases > /beifen/all.sql Enter password: （输入数据库密码） *****把整个mysql数据库中的库全部备份 [root@mysql ~]# ll /beifen/ 总用量 652 -rw-r--r-- 1 root root  12月 8 18:33 all.sql -rw-r--r-- 1 root root 1807 12月 8 18:31 cless-aaa.sql -rw-r--r-- 1 root root 1947 12月 8 18:30 cless.sql 

（3）恢复数据库

基本语法： mysql 选项 库名 表名 < /备份路径/备份文件名

格式1： 恢复库中的删除的某个表

示例：

先进入数据库删除指定的表 mysql> use cless; （进入cless库） Database changed mysql> drop table aaa; （删除aaa表） Query OK, 0 rows affected (0.00 sec) mysql> show tables; （查看cless库中的所有表，发现已经没有了） Empty set (0.00 sec) mysql> exit Bye [root@mysql ~]# mysql -u root -p cless < /beifen/cless-aaa.sql Enter password: [root@mysql ~]# mysql -u root -p123.com (再次进入数据库) mysql> use cless; （进入cless库中） Database changed mysql> show tables; （查看cless库中所有表，发现aaa表已经恢复） +-----------------+ | Tables_in_cless | +-----------------+ | aaa | +-----------------+ 1 row in set (0.00 sec) 

格式2：恢复删除的库

示例：

[root@mysql ~]# mysql -u root -p -e 'drop database cless;' （-e 可以直接修改数据库，命令是一样的，这里是删除数据库cless） Enter password: [root@mysql ~]# mysql -u root -p -e 'show databases;' （查看数据库，发现cless没有了） Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | mysql | | performance_schema | | test | +-------------------- [root@mysql ~]# mysql -u root -p < /beifen/cless.sql （恢复库cless） Enter password: [root@mysql ~]# mysql -u root -p -e 'show databases;' （再次查看发现cless库已经恢复了） Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | cless | | mysql | | performance_schema | | test | +--------------------+ 

四、mysql数据库的增量备份与恢复

定义： 备份上一次备份后增量或改变的文件或内容

• 特点：
  （1）备份量小
  （2）备份时间短
  （3）恢复麻烦（缺点）
  （4）需要进行上次的完全备份恢复和完全备份后所有的增量备份的恢复
  （5）通过mysql提供的二进制日志间接实现增量备份（实现备份的方法）

  
  
  
  
  
  
  
  
  
  

• Mysql二进制日志对备份的意义（为什么要使用二进制日志进行备份）：
  （1）二进制日志保存了所有的更新或者更新数据库的操作
  （2）Mysql服务一启动，二进制日志就开始记录
  （3）二进制日志达到峰值时或者接收到flush logs 命令后会重新创建新的日志文件，生成二进制文件序列，并且及时把日志存储到安全的位置，形成一个时间段的增量备份
  （4）二进制日志是通过max_binlog_size项来设置文件的最大值，只要日志文件超过该设置的大小，就会自动创建新文件

  
  
  
  
  
  
  
  

• 开启二进制日志的步骤：

[root@mysql ~]# cat <<A>> /etc/my.cnf （往/etc/my.cnf下插入） > log-bin=/usr/local/mysql/mysql-bin > A [root@mysql ~]# tail -3 /etc/my.cnf # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=/usr/local/mysql/mysql-bin [root@mysql ~]# systemctl restart mysqld （重启mysql服务） [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin* （查看日志，日志是以mysql-bin开头的） -rw-rw---- 1 mysql mysql 120 12月 8 21:31 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 34 12月 8 21:31 /usr/local/mysql/mysql-bin.index （二进制日志文件的扩展名是一个6 位数字） 

五、Mysql数据库备份与恢复案例

（1）一般恢复

（将所有备份的二进制日志内容全部恢复,完整备份和增量备份以及恢复）

[root@mysql ~]# mysql -u root -p123.com (登录数据库) mysql> create database client; （创建一个新的库） Query OK, 1 row affected (0.00 sec) mysql> use client; （切换库） Database changed mysql> create table user_info(身份证 char(18) not null,姓名 char(12)not null,性别 char(2),用户id号 char(12) not null,资费 int(10)); （创建表） Query OK, 0 rows affected (0.00 sec) mysql> insert into user_info values('000001','张三','男','001','100'); （依次插入数据） Query OK, 1 row affected (0.01 sec) mysql> insert into user_info values('000002','李四','男','002','80'); Query OK, 1 row affected (0.00 sec) mysql> insert into user_info values('000003','王五','女','003','90'); Query OK, 1 row affected (0.00 sec) mysql> describe user_info; （查看表的结构） +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | 身份证 | char(18) | NO | | NULL | | | 姓名 | char(12) | NO | | NULL | | | 性别 | char(2) | YES | | NULL | | | 用户id号 | char(12) | NO | | NULL | | | 资费 | int(10) | YES | | NULL | | +-------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from user_info; （查看表的信息） +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | +-----------+--------+--------+-------------+--------+ 3 rows in set (0.00 sec) mysql> exit （退出数据库，准备开始完整备份） Bye [root@mysql ~]# mkdir /mysql_beifen （创建备份目录） [root@mysql ~]# mysqldump -u root -p client user_info > /mysql_beifen/client-user_info-$(date +%F).sql （使用mysqldump工具进行完整备份） Enter password: [root@mysql ~]# mysqladmin -u root -p flush-logs （生成二进制日志文件） Enter password: [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.* （查看二进制日志文件，发现多了一个002的文件） -rw-rw---- 1 mysql mysql 1249 12月 9 00:28 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 120 12月 9 00:28 /usr/local/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 68 12月 9 00:28 /usr/local/mysql/mysql-bin.index [root@mysql ~]# mysql -u root -p123.com （再次登录数据库） mysql> use client; （切换到指定库） Database changed mysql> insert into user_info values('000004','流星','男','004','55'); （再次往表里插入数据） Query OK, 1 row affected (0.00 sec) mysql> insert into user_info values('000005','飞翔','女','005','66'); Query OK, 1 row affected (0.00 sec) mysql> exit Bye [root@mysql ~]# mysqladmin -u root -p flush-logs （再次生成二进制日志文件） Enter password: [root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.* （再次查看发现又多了一个003的日志文件） -rw-rw---- 1 mysql mysql 1249 12月 9 00:28 /usr/local/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 679 12月 9 00:34 /usr/local/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 120 12月 9 00:34 /usr/local/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql 102 12月 9 00:34 /usr/local/mysql/mysql-bin.index [root@mysql ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_beifen/ （对第二次备份的002文件复制到创建的mysql备份目录） （因为只要是开启二进制文件功能就会产生001的日志文件，002是写了五行数据后的日志文件，再次生成的003日志文件什么都没有，也就是后面写的两行数据是写在002上了，003就是又开启了一个新的日志文件） [root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （模拟误操作删除表） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看表中信息） Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist （因为已经删除直接报错） 下面先恢复完整备份再恢复增量备份 [root@mysql ~]# mysql -u root -p client </mysql_beifen/client-user_info-2020-12-09.sql （进行完整备份） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看，发现已经有了三行数据记录，说明完整备份成功） Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | +-----------+--------+--------+-------------+--------+ 恢复增量备份 [root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 |mysql -u root -p （使用二进制日志进行恢复，这里使用的是备份目录，也可以直接使用二进制日志的目录进行备份） （mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002 | mysql -u root -p） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （发现五行数据都恢复了） Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | | 000004 | 流星 | 男 | 004 | 55 | | 000005 | 飞翔 | 女 | 005 | 66 | +-----------+--------+--------+-------------+--------+ 

（2）基于位置的恢复

（用二进制日志内容去恢复到指定的位置）

[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （再次删除表） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表，发现报错） Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist **********第一种情况：只想恢复到“流星” 的数据，不恢复“飞翔” 的数据 [root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql （只恢复一次完整备份，直接恢复到王五的数据） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表发现恢复到了王五的数据） Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | +-----------+--------+--------+-------------+--------+ [root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制日志文件） （查看二进制日志文件时发现每进行一个操作都有独特的编号，随着操作的增加，编号数在不断增加，这个编号称为“操作ID”） 。。。。。。 （找到插入‘流星’时的数据，发现操作id是203） # at 203 # 0:32:59 server id 1 end_log_pos 345 CRC32 0xbc Query thread_id=5 exec_time=0 error_code=0 use `client`/*!*/; SET TIMESTAMP=/*!*/; insert into user_info values('000004','流星','男','004','55') /*!*/; 。。。。。。 [root@mysql ~]# mysqlbinlog --no-defaults --stop-position='459' /mysql_beifen/mysql-bin.000002 |mysql -u root -p （只恢复编号id是459之前的，不包括459，--stop-position='459' ： 表示恢复到这个ID后结束恢复） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | | 000004 | 流星 | 男 | 004 | 55 | +-----------+--------+--------+-------------+--------+ **********第二种情况： 只想恢复“飞翔”；不恢复“流星” [root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （再次删除表) Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查看表发现报错） Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist [root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql （先恢复完整备份） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表，发现恢复了三行数据） Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | +-----------+--------+--------+-------------+--------+ [root@mysql ~]# mysqlbinlog --no-defaults --start-position='459' /mysql_l_beifen/mysql-bin.000002 |mysql -u root （恢复从操作id459开始的数据） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看发现恢复成功） Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | | 000005 | 飞翔 | 女 | 005 | 66 | +-----------+--------+--------+-------------+--------+ 

--stop-position： 表示恢复时间节点之前的数据也就是恢复到指定id结束
--start-position：表示恢复时间节点之后的数据也就是从指定id开始恢复

（3）基于时间点的恢复

--stop-datetime： 表示恢复时间节点之前的数据也就是恢复到指定时间节点结束
--start-datetime：表示恢复时间节点之后的数据也就是从指定时间节点开始恢复
查看日志获取时间节点

基于时间点的恢复，只恢复“飞翔”不恢复“流星” [root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （删除表) Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查看表发现报错） Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist [root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql （恢复完整备份） Enter password: [root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制文件获取时间节点） 。。。。。 # at 459 # 0:33:14 server id 1 end_log_pos 601 CRC32 0xe7d6d2ad Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=/*!*/; insert into user_info values('000005','飞翔','女','005','66') /*!*/; 。。。。。 [root@mysql ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-09 0:33:14' /mysql_beifen/mysql-bin.000002 | mysql -u root -p （恢复从2020年12月9日零点三十三分十四秒开始的数据） Enter password: [root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' Enter password: +-----------+--------+--------+-------------+--------+ | 身份证 | 姓名 | 性别 | 用户id号 | 资费 | +-----------+--------+--------+-------------+--------+ | 000001 | 张三 | 男 | 001 | 100 | | 000002 | 李四 | 男 | 002 | 80 | | 000003 | 王五 | 女 | 003 | 90 | | 000005 | 飞翔 | 女 | 005 | 66 | +-----------+--------+--------+-------------+--------+ 

at 后面跟的就是操作id
第二行的201209 0:33:14 就是2020年的12月九日 零点的三十山分十四秒