Summary of several methods for automatic backup of windows mysql

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly organizes several methods of automatic backup of Windows MySQL. It has a good reference value. Friends who need it can take a look.

Based on the previous article method, automatic backup can be achieved by adding batch processing commands. Just because the backup file names in the batch command are quite special according to time, so I have compiled a special article.

1. Copy the date folder backup

==============================

Imaginary environment:

MySQL installation location: C:\MySQL

The forum database name is: bbs

Database backup destination: C:\db_bak\

============================

Create a new 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 use Windows' "Scheduled Tasks" to execute it regularly This batch script will do. (For example: execute back_db.bat at 3 am every day)
Explanation: The backup and recovery operations are relatively simple, the integrity is relatively high, and the backup cycle control is more flexible. For example, use %date:~0,10%. This method is suitable for users who have independent hosts but have no management experience in MySQL. The disadvantage is that it takes up a lot of space, and mysql will be disconnected for a short time during the backup (for example, it takes about 5 seconds for a database of about 30M). For the usage of %date:~0,10%, refer to .

2. Mysqldump is backed up into a sql file

==============

Imaginary environment:

MySQL installation Location: C:\MySQL

Forum database name is: 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******************************

will be the above Save the code as backup_db.bat
and then use Windows' "Scheduled Tasks" to execute the script regularly. (For example: execute back_db.bat at 5 a.m. every day)

Note: This method does not need to close the database, and can back up files according to the time of each day.

The current date is obtained by combining %date:~5,2%. The result of the combination is yyyymmdd. The date format obtained by the date command defaults to yyyy-mm-dd (if it is not in this format, you can use the pause command to pause the command line window to see the current computer date format obtained through %date:~,20%), so through %date:~5,2%, you can get the two characters starting from the fifth character in the date, such as today For 2009-02-05, you can get 02 through %date:~5,2%. (The subscript of the date string starts from 0)

3. Use WinRAR to perform regular backup of the MySQL database.

For MySQL backup, the best way is to directly back up the Data directory of the MySQL database. The following provides a method to use WinRAR to perform regular backup of the Data directory.

First of all, of course, WinRAR must be installed on the computer.

Write the following command into a text file

****************************** ****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

******** *************************Code End************************* ****

Save, and then change the extension of the text file to CMD. Enter the control panel, open Scheduled Tasks, and double-click "Add Scheduled Task". Find the CMD file just now in the scheduled task wizard, and then specify a running time and the account password used when running for this task.

The disadvantage of this method is that it takes up more time, compression during backup takes time, and mysql disconnection takes more time than the first method, but it is good for file naming.

The above is a summary of several methods for automatic backup of Windows MySQL. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!