一. 数据备份基础知识
1. 备份：存储的数据副本；
原始数据：持续改变；
恢复：把副本应用到线上系统；
仅能恢复至备份操作时刻的数据状态；
2. 基于时间点的数据恢复:
即基于 binary logs的数据数据,可以根据指定时间点以后发生的事务来恢复数据
3. 为什么备份？
灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、…
测试；
4. 备份时应该注意事项：
能容忍最多丢失多少数据；
恢复数据需要在多长时间内完成；
需要恢复哪些数据；
5. 数据的恢复与备份都应该做恢复演练：
测试备份的可用性；
增强恢复操作效率；
…
6. 备份类型：
备份的数据的集范围：
完全备份和部分备份
完全备份：整个数据集；
部分备份：数据集的一部分，比如部分表；
7. 完全备份、增量备份、差异备份：
完全备份
增量备份：仅备份自上一次完全备份或 增量备份以来变化的那部数据；
差异备份：仅备份自上一次完全备份以来变化的那部数据；
8. 物理备份、逻辑备份：
物理备份：复制数据文件进行备份；
逻辑备份：从数据库导出数据另存在一个或多个文件中；
9. 根据数据服务是否在线：
热备：读写操作均可进行的状态下所做的备份；
温备：可读但不可写状态下进行的备份；
冷备：读写操作均不可进行的状态下所做的备份；
10.备份需要考虑因素：
锁定资源多长时间？
备份过程的时长？
备份时的服务器负载？
恢复过程的时长？
11. 备份策略：
完全+差异
完全+增量
备份手段：物理、逻辑
备份频率:
基于数据变化量考虑；
基于可用的备份存储空间考虑；
12. 备份什么？
数据
二进制日志、InnoDB的事务日志；
代码（存储过程、存储函数、触发器、事件调度器）
服务器的配置文件
13. 备份工具：
mysqldump：mysql服务自带的备份工具；逻辑备份工具；
支持完全、部分备份；
InnoDB：支持热备；
MyISAM：支持温备；
cp/tar
lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
注意：不能仅备份数据文件；要同时备份事务日志；
前提：要求数据文件和事务日志位于同一个逻辑卷；
xtrabackup：
由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
完全备份、部分备份；
完全备份、增量备份；
完全备份、差异备份；
mysqlhotcopy
基于select实现的冷备份工具
备份：SELECT cluase INTO OUTFILE 'FILENAME';
恢复：CREATE TABLE
导入：LOAD DATA
二. mysqldump：
工作机制:
由mysqldump 发起一个新会话,连接至mysql 服务器端,并执行SELECT * ,使用SELECT命令查询并获得
对应数据库中的全部表数据,并重新建立一个相同配置的数据库,生成INSERT命令, 将刚刚SELECT 获得
的数据全部插入到新的数据库中,以实现备份效果;
1.特性
支持逻辑备份、完全备份、部分备份；
mysqldump 无法备份索引,仅能备份数据,因此恢复时候,需要重建索引
2.其他二次封装工具：
mydumper
phpMyAdmin
3.Usage:
mysqldump [OPTIONS] database_name [tables]
#备份指定数据或者表
注意: 此命令在还原前,不会先生成库,因此需要自己先建立一个库;
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
#备份指定数据库,可为多个
# –databases选项,会自动先创建库,再进行备份
mysqldump [OPTIONS] –all-databases [OPTIONS]
#备份全部数据库
-u user_name 指定登录用户
-h host_add 指定主机, 如localhost
-p passwd 密码
注意: 输出的备份文件,需要使用输出重定向来实现
eg :
mysqldump -uroot -p -hlocalhost hellodb > hellodb.sql
4.基于MyISAM存储引擎 的备份：
支持温备，备份时要锁定表；
-x, –lock-all-tables：锁定所有库的所有表，读锁,仅能读,不能写；
-l, –lock-tables：锁定指定库所有表；
5. 基于InnoDB存储引擎 的备份：
支持温备和热备(基于事务利用隔离级别来实现热备份)；
–single-transaction：创建一个事务，基于此快照执行热备份；
注意: 需要确保对应库下面的所有表,都是innodb 引擎
6. 其它选项：
-R, –routines：备份数据库的 存储过程和存储函数；
#常为必须项
–triggers :备份数据的触发器
#常为必须项
-E, –events :备份数据的事件调度器
–master-data[=#]
#定义指明备份时刻binlog 处于的日志文件以及具体位置
#注意: 需要先开启binlog 功能
0: 不记录,默认为不记录
1：记录为CHANGE MASTER TO语句，此语句不被注释；
2：记录为CHANGE MASTER TO语句，此语句被注释；
示例:
[root@localhost mysql]# mysqldump -uroot -hlocalhost -p --master-data=2 hellodb > hellodb.sql.3[root@localhost mysql]vim hellodb.sql.3
-- MySQL dump 10.14 Distrib 5.5.44-MariaDB, for Linux (x86_64)---- Host: localhost Database: hellodb-- -------------------------------------------------------- Server version 5.5.44-MariaDB-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000001', MASTER_LOG_POS=245;
–flush-logs：锁定表完成后，即进行日志刷新操作；
#可以是binlog 重新滚动, 以便恢复的时候,直接从binlog 第一个记录开始备份,提高效率
备份注意事项:
特别注意备份时候的各表时间点必须同步,否则恢复时候会出错, 因此备份多个数据库时候,必须同时锁定多个库
备份一个数据库时候,必须锁定库里面的全部表
7. 数据恢复详解:
1) 示例:
mysqldump -uroot -p -hlocalhost hellodb > hellodb.sql
备份文件内容示例:
-- MySQL dump 10.14 Distrib 5.5.44-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: hellodb -- ------------------------------------------------------ -- Server version 5.5.44-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `classes` -- DROP TABLE IF EXISTS `classes`; #备份前,会先删除同名的表,以避免冲突 /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `classes` ( ..........(略)
2) 基于binlog 的数据恢复示例:
操作: 新插入任意内容到hellodb库中的courses 表,
删除hellodb库中的courses 表其中couseid=3的项目
利用binlog 恢复到删除couseid=3前的数据
恢复步骤:
1. 准备好已经备份的数据文件hellodb.sql.3
2. 修改binlog 文件,将最后的操作事件剔除,
即使用mysqlbinlog–stop-position=### binlog_file > new_binlog_file
生成新的可以是用的binlog 文件
3. 进入mysql , 停用binlog: SET sql_log_bin=OFF;
4. 导入原始数据: \. /path to hellodb.sql.3
5. 导入binlog: \. /path to new_binlog_file
6. 启用binlog: SET sql_log_bin=ON;
——–> 完成
1. 准备数据
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> INSERT INTO courses (course) VALUES ('aaa'),('bbbb');Query OK,
2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | aaa |
| 9 | bbbb |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> DELETE FROM courses WHERE courseID=1;Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | aaa |
| 9 | bbbb |
+----------+----------------+
8 rows in set (0.00 sec)
2. 修改binlog 并生成新的日志文件
[root@localhost mysql]# mysqlbinlog master-log.000001
......
# at 8614#161120 9:52:11 server id 1 end_log_pos 8716 Queryhread_id=8 exec_time=0error_code=0SET TIMESTAMP=1479606731/*!*/;DELETE FROM courses WHERE courseID=1/*!*/;
......
[root@localhost mysql]# mysqlbinlog --stop-position=8614 master-log.000001 > tmp.sql
MariaDB [(none)]>SET sql_log_bin=OFF;
MariaDB [(none)]> \. /root/hellodb.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)
.......
MariaDB [hellodb]> \. /var/lib/mysql/tmp.sqlQuery OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec).......
MariaDB [hellodb]> SELECT * FROM courses;+----------+----------------+| CourseID | Course |+----------+----------------+| 1 | Hamo Gong || 2 | Kuihua Baodian || 3 | Jinshe Jianfa || 4 | Taiji Quan || 5 | Daiyu Zanghua || 6 | Weituo Zhang || 7 | Dagou Bangfa || 8 | aaa || 9 | bbbb |+----------+----------------+9 rows in set (0.00 sec)
MariaDB [(none)]>SET sql_log_bin=ON;
作业：备份脚本
三. 基于lvm2的备份：
#无法基于脚本实现,使用不多,了解即可
前提：数据目录位于逻辑卷，包含了数据文件和事务日志；
(1) 请求锁定所有表；
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制文件事件位置；
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE
(3) 创建快照卷
lvcreate -L # -s -p r – SNAM-NAME /dev/VG-NAME/LV-NAME
(4) 释放锁
mysql> UNLOCK TABLES
(5) 挂载快照卷，并执行备份，备份完成后删除快照卷；
(6) 周期性备份二进制日志；
percona：
InnoDB –> XtraDB (mariadb)
Innobackup –> Xtrabackup
四. Xtrabackup：
工作机制: 需要基于用户名和密码,连接到mysql 服务端,直接复制底层的数据块,并保存到本地指定位置,以实现备份
备份文件自动以时间戳命名,并支持tar 归档或者压缩存放
特别提醒:
需要手动备份/etc/myl.cnf 文件, 特定情景下,还需要备份 crontab, sudo 等文件
注意：未尽的内容，请参考官方文档；
1.特性:
MyISAM：温备，不支持增量备份；
InnoDB：热备，增量；
物理备份，速率快、可靠；备份完成后自动校验备份结果集是否可用；还原速度快；
2. Usage:
[innobackupex [–defaults-file=#] –backup | innobackupex [–defaults-file=#] –prepare] [OPTIONS]
#The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program.
innobackupex 详细使用,直接查询man innobackupex即可,有详细的说明;
3. 完全备份：
innobackupex –user=user_name –password=### /path to backup dir/
注意: 执行命令以后,必须确保在最后有提示 "completed OK " 才是最终备份成功!
4. 完全备份+binlog 还原：
完全备份：innobackupex –user –password= –host= /PATH/TO/BACKUP_DIR
恢复准备：innobackupex –apply-log /PATH/TO/BACKUP_DIR
#应用日志
数据恢复：innobackupex –copy-back
#数据拷贝(恢复)
注意：–copy-back需要在mysqld主机本地进行，mysqld服务不能启动；
innodb_log_file_size可能要重新设定；
5. 完全备份+增量备份+binlog 还原
数据备份策略： 完全备份+增量备份 (或 完全备份+差异备份 )
增量备份: ( –incremental-basedir 指定的为第一次完全备份或者上一次增量备份)
innnobackup –user=## –password= –incremental /path to backup_dir
–incremental-basedir=/path to last_backup_dir
–incremental: 用于指明此次备份为增量备份
–incremental-basedir=/path to last_backup_dir
#用于指明基于哪个备份文件作增量, 该文件可以为完全备份,
#也可以为上一次增量备份产出的备份文件
备注:
差异备份:–incremental-basedir指定的为上一次完全备份
数据恢复准备：
恢复机制:
先将最后一次完全备份的数据恢复进去(–redo-only ,只执行,不提交),
再将后续若干次增量备份的数据依次恢复到利用完全备份所恢复数据中
直到最后一个增量备份文件导入,才执行提交(即不适用–redo-only)
此后才使用binlog 回放恢复后续数据
innobackupex –apply-log –-redo-only BASEDIR(完全备份数据)
innobackupex –apply-log –redo-only BASEDIR(完全备份数据) —incremental-dir=INCREMENTAL-DIR(距完全备份最近的
一次增量备份数据)
………(重复合并后续的增量备份文件)
innobackupex –apply-log BASEDIR(已经合并的完全备份数据) —incremental-dir=INCREMENTAL-DIR(最后一次增量备
份的数据)
(此时取消–redo-only选项)
注意:由于部分数据,可能存在已经执行,但是并未commit提交的情况,为了防止恢复数据时候重复提交,因此
在恢复时候,需要使用–redo-only ,只执行,不提交,直到将最后一个增量备份恢复时,才提交,即不使用
–redo-only 选项
最后恢复工作：
innobackupex –copy-back BASEDIR(已经合并完后续全部增量备份的完全备份数据文件)
6.备份单库：
xtrabackup 支持单库备份,使用 –databases database_name 即可
# 此功能极少用到,了解即可
7. 安装与程序环境
1) 安装:需要单独安装percona-xtrabackup包
建议 使用yum 来安装RPM包,以解决依赖关系
[root@localhost ~]# yum install ./percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
2) 程序环境:
[root@localhost ~]# rpm -ql percona-xtrabackup/usr/bin/innobackupex #二次封装的xtrabackup /usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/ xbstream /usr/bin/xtrabackup/usr/share/doc/percona-xtrabackup-2.3.2 /usr/share/doc/percona-xtrabackup-2.3.2/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
备份文件说明:
eg :
innobackupex –user=root –password= /mydate
注意: xtrabckup_logfile 为二进制日志文件, 不可以直接CAT 查看, 容易损坏文件
[root@localhost 2016-11-20_12-31-34]# vim xtrabackup_checkpoints
backup_type = full-backuped
#此处可以看到备份类型from_lsn = 0 #起始日志序列号to_lsn = 1840910 last_lsn = 1840910 #结束日志序列号compact = 0 #是否压缩存放recover_binlog_info = 0#定义是否恢复二进制日志信息
~
[root@localhost 2016-11-20_12-31-34]# cat backup-my.cnf #mysql 配置信息备份 # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=. innodb_undo_tablespaces=0
————————————————————————————————————————-
8. 数据恢复详解:
1) 完全备份+binlog 下的数据恢复步骤:
1.数据恢复时候,mysql服务需要停止,并且mysql工作目录,需要清空(一般为/var/lib/mysql/)
2.做恢复数据的主机,也需要安装percona-xtrabackup
3.应用日志:
同步日志文件中的数据(已经提交执行commit, 未提交的则rockball)
innnobackupex –apply-log /path to backup_dir
需要completed OK! 才最终完成)
4.数据还原:
将数据从备份目录复制回实际数据库目录
innobackupex –copy-back /path to backup_dir
5.权限还原:
将已经恢复的数据,修改属主属组为mysql
chown -R mysql/*
6. 启动服务—>完成
2) 完全备份+增量备份+binlog 数据恢复:
情景: 已经做过一次完全备份,并做了一次增量备份,但在做下一次增量备份之前,
mysql-server 挂了,此时只能基于完全备份+增量备份的数据文件,利用
binlog 日志来回放事件,恢复数据
步骤:
1.数据恢复时候,mysql服务需要停止
2.需要的话,恢复已经备份的my.cnf,sudo ,crontab 等文件
3.查询最后一次增量备份在binlog 中的起始位置:
即查看最后一次增量备份数据中的xtrabckup_binlog_info
[root@localhost 2016-11-20_15-33-31]# cat xtrabackup_binlog_info master-log.000001 24686
4. 将挂掉的mysql服务器上的binlog 日志在最后一次增量备份起始位置以后的信息,都导出
保存为代用的日志文件
mysqlbinlog –start-position=24686 master-log.000001 > /mydate/tmp.sql
5. 将完全备份数据,增量备份数据,binlog 都拷贝到需要恢复的主机上
6. 在需要恢复的主机上,首先利用完全备份的数据恢复数据库
innobackupex –apply-log –redo-only /path to full_backup_file
7. 继续利用距完全备份最近的增量备份文件来恢复数据,即将后续的增量备份合并到完全备份中
innobackupex –apply-log –redo-only /path to full_backup_file –incremental-dir=/path to incre_backup_dir
#合并以后,查看完全备份文件中的xtrabackup_checkpoints ,其中的lsn(日志序列号)会
变为合并进来的增量备份的lsn
8. 若还存在后续的增量备份,则重复第7步,继续将后续的增量备份合并到完全备份文件中
innobackupex –apply-log –redo-only /path to full_backup_file –incremental-dir=/path to incre_backup_dir#
9. 合并最后一个增量备份文件,将–redo-only 取消(即执行提交)
innobackupex –apply-log /path to full_backup_file –incremental-dir=/path
to the_last_incre_backup_dir#
10.执行最后恢复操作
innobackupex –copy-back BASEDIR(已经合并完后续全部增量备份的完全备份数据文件)
11.权限还原:
将已经恢复的数据,修改属主属组为mysql
chown -R mysql/*
12.启动mysql ,并进去mysql ,暂停二进制日志记录 sql_log_bin
SET sql_log_bin=OFF;
13. 导入准备好的二进制日志文件,回放后续事件:
mysql -uroot -p -hlocalhost < /mydate/tmp.sql
14. 检查数据恢复情况,若成功还原,则启用二进制日志记录
SET sql_log_bin=ON;
————->完成;
原创文章，作者：ldt195175108，如若转载，请注明出处：http://www.178linux.com/60153
