Mysql/Mariadb备份(xtrabackup)还原实战

有运维或运维开发方面的需求,可以联系博主QQ 452336092或Email:admin#centos.bz(收费)

文章目录
[隐藏]

一、概述

之前的文章说到mysql的安装与mysql的基本使用;本文是后续补充,主要说明针对mysql或mariadb的备份与还原;众所周知,数据是重中之重,因此平时对企业数据需要做备份,当数据系统崩溃,数据丢失异常时,才能依据备份文件进行恢复!

本次的环境:
CentOS7.4_x64 , mysql5.7.21, xtrabackup

mysql的安装配置可参考之前系列文章;只补充相关配置项的开启;以及xtrabackup安装使用;
用到的演示数据导入mysql数据库

[root@db ~]# mysql -uroot -predhat < testdb.sql
或
mysql> source   testdb.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mysql              |
| performance_schema |
| study              |
| sys                |
以上study即为测试数据库包含以下测试表

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class           |
| course          |
| part            |
| score           |
| student         |
| tb31            |
| tb32            |
| teacher         |
| test1           |
| test2           |
| user_info       |
+-----------------+

测试数据库及数据表准备完成,在进行数据的备份与恢复前,我们先简单了解下数据库备份与恢复的相关概念原理;

关于数据库的备份与还原

为什么备份?
主要是为了灾难恢复如:硬件故障(冗余)、软件故障(bug)、自然灾害、黑客攻击、误操作、以及测试需要导出数据等;
还原或叫恢复时即基于以往的备份文件;

备份类型

全量备份、增量备份、差异备份:
完全备份: 备份数据的副本(某时间点);
增量备份:仅备份自上一次完全备份或 增量备份以来变量的那部数据;
差异备份:仅备份自上一次完全备份以来变量的那部数据;

物理备份、逻辑备份:
物理备份:复制数据文件进行的备份;
逻辑备份:从数据库导出数据另存在一个或多个文件中;

根据数据服务是否在线:
热备:读写操作均可进行的状态下所做的备份;
温备:可读但不可写状态下进行的备份;
冷备:读写操作均不可进行的状态下所做的备份
以上的各备份类型备份执行时只能备份数据在备份时的状态,如想要恢复数据库崩溃那一刻的状态,需要打开binary log功能,需要基于备份的数据+binary log来恢复到数据崩溃前一刻的状态;
备份的工具有mysqldump(温备,不适合大型数据的在线备份),xtrabackup(支持对InnoDB热备,开源专业的备份数据,支持mysql/mariadb)本文将通过mysqldump与xtrabackup来说明数据的备份与恢复(异地);

无论那种工具备份,在恢复时均要binary log才能恢复到崩溃前的状态;因此需要配置数据库开启binary log功能;以下能mysql5.7.21

#cat /usr/local/mysql/etc/my.cnf
server-id       = 1
log_bin         = /data1/mysqldb/mysql-bin.log

二、mysqldump备份与恢复

mysqldump使用说明

单进程逻辑备份、完全备份、部分备份;

Usage: 
mysqldump [OPTIONS] database [tables]
         OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
         OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump mydb:表级别备份(还原时库需要存在)
mysqldump --databases mydb:库级别备份(库不在会自行创建库)

MyISAM存储引擎:支持温备,备份时要锁定表;
         -x, --lock-all-tables:锁定所有库的所有表,读锁;
         -l, --lock-tables:锁定指定库所有表;

InnoDB存储引擎:支持温备和热备;
         -x, --lock-all-tables:锁定所有库的所有表,读锁;
         -l, --lock-tables:锁定指定库所有表;
        --single-transaction:创建一个事务,基于此快照执行备份;
                -R, --routines:存储过程和存储函数;
                --triggers      触发器
                -E, --events      事件

                 --master-data[=#]
                        1:记录为CHANGE MASTER TO语句,此语句不被注释;
                        2:记录为CHANGE MASTER TO语句,此语句被注释;

                --flush-logs:锁定表完成后,即进行日志刷新操作(重新生成binlog日志);

基于mysqldump备份study数据库

热备,备份存储过程和存储函数,事件,并记得下事件位置;(便于从binlog中的位置开始恢复到故障前)
#mysqldump -uroot -predhat --single-transaction  -R -E --triggers --master-data=2 --databases study >/home/san/studydb.sql

说明:

less studydb.sql

会看到以下内容

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;

这就是–master-data=2 选项作用,注释了,binary log 点在154

模拟备份后数据修改操作

修改前的:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
增加一条:
mysql> insert into user_info values(13,'hi',18,'男',4);
Query OK, 1 row affected (0.03 sec)
删除一条:
mysql> delete  from user_info where nid=1;
Query OK, 1 row affected (0.01 sec)
最终在上次备份后user_info数据如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
可以看出少了一条,加了一条;

模拟数据库损坏并恢复study数据库

关闭mysql并到数据目录删除study数据库;

假设发现study数据已经丢失了;

数据库运行正常;查看binlog位置
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       815 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |   1890875 |
| mysql-bin.000005 |       725 |
+------------------+-----------+
记住这里最后一个binlog文件及位置是mysql-bin.000005   725
结合上面备份文件中的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154; 可以分析出备份时位置是154而数据库丢失前是725
因此我们恢复study数据库里需要恢复上次的全备+加mysql-bin.000005中的154-725内容;

模拟study丢失过程(传说溃的删库路)
[root@db mysqldb]# service stop mysqld
[root@db mysqldb]# pwd
/data1/mysqldb
[root@db mysqldb]# rm -rf  study/
启动数据库
[root@db mysqldb]# service stop mysqld
登录数据库并查看发现study数据库已经丢失了

还原数据库

mysql -uroot -predhat < studydb.sql
mysql> show databases;
可发现已经恢复;但是之前完整备份的到崩溃前的修改不见了;如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

结合binlog恢复:

从binlog上导出sql文件
[root@db mysqldb]# mysqlbinlog mysql-bin.000005 >/root/binlog.sql
登录mysql恢复
恢复过程中临时关闭binlog记录
mysql> set @@session.sql_log_bin=OFF;
mysql> source binlog.sql;
Query OK, 0 rows affected (0.00 sec

mysql> set @@session.sql_log_bin=ON;
mysql> use study; 
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

可以看出study数据库已经恢复到崩溃损坏前的状态;另外完全 可以新准备一台数据库服务器;把sql转移到新机器上恢复;前提数据配置参数需要一样;

三、xtrabackup备份与恢复

xtrabackup简介

xtrabackup是Percona一款开源工具,支持innodb,Xtradb(mariadb)引擎数据库的热备;
对MyISAM:温备,不支持增量备份;InnoDB:热备,增量;
物理备份,速率快、可靠;备份完成后自动校验备份结果集是否可用;还原速度快
功能介绍与Innobackup(mysql企业版收费)对比参考官网
所数据库引擎请使用innodb引擎

xtrabackup安装与使用说明

安装
[官方下载地址](https://www.percona.com/downloads/XtraBackup/LATEST/)
本次使用percona-xtrabackup-24-2.4.8-1
[root@db ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@db ~]# yum install ./percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm -y

**获取帮助与使用:**
可以通过man  xtrabackup 获取详细使用说明与实例
Usage: 
 innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
 备份用到的主要选项:
    --defaults-file=     #mysql或mariadb配置文件
        --user=                  #备份时使用的用户(对备份的数据库有备份权限)
        --password=         #备份用户密码
        -H | --host=            #localhost或远程主机

**恢复时到的主要选项:**
        --apply-log         #分析获取binary log文件生成backup_binlog_info文件
        ---copy-back      #基于backup_binlog_info等文件恢复
注:innobackupex是xtrabackup的软件链接;

xtrabackup全备与恢复:

注意:备份时数据库是在线状态;恢复时需要离线并且mysql数据目录为空;
备份:

创建备份目录 
mkdir -pv /data/backup
创建备份授权账号root(可以是其他用户最小权限)
mysql> GRANT ALL ON *.* TO 'root'@'127.0.0.1' identified by "redhat";
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@db mysqldb]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
看到类似如下信息表示备份成功:
xtrabackup: Transaction log of lsn (7701576) to (7701585) was copied.
180401 11:52:35 completed OK!
同时在/data/backup目录中产生以时间为目录的备份目录
[root@db backup]# ll /data/backup/
drwxr-x--- 14 root root 4096 4月   1 11:52 2018-04-01_11-52-29

备份后对数据库study 中的表进行修改

删除student表
mysql> drop table student;
Query OK, 0 rows affected (0.04 sec)
往user_info表中插入两行
mysql> insert into user_info values(1,"san",18,"男",4),(14,"Hello",28,"女",2);
Query OK, 1 row affected (0.00 sec)

模拟数据库崩溃

注意binlog文件备份好;如果binglog和数据目录在一起
[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

恢复数据:

切换到备份数据目录
[root@db backup]# cd /data/backup/2018-04-01_11-52-29
事务回滚不提交
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log ./
类似以下提示表示完成:
InnoDB: Shutdown completed; log sequence number 7702056
180401 12:13:40 completed OK!

数据还原

由于centos7默认有/etc/my.cnf文件
因此需要重命名my.cnf或移除以免影响恢复;
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back ./
类似以下提示表示恢复完成:
180401 12:16:15 [01]        ...done
180401 12:16:15 completed OK!

恢复binlog中信息

查看全备中的binlog信息(文件和位置)

[root@db backup]# cat /data/backup/2018-04-01_11-52-29/xtrabackup_binlog_info
mysql-bin.000008        14775
由引可知在上次全备时的binglog文件是mysql-bin.000008位置为14775
获取binlog信息
[root@db backup]# mysqlbinlog -j 14775 mysql-bin.000008 >/data/backup/binlog.sql
还原binlog中的内容(全备后的修改数据内容)
切换到mysql数据目录(/data1/mysqldb)并修改权限
[root@db mysqldb]# cd /data1/mysqldb
[root@db mysqldb]# chown mysql.mysql *  -R
启动mysql
[root@db mysqldb]# service mysqld start

登录数据库并导入binlog.sql

mysql> source /data/backup/binlog.sql
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

xtrabackup 增量备份与恢复

备份流程:
首次增量备份是基于完整备份后做的增量备份 ,后面的增量备份将基于前一次增量备份;
恢复流程:
合并完整备份事务 –>再合并第一次增量的事务–>….最后一次增量备份 +binlog日志

完整备份:

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7802468) to (7802477) was copied.
180401 13:13:13 completed OK!
[root@db ~# ll /data/backup
2018-04-01_13-13-10    ######完整备份目录

模拟数据库的修改操作

删除第10行并新增一行
mysql> delete from user_info where nid=10;
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info value(15,'hehe',22,'男',1);
Query OK, 1 row affected (0.01 sec)

第一次增量备份

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-13-10/
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7803424) to (7803433) was copied.
180401 13:17:26 completed OK!

再次模拟数据库的修改操作

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
13 rows in set (0.01 sec)
插入一行再删除一行
mysql> insert into user_info value(16,'haha',21,'女',3);
Query OK, 1 row affected (0.01 sec)

mysql> delete from user_info where nid=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
|  16 | haha  |   21 | 女     |        3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

第二次增量备份:

[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-17-21/
注意:这里的 --incremental-basedir=/data/backup/2018-04-01_13-17-21/ 是上一次增量备份 产生的备份 目录 
如果基于第一次完整备份 则成为差异备份 

找出最近一次增量备份的binlog文件及信息

cd /data/backup/2018-04-01_13-21-56
[root@db 2018-04-01_13-21-56]# cat xtrabackup_binlog_info 
mysql-bin.000001    17452
备份 mysql-bin.000001 到/data/backup中
[root@db backup]# cd /data/backup
[root@db backup]# cp /data1/mysqldb/mysql-bin.000001 .
[root@db backup]# mysqlbinlog mysql-bin.000001 >binlog.sql

模拟数据库崩溃数据丢失

[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

数据恢复

[root@n1 backup]# ls
2018-04-01_13-13-10     2018-04-01_13-17-21    2018-04-01_13-21-56     binlog.sql    mysql-bin.000001

依次是完全整备份 ,第一次和第二次增量备份 目录 ,以及备份出来的binlog文件与binlog.sql
恢复过程:
首先对第1个(完整备份)合并只提交事务不回滚 再把第2个目录合并提交事务不回滚到第一个,再把第3个合并到第1个中;最后做一次回滚,再做统一事务提交;最后再加binlog恢复

完整备份 的事务合并
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/
合并第一次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-17-21/
合并第二次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-21-56/
合并所有的事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --apply-log 2018-04-01_13-13-10/
提交还原事务
[root@db backup] innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --copy-back 2018-04-01_13-13-10/

修改还原数据权限与启动数据库:

[root@db backup]chown mysq.mysql /data1/mysqldb -R
[root@db backup] systemctl start mysqld

binlog事务恢复

mysql> source /data/backup/binlog.sql
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name | age | gender | part_nid |
+-----+-------+------+--------+----------+
| 1 | san | 18 | 男 | 4 |
| 4 | Ling | 28 | 男 | 4 |
| 5 | ling | 28 | 男 | 3 |
| 6 | dong | 30 | 男 | 1 |
| 7 | b | 11 | 女 | 1 |
| 8 | c | 12 | 女 | 1 |
| 9 | d | 18 | 女 | 4 |
| 11 | f | 23 | 男 | 2 |
| 12 | dongy | 22 | 男 | 1 |
| 13 | hi | 18 | 男 | 4 |
| 14 | Hello | 28 | 女 | 2 |
| 15 | hehe | 22 | 男 | 1 |
| 16 | haha | 21 | 女 | 3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

到此增量备份 与恢复 已经 完成!

总结

日常数据库的备份是十分有必要的,而且不管用什么方法恢复,开启binary log十分重要,否则恢复不完整;binary log最好不要和数据目录一起,另外建议数据目录和binary log所在目录不要放在同一块物理磁盘;同时需要计划备份并实现异地备份;这样出现删库跑或崩溃数据丢失时就不怕了!本文很多步骤,可能存在遗漏之处,如有错误之处,欢迎指点。

原文出处:51cto -> http://blog.51cto.com/dyc2005/2093514

打赏

如果此文对你有所帮助,请随意打赏鼓励作者^_^