Code for automatic backup and cleanup of mysql databases using batch processing

Some netizens asked me how to automatically back up the MySQL database under win2003. Since it is an automatic backup, I must write a script. I thought, this is not very difficult, it is very easy to implement, backup can be implemented using scripts, Then how should we implement it automatically? It is also very simple. simply use the "Task Plan" function provided by windows to set a time so that the system can run scripts on a regular basis. Isn't automatic Database Backup enabled?

However, there are already many mysql backup software. For example, I prefer to use good backup software provided by the guardian.

: Http://www.jb51.net/softs/42944.html

First, paste the script code:

Copy codeThe Code is 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

You can change row 9th to set filename = bugdb % date :~ 0, 10%. SQL

Explain the meaning of each sentence in sequence:

• Row 4: Delete the SQL file with the "bugdb _" character in the file name in the specified directory. Because this code was previously written by me and runs once a day on the company's server. Therefore, before each backup, delete the files that have been successfully backed up on the previous day.
• Line 5: Enter the BINLOG of mysqlbecause There Is A mysqldump.exe file in this directory. This file is a MySQL database-provided tool for backing up and restoring the MySQL database. This script file exactly uses this tool.
• Row 6: The Year of the current system date, represented by four digits, for example, 2010.
• Row 7: The month of the current system date, expressed in two digits, for example, 03.
• Row 8: returns the date of the current system date, expressed in two digits, for example, 12.
• Row 9: defines the backup file name. The final file name exists in the form of bugdb_20100312. SQL, that is, the file name plus date.
• Row 10: perform backup.
Then explain the syntax format of mysqldump. Format:

1.mysqldump.exe "Name of the database to be backed up"-u (User Name)-p (password)> "path and file name of the backup file", "> export is the output direction, that is, output the data backed up by mysqldump.exe to a file and save it.

Copy the preceding script to a text file and save it *. bat, such as backup. bat's batch file will be used later. I will store it in D:/scripts/backup_bugdb.bat.

Open "Task Scheduler" in "Control Panel" and create a scheduled task:



In "run", click the Browse button to find the saved backup. for bat batch files, set them on the "Plan" tab and "Settings" tab based on your actual needs. After the settings are complete, click "OK" to save the task. Next, the system periodically runs the script at the specified time to automatically back up the database.



Note: When you are prompted "no permission" during the settings, check the above: only run after login

In addition, the command for restoring the database is attached:

Copy codeThe Code is as follows:
D:/html/wamp/mysql/bin/mysql.exe-uroot-p123456 -- default-character-set = utf8 bugdb <F:/bugdb_20100312. SQL