MySQL通过bat命令备份windows下的mysql数据库_MySQL
May 30, 2016 pm 05:11 PM前言:
朋友说在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别人脚本的时候,要注意这些格式问题免得出错。

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How does Go language implement the addition, deletion, modification and query operations of the database?

How to use MySQL backup and restore in PHP?

Detailed tutorial on establishing a database connection using MySQLi in PHP

How does Hibernate implement polymorphic mapping?

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

Analysis of the basic principles of MySQL database management system

An in-depth analysis of how HTML reads the database

Tips and practices for handling Chinese garbled characters in databases with PHP
