当前位置:Gxlcms > mysql > MySQL通过bat命令备份windows下的mysql数据库_MySQL

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

时间:2021-07-01 10:21:17 帮助过:28人阅读

前言:

朋友说在windows下面用bat命令备份失败,他一时找不到问题所在,于是找我帮忙查看下。其实我也没有用过bat写脚本,不过临时抱佛脚,bat脚本总不会比shell还难吧。

1,找回丢失的root密码

我电脑上倒是原来安装了mysql,但是由于习惯用linux下的mysql,windows的这个长久不使用,连root密码都不知道了,还得需要找回root密码,不然没有登录做测试

1.1、 首先检查mysql服务是否启动,若已启动则先将其停止服务,可在开始菜单的运行,使用命令:

net stop mysql

打开第一个cmd1窗口,切换到mysql的bin目录,运行命令:

mysqld --defaults-file="C:\ProgramFiles\MySQL\MySQL Server 5.1\my.ini" --console --skip-grant-tables

注释:

该命令通过跳过权限安全检查,开启mysql服务,这样连接mysql时,可以不用输入用户密码。 此时已经开启了mysql服务了!

这个窗口保留不关闭。

1.2、打开第二个cmd2窗口,连接mysql:

输入命令:

mysql -u root -p

出现:

Enter password:

在这里直接回车,不用输入密码。

然后就就会出现登录成功的信息,

使用命令:

show databases;

使用命令切换到mysql数据库:

use mysql;

使用命令更改root密码:

UPDATE user SET Password=PASSWORD('root')where USER='root';

刷新权限:

FLUSH PRIVILEGES;

然后退出,重新登录:

quit

重新登录:可以关掉之前的cmd1 窗口了。然后用net start mysql 启动服务

mysql -u root -p

出现输入密码提示,输入新的密码即可登录:

Enter password: ***********

显示登录信息:成功 就可以了。

PS:原blog地址为:http://blog.csdn.net/mchdba/article/details/48039035,谢绝转载

2,开始调试

2.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

2.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');

2.3 我的调试过程

简单化调试,

(1)去掉了for循环,因为一个库成功了,那么N个库也会相应成功了。

(2)文件名失效导致mysqldump报错

D:\mysql-5.6.21-winx64\bin>D:\vm\backup.bat

20150827230401

Startdump databases...

Dumpingdatabase hoomsun_credit ...

Warning:Using a password on the command line interface can be insecure.

mysqldump:Couldn't find table: "04"

Dumpingdatabase manonggu ...

查到原因是生成了hoomsun_credit_20150827231815.sql的文件名,这样是无效的,如下显示

D:\mysql-5.6.21-winx64\bin>D:\vm\backup.bat

20150827231815.sql

hoomsun_credit_20150827231815.sql

Startdump databases...

Dumpingdatabase %D ...

Warning:Using a password on the command line interface can be insecure.

mysqldump:Couldn't find table: ".sql"

Done

D:\mysql-5.6.21-winx64\bin>D:\vm\backup.bat

这样我就分析出了是由于文件名变量出错,肯定是格式问题,于是我删除了原来的关于文件名的set ADDON=%YEAR%%MONTH%%DAY%%HOUR%%MINUTE%%SECOND% 这一行代码,我自己手动再敲一遍,然后运行正常如下:

D:\mysql-5.6.21-winx64\bin>D:\vm\backup.bat

20150827231922.sql

hoomsun_credit_20150827231922.sql

Startdump databases...

Dumpingdatabase %D ...

Warning:Using a password on the command line interface can be insecure.

Done

D:\mysql-5.6.21-winx64\bin>

3,贴下我最终修改过的代码

@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 h=%time:~0,2%

set h=%h: =0%

setbak_filename=%date:~0,4%%date:~5,2%%date:~8,2%%h%%time:~3,2%%time:~6,2%.sql

echo %bak_filename%

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%

set BACKUP_FILE=%DATABASES%_%ADDON%.sql

echo %BACKUP_FILE%

:: create dir

if not exist %DIR% (

echo %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% >%BACKUP_FILE%

%MYSQL%\mysqldump.exe -u%USERNAME%-p%PASSWORD% %DATABASES% > %BACKUP_FILE%

::)

echo Done

:exit

4,总结

bat脚本中,对于截取日期生成文件目录已经文件名的情况,要特别注意编码格式,否则会形成尾部多空格的情况,比如hoomsun_credit_20150827231815 .sql,遇到这种,就会报错的。所以大家在网上copy别人脚本的时候,要注意这些格式问题免得出错。

人气教程排行