MySQL backs up MySQL database under Windows with bat command

Objective:

friends say in Windows below with Bat command backup failed, he could not find the problem, then asked me to help check the next. In fact, I have not used bat to write scripts, but cram,bat script is not more difficult than the shell .

1, retrieve the LostRootPassword

I was originally installed on the computer MySQL, but because of the habit of Linux under the mysql,windows of this long-term use, and even root password do not know, but also need to retrieve the root password, or do not log in to do the test

1.1, first check whether the MySQL service is started, if it has been started to stop the service, can be run in the Start menu, using the command:

net stop MySQL

Open the first cmd1 window, switch to the MySQL bin directory, and run the command:

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

Comments:

This command starts the MySQL service by skipping permission security checks, so that when you connect to MySQL, you do not have to enter a user password. The MySQL service is now open!

This window is reserved for not closing.

1.2. Open a second CMD2 window to connect to MySQL:

Enter the command:

Mysql-u root-p

Appear:

Enter Password:

Enter the carriage directly here without entering the password.

Then there will be a login success message,

Use the command:

show databases;

To switch to the MySQL database using the command:

Use MySQL;

To change the root password using the command:

UPDATE user SET Password=password (' root ') where user= ' root ';

Refresh permissions:

FLUSH privileges;

Then exit and log back in:

Quit

Re-login: You can turn off the previous Cmd1 window. Then start the service with net start MySQL

Mysql-u root-p

Enter a password prompt, enter a new password to log in:

Enter Password: ***********

Display login Information: Success is all right.

PS: The original blog address is: http://blog.csdn.net/mchdba/article/details/48039035, declined reprint

2, start debugging

2.1paste the script that the friend sends the unsuccessful run

@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 Starting the library to prepare the test and the data

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 My debugging process

Simplified commissioning,

(1) The For loop is removed, because a library succeeds, and then the N libraries will succeed accordingly.

(2) Invalid file name causes mysqldump error

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 is insecure.

Mysqldump:couldn ' t find table: "04"

Dumpingdatabase Manonggu ...

The reason is that the file name of the Hoomsun_credit_20150827231815.sql is generated, which is invalid, as shown below

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 is insecure.

Mysqldump:couldn ' t find table: '. sql '

Done

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

So I analyzed the reason is because the file name variable error, is definitely a format problem, so I deleted the original about the file name of the set addon=%year%%month%%day%%hour%%minute%%second% this line of code, I myself to knock again, Then run normally as follows:

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 is insecure.

Done

D:\mysql-5.6.21-winx64\bin>

3and put down the code I finally modified.

@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%) does (

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, summarize

BAT script, for the interception date generated file directory has the name of the case, pay special attention to the encoding format, or it will form the tail of a lot of space, such as hoomsun_credit_20150827231815. SQL, encountered this, will be error. So when you copy someone else's script online, pay attention to these formatting problems before you make mistakes.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL backs up MySQL database under Windows with bat command