mysql增量备份和全量备份

项目需要对mysql数据库每天进行一次增量备份，一周进行一次全量备份，研究了大半天，整理出来的脚本，其实停简单的，理解了binlog其实就是so easy.(windows和Linux的脚本都上传到了csdn，需要的朋友可以在最下面下载)

1.前提

必须要开启bin-log才可以。

开启的方式在my.ini或my.cnf文件中。(我自己windows上装的5.5，linux上装的5.6)

5.6版本bin-log不能指定路径，只需要将log_bin前面的注释去掉就可以了。

5.5版本可以指定bin-log路径。

log-bin=E:/backup/logbin.log

2.增量备份

set PATH=C:\Program Files\WINRAR;%PATH%
set MYSQLPATH=E:\software\mysql
set BAKPATH=E:\backup
set USERNAME=root
set PASSWORD=123456
IF NOT EXIST "%BAKPATH%\bk-inc" MD "%BAKPATH%\bk-inc"

%MYSQLPATH%\bin\mysqladmin -u%USERNAME% -p%PASSWORD% flush-logs

xcopy /c /h /y %BAKPATH%\logbin.0* %BAKPATH%\bk-inc

@echo  %date% %time% dIncremental bakup finish >> E:\backup\bk.log

3.全量备份

set PATH=C:\Program Files\WINRAR;%PATH%
set MYSQLPATH=E:\software\mysql
set BAKPATH=E:\backup
set USERNAME=root
set PASSWORD=123456
set DB=dataset6c
set DT=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%
set DT=%DT: =0%

IF NOT EXIST "%BAKPATH%\bk-full" MD "%BAKPATH%\bk-full"
IF NOT EXIST "%BAKPATH%\data" MD "%BAKPATH%\data"

%MYSQLPATH%\bin\mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-character-set=utf8 --databases %DB% --flush-logs --master-data=2 --delete-master-logs > %BAKPATH%\data\dataset6c_%DT%.sql

cd %BAKPATH%\data
rar a -ag %BAKPATH%\bk-full\ *.sql
cd %BAKPATH%\bk-inc
rar a -ag ./ logbin.**
del logbin.**
rmdir /s /q %BAKPATH%\data\
@echo %date% %time% full bakup finish today: %today% >> E:\backup\bk.log

4.定时任务

linux采用crontab

windows用系统自带的定时任务

5.数据恢复

参考:  http://blog.csdn.net/jbboy/article/details/42489419

6.脚本下载

点击下载