MySql备份方案

一. 几种备份方式及策略

1、逻辑备份

使用mysql自带的mysqldump工具备份生成sql文件 。
优点：最大好处是能够与正在运行的mysql自动协同工作，在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作，sql文件通用方便迁移部署。 
缺点：速度比较慢，特别是数据量大多的时候很耗时间。如果数据库正处于提供给用户服务状态，这段时间操作过程中，意味着要锁定表(只能读不能写入数据)，服务就会受到影响。 

2、物理备份

直接拷贝mysql的数据目录。
缺点：不能操作正在运行的mysql服务器(拷贝过程若有用户访问更新数据，就无法备份当时的数据) ，可能无法移植到其他机器上去。直接拷贝只适用于myisam类型的表，这种类型的表是与机器独立的。但实际情况是，你设计数据库的时候不可能全部使用myisam类型表。更多的情况是，根据业务特点(比如支持事务使用innodb)，查询速度和服务性能来选择表类型的。
如果正在拷贝一个表时改变它，拷贝就失去意义。如果数据库表在文件系统备份过程中被修改，进入备份的表文件主语不一致的状态，而对以后的恢复表将失去意义。 
保证你的拷贝完整性的最好方法是：关闭服务器，拷贝文件，然后重启服务器。 

3. 主从备份机制

mysql没有增量备份机制，大数据量备份是个头疼的问题，还好mysql提供主从机制。
优点：适合大数据量场景，还可以满足读写分离，提高数据库访问速度。大部分互联网公司搭建多台数据库服务器，进行主从复制。
主从机制常遇到的问题是，如何保证数据不堵塞，不延迟。这个问题还是可以容忍的，有一些方案可以改善。

4. 备份策略的权衡

要速度快，本地备份，建议使用物理备份。
保证通用性，快平台迁移部署，推荐逻辑备份。
高可用性，读写分离，硬件资源充足，推荐采用主从机制。

二. Windows环境下自动定时备份

1. 备份策略

使用mysql自带的mysqldump工具进行逻辑备份，编写批处理脚本文件，结合windows【任务计划程序】完成自动定时异地备份。

设定如下：
备份文件存储目录：    D:\backup\mysqldb
本机需要mysql环境，mysqldump所在目录：    D:\prg\mysql5.6\bin
backup_mysql.bat文件所在目录：    D:\backup

2. 编写批处理脚本

backup_mysql.bat文件，内容如下：

@echo off
echo mysqldb backup is starting...
if "%time:~0,1%"==" " set "time=0%time:~1%"
set filename=D:\backup\mysqldb\db-%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.sql
echo %filename%
D:\prg\mysql5.6\bin\mysqldump --default-character-set=utf8 -h[HOST_IP] -u[USER] -p[PASSWORD] [DB_NAME]>%filename%
echo finish backup

3. 创建windows任务计划

【开始】-【附件】-【系统工具】-【任务计划程序】-【创建任务】-【常规】

【触发器】，每天凌晨2点开始执行任务

【操作】-【新建操作】，选择执行脚本文件backup_mysql.bat

点击【确定】，弹出windows系统账号/密码设置窗口

点击【确定】，任务计划列表中出现刚创建好的任务，选中右键【运行】，执行批处理脚本。

如果运行成功，在D:\backup\mysqldb目录下将生产一个名称类似db-yyyy-MM-dd-hh-mm-ss.sql的文件，打开文件能看见一行行sql语句。

小结：日常运维，只需检查备份目录是否生成对应每天的sql文件，这种备份方式比较适合数据量小、跨平台通用迁移、异地备份的场景。

转载于:https://my.oschina.net/u/3010158/blog/1530120