MySQL数据备份及恢复

目录

• 一、binlog日志恢复
  • 1.1 查看二进制日志功能是否打开
  • 1.2 开启二进制日志功能
  • 1.3 确认二进制日志功能已经开启
  • 1.4 执行增删改以便测试bin_log是否有记录
  • 1.5 MySQL中查看二进制日志文件
    • ① 查看二进制日志文件
    • ② 查看二进制日志文件内容
  • 1.6 通过二进制日志恢复数据
    • ① 第一步
    • ② 第二步
    • ③ 第三步
    • ④ 第四步
    • ⑤ 第五步
• 二、mysqldump备份工具
  • 2.1 备份一个表
  • 2.2 恢复mysql数据库中的user表
  • 2.3 备份mysql库
  • 2.4 恢复mysql库
  • 2.5 备份所有的库
  • 2.6 生产使用的备份命令
• 三、xtrabackup概述及安装
  • 3.1 xtrabackup概述
  • 3.2 xtrabackup安装
• 四、xtrabackup完全备份+binlog增量备份
  • 4.1 开启二进制日志
  • 4.2 创建备份所需目录
  • 4.3 创建备份用户
  • 4.4 完整备份
  • 4.5 查看备份后的文件
  • 4.6 通过二进制日志进行增量备份
  • 4.7 模拟数据丢失、恢复数据
  • 4.8 还原增量备份

MySQL备份一般采用全库备份加日志备份的方式，根据业务的需要，可以采用每周日凌晨1点进行完全备份以及每小时进行一次增量备份，这样在MySQL故障后可以使用完全备份和日志备份尽可能的去恢复最完整的数据。

一、binlog日志恢复

MySQL的二进制日志记录着该数据库所有增删改的操作日志（前提是需要自己开启binlog），还包括了这些操作的执行时间，binlog的使用场景无外乎就是主从同步及恢复数据库。开启binlog功能，需要编辑MySQL的主配置文件！

1.1 查看二进制日志功能是否打开

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
#OFF表示未开启

1.2 开启二进制日志功能

[root@db01 ~]# vim /etc/my.cnf
#在mysqld字段下写入下面配置
log-bin=/usr/local/mysql/data/bin-log
#指定二进制日志存放路径及二进制日志前缀
server_id=1
#如果要开启bin-log功能，需指定server_id，否则可能报错
[root@db01 ~]# systemctl restart mysqld
#开启bin-log功能需重启mysql服务
[root@db01 ~]# cd /usr/local/mysql/data/
[root@db01 data]# ll bin-log.*
-rw-r----- 1 mysql mysql 154 4月  16 20:35 bin-log.000001
#该文件为二进制日志文件，每次重启mysql或执行flush logs命令，就会产生一个新的二进制日志文件
-rw-r----- 1 mysql mysql  37 4月  16 20:35 bin-log.index
#二进制日志文件的索引

注意：开启二进制日志功能后，所有增删改的操作都会记录到二进制日志文件当中，注意，是增删改的操作，不包括查操作！

1.3 确认二进制日志功能已经开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
#ON表示已经开启二进制日志功能

1.4 执行增删改以便测试bin_log是否有记录

mysql> reset master;
#情况所有的二进制文件，从000001开始
mysql>  create database test_db1;
#创建一个数据库
mysql> use test_db1;
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
#创建一个表
mysql> insert into tb1(name) values ('zhangsan');
mysql> insert into tb1(name) values ('lisi');
#插入测试数据
mysql> flush logs;
#重新开始一个新的日志文件再执行操作。注意，此时上面所有的操作写入的是第一个二进制日志文件
mysql> delete from tb1 where name='lisi';
#删除第二条数据
mysql> insert into tb1(name) values ('tom');
#再插入一条新数据
#以上两条操作是为了写入第二个日志文件

1.5 MySQL中查看二进制日志文件

① 查看二进制日志文件

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |      1129 |
| bin-log.000002 |       693 |
+----------------+-----------+

② 查看二进制日志文件内容

完整的命令格式：

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
# in：指定要查看的二进制文件；
# from：指定从哪个“pos”位置开始查看
# limit：限制返回的行数，offset是指跳过多少行再显示

注：如果不指定二进制文件名，那么默认显示第一个二进制日志文件中的事件，文件内容中包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

mysql> show binlog events in 'bin-log.000001';
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                 |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| bin-log.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.29-log, Binlog ver: 4                                                |
| bin-log.000001 |  123 | Previous_gtids |         1 |         154 |                                                                                      |
| bin-log.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| bin-log.000001 |  219 | Query          |         1 |         325 | create database test_db1                                                             |
| bin-log.000001 |  325 | Anonymous_Gtid |         1 |         390 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| bin-log.000001 |  390 | Query          |         1 |         540 | use `test_db1`; create table tb1(id int primary key auto_increment,name varchar(20)) |
| bin-log.000001 |  540 | Anonymous_Gtid |         1 |         605 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| bin-log.000001 |  605 | Query          |         1 |         681 | BEGIN                                                                                |
| bin-log.000001 |  681 | Table_map      |         1 |         734 | table_id: 108 (test_db1.tb1)                                                         |
| bin-log.000001 |  734 | Write_rows     |         1 |         783 | table_id: 108 flags: STMT_END_F                                                      |
| bin-log.000001 |  783 | Xid            |         1 |         814 | COMMIT /* xid=13 */                                                                  |
| bin-log.000001 |  814 | Anonymous_Gtid |         1 |         879 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| bin-log.000001 |  879 | Query          |         1 |         955 | BEGIN                                                                                |
| bin-log.000001 |  955 | Table_map      |         1 |        1008 | table_id: 108 (test_db1.tb1)                                                         |
| bin-log.000001 | 1008 | Write_rows     |         1 |        1053 | table_id: 108 flags: STMT_END_F                                                      |
| bin-log.000001 | 1053 | Xid            |         1 |        1084 | COMMIT /* xid=14 */                                                                  |
| bin-log.000001 | 1084 | Rotate         |         1 |        1129 | bin-log.000002;pos=4                                                                 |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------+

• show master logs：也是查看二进制日志文件；
• purge binary logs：用于删除二进制文件；

举例：

purge binary logs to 'mysql-bin.00010'; 
#把这个文件之前的其他文件都删除掉
purge binary logs before '2016-08-28 22:46:26';
#把指定时间之前的二进制文件删除了

1.6 通过二进制日志恢复数据

假设在开始删除lisi记录的那条sql语句是误操作，现在要通过二进制日志来恢复数据。

① 第一步

首先需要找到删除lisi记录的sql语句在二进制日志中的位置，每条sql语句都是一个事务，所以需要从其begin到commit，才算是完整的sql语句。如下：

[root@db01 ~]# cd /usr/local/mysql/data/
[root@db01 data]# mysqlbinlog -v bin-log.000002
#查看二进制日志文件
# at 348     
#200416 22:36:13 server id 1  end_log_pos 393 CRC32 0x32b0e8ea 	Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
XW2YXhMBAAAANQAAAFwBAAAAAGwAAAAAAAEACHRlc3RfZGIxAAN0YjEAAgMPAjwAAlJf/qg=
XW2YXiABAAAALQAAAIkBAAAAAGwAAAAAAAEAAgAC//wCAAAABGxpc2nq6LAy
'/*!*/;
### DELETE FROM `test_db1`.`tb1`   #找到删除数据的SQL语句
### WHERE
###   @1=2
###   @2='lisi'
# at 393
#200416 22:36:13 server id 1  end_log_pos 424 CRC32 0x08192b48 	Xid = 16
COMMIT/*!*/;
# at 424
#可以看出，delete事件发生在position是348，事件结束是393

② 第二步

事件恢复流程：直接用bin-log日志将数据库恢复到删除位置219前，然后跳过故障点，再进行恢复下面所有的操作，具体恢复流程如下：

[root@db01 data]# mysqlbinlog bin-log.000001 > /tmp/01.sql
[root@db01 data]# mysqlbinlog --stop-position=348 bin-log.000002 > /tmp/348.sql
[root@db01 data]# mysqlbinlog --start-position=393 bin-log.000002 > /tmp/393.sql
#导出相关binlog文件（将二进制文件转换为sql语句生成新的文件）

上述指令中，第一条比较好理解，无非就是使用msyqlbinlog查看第一个二进制文件，并生成新文件，后面两条指令呢，--stop-postition意思是查看时到219这个位置不查看，一直到393才又开始接着查看。最后的结果就是新生成的文件中不会包含删除lisi记录的sql语句。

③ 第三步

模拟误操作！

mysql> drop database test_db1;
#删除数据库

④ 第四步

恢复数据！

[root@db01 data]# mysql -uroot -p123 < /tmp/01.sql 
#恢复第一个日志文件
[root@db01 data]# mysql -uroot -p123 < /tmp/348.sql 
#恢复第二个日志文件
[root@db01 data]# mysql -uroot -p123 < /tmp/393.sql 
#恢复第三个日志文件

⑤ 第五步

验证数据已经恢复！

mysql> select schema();
+----------+
| schema() |
+----------+
| test_db1 |
+----------+
#确认当前所在库
mysql> select * from tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | tom      |
+----+----------+
#验证数据

二、mysqldump备份工具

mysqldump是mysql用于备份和数据转移的一个工具。主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建数据库所需要的 SQL命令，如CREATE DATABASE、CREATE TABLE、INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作。

2.1 备份一个表

[root@db01 data]# mysqldump -uroot -p123 mysql user > /tmp/mysql-user.sql
 #备份mysql库中的user表
[root@db01 data]# ll /tmp/mysql-user.sql 
#确认备份文件
-rw-r--r-- 1 root root 5665 4月  16 23:06 /tmp/mysql-user.sql

2.2 恢复mysql数据库中的user表

[root@db01 data]# mysql -uroot -p123 mysql < /tmp/mysql-user.sql 

2.3 备份mysql库

[root@db01 data]# mysqldump -uroot -p123 --databases mysql > /tmp/mysql.sql
[root@db01 data]# ll /tmp/mysql.sql 
-rw-r--r-- 1 root root 1180261 4月  16 23:10 /tmp/mysql.sql

2.4 恢复mysql库

[root@db01 data]# mysql -uroot -p123 < /tmp/mysql.sql 

2.5 备份所有的库

当导出的数据量较大时，可以添加“--opt”选项以优化执行速度！

[root@db01 data]# mysqldump -uroot -p123 --opt --all-databases > /tmp/all-data.sql
[root@db01 data]# ll /opt/all-data.sql
-rw-r--r-- 1 root root 1896852 4月  16 23:13 /tmp/all-data.sql

2.6 生产使用的备份命令

[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /mnt/full.sql
#将数据库中的所有内容进行备份
# --master-data=2：备份时自动记录binlog信息便于恢复
# --single-transaction：减少锁表时间
# -R：备份存储过程
# -E：备份事件
# --triggers：备份触发器
# --max_allowed_packet=64M：要发送或接收的最大数据包长度

三、xtrabackup概述及安装

3.1 xtrabackup概述

Xtrabackup提供了两种命令行工具：

• xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；
• innobackupex：是一个perl脚本，在执行过程中会调用xtrabackup命令，这个命令即可以实现备份InnoDB，也可以备份Myisam引擎的对象；

xtrabackup是由percona提供的MySQL数据库备份工具，其备份速度快并且可靠；备份过程不会打断正在执行的事务；能够基于压缩等功能节约磁盘空间和流量；自动实现备份检验；还原速度快。

3.2 xtrabackup安装

[root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
[root@db01 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

xtrabackup中主要包含两个工具：

• xtrabackup：是用于热备份innodb，xtradb表中数据的工具，支持在线热备份，可以在不加锁的情况下备份innodb数据表，不过该工具不能操作myisam引擎表；
• innobackupex：是将xtrabackup进行封装的perl脚本，能同时处理innodb和myisam，但在处理myisam时需要加一个读锁，由于操作myisam时需要加读锁，所以会堵塞线上服务的写操作，而Innodb没有这样的限制；

四、xtrabackup完全备份+binlog增量备份

4.1 开启二进制日志

[root@db01 ~]# vim /etc/my.cnf
#在mysqld字段下写入下面配置
log-bin=/usr/local/mysql/data/bin-log
#指定二进制日志存放路径及二进制日志前缀
server_id=1
#如果要开启bin-log功能，需指定server_id，否则可能报错
[client]
socket=/usr/local/mysql/mysql.sock
#xtrabackup是服务器的客户端工具，连接数据库时需指定sock文件
[root@db01 ~]# systemctl restart mysqld
#开启bin-log功能需重启mysql服务

4.2 创建备份所需目录

[root@db01 ~]# mkdir -p /opt/mysqlbackup/{full,inc}
#full：全备存放的目录； inc：增量备份存放的目录

4.3 创建备份用户

[root@db01 ~]# mysql -uroot -p123
mysql> create user bakuser@'localhost' identified by '123';
mysql> revoke all privileges,grant option from 'bakuser'@'localhost';
mysql> grant reload,lock tables,replication client,process on *.* to bakuser@'localhost';
mysql> flush privileges;

4.4 完整备份

[root@db01 ~]# innobackupex --user=bakuser --password=123 /opt/mysqlbackup/full/
……………………
200417 11:20:45 completed OK!
#出现该信息表示备份成功
# --user：指定连接数据库的用户名；
# --password：指定连接数据库的密码；
# --defaults-file：指定数据库的配置文件my.cnf，innobackupex要从其中获取datadir等信息，如果不指定，则会默认去搜索my.cnf这个文件，搜索顺序和mysql启动时的搜索顺序一样；
# --database：指定要备份的数据据库，这里指定的数据库只对myisam表有效，对于innodb数据来说都是全备（所有数据库中的innodb数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；
# /opt/mysqlbackup/full：是备份文件的存放位置；

4.5 查看备份后的文件

[root@db01 ~]# ll /opt/mysqlbackup/full/2020-04-17_11-20-43/
#备份后目录为当前时间的年月日时分秒
总用量 12340
-rw-r----- 1 root root      487 4月  17 11:20 backup-my.cnf
#备份命令用到的配置选项信息
-rw-r----- 1 root root      314 4月  17 11:20 ib_buffer_pool
#buffer缓冲区相关的信息
-rw-r----- 1 root root 12582912 4月  17 11:20 ibdata1
drwxr-x--- 2 root root     4096 4月  17 11:20 mysql
drwxr-x--- 2 root root     8192 4月  17 11:20 performance_schema
drwxr-x--- 2 root root     8192 4月  17 11:20 sys
drwxr-x--- 2 root root       20 4月  17 11:20 test
drwxr-x--- 2 root root       20 4月  17 11:20 test01
drwxr-x--- 2 root root       20 4月  17 11:20 test02
-rw-r----- 1 root root       20 4月  17 11:20 xtrabackup_binlog_info
#二进制日志的截止位置
-rw-r----- 1 root root      113 4月  17 11:20 xtrabackup_checkpoints
#保存的是备份类型（如完全或增量）、备份状态（如是否已经为perpared状态（备份恢复前需要的状态）），和LSN（日志序列号）范围信息，每个innodb页（一般为16k大小）都会包含一个日志序列号，LSN时整个数据库系统同的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
-rw-r----- 1 root root      482 4月  17 11:20 xtrabackup_info
#备份指令相关的信息
-rw-r----- 1 root root     2560 4月  17 11:20 xtrabackup_logfile

如果只想让备份后的目录为年月日的格式，可以使用以下命令：

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /opt/mysqlbackup/full/full_`date +%F`
[root@db01 ~]# ll /opt/mysqlbackup/full/
总用量 0
drwxr-x--- 8 root root 263 4月  17 11:20 2020-04-17_11-20-43
drwxr-x--- 8 root root 263 4月  17 11:28 full_2020-04-17
#更改后的目录名

4.6 通过二进制日志进行增量备份

在进行增量备份前，需要先查看到完全备份时binlog日志位置（position），如下：

[root@db01 ~]# cat /opt/mysqlbackup/full/2020-04-17_11-20-43/xtrabackup_binlog_info 
bin-log.000001	1502
# 得到完全备份是备份到了bin_log.000001二进制日志中的1502的位置

通过二进制日志进行增量备份

在增量备份前，自行向数据库中进行增删改等操作，以便产生新的二进制日志。

[root@db01 ~]# mysqlbinlog --start-position=1502 /usr/local/mysql/data/bin-log.000001 > /opt/mysqlbackup/inc/`date +%F`.sql

4.7 模拟数据丢失、恢复数据

[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

还原完全备份的大概流程如下：
准备（prepare）一个完全备份，一般情况下，在备份完成后，数据还不能用于恢复操作，因为备份的数据中可能包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态，“准备”的主要作用就是通过回滚未提交的事务及同步已提交的事务至数据文件也使得数据文件处于一致性状态。在准备过程结束后，Innodb表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始的点。

通过--apply-log选项可用于实现上述功能，命令如下：

[root@db01 ~]# innobackupex --apply-log /opt/mysqlbackup/full/2020-04-17_11-20-43/
……………………
200417 11:38:22 completed OK!
#恢复成功
[root@db01 ~]# cat /opt/mysqlbackup/full/2020-04-17_11-20-43/xtrabackup_checkpoints 
backup_type = full-prepared
#当准备工作完成后，备份目录下的此文件内容中的备份类型会为：full-prepared
from_lsn = 0
to_lsn = 2629998
last_lsn = 2630007
compact = 0
recover_binlog_info = 0

在实现“准备”的过程中，innobackupex通常还可以使用“--user-memory”选项来指定其可以使用的内存大小，默认为100M，如果有足够的内存，可以多划分一些内存给prepare的过程，以提高其完成速度。

在准备工作完成后，即可使用以下命令进行恢复：

[root@db01 ~]#  innobackupex --copy-back /opt/mysqlbackup/full/2020-04-17_11-20-43/
……………………
200417 11:41:20 completed OK!
#表示恢复成功
[root@db01 ~]# ll /usr/local/mysql/data/
总用量 122920
-rw-r----- 1 root root      314 4月  17 11:41 ib_buffer_pool
-rw-r----- 1 root root 12582912 4月  17 11:41 ibdata1
-rw-r----- 1 root root 50331648 4月  17 11:41 ib_logfile0
-rw-r----- 1 root root 50331648 4月  17 11:41 ib_logfile1
-rw-r----- 1 root root 12582912 4月  17 11:41 ibtmp1
drwxr-x--- 2 root root     4096 4月  17 11:41 mysql
drwxr-x--- 2 root root     8192 4月  17 11:41 performance_schema
drwxr-x--- 2 root root     8192 4月  17 11:41 sys
drwxr-x--- 2 root root       20 4月  17 11:41 test
drwxr-x--- 2 root root       20 4月  17 11:41 test01
drwxr-x--- 2 root root       20 4月  17 11:41 test02
-rw-r----- 1 root root      482 4月  17 11:41 xtrabackup_info
-rw-r----- 1 root root        1 4月  17 11:41 xtrabackup_master_key_id
#确认数据已经恢复，但是用户都是root
[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/
#更改文件所属权限
[root@db01 ~]# systemctl start mysqld
#如果丢失时，没有关闭数据库，那么在数据恢复后，需要重启服务器，否则数据不统一

4.8 还原增量备份

为了防止还原时产生大量的二进制日志，在还原时最好临时关闭二进制日志，如下：

[root@db01 ~]# mysql -uroot -p123 -e 'set sql_log_bin=0;'
#临时关闭二进制日志
[root@db01 ~]# mysql -uroot -p123 < /opt/mysqlbackup/inc/2020-04-17.sql 
#恢复二进制日志
[root@db01 ~]# mysql -uroot -p123 -e 'set sql_log_bin=1;'
#开启二进制日志
[root@db01 ~]# mysql -uroot -p123 -e 'select * from test.t1;'
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
#确认数据已经被恢复