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? First, paste the script code:

 

{
DP. Sh. toolbar. Command ('viewsource', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> View plain {
DP. Sh. toolbar. Command ('copytoclipboard', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> copy to clipboard {
DP. Sh. toolbar. Command ('printsource', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> Print {
DP. Sh. toolbar. Command ('about', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# ">?

1. @ Echo on
3. Rem ------------------------ backup bugdb which is InnoDB -----------------------------
4. Del C:/backup/website/bugdb _ *. SQL
5. Cd f:/usr/Wamp/MySQL/bin
6. Set year = % Date :~ 0, 4%
7. Set month = % Date :~ 5, 2%
8. Set day = % Date :~ 8, 2%
9. Set filename = bugdb _ % year % month % day %. SQL
10. Mysqldump.exe bugdb-uroot-p123456> F:/backup/website/% filename %
12. @ 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 name "bugdb _" 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.
• The fifth line: Enter the BINLOG of mysql, because there is a mysqldump.exe file under this directory. This file is a MySQL database-provided backup and restoration tool. This script file is used by this tool.
• Row 6: The Year of the current system date, represented by four digits, for example, 2010.
• Row 7: takes 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 a backup.

Then explain the syntax format of mysqldump. Format:

 
 

  
  
Mysqldump.exe "Name of the database to be backed up"-U (User Name)-P (password)> "path and file name of the backup file"
 
 

Among them, "> 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:

 

{
DP. Sh. toolbar. Command ('viewsource', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> View plain {
DP. Sh. toolbar. Command ('copytoclipboard', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> copy to clipboard {
DP. Sh. toolbar. Command ('printsource', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# "> Print {
DP. Sh. toolbar. Command ('about', this); Return false;
} "Href =" http://blog.csdn.net/lifesounds/archive/2010/06/23/5688695.aspx# ">?

1. D:/html/Wamp/MySQL/bin/mysql.exe-uroot-p123456 -- default-character-set = utf8 bugdb <F:/bugdb_20100312. SQL