超哥 Mysql/Mariadb备份(xtrabackup)还原实战
文章目录
[隐藏]
- 一、概述
- 二、mysqldump备份与恢复
- 三、xtrabackup备份与恢复
- 总结
一、概述
之前的文章说到mysql的安装与mysql的基本使用;本文是后续补充,主要说明针对mysql或mariadb的备份与还原;众所周知,数据是重中之重,因此平时对企业数据需要做备份,当数据系统崩溃,数据丢失异常时,才能依据备份文件进行恢复!
本次的环境:
CentOS7.4_x64 , mysql5.7.21, xtrabackup
mysql的安装配置可参考之前系列文章;只补充相关配置项的开启;以及xtrabackup安装使用;
用到的演示数据导入mysql数据库
[[email protected] ~]# 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 n 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丢失过程(传说溃的删库路) [[email protected] mysqldb]# service stop mysqld [[email protected] mysqldb]# pwd /data1/mysqldb [[email protected] mysqldb]# rm -rf study/ 启动数据库 [[email protected] 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文件 [[email protected] 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 [[email protected] ~]# 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 [[email protected] ~]# 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) [[email protected] 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目录中产生以时间为目录的备份目录 [[email protected] 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和数据目录在一起 [[email protected] backup]# service mysqld stop [[email protected] backup]# rm -rf /data1/mysqldb/* 恢复数据:
切换到备份数据目录 [[email protected] backup]# cd /data/backup/2018-04-01_11-52-29 事务回滚不提交 [[email protected] 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或移除以免影响恢复; [[email protected] 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信息(文件和位置)
[[email protected] backup]# cat /data/backup/2018-04-01_11-52-29/xtrabackup_binlog_info mysql-bin.000008 14775 由引可知在上次全备时的binglog文件是mysql-bin.000008位置为14775 获取binlog信息 [[email protected] backup]# mysqlbinlog -j 14775 mysql-bin.000008 >/data/backup/binlog.sql 还原binlog中的内容(全备后的修改数据内容) 切换到mysql数据目录(/data1/mysqldb)并修改权限 [[email protected] mysqldb]# cd /data1/mysqldb [[email protected] mysqldb]# chown mysql.mysql * -R 启动mysql [[email protected] 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日志
完整备份:
[[email protected] ~# 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! [r[email protected] ~# ll /data/backup 2018-04-01_13-13-10 ######完整备份目录 模拟数据库的修改操作
删除第10行并新增一行 mysql> delete from user_info where nhehe',22,'男',1); Query OK, 1 row affected (0.01 sec) 第一次增量备份
[[email protected] ~# 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 n 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) 第二次增量备份:
[[email protected] 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 [[email protected] 2018-04-01_13-21-56]# cat xtrabackup_binlog_info mysql-bin.000001 17452 备份 mysql-bin.000001 到/data/backup中 [[email protected] backup]# cd /data/backup [[email protected] backup]# cp /data1/mysqldb/mysql-bin.000001 . [[email protected] backup]# mysqlbinlog mysql-bin.000001 >binlog.sql 模拟数据库崩溃数据丢失
[[email protected] backup]# service mysqld stop [[email protected] backup]# rm -rf /data1/mysqldb/* 数据恢复
[[email protected] 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恢复
完整备份 的事务合并 [[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ 合并第一次增量事务 [[email protected] 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/ 合并第二次增量事务 [[email protected] 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/ 合并所有的事务 [[email protected] backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log 2018-04-01_13-13-10/ 提交还原事务 [[email protected] backup] innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back 2018-04-01_13-13-10/ 修改还原数据权限与启动数据库:
[[email protected] backup]chown mysq.mysql /data1/mysqldb -R [[email protected] 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
本站所发布的一切资源仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。本站信息来自网络，版权争议与本站无关。您必须在下载后的24个小时之内，从您的电脑中彻底删除上述内容。如果您喜欢该程序，请支持正版软件，购买注册，得到更好的正版服务。如果侵犯你的利益，请发送邮箱到 [email protected]，我们会很快的为您处理。
