Table of Contents
1,找回丢失的root密码
2.1 贴下朋友发的运行不成功的脚本
2.2 开始准备测试的库以及数据
2.3 我的调试过程
3,贴下我最终修改过的代码
4,总结
Home Database Mysql Tutorial MySQL通过bat命令备份windows下的mysql数据库_MySQL

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

May 30, 2016 pm 05:11 PM
Order backup database

前言:

朋友说在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别人脚本的时候,要注意这些格式问题免得出错。

 

 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How does Go language implement the addition, deletion, modification and query operations of the database? How does Go language implement the addition, deletion, modification and query operations of the database? Mar 27, 2024 pm 09:39 PM

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

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

Detailed tutorial on establishing a database connection using MySQLi in PHP

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

How does Hibernate implement polymorphic mapping?

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

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

Analysis of the basic principles of MySQL database management system Analysis of the basic principles of MySQL database management system Mar 25, 2024 pm 12:42 PM

Analysis of the basic principles of MySQL database management system

An in-depth analysis of how HTML reads the database An in-depth analysis of how HTML reads the database Apr 09, 2024 pm 12:36 PM

An in-depth analysis of how HTML reads the database

Tips and practices for handling Chinese garbled characters in databases with PHP Tips and practices for handling Chinese garbled characters in databases with PHP Mar 27, 2024 pm 05:21 PM

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

See all articles