MySQL备份与恢复

一、数据库备份的类型


按照服务器的运行状态,可分为:冷备、热备、温备。

  • 冷备:数据库处于停机状态下进行备份。

  • 热备:数据库处于运行状态下备份,在热备期间,数据库的读写操作均可正常进行。

    ​ 在mysql中,MyISAM存储引擎不支持热备,InnoDB存储引擎支持热备。

  • 温备:数据库处于运行状态下备份,但是在温备期间数据库只能进行读操作,不能进行写操作


按照备份后的内容量,可分为:全备、增备、差异备份。

  • 全量备份:对所有数据进行一个完全的备份。
  • 增备备份:对上一次"备份"以后变化的数据的备份。
  • 差异备份:对上一次"全量备份"以后变化的数据的备份。

按照备份的方式,可分为:逻辑备份、物理备份。

  • 逻辑备份:将数据从数据库中导出,并将导出的数据进行存档备份。

  • 物理备份:直接备份数据库所对应的数据文件。物理备份相对逻辑备份来说,性能更强。

二、数据库备份方式

在数据库备份当中,一般会采用逻辑备份、物理备份这两种方式对数据库进行备份。

2.1 逻辑备份

基于SQL语句进行备份。

常见得逻辑备份方式:

  • 以binlog方式进行备份

  • 使用mysqldump备份工具备份

  • replication(数据库主从)

  • select into outfile (使用SQL语句导出数据到文件)

2.2 物理备份

基于数据文件的备份。

常见物理备份方式:

  • 使用xtrabackup备份工具备份

三、MySQL主流备份工具之mysqldump

mysqldump是MySQL自带的免费备份工具,是一种逻辑备份工具。

在MySQL 5.7中新增了一个mysqldump的改进版备份工具mysqlpump,它支持基于表级别的并行备份。

3.1 备份方法

  • 备份单表或者一组指定的库表
mysqldump [options] db_name [tbl_name ...]
  • 备份一组或者一个数据库下的所有表
mysqldump [options] --databases db_name ...
  • 备份整个数据库实例
mysqldump [options] --all-databases

3.2 mysqldump常用命令行选项

  • -A,--all-databases:备份所有数据库下的所有表。

  • -B,--databases:备份指定数据库下的所有表。

  • --master-data[=value]:则在备份文件中会生成一 条CHANGE MASTER TO语句(包含binlog pos,binlog file)

    • value=2:备份文件中生成的CHANGE MASTER TO语句将被注释
    • value=1:该语句不会被注释
    • value=0:不记录CHANGE MASTER TO语句
  • -F,--flush-logs:在开始备份之前刷新MySQL二进制日志文件。

  • --triggers:在备份文件中包含每个备份表的触发器。

  • -E,--events:在备份数据中包含数据库的事件。

  • --single-transaction:在备份数据之前开启一个事务快照,然后在事务快照内进行备份。

    • 结合--master-data实现热备
  • -d,--no-data:不备份表数据,只备份表结构。

  • -t,--no-create-info:只备份数据,不备份表结构。

  • -R,--routines:备份数据中存储过程和函数。

3.3 mysqldump备份注意

  • mysqldump在备份和恢复时都需要MySQL实例启动为前提
  • 一般数据量级100G以内,大约15-30分钟可以恢复(TB、PB、EB就需要考虑别的方式)
  • mysqldump是以覆盖的形式恢复数据的

3.4 实战:模拟完全备份和恢复

在备份源数据库中插入一些数据

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | tset1 |
|  2 | test2 |
|  3 | test3 |
|  4 | test4 |
|  5 | test5 |
|  6 | test6 |
+----+-------+
6 rows in set (0.00 sec)

mysql> insert into test(name) values("tset7"),("test8"),("test9"),("test10");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
......
|  7 | test7  |
|  8 | test8  |
|  9 | test9  |
| 10 | test10 |
+----+--------+
10 rows in set (0.00 sec)

在远程备份服务器中使用mysqldump远程备份数据:

[root@db02 ~]# mkdir /data/backup/mysql/mysqldump -p
[root@db02 ~]# cd /data/backup/mysql/mysqldump/
[root@db02 mysqldump]# mysqldump -h 172.16.1.51 -uroot -p123 --single-transaction \
--master-data=2 --triggers --events -R -A > backup_`data +%F_%H_%M_%S`.sql
[root@db02 mysqldump]# ls -lh
total 347M
-rw-r--r-- 1 root root 347M Jun 19 23:37 backup_2020-06-19_23_37_03.sql

查看备份文件中的binlog pos位置、binlog文件:

[root@db02 mysqldump]# head -100 backup_2020-06-19_23_37_03.sql | grep -i CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=911;

把备份文件导入恢复服务器的数据库实例中:

[root@db01 mysqldump]# mysql -uroot -p123 -e "reset master;"
[root@db01 mysqldump]# mysql -uroot -p123 < backup_2020-06-19_23_37_03.sql

登录恢复服务器中的数据库,校验数据:

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | tset1  |
|  2 | test2  |
|  3 | test3  |
|  4 | test4  |
|  5 | test5  |
|  6 | test6  |
|  7 | tset7  |
|  8 | test8  |
|  9 | test9  |
| 10 | test10 |
+----+--------+
10 rows in set (0.01 sec)

四、MySQL主流备份工具之XtraBackup

XtraBackup是一款开源的热备份软件(物理备份),它可以非阻塞地对InnoDB和XtraDB数据库进行备份。

XtraBackup的出现,解决了mysqldump在备份数据量大的数据库时,备份和恢复的时间高开销的问题。

4.1 XtraBackup的都优点

  • 快速可靠地完成备份。
  • 在备份期间不间断地处理事务。
  • 节省磁盘空间和网络带宽。
  • 自动备份验证。
  • 更快地恢复,以保障业务有更长的在线时间。

4.2 备份方式(物理备份)

  • 对于非innodb表(比如myisam)是直接锁表,cp数据文件,属于一种温备。
  • 对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
  • 备份时读取配置文件/etc/my.cnf

4.3 下载安装XtraBackup软件

  • 安装依赖
yum install -y perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-Digest-MD5 perl-Digest libev rsync
  • 安装XtraBackup
yum install -y https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm 

4.4 innobackupex命令常用选项

  • --user=USER:备份服务器数据库用户名
  • --password=PASSWORD:备份服务器数据库密码
  • --host=HOST:备份服务器的IP
  • --port=PORT:备份服务器的端口号
  • --no-timestamp:不在指定备份目录下创建以时间戳命名的目录
  • --defaults-file:指定默认的my.cnf配置文件
  • --apply-log:在指定备份目录下执行应用xtrabackup_logfile文件中的事务日志操作,同时根据配置文件中的innodb_log_file_size系统参数生成新的事务日志
  • --redo-only:针对未提交的事务不执行回滚操作,结合--apply-log一起使用
  • --copy-back:会从指定目录中读取数据文件,并复制到目标数据库的数据目录中
  • --incremental:告诉xtrabackup创建一个增量备份,而不是一个完整的备份
  • --incremental-basedir=DIRECTORY:与--incremental一同使用,指定完全备份或上一次增量备份的路径
  • --incremental-dir=DIRECTORY:项用于在应用redo日志时指定一个增量备份目录

4.5 实战:模拟完全备份与恢复

使用innobackupex命令完全备份数据库文件,并将备份文件放在/data/backup/mysql/test_backup目录下。

[root@db01 ~]# mkdir -p /data/backup/mysql/test_backup
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=123 \
--no-timestamp /data/backup/mysql/test_backup/
......
200621 21:18:48 completed OK!     #看到类似的输出信息,则表示备份成功!

对备份目录执行--apply-log操作:

[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /data/backup/mysql/test_backup/
......
200621 21:22:06 completed OK!     #看到类似的输出信息,则表示--apply-log操作执行成功!

现在模拟数据库数据丢失情况。即先停止数据库,清空数据目录。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

恢复数据,对备份目录执行--copy-back操作,把备份文件复制到之前清空的数据目录下:

[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/mysql/test_backup/
......
200621 21:51:42 completed OK!     #看到类似的输出信息,表示--copy-back操作执行成功!  

修改数据目录的属主、属组,启动数据库:

[root@db01 ~]# chown -R mysql:mysql /usr/local/mysql/data
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库,校验数据是否正确:

[root@db01 ~]# mysql -uroot -p123 -e "show databases;"

4.6 实战:模拟增量备份与恢复

增量备份特性

  • 基于上一次备份进行增量
  • 增量备份无法单独恢复,必须基于全备进行恢复
  • 所有增量必须要按顺序合并到全备当中

1.完全备份和增量备份

使用innobackupex命令给数据库进行一次完全备份,备份目录到backup-$(date +%F)

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /data/backup/mysql/backup-$(date +%F)
......
200622 18:38:04 completed OK!

完全备份成功后,进入数据库新建库、表并插入一些数据(制造增量数据):

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb1(id) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

进行第一次增备备份。第一次增量备份的basedir是完全备份,因为在第一 次增量备份之前的最近一次备份只有完全备份。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/backup-2020-06-22/ --incremental /data/backup/mysql/incremental_one
......
200622 18:54:29 completed OK!

继续制造测试数据:

mysql> create table tb2(id int,name varchar(10));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into tb2(id,name) values(1,'name1'),(2,'name2'),(3,'name3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb2;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
+------+-------+
3 rows in set (0.00 sec)

进行第二次增量备份。第二次增量备份就不需要再基于完全备份了,因为最近一次备份是第一次增量备份,这里第二次增量备份的basedir是基于第一次增量备份的。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/incremental_one/ --incremental /data/backup/mysql/incremental_two
......
200622 19:06:28 completed OK!

2.增量备份恢复

在恢复数据前,先停止数据库并清空相关数据目录(建议先备份在情况)。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

进入完全备份目录,执行--apply-log操作需要带上--redo-only选项,只应用完成redo日志,不对未提交的事务执行回滚操作。

[root@db01 ~]# cd /data/backup/mysql/backup-2020-06-22
[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only ./
......
200622 19:54:00 completed OK!

在完全备份的基础上执行第一次增量备份--apply-log操作(带上--redo-only)

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_one/
......
200622 19:58:29 completed OK!

在执行--apply-log操作的第一次增量备份的完全备份目录中,再对第二次增量备份执行--apply-log操作,因为这是最后一次增量备份,所以不需要加--redo-only选项。

[root@db01 backup-2020-06-22]# innobackupex --apply-log /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_two/
......
200622 20:02:57 completed OK!

最后,在对完全备份目录执行一次--apply-log操作,生成redo log文件。

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/
......
200622 20:38:34 completed OK!

[root@db01 backup-2020-06-22]# ll -h
total 243M
-rw-r----- 1 root root  430 Jun 22 20:33 backup-my.cnf
drwxr-x--- 2 root root   70 Jun 22 20:37 binlog
drwxr-x--- 2 root root 4.0K Jun 22 20:37 db_test1
drwxr-x--- 2 root root   20 Jun 22 20:37 db_test3
-rw-r----- 1 root root  76M Jun 22 20:38 ibdata1
-rw-r----- 1 root root  50M Jun 22 20:33 ibdata2
-rw-r----- 1 root root  48M Jun 22 20:38 ib_logfile0
-rw-r----- 1 root root  48M Jun 22 20:38 ib_logfile1
-rw-r----- 1 root root  12M Jun 22 20:37 ibtmp1
drwxr-x--- 2 root root 4.0K Jun 22 20:37 mysql
drwxr-x--- 2 root root 4.0K Jun 22 20:37 performance_schema
drwxr-x--- 2 root root   60 Jun 22 20:37 test
drwxr-x--- 2 root root   80 Jun 22 20:37 test1
drwxr-x--- 2 root root  176 Jun 22 20:37 world
-rw-r----- 1 root root   27 Jun 22 20:37 xtrabackup_binlog_info
-rw-r--r-- 1 root root   27 Jun 22 20:38 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root  117 Jun 22 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root  595 Jun 22 20:37 xtrabackup_info
-rw-r----- 1 root root 8.0M Jun 22 20:35 xtrabackup_logfile

将完全备份目录下的数据文件复制到相关目录下:

[root@db01 backup-2020-06-22]# innobackupex --copy-back ./
......
200622 20:40:22 completed OK!

修改数据目录属主并启动MySQL:

[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库,效验数据。

[root@db01 ~]# mysql -uroot -p123 -e "use test1;select * from tb1,tb2;"
点赞
  1. 拾贰说道:
    Google Chrome Windows 10
    差评!!! :hehe:

发表回复