MySQL备份与恢复

数据库备份

数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因:
  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾，地震）和盗窃

数据库备份的分类

• 从物理与逻辑的角度，备份可分为
  • 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
  • 物理备份方法
    • 冷备份（脱机备份）：是在关闭数据库的时候进行的
    • 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
    • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
  • 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
• 从数据库的备份策略角度，备份可分为
  • 完全备份：每次对数据库进行完整的备份
  • 差异备份：备份自从上次完全备份之后被修改过的文件
  • 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

常见的备份方法

• 物理冷备
  • 备份时数据库处于关闭状态，直接打包数据库文件
  • 备份速度快，恢复时也是最简单的
• 专用备份工具mydump或mysqlhotcopy
  • mysqldump常用的逻辑备份工具
  • mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表
• 启用二进制日志进行增量备份
  • 进行增量备份，需要刷新二进制日志
• 第三方工具备份
  • 免费的MySQL热备份软件Percona XtraBackup

MySQL完全备份与恢复

MySQL完全备份

• 是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础
• 优点：
  • 备份与恢复操作简单方便
• 缺点:
  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

数据库完全备份分类

• 物理冷备份与恢复
  • 关闭MySQL数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换先有MySQL目录即可
• mysqldump备份与恢复
  • MySQL自带的备份工具，可方便实现对MySQL的备份
  • 可以将指定的库、表导出为SQL脚本
  • 使用命令mysql导入备份的数据

MySQL物理冷备份及恢复

物理冷备份
先关闭数据库，之后打包备份
恢复数据库
恢复数据库，采用将备份数据mv成线上库文件夹的方式

步骤

创建数据库数据

mysql> create database zs;
Query OK, 1 row affected (0.02 sec)

mysql> use zs;
Database changed
mysql> create table aa(id int(10));
Query OK, 0 rows affected (0.02 sec)

mysql> desc aa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

数据备份打包，数据丢失，损坏

[root@server3 ~]# systemctl stop mysqld
[root@server3 ~]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@server3 ~]# mysql
mysql> drop database zs;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
9 rows in set (0.00 sec)

恢复数据

[root@server3 ~]# systemctl stop mysqld
[root@server3 ~]# cd /opt
[root@server3 opt]# tar zxvf mysql_all-2020-12-28.tar.gz
[root@server3 opt]# mkdir mysql_old
[root@server3 opt]# mv /usr/local/mysql/data/ mysql_old/
[root@server3 opt]# mv usr/local/mysql/data/ /usr/local/mysql/
[root@server3 opt]# cd /usr/local/mysql/
[root@server3 mysql]# ll
总用量 64
drwxr-xr-x.  2 mysql mysql  4096 12月 22 23:30 bin
-rw-r--r--.  1 mysql mysql 17987 9月  13 2017 COPYING
-rw-r--r--.  1 mysql mysql 17987 9月  13 2017 COPYING-test
drwxr-x---. 11 mysql mysql   213 12月 28 14:56 data
drwxr-xr-x.  2 mysql mysql    55 12月 22 23:30 docs
drwxr-xr-x.  3 mysql mysql  4096 12月 22 23:30 include
drwxr-xr-x.  4 mysql mysql   191 12月 22 23:30 lib
drwxr-xr-x.  4 mysql mysql    30 12月 22 23:30 man
drwxr-xr-x. 10 mysql mysql  4096 12月 22 23:30 mysql-test
-rw-r--r--.  1 mysql mysql  2478 9月  13 2017 README
-rw-r--r--.  1 mysql mysql  2478 9月  13 2017 README-test
drwxr-xr-x. 28 mysql mysql  4096 12月 22 23:30 share
drwxr-xr-x.  2 mysql mysql    90 12月 22 23:30 support-files
drwxr-xr-x.  3 mysql mysql    17 12月 22 23:30 usr
[root@server3 mysql]# cd
[root@server3 ~]# systemctl start mysqld
[root@server3 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
| zs                 |
+--------------------+
10 rows in set (0.00 sec)

mysql> use zs;
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_zs |
+--------------+
| aa           |
+--------------+
1 row in set (0.00 sec)

mysql> desc aa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysqldump（热备份）备份数据库

[root@server3 ~]# mysqldump -u root -p --all-databses > all-data-$(date +%F).sql  #备份所有数据库
[root@server3 ~]# mysqldump -u root -p -databases auth mysql > auth-mysql.sql     #备份auth和mysql库
[root@server3 ~]# mysqldump -u root -p auth > auth-$(data +%F).sql                #备份auth数据库
[root@server3 ~]# mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql    #备份mysql的user表
[root@server3 ~]# mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql   #备份mysql库user表的结构

恢复数据库
基于mysql命令

[root@server3 ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.20 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> drop database zs;  #删除zs库模拟数据库故障
Query OK, 1 row affected (0.08 sec)

mysql> create database bb; #建立空库
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

[root@server3 ~]# mysql -u root bb < test-2020-12-28.sql #恢复至b库
[root@server3 ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.20 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| bb                 |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
10 rows in set (0.01 sec)

mysql> use bb;
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_bb |
+--------------+
| aa           |
+--------------+
1 row in set (0.01 sec)

基于脚本恢复（source）

mysql> drop database bb;
Query OK, 1 row affected (0.00 sec)

mysql> create database cc;
Query OK, 1 row affected (0.00 sec)

mysql> use cc;
Database changed

mysql> source /root/test-2020-12-28.sql;
mysql> show tables;
+--------------+
| Tables_in_cc |
+--------------+
| aa           |
+--------------+
1 row in set (0.01 sec)

mysql> select * from aa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

MySQL增量备份与恢复

MySQL增量备份

• 使用mysqldump进行完全备份存在的问题
  • 备份数据中有着重复数据
  • 备份时间与恢复时间过长
• 是自上一次备份后增加/变化的文件或者内容
• 特点
  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复（同一个日志文件）
• MySQL没有提供直接的增量备份方法
• 可通过MySQL提供的二进制日志间接实现增量备份
• MySQL二进制日志对备份的意义
  • 二进制日志保存了所有更新或者可能更新数据库的操作
  • 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_ size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
  • 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

MySQL数据库增量恢复

• 一般恢复
  • 将所有备份的二进制日志内容全部恢复
• 基于位置恢复
  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作
  • 发生错误节点之前的一个节点，上一次正确操作的位置点停止
• 基于时间点恢复
  • 跳过某个发生错误的时间点实现数据恢复
  • 在错误时间点停止，在下一个正确时间点开始

一般恢复

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

基于时间点恢复

开启二进制日志文件

[root@server3 ~]# vi /etc/my.cnf
添加
log_bin=/usr/local/mysql/data/mysql_bin

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

查看二进制日志内容

[root@server3 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001
--no-defaults  #解决utf-8报错  --base64-output=decode-rows  #解决乱码问题  -v  #输出文件指向

创建数据库数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| bb                 |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
10 rows in set (0.00 sec)

mysql> use aaa;
Database changed
mysql> create table a(name varchar(128), score int(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values('zhang',10);
Query OK, 1 row affected (0.11 sec)

mysql> insert into a values('li',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+-------+-------+
| name  | score |
+-------+-------+
| zhang |    10 |
| li    |    30 |
+-------+-------+
2 rows in set (0.00 sec)

完全备份

[root@server3 aaa]# mysqldump -uroot aaa > /opt/aaa-a.sql
[root@server3 aaa]# cd /opt
[root@server3 opt]# ll
总用量 1404
-rw-r--r--. 1 root root    1810 12月 29 14:52 aaa-a.sql
-rw-r--r--. 1 root root 1431779 12月 28 14:56 mysql_all-2020-12-28.tar.gz
drwxr-xr-x. 3 root root      18 12月 28 16:16 mysql_old
drwxr-xr-x. 2 root root       6 3月  26 2015 rh
drwxr-xr-x. 3 root root      19 12月 28 16:15 usr
[root@server3 opt]# mysqladmin -uroot flush-logs  #刷新日志
[root@server3 opt]# cd /usr/local/mysql/data/
[root@server3 data]# ll
总用量 188468
drwxr-x---. 2 mysql mysql       46 12月 29 14:00 aaa
drwxr-x---. 2 mysql mysql       86 12月 24 00:15 auth
-rw-r-----. 1 mysql mysql       56 12月 23 22:41 auto.cnf
drwxr-x---. 2 mysql mysql       48 12月 29 09:11 bb
-rw-r-----. 1 mysql mysql      446 12月 29 13:46 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 12月 29 14:02 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 29 14:02 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 23 22:41 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 12月 29 14:52 ibtmp1
drwxr-x---. 2 mysql mysql       52 12月 25 16:24 info
drwxr-x---. 2 mysql mysql       20 12月 23 23:29 lb
drwxr-x---. 2 mysql mysql     4096 12月 23 22:41 mysql
-rw-r-----. 1 mysql mysql      903 12月 29 14:53 mysql_bin.000001
-rw-r-----. 1 mysql mysql      154 12月 29 14:53 mysql_bin.000002
-rw-r-----. 1 mysql mysql       78 12月 29 14:53 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 12月 23 22:41 performance_schema
drwxr-x---. 2 mysql mysql     4096 12月 26 16:11 qwe
drwxr-x---. 2 mysql mysql     8192 12月 23 22:41 sys

再次增量备份

mysql> insert into a values ('piao',5); #插入数据记录 正确操作
Query OK, 1 row affected (0.01 sec)

mysql> delete from a where name='zhang';  #删除数据记录，误操作
Query OK, 1 row affected (0.01 sec)

mysql> insert into a values('meng',50); #插入数据记录 正确操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------+-------+
| name | score |
+------+-------+
| li   |    30 |
| piao |     5 |
| meng |    50 |
+------+-------+
3 rows in set (0.00 sec)

查看二进制日志文件

[root@server3 ~]# mysqladmin -uroot flush-logs #刷新
[root@server3 ~]# cd /usr/local/mysql/data/
[root@server3 data]# ll

查看日志

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

查找到错误时间点停止

在下一个正确时间点开始

模拟数据损坏

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| bb                 |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
10 rows in set (0.00 sec)

mysql> drop database aaa;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bb                 |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
9 rows in set (0.00 sec)

mysql> create database aaa; #创建同名为空的数据库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| bb                 |
| info               |
| lb                 |
| mysql              |
| performance_schema |
| qwe                |
| sys                |
+--------------------+
10 rows in set (0.00 sec)

mysql> use aaa;
Database changed
mysql> source /opt/aaa-a.sql #还原完全备份的那个数据库
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
+-------+-------+
| name  | score |
+-------+-------+
| zhang |    10 |
| li    |    30 |
+-------+-------+
2 rows in set (0.00 sec)

恢复数据

[root@server3 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-12-29 15:03:35' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot ##停止在错误的时间点
[root@server3 ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-29 15:04:31' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot #开始在正确的时间点
[root@server3 ~]# mysql -uroot
mysql> use aaa;
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 a;
+-------+-------+
| name  | score |
+-------+-------+
| zhang |    10 |
| li    |    30 |
| piao  |     5 |
| meng  |    50 |
+-------+-------+
4 rows in set (0.00 sec)

基于位置的恢复

mysql> use aaa;
Database changed
mysql> select * from a;
+-------+-------+
| name  | score |
+-------+-------+
| zhang |    10 |
| li    |    30 |
| piao  |     5 |
| meng  |    50 |
+-------+-------+
4 rows in set (0.00 sec)

mysql> delete from a where name='li';
Query OK, 1 row affected (0.01 sec)

mysql> delete from a where name='piao';
Query OK, 1 row affected (0.00 sec)
[root@server3 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003  #查看日志

2226开始

2483开始


数据恢复

[root@server3 ~]# mysqlbinlog --no-defaults --stop-position='2226' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot
[root@server3 ~]# mysqlbinlog --no-defaults --stop-position='2483' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot

mysql> use aaa;
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 a;
+-------+-------+
| name  | score |
+-------+-------+
| zhang |    10 |
| li    |    30 |
| piao  |     5 |
| meng  |    50 |
+-------+-------+
4 rows in set (0.00 sec)