Methods for automatic windowsmysql backup _ MySQL

Source: Internet
Author: User
Tags mysql backup
Methods for automatic windowsmysql backup

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/


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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.