MySQL数据库的备份和恢复（案例详解）

最新推荐文章于 2024-09-02 17:39:44 发布

peihhua

最新推荐文章于 2024-09-02 17:39:44 发布

阅读量7.5k

点赞数 7

文章标签：数据库 mysql linux

本文链接：https://blog.csdn.net/weixin_48192495/article/details/108561882

版权

一. 造成数据库丢失的原因

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

二. MySQL 备份的分类

2.1 物理备份

冷备份：在数据关闭的状态下运行
热备份：在数据库运行的状态时进行备份操作，该备份方法依赖数据库日志文件。
温备份：数据局锁定表格（可以读不可以写）的状态年备份数据

2.2 逻辑备份

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据结构（create database、create tabale）
和内容（insert语句或分割文本文件）的信息

三. MySQL 的备份策略

3.1 完全备份

每次对数据库进行完整的备份，即对整个数据的备份，数据库结构和文件结构备份。保存的是完整时刻的数据库

3.2 差异备份

备份那些自从上次完整备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份的数据量会越来越大，
恢复数据只是恢复上次的完整备份和最近一次的差异备份。

3.3 增量备份

只有那些在上次完全备份或者增量备份后被修改的文件才会备份，以上次完成整备份，或者上次的增量备份的时间点，
仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份数据速度快，但恢复时候，需要从上次的完整备份
开始到最后一次的增量备份之间所有的增量依次恢复，如中间某次的恢复数据备份损坏，将导致数据的丢失。

四. MySQL常用的备份方法

4.1 数据库完全备份分类

在这里插入图片描述

4.2 增量备份：二进制日志备份

在这里插入图片描述

4.3 第三方工具备份

Percona XtraBackup
Xtrabackup、innobackupex、xbstream

五. MySQL具体的备份实现操作

5.1 MySQL数据完全备份操作

物理冷备份与恢复（tar命令直接打包数据库文件夹）
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

2）模拟故障

[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目录被移走 ，模拟故障成功

3）数据库恢复

[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
.....省略.....

4）启动数据库

[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

5.2 mysqldump备份与恢复

将指定的库、表、或全部的库导出为SQL脚本

5.21 mysqldump的备份操作

mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复

mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump [选项] --all-databases > /备份路径/备份文件名

1）创建实验环境

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'));

2）mysqldump备份数据表

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

3）mysqldump备份数据库

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

5.22 mysql恢复操作

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

1）恢复数据库表

[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:

2）恢复数据库

[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:

5.3 MySQL数据库增量备份恢复

5.31 MySQL增量备份的特点与意义

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

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

5.32 修改配置文件/etc/my.cnf 开启二进制日志备份功能

[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

生成二进制日志文件

[root@localhost ~]# ll /usr/local/mysql/data/ 在这里插入图片描述

5.33 MySQL增量恢复的方法

5.331 一般恢复

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

5.332 基于位置的恢复

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

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

5.333 基于时间点的恢复

1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日小时:分钟:秒’

3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

六. mysql企业备份案例

6.1 一般恢复（丢什么数据，找什么数据）

[root@localhost opt]# mysql -u root -p
Enter password: 
mysql> create database client;
mysql> use client;
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('000006','张三','男','016','10');
mysql> insert into user_info values('000007','李四','女','017','91');
mysql> insert into user_info values('000008','王五','女','018','23');
mysql> select * from user_info;
mysql> show master logs;                                    ####查看当前数据库binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1492 |
+------------------+-----------+
1 row in set (0.00 sec)

6.2 进行一次完全备份

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

6.3 继续录入新的内容并进行增量备份

[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/

6.4 模拟误操作删除 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

6.5 恢复操作

完全备份的恢复

[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 |
+-----------+--------+--------+-------------+--------+

6.6 基于位置的恢复

[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info'    ### 删除表user_info
Enter password: 

[root@localhost mysql_bak]# mysql -uroot -p -e 'select * from client.user_info'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@localhost ~]# 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 --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002              ####--base64-output=decode-rows    64解码    ###  -v   换行显示   查看二进制日志文件
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p      ####操作的节点附近即可
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
###恢复赵六
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='657' /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: 
###恢复孙七


**基于位

置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。**

6.7 基于时间恢复

[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002              ####--base64-output=decode-rows    64解码    ###  -v   换行显示   查看二进制日志文件
mysqlbinlog --no-defaults --stop-datetime='2020-9-13 4:14:09 ' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复赵六
mysqlbinlog --no-defaults --start-datetime='2020-9-13  4:13:56' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复孙七