Code _MYSQL for automatic backup and cleanup of Mysql database with batch processing

Some netizens asked me under the Win2003 how to automatically back up the MySQL database, since it is automatic backup, it must write script. I thought, this is not very difficult, it is very easy to implement, backup can be implemented in a script, that automatic how to achieve it? Also very simple, with Windows with the "mission planning" function, set a time, so that the system timed running scripts, do not realize the automatic backup database function?

But now there are a lot of MySQL backup software, for example, I prefer to use the security of God's good backup software.

Download Address: http://www.jb51.net/softs/42944.html

First, post the script code:

Copy Code code as follows:

@echo on

REM------------------------backup bugdb which is InnoDB-----------------------------
Del C:/backup/website/bugdb_*.sql
CD F:/usr/wamp/mysql/bin
Set year=%date:~0,4%
Set month=%date:~5,2%
Set day=%date:~8,2%
Set Filename=bugdb_%year%%month%%day%.sql
Mysqldump.exe bugdb-uroot-p123456 > F:/backup/website/%filename%
@echo off

Line 9th can also be replaced with set Filename=bugdb%date:~0,10%.sql

Explain the meaning of each code in turn:

• Line Fourth: Deletes the file name under the specified directory containing the word "bugdb_" SQL file. Because this code was written by me earlier, run every night on the company's server. So before each backup, first delete the files that were successfully backed up the previous night.
• Line Fifth: Enter the MySQL bin directory, because this directory has a Mysqldump.exe file, the file when the MySQL database with the backup and restore the MySQL database Tools, this script file is used to this tool.
• Line sixth: The year of the current system date, expressed as four digits, such as 2010.
• Line seventh: The month of the current system date, expressed as two digits, such as 03.
• Line eighth: the date of the current system date, expressed as two digits, such as 12.
• Line Nineth: Define the backup file name, the final file name in bugdb_20100312.sql form, that is, the file name plus date form.
• Line tenth: Perform a backup.
Again, explain the syntax format of mysqldump. The format is:

1.mysqldump.exe "database name to be backed up"--------------------------------------------------> "Backup files stored in the path and filename"

Copy the above script into a text file and save it as a *.bat, such as a backup.bat batch file, and then use the file, which I have d:/scripts/backup_bugdb.bat.

Open Task Scheduler in Control Panel to create a new scheduled task:



In the "Run" inside the browse button to find the Backup.bat batch file just saved, on the Schedule tab and the Settings tab according to their actual needs to set, after setting up the point "OK" to save the task. The system then runs the script periodically for a specified period of time to automatically back up the database.



Note: When setting prompts you "do not have permission" time, tick the above: only after login to run

Also attach the command to restore the database:

Copy Code code as follows:

d:/html/wamp/mysql/bin/mysql.exe-uroot-p123456--default-character-set=utf8 Bugdb < F:/bugdb_20100312.sql