56,677
社区成员
发帖
与我相关
我的任务
分享
mysqldump -h192.168.1.123 -uroot -pdump_user -A > test.sql
@echo off
SET "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
md "E:\Backup\Mysql\%Ymd%"
"F:\MySQL5.1\bin\mysqldump" -h192.168.1.123 -uroot -pwd -A > "E:\Backup\Mysql\%Ymd%\mysqldb_bak.sql"
pause
-- Windows下自动备份所有数据库:
-- 假设我将每天的备份文件备份于 D:\mysql_bk\20110818 目录下(注:20110818是当天备份时的日期)
-- Step 1: 在 D:\mysql_bk 目录下创建一个 mysql_bk.sql 文件,其内容如下:
SELECT t.bk_sql
FROM (
SELECT concat('if exist "','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),
'" rmdir /s /q "D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),'"') as bk_sql, 1 as cmd_order
UNION ALL
SELECT concat('md "','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),'"') as bk_sql, 2 as cmd_order
UNION ALL
SELECT concat('mysqldump -uroot -pgroupon1@3$ ',SCHEMA_NAME,' > ',
'"','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),
'\/',SCHEMA_NAME,'_',date_format(CURDATE(),'%Y%m%d'),'.sql','"') as bk_sql, 3 as cmd_order
FROM information_schema.SCHEMATA ) t
ORDER BY t.cmd_order asc
into outfile 'D:/mysql_bk/mysql_dump.bat';
-- Step 2: 在 D:\mysql_bk 目录下创建一个 mysql_bk.bat 文件,其内容如下:
if exist "D:\mysql_bk\mysql_dump.bat" del /s /q "D:\mysql_bk\mysql_dump.bat"
mysql -uroot -pgroupon1@3$ information_schema < D:\mysql_bk\mysql_bk.sql
call D:/mysql_bk/mysql_dump.bat /c /q
-- Step 3: 创建一个任务计划,每天凌晨 2:00 执行 mysql_bk.bat 文件即可
-- Windows下自动备份所有数据库:
-- 假设我将每天的备份文件备份于 D:\mysql_bk\20110818 目录下(注:20110818是当天备份时的日期)
-- Step 1: 在 D:\mysql_bk 目录下创建一个 mysql_bk.sql 文件,其内容如下:
SELECT t.bk_sql
FROM (
SELECT concat('if exist "','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),
'" rmdir /s /q "D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),'"') as bk_sql, 1 as cmd_order
UNION ALL
SELECT concat('md "','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),'"') as bk_sql, 2 as cmd_order
UNION ALL
SELECT concat('mysqldump -uroot -pgroupon1@3$ ',SCHEMA_NAME,' > ',
'"','D:\/mysql_bk\/',date_format(CURDATE(),'%Y%m%d'),
'\/',SCHEMA_NAME,'_',date_format(CURDATE(),'%Y%m%d'),'.sql','"') as bk_sql, 3 as cmd_order
FROM information_schema.SCHEMATA ) t
ORDER BY t.cmd_order asc
into outfile 'D:/mysql_bk/mysql_dump.bat';
-- Step 2: 在 D:\mysql_bk 目录下创建一个 mysql_bk.bat 文件,其内容如下:
if exist "D:\mysql_bk\mysql_dump.bat" del /s /q "D:\mysql_bk\mysql_dump.bat"
mysql -uroot -pgroupon1@3$ information_schema < D:\mysql_bk\mysql_bk.sql
call D:/mysql_bk/mysql_dump.bat /c /q
-- Step 3: 创建一个任务计划,每天凌晨 2:00 执行 mysql_bk.bat 文件即可
@echo off
echo 现在是 %DATE% %TIME%
echo.
echo MySQL数据库备份
echo *****************************
echo.
SET "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
for /f %%i in ('mysql -hIP -u用户 -p密码 -N -e "show databases";') do echo %%i
echo.
echo MySQL数据库备份完成,请进行检查。。。
echo.
echo.
pause
@echo off
echo 现在是 %DATE% %TIME%
echo.
echo MySQL数据库备份
echo *****************************
echo.
SET "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
md "E:\Backup\Mysql\%Ymd%"
for /f %i in ('F:\APMServ5.2.6\MySQL5.1\bin\mysql -h192.57.20.101 -uroot -p123 -N -e " show databases";')
do F:\APMServ5.2.6\MySQL5.1\bin\mysqldump -h192.57.20.101 -uroot -p123 %i >"E:\Backup\Mysql\%Ymd%\%i.sql"
echo.
echo MySQL数据库备份完成,请进行检查。。。
echo.
echo.
pause
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
+--------------------+
| information_schema |
+--------------------+
for /f %i in ('mysql -u用户 -p密码 -hIP -N -e "show databases";') do mysqldump -u用户 -p密码 -hIP %i > %i.sql