Automatic mysql database backup method in windows

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