Several ways to automatically backup Windows MySQL

based on the previous article method, the batch command is added to enable automatic backup. It is only because the name of the backup file in the batch command is special in terms of time, so it's a special sort of article.

1. Copy the date folder backup
============================
Hypothetical environment:
MySQL Installation Location: C:\MySQL
Forum database name is: BBS
Database backup destination: C:\db_bak\
============================
Create a new Db_bak.bat, 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. (ex: Daily 3 o'clock in the morning execution of Back_db.bat)
Explanation: The operation of backup and restore is relatively simple, the integrity is relatively high, the control backup period is more flexible, for example, with%date:~0,10%. This method is suitable for users with independent hosts but no management experience with MySQL. The disadvantage is that there is a lot of space, MySQL will be disconnected for a short time (for example: about 30M of the database time is about 5s), for the use of%date:~0,10% reference.


2. mysqldump Backup to SQL file
==============
Hypothetical 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*****************************

Save the above code as Backup_db.bat
You can then use the Windows scheduled task to execute the script at timed intervals. (ex: Daily 5 o'clock in the morning execution of Back_db.bat)

Description: This method does not have to close the database and can back up files by name every day.

By%date:~5,2% to combine the current date, the combined effect of the yyyymmdd,date command gets the date format default to YYYY-MM-DD (if not this format can be paused by the Pause Command Line window to see through the%date:~,20% Get 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 by%date:~5,2% you get 02. (The index of the date string is 0-based)


3, and the MySQL database is scheduled to be backed up using WinRAR.  
for MySQL backup, the best way is to directly back up the data directory of the MySQL database. The following provides a way to use WinRAR to perform a scheduled backup of the data directory.

Of course, the first thing to do is to install WinRAR on your computer.

Writes the following command to a text file

*******************************code start*****************************
Net Stop MySQL

c:\progra~1\winrar\winrar a-ag-k-r-s d:\mysql.rar d:\mysql\data

net start MySQL
******** Code end*****************************

to save and then modify the text file's extension to CMD. Go to Control Panel, open the scheduled task, and double-click Add Scheduled Task. Locate the cmd file in the Scheduled Tasks wizard, and then specify a runtime and password for the run time for the task.
   The disadvantage of this method is that it takes more time to compress, and it takes time for the compression to take longer, MySQL disconnects more time than the first method, but it is good for file naming.