Windows Planning task Implementation MySQL database automatic backup Batch command

Source: Internet
Author: User
Tags datetime modifier rar mysql backup

Method One

Specific code
Backup

The code is as follows Copy Code

@echo Off & setlocal enableextensions

::----------Configuration Items----------

:: Backup placement path, plus
Set Backup_path=d:backup

:: Database name to back up, multiple spaces separated
Set Databases=database1 database2 database3

:: MySQL User Name
Set Username=root

:: MySQL Password
Set password=123456

:: MySQL Bin directory, plus
:: If you can use Mysqldump directly (add MySQL Bin directory to environment variable at installation), leave this blank
Set Mysql=d:servermysqlbin

:: WinRAR with command-line tools executable path, long file name note in 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 to return is not 24-hour system (no 0 fill), please modify this 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%%d.%addon%.rar%%d.%addon%.sql
del/f/s/q%%d.%addon%.sql 2>nul
)
)
Echo Done

: Exit

Increase Scheduled Tasks

The code is as follows Copy Code

@echo off

::-------Configuration Items-------

:: File name to execute
Set File=d:croncron_backup.bat

:: Scheduling Frequency type
Set frequency=daily

:: Frequency, corresponding to the planned frequency type above
Set Modifier=1

:: Time of execution of the plan (24-hour system)
Set datetime=00:30:00

:: The name of the plan
Set name= "Backup Cron Job"

:: Scheduled to execute user, not recommended for modification
Set user= "System"

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

Schtasks/create/ru%user%/sc%frequency%/mo%modifier%/st%datetime%/tn%name%/tr%FILE%
Pause

Method Two

Here's a simpler and quicker way

Build a notepad and rename it to: Mysql_backup.bat then click the right button to select Edit, paste the following section in and then modify the save, such as 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


Above the D disk and Db_backup, and other directories can be modified, the script means to copy Taobao this directory to D disk db_backup This directory inside the directory of the name of the day, such as: d:backup2010-05-23, please note that, Many people on the Internet reprinted the said to%date:~4,10% this as the day directory is wrong.

Then create a new scheduled task, add the Mysql_backup.bat, set to run the next few hours every morning, this part is no longer detailed,


Method Three

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

The code is as follows Copy Code
net stop MySQL
xcopy C:mysqldatabbs*.* c:db_bakbbs%date:~0,3%/y
net start MySQL

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 is more flexible, for example, save a week of data, with%date:~0,3%, save the daily data, with%date:~4,10%. This method is suitable for users who have a standalone host but have no management experience with MySQL. The disadvantage is that a lot of space, the backup period MySQL will be a short time to disconnect (for example: about 30M of the database takes about 5s).


==============
Hypothetical environment:
MySQL Installation Location: C:mysql
Forum database name: BBS
MySQL Root Password: 123456
Database backup destinations: 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 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)

--------------------------------------------------------------------------------------------------------------- -
Use WinRAR to make a regular backup of MySQL database.
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

The code is as follows Copy Code

net stop MySQL

Del d:mysqldata/q

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

net start MySQL

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.

To implement a scheduled backup under Windows We need to use the Windows Planning task is now implemented, you can think of more things in this implementation oh.

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.