Several ways to automatically backup Windows MySQL

Original link: http://www.cnblogs.com/liongis/archive/2013/03/12/2956573.html

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 starting from 0)


3. Use WinRAR to make regular backup of MySQL database.
The best way to backup MySQL is to back up the data directory of the MySQL database directly. The following provides a way to use WinRAR to perform a scheduled backup of the data directory.

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

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

Save, and then modify the extension of the text file 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 backup to be compressed, and MySQL disconnects more time than the first method, but it is good for file naming.

Several ways to automatically backup Windows MySQL