MySQL运维33-MySQL备份

1、MySQL备份策略

1. 优先基于从库做备份：减少对生产环境的负荷。
2. 优先做热备份：热(Hot)的意思是备份时不关闭MySQL服务、不影响生产。
3. 数据量大优先做物理备份，数据量小可做逻辑备份：逻辑备份（一般是mysqldump）更简单，但恢复时间长。
4. 应制定完善的数据备份策略：包括日备份、周备份、月备份、季备份。数据量大时，定期全量备份(低频)，再经常增量备份(高频)。原则上近期要每天备份。expi re_logs_days参数至少要跨越2~3个备份；
5. 备份服务器应该视为与生产服务器一样重要：备份文件应该和数据库主机物理分开，可以选择FTP上传或其他网络传输方式把备份文件保留在独立的备份服务器上。可以使用NFS挂载文件系统的方式进行远程备份。
6. 主库应启用二进制日志：以便搭建从库，做时间点恢复。

2、使用mysqldump备份和恢复数据

2.1、基本的备份和恢复

1. 整库备份命令格式

shell> mysqldump --databases db_name > db_name.sql

2. 恢复方式一：

shell> mysql db_name < db_name.sql

3. 恢复方式二，可以使用source命令来执行：

mysql > source /path/to/db_name.sql

4. 生产环境中一般为主从配置，一般应在从库上进行定时备份。如下是一个使用mysqldump备份的完整示例。

mysqldump --single-transaction --flush-logs --master-data=2 --hex-blob -R -E -f --all-databases 2>> /path/to/log | gzip > sql.name.gz

• –single-transaction参数：实现无阻塞备份，只在开始时锁表，然后就是无阻塞备份，即备份不影响事务的运行。
• -f参数：由于视图有依赖，如果基础表不存在或没有权限，那么视图的导出将会失败，而且会导致mysqldump命令的退出，为了避免这个问题，可以添加一个参数-f强制导出数据而不是中途退出。
• –result-file参数：对于mysqldump的备份，需要检查其输出，检查是否有错误或警告，正常备份结束后，应该有“Dump completed on”字样，我们可以使用–result-file参数保存mysqldump结果。

2.2、存储过程的备份和恢复

1. 备份存储过程：

shell> mysqldump  -td -R --triggers=false  db_name > db_name_procedure.sql

2. 仅备份数据，而不备份存储和触发器。

mysqldump --triggers=false  db_name > db_name_data.sql

3. 触发器恢复时，需要先删除数据库中原有的触发器。由于导出触发器的转储文件里没有DROP TRIGGIER语句，因此我们需要手动生成DROP TRIGGER的语句，如下语句可以基于information_schema自动生成触发器删除语句：

SELECT CONCAT('drop trigger ',TRIGGER_NAME,';') INTO OUTFILE '/tmp/drop_trigger.sql' FROM information_schema.triggers WHERE TRIGGER_SCHEMA='db_name';

3、总结

1. MySQL的备份最好在从库上进行，数据量大适合用物理备份（数据文件的备份），数据量小适合用逻辑备份（mysqldump）。
2. MySQL的备份最好做热备份，就是不影响生产，不需要停机。
3. 数据量大的库建议低频定期做全备，高频定期做增备。