MySQL automatic backup batch under Windows

MySQL automatic backup batch under Windows

2011-05-04 09:16:45| Category: MySQL| Report | Font size Subscription

Backup by system time

Note If there are spaces in the MySQL installation path. The. bat file will be copied to the appropriate directory.

1. Direct copy by system time

net stop MySQL
xcopy Data\*.* d:\bonadb\%date:~0,10%\/y/s/e
net start MySQL

2. Compress the backup with compression software

net stop MySQL
C:\progra~1\winrar\winrar a-ag-k-r-s D:\bonadb\mysql.rar data
net start MySQL

The following references:

Automatic backup method for MySQL database under Windows
============================
Hypothetical environment:
MySQL Installation Location: C:\MySQL
Forum database name is: BBS
Database backup destination: C:\db_bak\
============================
Create a new Db_bak.bat, write the following code

net stop MySQL
xcopy C:\mysql\data\bbs\*.* c:\db_bak\bbs\%date:~0,3%\/y
net start MySQL

You can then use Windows Scheduled Tasks to execute the batch script at timed intervals. (ex: Daily 3 o'clock in the morning execution of Back_db.bat)
Explanation: The operation of backup and restore is relatively simple, the integrity is relatively high, the control backup period is more flexible, for example, save a week of data, with%date:~0,3%, save the daily data, with%date:~4,10%. This method is suitable for users with independent hosts but no management experience with MySQL. The disadvantage is that there is more space, and MySQL will be disconnected for a short time during backup (for example, about 5s for a database of about 30M).

==============
Hypothetical environment:
MySQL Installation Location: C:\MySQL
Forum database name is: BBS
MySQL Root Password: 123456
Database backup destination: D:\db_backup\

Script:

@echo off
C:\MySQL\bin\mysqladmin-u Root--password=123456 shutdown
C:\MySQL\bin\mysqldump--opt-u root--password=123456 bbs > D:\db_backup\bbs.sql
C:\MySQL\bin\mysqld-nt

Save the above code as Backup_db.bat
You can then use the Windows scheduled task to execute the script at timed intervals. (ex: Daily 5 o'clock in the morning execution of Back_db.bat)

--------------------------------------------------------------------------------------------------------------- -
Perform a scheduled backup of the MySQL database using WinRAR.
The best way to backup MySQL is to back up the data directory of the MySQL database directly. The following provides a way to use WinRAR to perform a scheduled backup of the data directory.

First of all, of course, install the WinRAR on the computer.

Write the following command to a text file

net stop MySQL
Del d:\mysql\data/q

C:\progra~1\winrar\winrar a-ag-k-r-s D:\mysql.rar d:\mysql\data

net start MySQL

Save, and then modify the extension of the text file to cmd.

Go to Control Panel, open the scheduled task, and double-click Add Scheduled Task. Locate the cmd file in the Scheduled Tasks wizard, and then specify a runtime and password for the run time for the task.
net stop MySQL
C:\progra~1\winrar\winrar.exe a-ag-k-r-s E:\backup\database_tzlinksys.rar E:\Database\tzlinksys
net start MySQL

net stop MySQL
C:\progra~1\winrar\winrar.exe a-ag-k-r-s E:\backup\database_2mw.rar E:\Database\immwsys
net start MySQL

forfiles/d -5/p e:\backup/s/M * */C "cmd/c del @file%%i/* To automatically delete data backed up for the first 5 days */

@echo off

TITLE ERP Database automatic backup management Author: Cold rain

: Start

Cls

COLOR 1f

rem Use the color command to make changes to the console output color

MODE con:cols=77 lines=14

REM Mode statement for setting the width and height of the form

ECHO----------------------------------------------------------------------------

Echo is performing a system data backup operation

echo please do not close this window temporarily, the system will automatically close the window after the backup is finished

ECHO----------------------------------------------------------------------------

REM---------------------------------------the legendary dividing line-------------------------------------------

REM Judgment processing of hours

Set dt=%time:~0,2%

If%dt% LSS set DT=%DT: =0%

Echo is performing an ERP database backup operation

mysqldump-uroot-pwmywhkerfd1231 ERP > D:\usr\bak\data\erp\erp. Com_%date:~0,4%%date:~5,2%%date:~8,2%_%dt%_%time:~3,2%.sql

echo DJ Valentine's Dance network backup operation is complete ...

D:

CD d:\ "program Files" \winrar

RAR A-DF-EP-HPWMYWHKERFD3211-M5 D:\usr\bak\data\erp\erp. Com_%date:~0,4%%date:~5,2%%date:~8,2%_%dt%_%time:~3,2%.rar D:\usr\bak\data\erp\erp. Com_%date:~0,4%%date:~5,2%%date:~8,2%_%dt%_%time:~3,2%.sql

REM---------------------------------------the legendary dividing line-------------------------------------------

After you save this batch as a Bakmysql.bat file. Then use the win automatic plan to invoke and can.

This article from Csdn Blog, reproduced please indicate the source: Http://blog.csdn.net/wangs sdn/archive/2010/09/20/5897656.aspx