bat 备份mysql数据表_BAT脚本备份MYSQL数据库

@echo off & setlocal ENABLEEXTENSIONS

::备份目录

set BACKUP_PATH=E:\DataBase_backup\

::数据库名称

set DATABASES=testdb

::账户

set USERNAME=root

::密码

set PASSWORD=rootpassword

::MYSQL路径，设置到bin

set MYSQL=D:\MariaDB\bin

set h=%time:~0,2%

set h=%h: =0%

set bak_filename=%DATABASES%_%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%\

echo %DIR%%bak_filename%

:: 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 %DATABASES% ...

::%MYSQL%\mysqldump -u%USERNAME% -p%PASSWORD% %DATABASES% > %bak_filename%

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

::)

echo Done

echo. & pause

出现错误 Host 'localhost' is not allowed to connect to this MariaDB server" when trying to connect 的话可以用能连接上mysql的方法，先连接上，然后执行#1.授权法

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; // localhost:表示从localhost连接到mysql服务器,或者127.0.0.1 同理

#刷新权限

FLUSH   PRIVILEGES;

#或者

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;//%表示从任何IP连接到服务器

#刷新权限

FLUSH   PRIVILEGES;