Mysql备份和恢复的类型，备份恢复的几种方法！ --个人整理

最新推荐文章于 2024-08-17 23:12:13 发布

卖女孩的小伙柴as

最新推荐文章于 2024-08-17 23:12:13 发布

阅读量1.5k

点赞数 6

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_47320286/article/details/108562220

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

个人整理，感谢观看

一，为什么要做数据备份？
二，备份的分类
三，常见的备份方法
四，mysql完全备份操作
- 1.物理备份与恢复
- 2.mysqldump备份和恢复
五，增量备份和恢复

一，为什么要做数据备份？

在生产环境中，数据的安全性非常重要，任何数据的丢失都可能会产生严重的后果，
造成数据丢失的原因：
程序错误
人为操作失误
运算错误
磁盘故障
灾难
等等…
所以说备份数据库是非常重要的，对咱们的数据安全性也就能大大的提高

二，备份的分类

物理备份
对数据库操作系统的物理文件（数据文件，日志文件等…）备份
物理备份方法：

冷备份（脱机备份）：需要关闭数据库，才能进行
热备份（联机备份）：数据库处于运行状态，依赖数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份：对数据库逻辑组件（数据库中的表…）的备份
从数据库的备份策略角度，备份可分为

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

三，常见的备份方法

1.物理冷备份
tar命令

2.专用备份工具
mysqldump
mysqlhotcopy

3.二进制日志

4.第三方工具备份
Percona XtraBackup
Xtrabackup、innobackupex、xbstream

四，mysql完全备份操作

1.物理备份与恢复


备份：
 systemctl  stop mysqld                                     #先关闭数据库服务
 mkdir /bakup                                                    #创建备份目录
 tar zcvf /bakup/mysql_all-$(date +%F).tar.gz  /usr/local/mysql/data/       #压缩目录备份
 cd /backup/                                                        #查看备份


模拟故障：
mkdir /bak                                                         #创建目录模拟故障
 mv /usr/local/mysql/data/  /bak                        #移动数据文件目录
cd /usr/local/mysql/                                           #查看模拟故障是否成功
 ll

恢复：
mkdir /restore                                                                              #创建恢复存放目录
tar zxvf /bakup/mysql_all-2020-09-13.tar.gz  -C  /restore/          #将之前压缩备份的目录，解压到指定的新建恢复存放目录
 mv /restore/usr/local/mysql/data/  /usr/local/mysql/                   #将解压的数据文件目录data，移动到mysql下
systemctl  start  mysqld                                                               #启动服务
systemctl status mysqld                                                               #查看数据库开启正常
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 06:03:42 EDT; 3h 56min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9819 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 9801 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 9822 (mysqld)

2.mysqldump备份和恢复

mysqldump 可以将指定的库，表，或全部的库导出为sql脚本；

备份的几种操作：

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

示例：
mysqldump -u root -p mysql user >mysql-user.sql #备份数据路mysql中的user表，文件保存在当前操作目录下面
Enter password: ###输入密码abc123
mysqldump -u root -p --databases auth >auth.sql #备份auth数据库文件保存在当前操作目录下面
Enter password: ####输入密码abc123

操作：
准备环境：
创建数据库—创建表—表内增加点数据

1.create database wow;

2.use wow;

3.create talbe lol(name CHAR(16) NOT NULL,password CHAR(48) 
DEFAULT'',primary key (name));

4.mysql> insert into lol(name,password) 
    -> values
    -> ('xiaohong',password('123456'));
    
5.mysql> insert into lol values ('lisi',password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

对数据库的表备份：
 mysqldump -u root -p  wow  lol >lol.sql                             #对数据库中的lol表做备份
Enter password:                                                                    #输入密码


恢复：
mysql -u root -p uou < lol.sql                                              #uou是之前存在的空库，将备份表lol导入到uou库中；
Enter password: 

mysql -u root -p -e 'show tables from uou'                           #查看数据表lol是否导入成功
Enter password: 
+---------------+
| Tables_in_uou |
+---------------+
| lol           |
| user          |
+---------------+



对数据库做备份：
mysqldump -u root -p --databases wow >wow.sql
Enter password: 


模拟数据库故障：
[root@localhost ~]# mysql -u root -p -e 'drop database wow'        #将库wow删除，模拟故障（高危操作哦！！！！）
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'show databases'            #查看下是否被删除
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| uou                |
+--------------------+

恢复：
[root@localhost ~]# mysql -u root -p < wow.sql                          #将库wow导入数据库中
Enter password: 

[root@localhost ~]# mysql -u root -p -e 'show databases'          #查看下数据库中的库
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| uou                |
| wow                |
+--------------------+

五，增量备份和恢复

1、增量备份的特点

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

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

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

方法:

一般恢复：（一般丢什么数据，找什么数据）
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
基于位置恢复：
1、恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

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

基于时间恢复：
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 密码

一般恢复：

准备环境：
生成二进制文件--常见库--表
1.vi /etc/my.cnf  在[mysqld]配置内，添加
log-bin=/usr/local/mysql/data/mysql-bin 
2.systemctl  restart mysqld      #重启服务
3.ll  /usr/local/mysql/data            #查看时候有二进制文件日志生成
....省略部分...
-rw-r-----. 1 mysql mysql      154 Sep 13 11:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql       39 Sep 13 11:50 mysql-bin.index
....省略部分...

在这里插入代码片创建测试环境：
mysql> create database cl;
Query OK, 1 row affected (0.01 sec)

mysql> use cl;
Database changed
create table cls(工号 char(20) not null,姓名 char(10) not null,工资 char(6) not null,部门 char(5));
Query OK, 0 rows affected (0.01 sec)

insert into cls values ('001','熏无空','18888',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('002','沙屋静','16666',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('003','竹巴结','14444',3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from cls;
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
+--------+-----------+--------+--------+
3 rows in set (0.00 sec)

mysql> show master logs;                  #查看是否生成二进制日志文件，记录着操作
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1802 |
+------------------+-----------+
1 row in set (0.00 sec)

进行一次完全备份：
mysql> flush tables with read lock;    //锁表 只读，不能写
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

 mysqldump -u root -p cl cls >/mysql-bak/cls-$(date +%F).sql
Enter password: 

ll /mysql-bak/
total 4
-rw-r--r--. 1 root root 1952 Sep 13 12:22 cls-2020-09-13.sql

 mysqladmin -u root -p flush-logs
Enter password: 

 ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql     1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql      154 Sep 13 12:24 mysql-bin.000002
-rw-r-----. 1 mysql mysql       78 Sep 13 12:24 mysql-bin.index
...省略部分...

录入新的内容并进行一次增量备份：

mysql> unlock tables;                   //解表；



Query OK, 0 rows affected (0.00 sec)

mysql> use cl;
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> insert into cls values ('004','滕森','10000',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('005','金娇','10000',5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from cls;
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金娇      | 10000  | 5      |
+--------+-----------+--------+--------+
5 rows in set (0.00 sec)


mysqladmin -u root -p flush-logs;            //再次刷新二进制日志文件，看操作是否被记录生成文件
Enter password: 

ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql     1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql      753 Sep 13 12:30 mysql-bin.000002
-rw-r-----. 1 mysql mysql      154 Sep 13 12:30 mysql-bin.000003
-rw-r-----. 1 mysql mysql      117 Sep 13 12:30 mysql-bin.index
...省略部分...

 cp /usr/local/mysql/data/mysql-bin.000002  /mysql-bak/          //将二进制日志文件复制到咱们创建的备份目录下



模拟失误操作删除cls表；
 mysql -u root -p -e 'drop table cl.cls'
Enter password: 

mysql -u root -p -e 'select * from cl.cls'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'cl.cls' doesn't exist


恢复操作：（完全备份恢复）
mysql -u root -p cl < /mysql-bak/cls-2020-09-13.sql 
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
+--------+-----------+--------+--------+

增量备份恢复：
 mysqlbinlog --no-defaults /mysql-bak/mysql-bin.000002 |mysql -u root -p Enter password: 


 mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金娇      | 10000  | 5      |
+--------+-----------+--------+--------+

基于位置恢复：

模拟删除表cls：
删除表cls---恢复下完全备份cl.2020.9.13.sql--查看二进制日志文件内想要恢复的位置

mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002              ####--base64-output=decode-rows    64解码    ###  -v   换行显示

.........省略部分...........
#at 289
#200913 12:27:59 server id 1  end_log_pos 344 CRC32 0x25b12913  Table_map: `cl`.`cls` mapped to number 225
#at 344
#200913 12:27:59 server id 1  end_log_pos 399 CRC32 0xbd609658  Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
###   @1='004'
###   @2='滕森'
###   @3='10000'
###   @4='4'
# at 399
#200913 12:27:59 server id 1  end_log_pos 430 CRC32 0x3561ca6a  Xid = 74
COMMIT/*!*/;
# at 565
#200913 12:28:26 server id 1  end_log_pos 620 CRC32 0x5e09ca65  Table_map: `cl`.`cls` mapped to number 225
# at 620
#200913 12:28:26 server id 1  end_log_pos 675 CRC32 0x9a65af81  Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
###   @1='005'
###   @2='金娇'
###   @3='10000'
###   @4='5'
# at 675
#200913 12:28:26 server id 1  end_log_pos 706 CRC32 0x510d9e90  Xid = 75
COMMIT/*!*/;
.........省略部分...........

咱们只想恢复滕森的数据！操作如下：

从文件可以得知，at289- at565  这位置范围是滕森

 mysqlbinlog  --no-defaults --stop-position='430' /mysql-bak/mysql-bin.000002 |mysql -u root -p       #位置在操作节点附近即可
Enter password: 
[root@localhost ~]# mysql -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
+--------+-----------+--------+--------+

另一种操作方法：start 跟上面差不多操作

注意： 基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。

基于时间戳恢复

比如咱们通过时间戳恢复金娇的数据
20200913 12:28:26 # 日志文件中金娇所在的时间戳


 mysqlbinlog --no-defaults --start-datetime='2020-09-13 12:28:26' /mysql-bak/mysql-bin.000002  |mysql -u root -p 
Enter password: 

[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工号   | 姓名      | 工资   | 部门   |
+--------+-----------+--------+--------+
| 001    | 熏无空    | 18888  | 1      |
| 002    | 沙屋静    | 16666  | 2      |
| 003    | 竹巴结    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金娇      | 10000  | 5      |
+--------+-----------+--------+--------+