MySQL系列——MySQL备份和恢复

MYSQL的备份和还原
备份 ：副本
RAID1，RAID10 ：保证硬件损坏而不会业务中止；
备份和还原 ：可以保证因逻辑错误引起的数据丢失
备份类型 ：
热备份 ：读写不受影响
温备份 ：仅可以执行读操作
冷备份 ：离线备份，读、写操作均中止

物理备份和逻辑备份
物理备份 ：复制数据文件
逻辑备份 ：将数据导出至文本文件中

完全备份、增量备份和差异备份
备份什么：
数据、配置文件、二进制日志、事务日志
主从服务器架构

物理备份 ：速度快
逻辑备份 ：速度慢、丢失浮点数精度：方便使用文本处理工具直接对其处理，可移植能力强;

备份策略 ：完全+增量 、 完全+差异
MYSQL备份工具 ：
mysqldump : 逻辑备份工具、MyISAM（温）、InnoDB（热备份）
mysqlhotcopy : 物理备份工具、温备份
文件系统工具 ：
cp ：冷备
lv : 逻辑卷的快照功能，几乎热备；
FLUSH TABLES；
LOCK TABLES
创建快照，释放锁，而后复制数据
第三组工具 ：
ibbackup : 商业工具
xtrabackup : 开源工具
MYSQL备份工具比较列表

mysqldump : 逻辑备份
mysqldump(完全备份)+二进制日志
完全+增量
备份单个数据库，或库中单个表
mysqldump DB_NAME [tb1] [tb2]
mysqldump --master-data={0|1|2}
0 : 不记录二进制日志文件及路位置
1 ：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器
2 ：以CHNAGE MASTER TO的方式记录位置,但默认为被注释；
--lock-all-tables : 锁定所有表
--flush-logs : 执行日志flush;
如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；
备份多个库 ：
--all-databases : 备份所有库
--datebases DB_NAME,DB_NAME,... 备份指定库
--events 事件
--routines 存储过程、存储函数
--triggers 触发器

FLUSH TABLES WITH READ LOCK； 刷新表并使用读锁

备份策略 ：周完全+每日增量
完全备份：mysqldump
增量备份：备份二进制日志文件（flush logs）
二进制日志 ：
set sql_log_bin=1 1=ON 0=OFF
二进制日志 ：
format
statement
row
mixed
mysqldump :
--databases DB1,DB2,...
--all-databases

逻辑备份 ：
1、浮点数据丢失精度；
2、备份出的数据更占用存储空间，压缩后可大大节省空间；
3、不适合对大数据库做完全备份；

通常做单张表的备份
SELECT INTO OUTFILE '/path/to/somefile.txt' FORM tb_name [where clause] ; 只备份数据，不备份SQL命令
LOAD DATA INFILE '/path/to/somefile.txt' INTO ta_name;

几乎热备 ：LVM
snapshot
前提 ：
1、数据文件要在逻辑卷上；
2、此逻辑卷所在卷组必须有足够空间使用快照卷；
3、数据文件和事务日志要在同一个逻辑卷上；
步骤 ：
1、打开会话，施加读锁，锁定所有表；
FLUSH TABLES WITH READ LOCK；
FLUSH LOGS；
2、通过另一个终端，保存二进制日志文件及相关位置信息；
mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info
3、创建快照卷
lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv
4、释放锁
UNLOCK TABLES；
5、挂载快照卷，备份
mount
cp
6、删除快照卷
7、备份二进制日志

二进制日志相关的几个选项 ：
innodb_support_xa={TRUE|FLASE}

sync_binlog=#
建议设定为1

percona :
ibbackup :InnoDB online physical backup
full
incremental
MyISAM : warm backup ,full
LVM --> mylvmbackup (perl scripts)

xtrabackup
xtradb : innodb的增强版
innodb :