MySQL常用备份方式及备份工具并举例
一、备份和恢复（数据）：
-
什么是备份：存储的数据副本；
原始数据面临的问题：持续改变； -
什么是恢复：把副本应用到线上系统；
仅能恢复至备份操作时刻的数据状态； -
基于时间点恢复：使用二进制文件binary logs;
二、备份作用
灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、...
测试；
三、备份时应该注意事项
1、能容忍最多丢失多少数据；
2、恢复数据需要在多长时间内完成；
3、需要恢复哪些数据；
4、做恢复演练，增强恢复操作效率；
5、测试备份的可用性；
四、备份类型：
1、按照备份的数据集的范围分为：
完全备份和部分备份
- 完全备份：整个数据集；
- 部分备份：数据集的一部分，比如部分表；
2、按照备份的数据完整性分为：
全量备份、增量备份、差异备份
- 全量备份：完全备份所有数据；
- 增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
- 差异备份：仅备份自上一次完全备份以来变量的那部数据；
3、按照备份方式分为：
物理备份、逻辑备份
- 物理备份：效率高，复制数据文件进行的备份；
- 逻辑备份：从数据库导出数据另存在一个或多个文件中；
4、根据数据服务是否在线分为：
- 热备：读写操作均可进行的状态下所做的备份，MyISAM不支持，innodb支持；
- 温备：可读但不可写状态下进行的备份；
- 冷备：读写操作均不可进行的状态下所做的备份；
五、备份需要考虑因素
- 锁定资源多长时间？
- 备份过程的时长？
- 备份时的服务器负载？
- 恢复过程的时长？
1、备份策略：
全量+差异 + binlogs二进制日志时间点还原
全量+增量 + binlogs二进制日志时间点还原
备份手段：物理、逻辑
2、备份内容
- 1、数据、二进制日志、InnoDB的事务日志，尽量分开存放；
- 2、代码（存储过程、存储函数、触发器、事件调度器）
- 3、服务器的配置文件
六、备份工具介绍
1、mysqldump工具
- mysql服务自带的备份工具；逻辑备份工具，可以做完全、部分备份；
InnoDB：热备；
MyISAM：温备；
2、cp/tar 拷贝复制文件
- lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
注意：不能仅备份数据文件；要同时备份事务日志；
前提：要求数据文件和事务日志位于同一个逻辑卷；
3、xtrabackup工具
- 由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
支持完全备份、部分备份；
支持完全备份、增量备份；
支持完全备份、差异备份；
4、mysqlhotcopy冷备工具
5、select语句备份，只能备份部分
- 备份：SELECT cluase INTO OUTFILE 'FILENAME';
恢复：CREATE TABLE
导入：LOAD DATA
使用备份工具的备份策略：
使用xtrabackup工具备份：
完全+差异+binlog备份
完全+增量+binlog备份
完全+binlog：mysqldump备份
七、使用mysqldump工具备份示例
- mysqldump是单线程，支持逻辑备份、完全备份、部分备份；
- 对mysqldump二次封装工具有mydumper、phpMyAdmin
1、格式：
mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump mydb：表级别备份
mysqldump --databases mydb：库级别备份
2、不同引擎使用的选项
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=2 热备的那个一刻，二进制日志处于的位置，并备份出来用CHANGE MASTER TO语句标记，2是备份时候自动注释掉，恢复时候不被执行；
1：记录为CHANGE MASTER TO语句，此语句不被注释；
2：记录为CHANGE MASTER TO语句，此语句被注释；
--flush-logs：锁定表完成后，即进行日志刷新操作，备份时候，从新生成一个二进制日志文件用于备份恢复；
3、热备数据库示例
#热备hellodb数据库示例
mysqldump -uroot -pxxx --single-transaction -R -E --triggers --master-data=2 --flush-logs --databases hellodb > /root/hellodb-fullback-$(date +%F).sql
#热备所有数据库
mysqldump -uroot -pxxx --single-transaction -R -E --triggers --master-data=2 --flush-logs --all-databases > /root/alldb-fullback-$(date +%F).sql
#查看二进制日志位置
less /root/alldb-fullback-2018-10-24.sql
.......MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=245;
#备份恢复
cp alldb-fullback-2018-10-24.sql /tmp/ #拷贝备份文件
cp master-log.000001 /root #拷贝二进制日志文件
mysqlbinlog master-log.000001 #读取二进制日志文件
mysqlbinlog master-log.000001 > /tmp/binlog.sql #把读的结果重定向到指定目录下
#进入数据库
mysql
SET @@session.sql_log_bin=OFF; #关闭二进制日志
\ ./alldb-fullback-2018-10-24.sql #读取备份文件恢复数据库
\ ./binlog.sql #读取二进制文件生成的备份文件恢复数据库
八、基于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
