MySQL全量、增量备份与恢复

作者: SCOR_PIO | 来源:发表于2020-01-11 00:00 被阅读0次

数据备份的重要性

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
1.程序错误
2.人为错误
3.计算机失败
4.磁盘失败
5.zai难和偷窃
数据库备份的分类
物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份又可分为脱机备份（冷备份）和联机备份（热备份）
-冷备份：是在关闭数据库的时候进行的
-热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
逻辑备份：对数据库组件（如表等数据库对象）的备份
表：表的结构文件(FRM)、数据文件(MYD)、索引文件(MYI)
从数据库的备份策略角度，备份可分为
1.完全备份
每次对数据进行完整的备份
完全备份：会把服务器内的所有数据全部备份，每次都这么执行
优点：安全
缺点：数据备份冗余，占用磁盘空间
2.差异备份
备份那些自从上次完全备份之后被修改过的文件
前提是必须要备份一次完全备份，接下来每次只备份基于完全备份的基础上被修改过的文件
3.增量备份
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
差异备份与增量备份比较：
相同点：基础都是完全备份
不同点：差异备份只参考基础的完全备份，
增量备份是参考上一次的数据备份与当前状态进行对比，备份被修改的文件
增量备份效率更高，空间利用率很高，但是在安全性能不高
mysql完全备份
完全备份是对整个数据库的备份、数据库结构和文件结构的备份
完全备份保存的是备份完成时刻的数据库
完全备份是增量备份的基础
完全备份的优缺点
优点
备份与恢复操作简单方便
缺点
数据存在大量的重复
占用大量的备份空间
备份与恢复空间长
mysqldump备份库
mysqldump备份库概述
mysql数据库的备份可以采用多种方式
直接打包数据库文件夹，如/usr/local/mysql/data————这种是物理层面的备份
使用专业备份工具 mysqldump————这种事逻辑层面的备份
mysqldump命令对库备份
mysql自带的备份工具，相当方便对mysql进行备份
通过该命令工具可以将指定的库、表或全部的库导出为sql脚本，在需要恢复时可进行数据恢复

实操
创建数据库、表、插入数据

mysqldump命令对单个库进行完全备份
添加多个数据库

[root@localhost mysql]# mysqldump -u root -p school > /opt/school.sql
Enter password: 
[root@localhost mysql]# ls /opt/
dir_SC_UTF8              mysql-5.7.20  php-7.1.10  school.sql
mysql-2020-01-07.tar.xz  nginx-1.12.2  rh          说明.htm
[root@localhost mysql]# cd /opt
[root@localhost opt]# ls school.sql 
school.sql
[root@localhost opt]# vim school.sql 
[root@localhost opt]#

mysqldump命令对多个库进行完全备份

mysqdump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2]… > /备份路径/备份文件名
多库备份举例
mysqldump -u root -p --databases auth mysql > /backup/databases-auth-mysql.sql
对所有库进行完全备份
mysqdump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
所有库备份举例
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql
使用mysqldump备份表的操作
mysqdump -u 用户名 -p [密码] [选项] 数据库名表名 > /备份路径/备份文件名
备份表的举例

mysqldump -u root -p mysql user > /backup/mysql-user.sql

[root@localhost opt]# mysqldump -uroot -p12341234 school info > /opt/school_info.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls /opt/school_info.sql 
/opt/school_info.sql
[root@localhost opt]# vim /opt/school_info.sql

基于表结构的备份

[root@localhost opt]# mysqldump -uroot -p12341234 -d school info > /opt/school_infod.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# vim /opt/school_infod.sql

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

source命令
mysql命令
注意：source命令是在mysql模式下面使用；mysql命令是在linux模式下使用。
使用source恢复数据库的步骤
登录到mysql数据库
执行source 备份sql脚本的路径
```
mysql [(none)] > source /backup/all-data.sql
```
示例：
备注：备份sql脚本的路径写绝对路径
恢复数据库，是恢复数据库里面的表，如果此时数据库也被删除，需要创建一个同名的数据库————仔细查看school.sql脚本，可以发现没有school数据库的操作

[root@localhost opt]# mysql -uroot -p
Enter password: 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use school;
Database changed

mysql> drop table info;
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/school.sql
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

使用mysql命令恢复数据
mysql -u -p [密码] < 库备份脚本的路径

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)
mysql> quit
Bye
[root@localhost opt]# 
[root@localhost opt]# mysql -uroot -p12341234 school < /opt/school.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p
Enter password: 
mysql> use school;
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_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql 命令恢复举例

mysql -u -p < /backup/all-data.sql

恢复表的操作

恢复表时同样可以使用source或者mysql命令进行
source恢复表的操作与恢复库的操作相同
当备份文件中只包含表的备份，而不包括创建库的语句是，必须指定库名，切目标库必须存在

mysql   -u  用户名 -p  [密码]    <   表备份脚本的路径
mysql -u -p mysql < /backup/mysql-user.sql

mysql备份思路:
定期实施备份，制定备份计划或者策略，并严格遵守
除了进行完全备份，开启mysql服务器的日志功能是很重要的
完全备份加上日志，可以对mysql进行最大化还原
使用统一的和易理解的备份文件名称
不要使用backup1/2这样没有意义的名字
推荐使用库名或者表名加上时间的命名规则
备份文件名使用时间+业务名+库名
要开启服务器的日志功能

mysql增量备份
诞生增量备份的原因
解决使用mysqldump进行完全备份时的存在的问题

备份数据中有重复数据，会造成数据冗余、占用磁盘空间
备份时间与回复时间长
增量备份就是备份上一次备份之后增加或变化的文件或内容
增量备份的优缺点:
优点：没有重复数据，备份量不大，时间短
缺点：恢复麻烦；需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
通过mysql的二进制日志文件分割间接实现增量备份
mysql没有提供直接的增量备份方法
可以通过mysql提供的二进制文件（binary logs）间接实现增量备份
mysql二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动mysql服务器后开始记录，并在文件达到maxbinlogsize所设置的大小或者接收到flush logs命令后重新创建新的日志文件
只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份
二进制文件针对位置点、时间点进行有效的恢复

查看日志文件需要进行解码
mysql数据库增量恢复

mysqlbinlog [–no-defaults] 增量备份文件 | 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=‘年-月-日 小时：分钟：秒’ 二进制日志 | mysql -u 用户名 -p 密码
#从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日 小时：分钟：秒’ --stop-datetime=‘年-月-日 小时：分钟：秒’ 二进制日志 | mysql -u 用户名 -p 密码

实操

[root@localhost opt]# ls
all.sql                  mysql-5.7.20  school_infod.sql
db_school_mysql.sql      nginx-1.12.2  school_info.sql
dir_SC_UTF8              php-7.1.10    school.sql
mysql-2020-01-07.tar.xz  rh            说明.htm
[root@localhost opt]# rm -rf *.sql
[root@localhost opt]# ls
dir_SC_UTF8              mysql-5.7.20  php-7.1.10  说明.htm
mysql-2020-01-07.tar.xz  nginx-1.12.2  rh

开启二进制日志功能，修改/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
'log-bin=mysql-bin
server-id = 1
default-storage-engine=Myisam

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 opt]# systemctl restart mysqld

查看二进制日志文件

[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# cd data/
[root@localhost data]# ls
auto.cnf        ibdata1      ibtmp1            mysql-bin.index     sys
bbs             ib_logfile0  mysql             performance_schema
ib_buffer_pool  ib_logfile1  'mysql-bin.000001'  school
[root@localhost data]#

做增量备份前，要先进行一次完全备份

[root@localhost data]# mysqldump -uroot -p12341234 school > /opt/school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# cd /opt
[root@localhost opt]# ls
dir_SC_UTF8              mysql-5.7.20  php-7.1.10  school.sql
mysql-2020-01-07.tar.xz  nginx-1.12.2  rh          说明.htm
[root@localhost opt]# vim school.sql

接下来做增量备份，此时，之前的操作被存放到001当中，接下来的操作会被存放到002当中

[root@localhost opt]# mysqladmin -uroot -p12341234 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ibtmp1            mysql-bin.000002    school
bbs             ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema

mysql> use school;
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> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('test01',66);
//这个是正常操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> delete from info where name='stu01';
//误操作
Query OK, 1 row affected (0.01 sec)

mysql> insert into info (name,score) values ('test02',99);
//正常操作
Query OK, 1 row affected (0.01 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  4 | test02 |  99.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.01 sec)
mysql> quit
Bye

此时在不知情的情况下，进行增量备份，此时误操作写在了002中

[root@localhost opt]# mysqladmin -uroot -p12341234 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql-bin.000001  performance_schema
bbs             ib_logfile1  mysql-bin.000002  school
ib_buffer_pool  ibtmp1       mysql-bin.000003  sys
ibdata1         mysql        mysql-bin.index

查看日志文件：-v 显示内容在界面，–base64解码器 output输出 decode-rows 读取按行读取

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002 
//查看二进制日志文件，不过可以发现看不懂
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
//这个是位置点
#200107 16:54:11 server id 1  end_log_pos 123 CRC32 0x76a9dc26  Start: binlog v 4, server v 5.7.20-log created 200107 16:54:11
//这个是时间点
BINLOG '
M0cUXg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
//像这样的就是被加密的命令
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002  > /opt/back.txt
//-v 显示内容在界面，--base64解码器 output输出 decode-rows 读取按行读取
[root@localhost data]# cd /opt
[root@localhost opt]# vim back.txt 
//下面的截图就是bak.txt中的数据信息

错误操作截图

200107 16:57:56 --stop-datetime /指从这个日志文件开始，执行到这个时间点时就停止
200107 16:58:46 --start-datetime /指这个日志文件中，从这个时间点开始向后面执行
先完全备份恢复，source /opt/school.sql

[root@localhost opt]# mysql -uroot -p12341234
mysql> use school;
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> drop table info;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist
mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/school.sql;
Query OK, 0 rows affected (0.00 sec)
。。。。。。

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql>

然后再增量恢复，即时间上的断点恢复

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2020-01-07 16:57:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 

[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2020-01-07 16:58:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 

[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
|  4 | test02 |  99.0 |
+----+--------+-------+
4 rows in set (0.00 sec)
//误操作删除的stu01 没有被删掉
mysql>

基于位置的恢复
错误操作的日志文件

[root@localhost opt]# mysqlbinlog --no-defaults --stop-postion='612' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
mysqlbinlog: [ERROR] unknown variable 'stop-postion=612'
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='612' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -uroot -p12341234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='716' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -uroot -p12341234mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  4 | test02 |  99.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> desc info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(4)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
| score | decimal(4,1) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost opt]# mysqlbinlog --no-defaults  /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
//这个是增量恢复，将日志文件内的所有操作全部执行

MySQL全量、增量备份与恢复

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

个人学习