MySQL通过bat命令备份windows下的mysql数据库

MYSQL LOGBIN 数据日志恢复数据库小说,mysqllogbin

======================================================

翻看服务器上的二进制日志(MYSQL命令行)
mysql> show binary logs;

二.1 贴下朋友发的运作不成功的脚本

@echo off & setlocal ENABLEEXTENSIONS

set BACKUP_PATH=D:\Backup\

set DATABASES=hoomsun_credit

set USERNAME=root

set PASSWORD=root

set MYSQL=D:\mysql-5.6.21-winx64\bin

set WINRAR=F:\winrar\Rar.exe

 

set YEAR=%date:~0,4%

set MONTH=%date:~5,2%

set DAY=%date:~8,2%

set HOUR=%time:~0,2%

set MINUTE=%time:~3,2%

set SECOND=%time:~6,2%

 

set DIR=%BACKUP_PATH%%YEAR%\%MONTH%\%DAY%\

setADDON=%YEAR%%MONTH%%DAY%%HOUR%%MINUTE%%SECOND%

 

:: create dir

if not exist %DIR% (

mkdir %DIR% 2>nul

)

if not exist %DIR% (

echo Backup path: %DIR% not exists, createdir failed.

goto exit

)

cd /d %DIR%

 

:: backup

echo Start dump databases…

for %%D in (%DATABASES%) do (

echo Dumping database %%D …

%MYSQL%mysqldump -u%USERNAME% -p%PASSWORD%%%D > %%D.%ADDON%.sql
2>nul

:: winrar

if exist %WINRAR% (

%WINRAR% a -k -r -s -m1 -ep1 %%D.%ADDON%.rar%%D.%ADDON%.sql 2>nul

del /F /S /Q %%D.%ADDON%.sql 2>nul

)

)

 

echo Done

 

:exit

 

 

二话不说,上代码:

BINLOG导出SQL文件

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqlbinlog
binlogpath > sqlfilepath  -d dbname -f –stop-datetime stopdatetime
 –start-datetime startdatetime

————-参数解释—————-

binlogpath
BINLOG完整路线(D:\data_backup\mysql_binlog\bin-log.000013)

sqlfilepath 导出到SQL文件的完好路线:(d:\a.13.11.sql)

stopdatetime BINLOG导出筛选甘休时间:(2017/07/1七T壹三:3三:2六)

startdatetime BINLOG导出筛选起初时间:(2017/07/一柒T0九:4三:0二)

dbname BINLOG导出筛选数据名称

 

======================================================

另新启2个BINLOG文件 MYSQL COMMAND 下施行

flush logs;

 

 

 

======================================================

BAT 为MYSQL试行多少个公文,并记下完毕时间日志,有待优化为 FOCR-V

======================================================

@echo off
mysql -uroot -ppwd -Ddbname<D:\cc\bin-log.000010.sql
set d=%date:~0,10%
set t=%time:~0,8%
echo %d%%t% bin-log.000010.sql execed >> d:\cc\result.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P
-Dleizuwenhuadb<D:\cc\bin-log.000011.sql
set d1=%date:~0,10%
set t1=%time:~0,8%
echo %d1%%t1% bin-log.000011.sql execed >> d:\cc\result.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P
-Dleizuwenhuadb<D:\cc\bin-log.000012.sql
set d2=%date:~0,10%
set t2=%time:~0,8%
echo %d2%%t2% bin-log.000012.sql execed >> d:\cc\result.txt

mysql -uroot -p1q2w3e4r5t^^Y^&U*I(O)P
-Dleizuwenhuadb<D:\cc\bin-log.000013.sql
set d3=%date:~0,10%
set t3=%time:~0,8%

echo %d3%%t3% bin-log.000013.sql execed >> d:\cc\result.txt
echo %d3%%t3% all exec complate >> d:\cc\result.txt

pause

2.贰 开头妄想测试的库以及数额

create database hoomsun_credit;

CREATE TABLE t (

idint(1) NOT NULL DEFAULT ‘0’,

name varchar(1) NOT NULL DEFAULT ”

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t VALUES (1,’a’);

 

 

 

@echo off & setlocal ENABLEEXTENSIONS

set BACKUP_PATH=D:\Backup\

set USERNAME=root

set PASSWORD=123456

set MYSQL=”D:\Huweishen.com\PHPWEB\MySQL Server 5.5\bin\”

set WINRAR=”D:\Program Files\WinRAR\Rar.exe”

set YEAR=%date:~0,4%

set MONTH=%date:~5,2%

set DAY=%date:~8,2%

set HOUR=%time:~0,2%

set MINUTE=%time:~3,2%

set SECOND=%time:~6,2%

set DIR=%BACKUP_PATH%%YEAR%\%MONTH%\%DAY%\

set ADDON=%YEAR%%MONTH%%DAY%%HOUR%%MINUTE%%SECOND%

:: create dir

if not exist %DIR% (

mkdir %DIR% 2>nul

)

if not exist %DIR% (

echo Backup path: %DIR% not exists, createdir failed.

goto exit

)

cd /d %DIR%

echo select db from mysql.db > %BACKUP_PATH%”sql.sql”

::set DATABASES=

%MYSQL%MySQL -u%USERNAME% -p%PASSWORD% < %BACKUP_PATH%sql.sql >
out.sql

set mysqldump=%MYSQL%”mysqldump”

:: backup

echo Start dump databases…

for /f “skip=1 tokens=* delims=” %%D in (‘FINDSTR /l /v “ECHO ”
out.sql’) do (

echo Dumping database %%D …

%mysqldump% -u%USERNAME% -p%PASSWORD% %%D > %%D.%ADDON%.sql
2>nul

:: winrar

if exist %WINRAR% (

%WINRAR% a -k -r -s -m1 -ep1 %%D.%ADDON%.rar %%D.%ADDON%.sql 2>nul

del /F /S /Q %%D.%ADDON%.sql 2>nul

)

)

)

echo Done

:exit

命令行实施SQL文件

mysql -f -uroot -ppwd -Ddbname<file

———- 参数解释————-

-f 忽略错误,继续实践

-u 数据库username

-p 数据pwd 特殊字符转义符^ 

-D 施行文书的指标数据库

file 实行的sql文件完整路径

======================================================

MySQL通过bat命令备份windows下的mysql数据库

前言:

爱人说在windows上面用bat命令备份失利,他有的时候找不到难点所在,于是找小编辅协助调查看下。其实自个儿也绝非用过bat写剧本,然则一时抱佛脚,bat脚本总不会比shell还难啊。

 

相关文章