MySQL数据备份与恢复

目录

一、数据备份的重要性

二、数据库备份类型

2.1 物理备份

2.2 逻辑备份

三、常见的备份方法

3.1 物理冷备

3.2 专用备份工具mydump或mysqlhotcopy

3.3 启用二进制日志进行增量备份

3.4 第三方工具备份

四、MySQL完全备份

五、数据库完全备份分类

5.1 物理冷备份与恢复

5.2 mysqldump备份与恢复

六、实战案例

6.1 物理备份

6.2 逻辑备份

1、完全备份与恢复

2、增量备份与恢复

①开启日志功能

②查看二进制日志内容

③增量恢复方法总结

④增量备份案例

七、简单小结

---

一、数据备份的重要性

1、备份的主要目的是灾难恢复

2、在生产环境中，数据的安全性至关重要

3、任何数据的丢失都可能产生严重的后果

4、造成数据丢失的原因

• 程序错误
• 人为操作失误
• 运算错误
• 磁盘故障
• 灾难(如火灾、地震)和盗窃

二、数据库备份类型

备份类型分为两种：物理备份和逻辑备份

2.1 物理备份

物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份

物理备份方法

• 冷备份(脱机备份) ：是在关闭数据库的时候进行的，依赖于数据文件
• 热备份(联机备份) ：数据库处于运行状态，依赖于数据库的日志文件
• 温备份：数据库锁定表格(不可写入但可读)的状态下进行备份操作

2.2 逻辑备份

逻辑备份：对数据库逻辑组件(如: 表等数据库对象)的备份

逻辑备份策略

• 完全备份：每次对数据进行完整的备份
• 差异备份：备份那些自从上次完全备份之后被修改过的文件
• 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

三、常见的备份方法

3.1 物理冷备

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的

3.2 专用备份工具mydump或mysqlhotcopy

• mysqldump常用的逻辑备份工具
• mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3.3 启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

• MySQL支持增量备份，进行增量备份时必须启用二进制日志。
• 二进制日志文件为用户提供复制，对执行 备份点后进行的数据库更改所需的信息进行恢复。
• 如果进行增量备份（包含自上次完全备份或增量备份 以来发生的数据修改），需要刷新二进制日志

3.4 第三方工具备份

• 免费的MySQL热备份软件Percona XtraBackup

四、MySQL完全备份

1. 是对整个数据库、数据库结构和文件结构的备份
2. 保存的是备份完成时刻的数据库
3. 是差异备份与增量备份的基础
4. MySQL完全备份优缺点

• 优点： 备份与恢复操作简单方便
• 缺点： 数据存在大量的重复
•             占用大量的备份空间
•             备份与恢复时间长

五、数据库完全备份分类

5.1 物理冷备份与恢复

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

5.2 mysqldump备份与恢复

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL 脚本
• 使用命令mysq|导入备份的数据

六、实战案例

6.1 物理备份

物理冷备份与恢复

①备份数据库

[root@localhost ~]# systemctl stop mysqld                 ###关闭数据库####
[root@localhost ~]# mkdir /backup                         ###在根下新建个backup目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/           
                                                          #####/usr/local/mysql/data备份压缩
[root@localhost ~]# cd /backup/                           ###进入/backup/         
[root@localhost backup]# ll                               ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月  12 22:39 mysql_all-2018-08-12.tar.gz

②模拟故障

[root@localhost backup]# mkdir /bak                       #在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/  #将目录data数据移动到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/             #仅mysql目录查看data
[root@localhost mysql]# ll                                #查看后data目录被移走 ，模拟故障成功

③数据库恢复

[root@localhost mysql]# mkdir /restore
####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2020-09-12.tar.gz -C /restore/
####将备份数据库解压到 /restore目录下
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
.....省略.....

④启动数据库

[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld
####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld    

物理温备份与恢复（mysqldump备份与恢复）

mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复
mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump [选项] --all-databases > /备份路径/备份文件名

①环境准备

mysql> create databases auth;                   ####创建auth 这个数据库
mysql> use auth;                                ####进入auth 这个数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
mysql> insert into users values('lisi', password('123456'));

②备份数据表

[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql
###备份数据路mysql中的user表  ，文件保存在当前操作目录下面
Enter password:                                                                                                  ###输入密码abc123            

③备份数据库

[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql
####备份auth数据库————文件保存在当前操作目录下面
Enter password:                                                                                             ####输入密码abc123 

④恢复数据表

[root@localhost /]# mysql -u root -p               ###进入数据库###
Enter password:                                    ###输入密码####
mysql> show databases;                             ###查看数据库####
mysql> create database text ;                      ####新建数据库text ,等下导表用###
mysql> exit                                        ####退出#####
[root@localhost opt]# mysql -u root -p text < mysql-user.sql 
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show tables from text'    #######验证输出结果####
Enter password:  

⑤恢复数据库

[root@localhost /]# mysql -u root -p -e 'drop database auth'
##模拟故障删除auth数据库
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show databases'
##查看databases所有数据库有没有auth，发现没有了
Enter password: 
[root@localhost /]# mysql -u root -p < ./auth.sql
####导入数据库auth
Enter password:                                                                                  ####输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'
#####查看databases所有数据库有没有auth，发现有了
Enter password: 

6.2 逻辑备份

1、完全备份与恢复

mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入:

• source命令
• mysql命令

1、source命令恢复数据库

模拟事故

[root@server1 backup]# mysql -uroot -pabc123 登录数据库
mysql> show databases;                       查看数据库信息
mysql> drop database school;                 删除数据库school
mysql> show databases;

创建备份

[root@server1 ~]# mysqldump -uroot -pabc123 school info > /opt/info.sql
[root@server1 ~]# mysql -uroot -pabc123 登录数据库查看
[root@server1 ~]# mysql -uroot -p123123 -e 'drop table school.info;'
#删除数据库的表

恢复数据

mysql> select * from info; 查询所有字段
mysql> show tables; 查看表信息
或免交互l> source /opt/info.sql
mysql -uroot -p123123 -e 'show tables from school;'

2、mysql命令恢复数据库

格式：mysql -u 用户名 -p [密码] < 库备份脚本的路径

举例：mysql -u root -pabc123 < /backup/school.sql

[root@mysql abc]# mysql -uroot -p123123 school < /abc/school.info.sql
#恢复info表
[root@mysql abc]# mysql -uroot -p123123 -e 'show tables from school;'
#查看info表
PS:mysqldump 严格来说属于温备份，会需要对表进行写入的锁定
在全量备份与恢复实验中，假设现有ky29（scholl）库，ky29（scholl）库中有一个test表，需要注意的
一点为：
① 当备份时加 --databases ,表示针对于ky29（scholl）库
#备份命令
mysqldump -uroot -p123123 --databases school > /opt/school_01.sql 备份库后
#恢复命令过程为：
mysql -uroot -p123123
drop database school ;
exit
mysql -uroot -p123123 < /opt/school_01.sql
② 当备份时不加 --databases，表示针对school库下的所有表
#备份命令
mysqldump -uroot -p123123 school > /opt/school_all.sql
#恢复过程：
mysql -uroot -p123123
drop database school;
create database school;
exit
mysql -uroot -p123123 school < /opt/school_02.sql
#查看school_01.sql 和school_02.sql

主要原因在于两种方式的备份（前者会从"create databases"开始,而后者则全是针对表格进行操作）

扩展：在生产环境中，可以使用Shell脚本自动实现定时备份（时间频率需要确认）

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -pabc123 kgc info1 >./kgc_infol_$(date +%Y%m%d).sql ;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

2、增量备份与恢复

①开启日志功能

注：日志对备份的作用

• 二进制日志保存了所有更新或者可能更新数据的操作
• 二进制日志在启动mysql服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接受到flush logs命令后重新创建新的日志文件，生成二进制的文件序列，并及时把这些日志文件保存到安全的存储位置，即可完成一个时间段的增量备份

[root@localhost mysql]# vim /etc/my.cnf                                               #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                                                                                                       #####找到这个模块
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin                     ####这个地方开启二进制日志功能

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@localhost mysql]# systemctl restart mysqld   

二进制日志文件的默认路径为/usr/local/mysql/data

mysqladmin -u 用户名 -p [密码] flush-logs
只需定时执行 flush-logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

②查看二进制日志内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

mysqlbinlog --no-defaults /opt/mysql-bin.000002
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 >
/opt/mysql-bin.000002

注：解释日志关注点

• at ：开始的位置点
• end_log_pos：结束的位置
• 时间戳： 210712 11:50:30
• SQL语句

③增量恢复方法总结

 一般恢复：

mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

断点恢复：

基于位置的恢复

• 恢复数据到指定位置

mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

• 从指定的位置开始恢复数据

mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

基于时间点的恢复

• 从日志开头截止到某个时间点的恢复

mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

• 从某个时间点到日志结尾的恢复

mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’

• 从某个时间点到某个时间点的恢复

mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

④增量备份案例

一般恢复

(1)模拟丢失更改的数据的恢复步骤（直接使用恢复即可）
① 备份ky11库中test1表
mysqldump -uroot -p123123 ky29 test1 > /opt/ky29_test29.sql
② 删除ky13库中test1表
drop table ky13.test1;
③ 恢复test1表
mysql -uroot -p ky13 < info-2023-04-29.sql
#查看日志文件
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v
mysql-bin.000002

(2)模拟丢失所有数据的恢复步骤
① 模拟丢失所有数据
[root@mysql data]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ky13 |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database ky29;
Query OK, 1 row affected (0.00 sec)
mysql> exit
② 基于mysql-bin.000002恢复
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例:
at 302
#201122 16:41:16
插入了"user3"的用户数据
at 623
#201122 16:41:24
插入了"user4"的用户数据

(1)基于位置恢复
① 插入三条数据
mysql> use ky29;
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into test1 values(3,'true');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(4,'f');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(5,'t');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
| 3 | true |
| 4 | f |
| 5 | t |
+------+------+
5 rows in set (0.00 sec)
#需求：以上id =4的数据操作失误，需要跳过
② 确认位置点，刷新二进制日志并删除test1表
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003
960 停止
1066 开始
#刷新日志
mysqladmin -uroot -p123123 flush-logs
mysql> use ky13;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_ky29 |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table ky29.test1;
Query OK, 0 rows affected (0.00 sec)
③ 基于位置点恢复
#仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -
uroot -p
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -
uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623'
/opt/mysql-bin.000002 | mysql -uroot -p #恢复从位置为400开始到位置为623为止

(2)、基于时间点恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stopdatetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
#仅恢复到16:41:24 之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime='2023-04-22 16:41:24' /opt/mysqlbin.000002 | mysql -uroot -p
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2023-04-22 16:41:24' /opt/mysqlbin.000002 | mysql -uroot -p
如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

进行一次完全备份

mysql> flush tables with read lock;           ##备份前需要将数据库加读锁，防止数据在备份时写入
mysql> exit

[root@localhost ~]# mkdir /mysql_bak  
[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql   ##这边括号中%F 的F必须是大写的
Enter password: 
[root@localhost ~]# ll /mysql_bak/
 mysqldump -u root -p client user_info > /mysql_bak/client.user_info-$(date +%F).sql
 Enter password: 
[root@localhost mysql_bak]# ll /mysql_bak/
total 4
-rw-r--r-- 1 root root 2055 Sep 13 04:08 client.user_info-2020-09-13.sql

[root@localhost mysql_bak]# ll /usr/local/mysql/data/
-rw-r----- 1 mysql mysql     1539 Sep 13 04:11 mysql-bin.000001
-rw-r----- 1 mysql mysql      154 Sep 13 04:11 mysql-bin.000002
-rw-r----- 1 mysql mysql       78 Sep 13 04:11 mysql-bin.index

增量备份

[root@localhost mysql_bak]# mysql -u root -p
Enter password: 
mysql> unlock tables;             #######解除表锁
Query OK, 0 rows affected (0.00 sec)
mysql> use client;
mysql> insert into user_info values('000009','赵六','男','019','37');
mysql> insert into user_info values('000010','孙七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

mysql> exit
Bye

[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost ~]# ll /usr/local/mysql/data
-rw-r----- 1 mysql mysql     1539 Sep 13 04:11 mysql-bin.000001
-rw-r----- 1 mysql mysql      797 Sep 13 04:15 mysql-bin.000002
-rw-r----- 1 mysql mysql      154 Sep 13 04:15 mysql-bin.000003
-rw-r----- 1 mysql mysql      117 Sep 13 04:15 mysql-bin.index

[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

完全备份的恢复

[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql 
Enter password: 
[root@localhost mysql_bak]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+

增量备份的恢复

[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p                
Enter password: 
[root@localhost ~]#  mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
| 000010    | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

七、简单小结

mysql备份与恢复:备份的类型:
1、物理备份、逻辑备份
物理备份:冷备、温备、热备
1）冷备: tar，需要关闭mysql服务，或者确保mysql服务在进行tar备份时，没有客户端的写入操作
2）温备: mysqldump，这个是mysql自带的备份工具，特性，逐表备份，每备份一张表时，会先drop
刑除，然后重新create 创建表结构，然后再锁表（仅锁定写的操作，但可读），进行insert语句的备
份，备份完成后，进行unlock解锁,然后继续备份下一个
3）热备: xtrabackup mysqlhotcopy等热备工具（第三方)，在mysql正常运行时，进行备份、

2、逻辑备份:
1）全量备份: tar、 mysqldump -u -p --all-databases
2）增量备份:主要使用bin-log 二进制日志来完成备份，同时每个增量备份的文件，可以通
过:mysqladmin -u -p flush-logs来刷新生成新的增备的日志文件，同时可以结合crontab完成自动刷新
PS:再进行基于二进制文件的增备恢复时，有必要的话，需要先恢复完备的数据，再逐个恢复增备的数
据，直到恢复至我们需要恢复的数据为止
3）差异备份:需要选择保存日志的格式为ROW（基于行）的备份方式日志保存的类型:

1)混合模式Mixed(建议使用)
2)基于行ROWS
3)基于sql (默认)

mysql 恢复的方式:
1) 基于冷备(tar）形式: tar zxvf解压打包的/usr/local/mysql/data数据下的内容
2）基于自带的温备工具:使用mysgldump进行备份后，可使用两种方式进行恢复
 ① : mysql -u -p 库名< /opt/mysql_all.sql(备份文件的路径)
 ② mysql -u -p -e 'source /opt/mysql_all.sql'直接使用source进行恢复
3）基于日志: bin-log，首先开启二进制日志的配置log-bin=mysql-bin log_format=MiXED,
然后在/usr/local/mysql/data MysQL工作目录下，会生成mysql-bin.00000x的二进制日志文件
接着使用mysqlbinlog --no-defaults 二进制日志路径│ mysql -u -p 基于完整的二进制日志文件进行恢复
基于位置点和时间点进行恢复:使用mysqlbinlog --no-defaults --base64-output=decode-rows -v二进
制日志文件路径，查看正确操作和需要跳过的错误操作的at (position位置点）和datetime（时间点）来
进行恢复