之前的文章说到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使用说明
单进程逻辑备份、完全备份、部分备份；
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是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所在目录不要放在同一块物理磁盘;同时需要计划备份并实现异地备份;这样出现删库跑或崩溃数据丢失时就不怕了!本文很多步骤,可能存在遗漏之处,如有错误之处,欢迎指点。