MySQL backup and recovery

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 put the scriptCodePost it:

 
 

  
  
 
  
@ Echo on
 
  
  
 
  
 
 
  
  
 
  
Rem ------------------------ backup bugdb whichIsInnoDB -----------------------------
 
  
  
 
  
Del F: \ backup \ website \ bugdb _ *. SQL
 
  
  
 
  
Cd f: \ USR \ Wamp \ mysql \ bin
 
  
  
 
  
SetYear = % Date :~ 0, 4%
 
  
  
 
  
SetMonth = % Date :~ 5, 2%
 
  
  

  
SetDay = % Date :~ 8, 2%
 
  
  
 
  
SetFilename = bugdb _ % year % month % day %. SQL
 
  
  
 
  
Mysqldump.exe bugdb-uroot-p123456> F: \ backup \ website \ % filename %
 
  
  
 
  
 
 
  
  
 
  
@ Echo off
 
 
 

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.
• Row 5: Enter the MySQL bin directory, because there isMysqldump.exe file. This file is used by the MySQL database to back up and restore the MySQL database.
• 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.

Let's explain it again.Syntax format of mysqldump. Format:

 
  
  


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

  
  
 

">" Is used to redirect the output.The data backed up by mysqldump.exe is output to a file and saved.

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.

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

 
  
  


   
   
D: \ HTML \ Wamp \ mysql \ bin \ mysql.exe-uroot-p123456

   
   
--Default-Character-Set= Utf8 bugdb <F: \ bugdb_20100312. SQL

  
  
 

Command for backing up MySQL database

Mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL

Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

Mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL

Directly compress and back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename | gzip> backupfile. SQL .gz

Back up a MySQL database table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2> backupfile. SQL

Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword-databases databasename1 databasename2 databasename3> multibackupfile. SQL

Back up database structures only

Mysqldump-no-data-databases databasename1 databasename2 databasename3> structurebackupfile. SQL

Back up all databases on the server

Mysqldump-all-databases> allbackupfile. SQL

Command for restoring MySQL database

Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL

Restore a compressed MySQL database

Gunzip <backupfile. SQL .gz | mysql-uusername-ppassword databasename

Transfer database to new server

Mysqldump-uusername-ppassword databasename | mysql-host = *. *-C databasename