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 658319 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
#201209 0:32:59 server id 1 end_log_pos 345 CRC32 0x322611bc Query thread_id=5 exec_time=0 error_code=0
use `client`/*!*/;
SET TIMESTAMP=1607445179/*!*/;
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
#201209 0:33:14 server id 1 end_log_pos 601 CRC32 0xe7d6d2ad Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1607445194/*!*/;
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月九日 零点的三十山分十四秒