Summary of several methods for Windows MySQL automatic backup _mysql

Based on the previous article method, the batch command is added to automate the backup process. Just because the name of the backup file in the batch command is unique in terms of time, make a special arrangement.

1. Copy Date folder Backup

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

Hypothetical environment:

MySQL Installation Location: C:\MySQL

Forum database name: BBS

Database backup destinations: 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 *****************************

You can then use Windows Scheduled Tasks to execute the batch script at timed intervals. (For example: Perform Back_db.bat 3 o'clock in the morning every day)
Explanation: Backup and restore operations are relatively simple, integrity is relatively high, control backup cycle more flexible, for example, with%date:~0,10%. This method is suitable for users who have a standalone host but have no management experience with MySQL. The disadvantage is to occupy more space, backup period MySQL will be a short period of time disconnect (for example: about 30M database time is about 5s), for the use of%date:~0,10% reference.

2, mysqldump backup into SQL files

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

Hypothetical environment:

MySQL Installation Location: C:\MySQL

Forum database name: BBS

MySQL Root Password: 123456

Database backup destinations: 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 above code as Backup_db.bat
You can then use Windows Scheduled Tasks to execute the script at timed intervals. (For example: Perform Back_db.bat 5 o'clock in the morning every day)

Note: This method can be used to back up files by the name of each day, without shutting down the database.

The current date is grouped by%date:~5,2%, and the combined effect is the date format of the Yyyymmdd,date command, which defaults to YYYY-MM-DD (if this is not the format you can pause the command line window through the pause command. See through%date:~,20% Gets the current computer date format), so you can get the two characters starting with the fifth character in the date by%date:~5,2%, for example, today is 2009-02-05 and 02 is available through%date:~5,2%. (The subscript for the date's string is starting at 0)

3, using WinRAR to the MySQL database for a scheduled backup.

For MySQL backup, the best way is to directly back up the MySQL database data directory. A method of using WinRAR to perform a scheduled backup of the data directory is provided below.

First of all, of course, install the WinRAR on the computer.

Write the following command to 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 modify the extension of the text file to cmd. Enter the control Panel, open the Scheduled task, and double-click Add Scheduled task. In the Scheduled Task Wizard, locate the cmd file just now, and then specify a run-time and run-time account password for this task.

The disadvantage of this approach is that it takes a lot of time to compress during backup time, and MySQL disconnects more time than the first method, but it is good for file naming.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, but also hope that a lot of support cloud Habitat community!