一概述
之前的文章说到 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 企业版收费)对比参考官网 https://www.percona.com/software/mysql-database/percona-xtrabackup/feature-comparison
所数据库引擎请使用 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 所在目录不要放在同一块物理磁盘; 同时需要计划备份并实现异地备份; 这样出现删库跑或崩溃数据丢失时就不怕了! 本文很多步骤, 可能存在遗漏之处, 如有错误之处, 欢迎指点;
来源: http://blog.51cto.com/dyc2005/2093514