Windows scheduled task implementation mysql database automatic backup Batch Processing Command

Source: Internet
Author: User
Tags mysql backup

We use the automatic batch processing program to back up the data. It can generate a backup file on a given day and save the data on a daily basis to a corresponding directory, it should be implemented in combination with windows scheduled tasks.

Method 1

Code
Backup

The Code is as follows: Copy code

@ Echo off & setlocal ENABLEEXTENSIONS

: ---------- Configuration item ----------

: Path where the backup is stored, add
Set BACKUP_PATH = D: Backup

: Name of the database to be backed up. Multiple database names are separated by spaces.
Set DATABASES = database1 database2 database3

: MySQL user name
Set USERNAME = root

: MySQL password
Set PASSWORD = 123456

: MySQL Bin directory, add
: If you can directly use mysqldump (the MySQL Bin directory is added to the environment variable during installation), leave it blank.
Set MYSQL = D: SERVERMySQLbin

: The executable file path of the WinRAR command line tool. For long file names, use the Dos long file name writing method.
Set WINRAR = C: Progra ~ 1WinRARRar.exe

: ---------- Do not modify the following ----------

Set YEAR = % date :~ 0, 4%
Set MONTH = % date :~ 5, 2%
Set DAY = % date :~ 8, 2%
: If the input time in dos does not return a 24-hour format (no filling is required), modify the value here.
Set HOUR = % time :~ 0, 2%
Set MINUTE = % time :~ 3,2%
Set SECOND = % time :~ 6, 2%

Set DIR = % BACKUP_PATH % YEAR % MONTH % DAY %
Set ADDON = % YEAR % MONTH % DAY % HOUR % MINUTE % SECOND %

: Create dir
If not exist % DIR % (
Mkdir % DIR % 2> nul
)
If not exist % DIR % (
Echo Backup path: % DIR % not exists, create dir failed.
Goto exit
)
Cd/d % DIR %

: Backup
Echo Start dump databases...
For % D in (% DATABASES %) do (
Echo Dumping database % D...
% MYSQL % mysqldump-u % USERNAME %-p % PASSWORD % D> % D. % ADDON %. SQL 2> nul
: Winrar
If exist % WINRAR % (
% WINRAR % a-k-r-s-m1-ep1 too many d.w.addon#.rar % D. % ADDON %. SQL 2> nul
Del/F/S/Q % D. % ADDON %. SQL 2> nul
)
)
Echo Done

: Exit

Add scheduled task

The Code is as follows: Copy code

@ Echo off

: ------- Configuration item -------

: Name of the file to be executed
Set FILE = D: croncron_backup.bat

: Scheduled Frequency Type
Set FREQUENCY = DAILY

: Frequency, which corresponds to the above plan Frequency Type
Set MODIFIER = 1

: Execution time of the plan (in 24-hour format)
Set DATETIME = 00:30:00

: Plan Name
Set NAME = "Backup Cron Job"

: The user who executes the plan. It is not recommended to modify it.
Set USER = "System"

: ------- Do not modify the following -------

Schtasks/Create/RU % USER %/SC % FREQUENCY %/MO % MODIFIER %/ST % DATETIME %/TN % NAME %/TR % FILE %
Pause

Method 2

The following is a simpler and faster method.

Create a notepad, rename it mysql_backup.bat, right-click and select edit, paste the following parts, and modify and save the settings. For example, if I want to back up the database taobao, the script is as follows:

The Code is as follows: Copy code

Net stop mysql

Xcopy D: mysqldatataobao *. * D: db_backup % date :~ 0, 10%/y

Net start mysql

 


You can modify the directories such as disk D and db_backup on your own. The script means to copy the directory named taobao to the directory named "db_backup" on disk D, for example: d: backup2010-05-23, please note that many people on the internet reprint said to % date :~ 4,10% this is incorrect as the directory of the day.

Create a new scheduled task and add mysql_backup.bat to it. Set it to run at a few o'clock every morning. This part will not be detailed,


Method 3

Hypothetical environment:
MySQL installation path: C: MySQL
Forum Database Name: bbs
Database Backup destination: C: db_bak
======================================
Create db_bak.bat and write the following code

 

The Code is as follows: Copy code
Net stop mysql
Xcopy c: mysqldatabbs *. * c: db_bakbbs % date :~ 0, 3%/y
Net start mysql

Then run the batch processing script periodically using Windows's scheduled task. (For example, execute back_db.bat at every day)
Explanation: backup and recovery operations are relatively simple, with high integrity and Flexible backup cycle control. For example, to save data for one week, use % date :~ 0, 3%, save the daily data, with % date :~ 4,10%. This method is suitable for users who have independent hosts but have no management experience on mysql. The disadvantage is that the database occupies a large amount 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 with a needle of about 30 mb ).


====================
Hypothetical environment:
MySQL installation path: C: MySQL
Forum Database Name: bbs
MySQL root Password 123456
Database Backup destination: D: db_backup

Script:

The Code is as follows: Copy code

@ Echo off
C: MySQLbinmysqladmin-u root -- password = 123456 shutdown
C: MySQLbinmysqldump -- opt-u root -- password = 123456 bbs> D: db_backupbbs. SQL
C: MySQLbinmysqld-nt

Save the preceding code as backup_db.bat.
Then, run the script periodically using the "scheduled task" of Windows. (For example, execute back_db.bat at every day)

Bytes ----------------------------------------------------------------------------------------------------------------
Use WinRAR to regularly back up MySQL databases.
For MySQL backup, the best way is to back up the Data directory of the MySQL database directly. The following provides a method for using WinRAR to regularly back up the Data directory.

First, install WinRAR on the computer.

Write the following command into a text file

The Code is as follows: Copy code

Net stop mysql

Del d: mysqldata/q

C: progra ~ 1 winrarwinrar a-ag-k-r-s d: mysql.rar d: mysqldata

Net start mysql

Save and modify the extension of the text file to CMD.

 

Go to the control panel, open the scheduled task, and double-click "add scheduled task ". In the scheduled task wizard, find the CMD file and specify a running time and the account and password used for the task.

In windows, we need to implement scheduled backup by using windows scheduled tasks. You can think of more things with this implementation.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.