文章目录

一、数据库备份的分类1.数据备份的重要性2.数据库备份的分类-13.数据库备份的分类-24.常见的备份方法二、MySQL完全备份与恢复1.MySQL完全备份-12.MySQL完全备份-23.数据库完全备份分类4.MySQL物理冷备份及恢复5.mysqldump备份数据库5.1 单库进行完全备份及恢复5.2 多库进行完全备份5.3 对所有库进行完全备份三、MySQL增量备份与恢复1. MySQL增量备份-12.Mysql增量备份-23.MySQL数据库增量恢复-14.MySQL数据库增量恢复-25.MySQL数据库增量恢复-36.增量备份案例1.基于时间点恢复配置流程2.基于位置点恢复2.1步骤2.2 配置流程

一、数据库备份的分类

1.数据备份的重要性

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

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

造成数据丢失的原因：

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

2.数据库备份的分类-1

从物理与逻辑的角度，备份可分为

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份 物理备份方法 冷备份（脱机备份）：是在关闭数据库的时候进行的热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

3.数据库备份的分类-2

从数据库的备份策略角度，备份可分为

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

4.常见的备份方法

物理冷备

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

专用备份工具mydump或mysqlhotcopy

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

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

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

二、MySQL完全备份与恢复

1.MySQL完全备份-1

是对整个数据库、数据库结构和文件结构的备份保存的是备份完成时刻的数据库是差异备份与增量备份的基础

2.MySQL完全备份-2

优点：

备份与恢复操作简单方便

缺点：

数据存在大量的重复占用大量的备份空间备份与恢复时间长

3.数据库完全备份分类

物理冷备份与恢复

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

mysqldump备份与恢复

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

4.MySQL物理冷备份及恢复

数据库中的所有数据文件都在这个目录中，直接整个目录打包(需关闭数据库)

[root@server1 data]# systemctl stop mysqld //先关闭数据库Redirecting to /bin/systemctl stop mysqld.service[root@server1 data]# mkdir /opt/backup //创建备份目录，[root@server1 data]# tar zcvf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data///把整个数据目录打包[root@server1 data]# cd /opt/backup[root@server1 backup]# ll总用量 1372-rw-r--r--. 1 root root 1401279 12月 27 08:18 mysql_all_-12-27.tar.gz

当数据库故障（数据丢失）时，直接把压缩包解压，数据挪回data目录下

5.mysqldump备份数据库

5.1 单库进行完全备份及恢复

格式：

musqldump -u 用户名 -p【密码】【选项】【数据库名】 > 备份路径/备份文件名

单库备份示例：

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || kk || mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)[root@server1 ~]# mysqldump -u root -p kk > /opt/kk.sql#导出备份文件kk.sqlEnter password:#输入密码[root@server1 ~]# cd /opt/[root@server1 opt]# ll总用量 4-rw-r--r--. 1 root root 1789 12月 27 05:30 kk.sqldrwxr-xr-x. 2 root root 6 3月 26 rh

备份完毕后可以查看kk.sql文件，可以看到没有备份创建数据库的语句，所以我们恢复的时候需要先创建原来的数据表，才可恢复

制造故障：

mysql> drop database kk;Query OK, 1 row affected (0.01 sec)直接恢复mysql> source /opt/kk.sqlERROR 1046 (3D000): No database selected #报错，没有数据库可选

如上所述，恢复时需要创建原先的数据库，才可以恢复

单库恢复：

mysql> create database kk;Query OK, 1 row affected (0.00 sec)mysql> use kk;Database changed方法一、在数据库内用source语句进行数据恢复mysql> source /opt/kk.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)........省略mysql> show databases; #查看kk库是否恢复+--------------------+| Database |+--------------------+| information_schema || jj || kk || mysql || performance_schema || sys|+--------------------+6 rows in set (0.00 sec)mysql> show tables; #查看kk库中的表是否恢复+--------------+| Tables_in_kk |+--------------+| a |+--------------+1 row in set (0.00 sec)方法二、用Linux命令mysql进行数据恢复mysql> drop table a; //把表删了Query OK, 0 rows affected (0.02 sec)mysql> show tables;Empty set (0.00 sec)mysql> exit;Bye[root@server1 opt]# mysql -uroot -p kk < /opt/kk.sql //反向导入恢复Enter password: [root@server1 opt]# mysql -uroot -p -e 'show tables from kk' //查看Enter password: +------------------+| Tables_in_school |+------------------+| a |+------------------+

5.2 多库进行完全备份

格式:

mysqldump -u 用户名 -p 【密码】【选项】 --databases 库名1 【库名2】… > /备份路径/备份文件名

多库备份示例：

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || jj || kk || mysql || performance_schema || sys|+--------------------+6 rows in set (0.00 sec)[root@server1 ~]# mysqldump -u root -pfa123 --databases kk jj > /opt/kk.jj[root@server1 ~]# cd /opt/[root@server1 opt]# ll总用量 8-rw-r--r--. 1 root root 2051 12月 27 05:57 kk.jj-rw-r--r--. 1 root root 1789 12月 27 05:30 kk.sqldrwxr-xr-x. 2 root root 6 3月 26 rh

多库备份好后，进入文件内查看，可以看到，在多库备份的时候，备份了创建数据库的语句，所以恢复的时候可以直接恢复

5.3 对所有库进行完全备份

格式：

musqldump -u 用户名 -p 【密码】【选项】 -add-databases > /备份路径/备份文件名

备份所有库示例：

[root@localhost ]# mysqldump -uroot -pfa123 --all-databases > /opt/all.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

三、MySQL增量备份与恢复

1. MySQL增量备份-1

使用mysqldump命令进行完全备份存在的问题

备份数据中有重复数据备份时间与恢复时间过长

是自上一次备份后增加/变化的文件或者内容

特点：

没有重复数据，备份量不大，时间短恢复需要上次完全备份及完全备份之后所有的- 增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

2.Mysql增量备份-2

MySQL没有提供直接的增量备份方法

可以通过 MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作二进制日志在启动MySQL服务器后开始记录，并在文件达到

max_binlog_size所设置的大小或者接收到flush logs命令后重新

创建新的日志文件只需定时执行flush logs方法重新创建新的日志，生成二进制文

件序列，并及时把这些日志保存到安全的地方就完成了一个时间

段的增量备份

3.MySQL数据库增量恢复-1

一般恢复

将所有备份的二进制日志内容全部恢复

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作可以基于精准的位置跳过错误的操作

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

4.MySQL数据库增量恢复-2

增量恢复的方法：

一般恢复

格式：mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p

基于位置的恢复

恢复数据到指定位置格式：mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

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

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

5.MySQL数据库增量恢复-3

增量恢复的方法：

基于时间点的恢复

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

格式：

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 密码

6.增量备份案例

1.基于时间点恢复

还原时间点的步骤：

删除原先坏掉的那张表还原完全备份的那个数据库停止在错误的时间点开始在正确的时间点

配置流程

1.开启二进制日志文件

[root@server1 ~]# vi /etc/f

2.重启数据库刷新，查看生成的日志文件

[root@server1 ~]# systemctl restart mysqld[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll

接下来关于所有的数据库操作，都被记录在000001文件里。

3.查看日志内容

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001 --no-defaults：解决utf-8报错--base64-output=decode-rows：解决乱码问题-v：输出文件指向无新操作，无新增的信息

4.创建数据库数据

[root@server1 ~]# mysql -uroot -pabc123 登录数据库mysql> show databases; 查看数据库信息mysql> use client; mysql> create table k(id int(10)，name varchar(128)，hobby int(10)，score int(20));mysql> insert into k values('1','zhangsan','1','80');mysql> insert into k values('2','lisi','6','60');mysql> insert into k values('3','wangwu','4','50');mysql> insert into k values('4','xiaohu','2','70');mysql> insert into k values('5','xiao','2','90');mysql> select * from k;+----+----------+-------+-------+| id | name| hobby | score |+----+----------+-------+-------+| 1 | zhangsan |1 | 80 || 2 | lisi|6 | 60 || 3 | wangwu |4 | 50 || 4 | xiaohu |2 | 70 || 5 | xiao|2 | 90 |+----+----------+-------+-------+5 rows in set (0.00 sec)mysql> exit 退出

5.完全备份

[root@server1 ~]# mysqldump -uroot -pfa123 k > /opt/client-k.sql[root@server1 ~]# cd /opt/[root@server1 opt]# ll总用量 4-rw-r--r--. 1 root root 775 12月 27 10:57 client-k.sqldrwxr-xr-x. 2 root root 6 3月 26 rh[root@server1 opt]# mysqladmin -uroot -p flush-logs[root@server1 opt]# cd /usr/local/mysql/data/[root@server1 data]# ll

6.数据配置

[root@server1 ~]# mysql -u root -pfa123mysql> use client;mysql> insert into k values(1,'fa',2,88);Query OK, 1 row affected (0.00 sec)mysql> insert into k values(2,'nb',3,68);Query OK, 1 row affected (0.00 sec)mysql> delete from k where name='zhangsan'; #误删数据Query OK, 1 row affected (0.01 sec)mysql> select * from k;+----+--------+-------+-------+| id | name | hobby | score |+----+--------+-------+-------+| 3 | wangwu |4 | 50 || 4 | xiaohu |2 | 70 || 5 | xiao |2 | 90 || 1 | fa|2 | 88 || 2 | nb|3 | 68 |+----+--------+-------+-------+5 rows in set (0.00 sec)

7.查看生成的日志文件

[root@server1 ~]# mysqladmin -uroot -p flush-logs[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

查找到错误时间点停止

在下一个正确时间点开始

9.模拟数据损坏恢复

[root@server1 ~]# mysql -uroot -pfa123 #登录数据库mysql> show databases;mysql> drop database client; mysql> show databases; mysql> create database client; mysql> show databases; mysql> use client; mysql> source /opt/client-k.sql #还原完全备份的那个数据库mysql> select * from k; mysql> exit mysql> select * from k;+----+--------+-------+-------+| id | name | hobby | score |+----+--------+-------+-------+| 3 | wangwu |4 | 50 || 4 | xiaohu |2 | 70 || 5 | xiao |2 | 90 || 1 | fa|2 | 88 || 2 | nb|3 | 68 |+----+--------+-------+-------+5 rows in set (0.00 sec)

2.基于位置点恢复

2.1步骤

位置点恢复删除原先坏掉的那张表任意删除两个数据字段利用日志，备份恢复查询该二进制日志内容上一次正确操作的位置点停止下一次正确操作的位置点开始

2.2 配置流程

1.设置数据库数据

[root@server1 ~]# mysql -uroot -pfa123 #登录数据库mysql> use client;mysql> select * from k; mysql> delete from k where name='lisi'; mysql> delete from k where name='nb'; [root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 #查看日志中操作的停止与操作的开始

2.恢复数据

[root@server1 ~]# mysqlbinlog --no-defaults --stop-position='3475' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p[root@server1 ~]# mysqlbinlog --no-defaults --start-position='3829' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p[root@server1 ~]# mysql -uroot -pfa123mysql> use client;mysql> select * from k; 查询所有字段

如果觉得《数据库损坏了怎么办？——完全备份及恢复 增量备份及恢复》对你有帮助，请点赞、收藏，并留下你的观点哦！