Methods for automatic windowsmysql backup _ MySQL

Methods for automatic windowsmysql backup bitsCN.com

Based on the methods in the previous article, add the batch processing command to implement automatic backup. The only difference is that the names of backup files in batch processing commands are named by Time.

1. copy the date folder backup
======================================
Hypothetical environment:
MySQL installation path: C:/MySQL
Forum database name: bbs
Database backup destination: C:/db_bak/
======================================
Create db_bak.bat and write the following code


* ***************************** Code Start ******* **********************
Net stop mysql
Xcopy c:/mysql/data/bbs/*. * c:/db_bak/bbs/% date :~ 0, 10%/S/I
Net start mysql
* ***************************** Code End ******* **********************

Then run the batch processing script periodically using Windows's scheduled task. (For example, execute back_db.bat at every day)
Explanation: The backup and recovery operations are simple, the integrity is relatively high, and the backup cycle is controlled flexibly. for example, % date :~ 0, 10%. This method is suitable for users who have independent hosts but have no management experience on mysql. The disadvantage is that it takes a lot of space and mysql will be disconnected for a short time during the backup period (for example, it takes about 5 seconds for a database of about 30 MB), for % date :~ For usage of %, see.


2. back up mysqldump to an SQL file
====================
Hypothetical environment:
MySQL installation path: C:/MySQL
Forum database name: bbs
MySQL root password 123456
Database backup destination: D:/db_backup/

Script:


Rem ****************************** Code Start ****** ***********************
@ Echo off

Set "Ymd = % date :~, 4% % date :~ 5, 2% % date :~ 8, 2%"
C:/MySQL/bin/mysqldump -- opt-u root -- password = 123456 bbs> D:/db_backup/bbs _ % Ymd %. SQL

@ Echo on
Rem ******************************* Code End ****** ***********************

Save the preceding code as backup_db.bat.
Then, run the script periodically using the "scheduled task" of Windows. (For example, execute back_db.bat at every day)

Note: You do not need to close the database, and you can back up files by name every day.

% Date :~ To combine to get the current date, the effect of the combination is yyyymmdd, the default date format obtained by the date command is yyyy-mm-dd (if not, you can use the pause command to suspend the command line window. % date :~, The date format of the current computer obtained in 20%), so % date :~ To get the two characters starting with the fifth character in the date. for example, today is, with % date :~ To get 02. (The subscript of the date string starts from 0)


3. use WinRAR to regularly back up MySQL databases.
For MySQL backup, the best way is to back up the Data directory of the MySQL database directly. The following provides a method for using WinRAR to regularly back up the Data directory.

First, install WinRAR on the computer.

Write the following command into a text file

* ***************************** Code Start ******* **********************
Net stop mysql

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

Net start mysql
* ***************************** Code End ******* **********************

Save and modify the extension of the text file to CMD. Go to the control panel, open the scheduled task, and double-click "add scheduled task ". In the scheduled task wizard, find the CMD file and specify a running time and the account and password used for the task.
The disadvantage of this method is that it takes a lot of time to compress the data during the backup. mysql disconnection takes more time than the first method, but the file name is good.

BitsCN.com