MySQL备份与恢复

备份类型

热备份：在线备份，读写不受影响

温备份：只能进行读操作

冷备份：离线备份，读写均不能

物理备份：复制数据文件

逻辑备份：将数据导出至文本文件（SQL）

完全备份：备份全部数据（某个库或者所有库的所有数据）

增量备份：仅备份上次完全备份或增量备份以后变化的数据

差异备份：仅备份上次完全备份以来变化的数据

备份什么？

1) 数据

2) 配置文件、与之相关的系统配置文件

3) 日志：二进制日志、事务日志

热备份：

MyISAM：LVM（近似于热备份）、温备份（锁机制）

InnoDB：xtrabackup（物理备份）、mysqldump

冷备份：

利用主从架构的从服务器进行备份

物理备份：速度快（操作系统级别的）

逻辑备份：速度慢、丢失浮点数精度、可移植性强、跨MySQL版本

方便使用文本处理工具进行二次处理

备份后的数据比原数据大，压缩后可大大节省空间

不适合对大数据库做完全备份

备份策略：
完全 + 增量
完全 + 差异

一周一次完全备份 + 一天一次增量备份
一天一次完全备份 + 一小时一次增量备份


备份工具：
mysqldump 逻辑备份
MyISAM 温备份
InnoDB 热备份
mysqlhotcopy 物理备份 温备份

文件系统工具
cp  冷备份
LVM 逻辑卷的快照功能,几乎热备

--------------------------------------------------
mysqldump [OPTION] db_name [table1] [table2]
# 备份单个库时,不含建库(create database db_name)语句
--master-data={0|1|2}
0：表示不记录二进制日志文件及其事件位置
1：表示以CHANGE MASTER的方式记录位置，可用于恢复后直接启动从服务器
2：表示以CHANGE MASTER的方式记录位置，但默认被注释掉
--lock-all-tables 锁定所有表
--lock-tables
--flush-logs 滚动日志

如果指定库中的所有表类型均为InnoDB，可使用 --single-transaction 启动热备
不需和 --lock-all-tables 一起使用

--all-databases 备份所有库
--databases DB_NAME,DB_NAME... 备份多个库

--events 事件
--routines 存储过程、存储函数
--triggers 触发器


eg：
备份策略：周完全+天增量
完全备份：mysqldump
增量备份：备份二进制日志文件（flush logs）
1) mysqldump --master-data=2 --lock-all-tables --all-databases --flush-logs
# 完全备份
2) mysql -e "flush logs"
# 刷新日志
cp MYSQL-BIN-LOG /path/to/ 或者 mysqlbinlog MYSQL-BIN-LOG > /path/to/
3) 重复过程 2)
4) 还原：
1. SET sql_log_bin=0 #临时关闭二进制日志
2. source /path/sqlfile
3. \. /path/sqlfile
4. SET sql_log_bin=1


--------------------------------------------------
SELECT INTO
用法：
SELECT field1,field2,... INTO OUTFILE file_path FROM table_name
[ fields terminated by '' enclosed by '' ]
[ lines terminated by '' starting by '' ];

注意：
1. 可以创建文件，但不能覆盖已有文件
2. 不能创建文件夹
3. \N 表示 null


LOAD DATA
用法：
LOAD DATA INFILE file_path INTO TABLE table_name;


--------------------------------------------------
LVM快照
几乎热备
注意：
1. 数据文件要在逻辑卷上
2. 此逻辑卷所在卷组必须有足够空间使用快照卷
3. 数据文件和事务日志要在同一个逻辑卷上
步骤：
1. 打开会话，施加读锁，锁定所有表
flush tables with read lock;
flush logs;
2. 通过另外一个终端，保存二进制日志文件及其位置信息
mysql -e "show master status\G" > /path/to/master-{date}.info
3. 创建快照
lvcreate -n NAME -L SIZE -p r -s VG_NAME /path/to/lv_backup
4. 释放锁
unlock tables;
5. 挂载、备份
mount
cp
6. 删除快照卷
lvremove
7. 增量备份二进制日志