MySQL数据库备份与恢复

最新推荐文章于 2022-04-28 15:19:39 发布

cenjeal

最新推荐文章于 2022-04-28 15:19:39 发布

阅读量1w

点赞数

分类专栏： MySQL数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/cenjeal/article/details/108563859

版权

MySQL数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一 MySQL数据库备份分类

1.1 物理与逻辑角度分类

数据库备份分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

冷备份：是在关闭数据库的时候进行的

热备份：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份：是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE 语句）和内容（INSERT 语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据

1.2 备份策略角度分类

完全备份: 每次对数据库进行完整的备份

对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长。

差异备份: 备份自从上次完全备份之后被修改后的文件

备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

增量备份: 只有在上次完全备份或者增量备份后被修改的文件才会被备份

从上次完整备份或上次增量备份的时间为时间节点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

1.3 常见的备份方法

物理冷备

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

物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹（通常位于/usr/local/mysql/data）来实现备份

专用备份工具mysqldump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup

二 MySQL完全备份与恢复

2.1 物理冷备份与恢复（tar命令直接打包数据库文件夹）

1.关闭数据库,在根下新建个backup目录,/usr/local/mysql/data备份压缩到目录/backup/下。进入/backup/ ,查看备份.
date +%F的含义：
在这里插入图片描述

[root@localhost ~]# systemctl stop mysqld                                
[root@localhost ~]# mkdir /backup                                        
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/           
[root@localhost ~]# cd /backup/                                                       
[root@localhost backup]# ll

在这里插入图片描述
2.将数据库文件/usr/local/mysql/data/转移至 bak 目录下，模拟故障。
在根下新建bak目录,将目录data数据移动到bak里面去,进mysql目录查看data，查看后data目录被移走，模拟故障成功。

[root@localhost backup]# mkdir /bak                                            
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/          
[root@localhost backup]# cd /usr/local/mysql/                             
[root@localhost mysql]# ll

3.数据库恢复
将备份数据库解压到 /usr/local/mysql/data目录下,查看mysql数据库启动状态，日志文件显示正常。

[root@localhost mysql]# tar xzvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data          
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld

2.2 mysqldump备份与恢复

mysqldump 命令可以将指定的库、表或全部的库导出为SQL脚本，便于在不同版本的MySQL服务器上使用。

语法：
1.备份指定库中的部分表：
mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名

2.备份一个或多个完整的库（包括其中所有的表）
mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名

3.备份 MySQL 服务器中所有的库。
mysqldump [选项] --all-databases > /备份路径/备份文件名

其中，常用的选项包括“-u”、“-p”，分别用于指定数据库用户名、密码

实例应用
创建auth这个数据库,进入auth这个数据库。创建表users，定义字段user_name和user_passwd

mysql> create databases auth;                              	
mysql> use 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('peiqi', password('123'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into users values('peihua', password('123'));
Query OK, 1 row affected, 1 warning (0.01 sec)

备份auth库中的users表
备份数据库auth中的users表，文件保存在当前操作目录下面

[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql
Enter password:

备份auth数据库
备份auth数据库,文件保存在当前操作目录下面

[root@localhost /]# mysqldump -u root -p auth >auth.sql     
Enter password:

备份多个数据库

[root@localhost /]# mysqldump -u root -p --databases school auth >/opt/school-auth.sql     
Enter password:

备份所有数据库

[root@localhost backup]# mysqldump -uroot -p --all-databases > /opt/all-database.sql
Enter password: 
[root@localhost backup]# ll
total 5336
-rw-r--r--. 1 root root 3103310 Sep 13 14:22 all-databases.sql
-rw-r--r--. 1 root root    2780 Sep 13 14:18 auth.sql
-rw-r--r--. 1 root root 2345327 Sep 13 09:59 mysql_all-2020-09-13.tar.gz
-rw-r--r--. 1 root root    6950 Sep 13 14:13 mysql-user.sql

恢复操作
方法1：

mysql>create database stu;
mysql>use stu;
mysql>source /opt/school.sql;

方法2：

mysql [选项] [库名] [表名] < /备份路径/备份文件名

mysql> create database authbak;
mysql> exit;
[root@localhost backup]# mysql -u root -p authbak <auth-users.sql
Enter password: 
[root@localhost backup]# mysql -u root -p -e 'show tables from authbak;'
Enter password: 
+-------------------+
| Tables_in_authbak |
+-------------------+
| users             |
+-------------------+


模拟故障
mysql> drop database auth;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| authbak            |
| myadm              |
| mysql              |
| performance_schema |
| sanji              |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
导入数据库auth
[root@localhost backup]# mysql -uroot -p < auth.sql
Enter password: 
[root@localhost backup]# mysql -u root -p -e 'show databases'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| authbak            |
| myadm              |
| mysql              |
| performance_schema |
| sanji              |
| sys                |
+--------------------+

三 MySQL增量备份与恢复

3.1 增量备份的特点

与完全备份完全不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完成完全备份及备份之后的所有增量备份才能恢复。

3.2 MySQL数据库二进制日志对备份的意义

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

3.3 开启二进制日志备份功能

编辑/etc/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

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
#添加这一行
log-bin=/usr/local/mysql/data/mysql-bin
:wq
重启数据库
[root@localhost mysql]# systemctl restart mysqld

[root@localhost backup]# ll /usr/local/mysql/data/
生成二进制日志文件
在这里插入图片描述

3.4 增量恢复的三种方法

3.4.1 一般恢复

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

语法：
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

3.4.2 基于位置的恢复

数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准

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

从指定的位置开始恢复数据
mysqlbinlog --start-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码

3.4.3 基于时间点的恢复

语法：
1.从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
2.从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
3.从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码

四 MySQL备份恢复案例

4.1 完全备份

首先创建数据库client以及表user_info

mysql> create database client;
Query OK, 1 row affected (0.01 sec)

mysql> use client;
Database changed
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10));

mysql> insert into user_info values('001','佩花','男','016','10');
mysql> insert into user_info values('002','佩琪','男','018','50');
mysql> insert into user_info values('003','乔治','男','021','15');

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 001       | 佩花   | 男     | 016         |     10 |
| 002       | 佩琪   | 男     | 018         |     50 |
| 003       | 乔治   | 男     | 021         |     15 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)


查看当前数据库binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1483 |
+------------------+-----------+
1 row in set (0.00 sec)

准备工作：备份前需要将数据库加读锁，防止数据在备份时写入

mysql> flush tables with read lock;
mysql> exit

创建/mysql_bak 目录并刷新日志

[root@localhost ~]# mkdir /mysql_bak                     

[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql      
Enter password: 
[root@localhost backup]# ll /mysql_bak/
total 4
-rw-r--r--. 1 root root 2046 Sep 13 15:20 client_userinfo-2020-09-13.sql

[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
[root@localhost ~]#  ll /usr/local/mysql/data/
-rw-r-----. 1 mysql mysql     1530 Sep 13 15:21 mysql-bin.000001
-rw-r-----. 1 mysql mysql      154 Sep 13 15:21 mysql-bin.000002
-rw-r-----. 1 mysql mysql       78 Sep 13 15:21 mysql-bin.index

4.2 增量备份

解锁表并插入两条新纪录

mysql> unlock tables;

mysql> use client;

mysql> insert into user_info values('004','碳质郎','男','019','37');

mysql> insert into user_info values('005','伊之助','男','020','36');

mysql> select * from user_info;
+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 姓别   | 用户ID号     |  资费    |
+-----------+-----------+--------+-------------+--------+
| 001       | 佩花      | 男     | 016         |     10 |
| 002       | 佩琪      | 男     | 018         |     50 |
| 003       | 乔治      | 男     | 021         |     15 |
| 004       | 碳质郎    | 男     | 019         |     37 |
| 005       | 伊之助    | 男     | 020         |     36 |
+-----------+-----------+--------+-------------+--------+
mysql> exit
Bye

这个时候再次刷新日志会产生一个新的日志mysqlr-bin.000003,而上面插入的两条记录保存在了mysql-bin.000002日志文件里

[root@localhost backup]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost backup]# ll /usr/local/mysql/data

在这里插入图片描述

4.3 模拟误操作删除user_info表

[root@localhost mysql_bak]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost mysql_bak]# 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

4.4 恢复操作

4.4.1 完全备份的恢复

[root@localhost backup]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-13.sql 
Enter password: 
[root@localhost backup]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 001       | 佩花   | 男     | 016         |     10 |
| 002       | 佩琪   | 男     | 018         |     50 |
| 003       | 乔治   | 男     | 021         |     15 |
+-----------+--------+--------+-------------+--------+

4.4.2 增量备份的恢复

[root@localhost backup]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password: 
[root@localhost backup]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 姓别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 001       | 佩花      | 男     | 016         |     10 |
| 002       | 佩琪      | 男     | 018         |     50 |
| 003       | 乔治      | 男     | 021         |     15 |
| 004       | 碳质郎    | 男     | 019         |     37 |
| 005       | 伊之助    | 男     | 020         |     36 |
+-----------+-----------+--------+-------------+--------+

4.4.3 基于位置的恢复

首先删除user_info表，把完全备份文件导入数据库client做个完全备份恢复，然后通过第16行的命令查看二进制文件，发现每进行一个操作之前都会有一个独特的编号，如“# at 154”。此编号随着操作数增多而变大，我们称之为操作 ID。在操作 ID 下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作ID或者时间标记。

[root@localhost backup]# mysql -uroot -p -e 'drop table client.user_info'
Enter password: 
[root@localhost backup]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-13.sql 
Enter password: 
[root@localhost backup]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 001       | 佩花   | 男     | 016         |     10 |
| 002       | 佩琪   | 男     | 018         |     50 |
| 003       | 乔治   | 男     | 021         |     15 |
+-----------+--------+--------+-------------+--------+


[root@localhost backup]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002

在这里插入图片描述

位置恢复
位置657是创建“伊之助”之前的一个点，位到这个位置点就停止恢复，所以碳质郎恢复了，而伊之助没有恢复

[root@localhost backup]# mysqlbinlog --no-defaults --stop-position='657' /mysql_bak/mysql-bin.000002 |mysql -u root -p

[root@localhost backup]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 姓别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 001       | 佩花      | 男     | 016         |     10 |
| 002       | 佩琪      | 男     | 018         |     50 |
| 003       | 乔治      | 男     | 021         |     15 |
| 004       | 碳质郎    | 男     | 019         |     37 |
+-----------+-----------+--------+-------------+--------+

再次模拟故障，恢复伊之助

[root@localhost backup]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost backup]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-13.sql 
Enter password: 
[root@localhost backup]# mysqlbinlog --no-defaults --start-position='591' /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password: 
[root@localhost backup]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 姓别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 001       | 佩花      | 男     | 016         |     10 |
| 002       | 佩琪      | 男     | 018         |     50 |
| 003       | 乔治      | 男     | 021         |     15 |
| 005       | 伊之助    | 男     | 020         |     36 |
+-----------+-----------+--------+-------------+--------+

4.4.4 基于时间恢复

再次删除表模拟故障然后进行一次完全备份恢复

[root@localhost backup]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost backup]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-13.sql 
Enter password:

时间恢复

mysqlbinlog --no-defaults --stop-datetime='2020-9-13 15:26:00' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复碳质郎
mysqlbinlog --no-defaults --start-datetime='2020-9-13 15:26:00' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复伊之助

cenjeal

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库备份与恢复

从上次完整备份或上次增量备份的时间为时间节点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
复制链接

扫一扫