Automatic mysql database backup method in windows

Source: Internet
Author: User
Tags mysql backup

Mysql database is a very important point in our WEB development and website. We can say that we need to back up the database in real time or one backup every day, but if we back up the database manually every day, this may be forgotten, next I will introduce how to use the batch processing method in windows to implement the regular mysql database backup method.

The steps are very simple, just do it in three steps:

1. Create a bat file and bat file content

The Code is as follows: Copy code


@ Echo off
Set host = localhost
Set username = root
Set passward = ansure
Set database = weihe
Set backup_path = D:/db_backup/
Echo "Mysql Backup, please waitting"
Mysqldump-h % host %-u % username %-p % passward % -- skip-lock-tables % database %> % backup_path %/% database % _ % date :~ 0, 10%. SQL
Echo "OK! "


// The following is an explanation
First, a common command for the bat file.
Second, set the month format. To ensure that the file name is not repeated and that the current file generation date is known, the generated format is: back_up (thurs18_00). SQL red.
Third, I copied mysqldump.exe from the installation file to this directory. The purpose is to prevent spaces (E:/backdata/tool/mysqldump.exe cannot contain spaces, and spaces must be specially processed, this is not detailed here ). -U is followed by the user name. Note that there is no space.-u root is incorrect, and-p is the same. -- The database can be followed by multiple databases.

2. Create a scheduled task and execute the bat file.

Create a scheduled task in the windows Task Scheduler. The execution of the scheduled task is the execution of the above bat file once every month, and the database is completely backed up, in the form of SQL, generally, it is set to around a.m.
Why is there an incremental backup once a month? If it is too frequent, it will consume disk space and a lot of database performance. Therefore, it is recommended that you take a long period of time to complete Incremental backup.

3. mysql Incremental Backup

The above scheme can be used to restore the database to the data of the previous month. However, if only the preceding backup scheme is used, the data of this month cannot be recovered. With the Incremental backup solution, you can restore to any time in the past to a more practical level. How is Incremental Backup backed up?
You only need to add this configuration at the end of my. ini file:

The Code is as follows: Copy code

# Log
Log-error = D:/backdata/logs/error. log
Log_bin = D:/backdata/logs/logbin. log
Long_query_time = 2
Log-slow-queries = D:/backdata/logs/slowquery. log

Here, the second line is used to generate the binfile. The other few logs are not used for backup, but are useful and occupy little space. It is of great benefit for analyzing mysql problems.

4. Back up database files directly

Create db_bak.bat and write the following code


* ***************************** Code Start ******* **********************

The Code is as follows: Copy code
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 :~ Usage reference

Related Article

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: info-contact@alibabacloud.com 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.