Batch script for automatic backup of MySQL under Windows

In a UNIX system environment, you can use a variety of Unix shells combined with cron tasks to achieve automatic backup of MySQL, that under Windows How to implement it, It's very simple. Just write a custom batch script in conjunction with Taskschd.msc (Task Scheduler), you can implement, recently need to modify the tuning Windows environment of MySQL in the way to write a logical backup using mysqldump batch script, as follows:

@echo  off::mysql_backup.batset hour=%time:~0,2%if  "%time:~0,1%" = = " "  set hour=0% time:~1,1%set now=%date:~0,4%%date:~5,2%%date:~8,2%%hour%%time:~3,2%%time:~6,2%:: Host ipset host= 127.0.0.1:: Port set port=3306:: User set username=root:: Password Set password=youpassword:: To back up the name of the database, If you have more than one library, separate the set databasename=database1 database2:: mysql bin  directory with a space::  if you add it when you install the configuration  MySQL Bin  directory to the environment variable (path) &nbsp, where you can leave blank set mysql=d:\mysql-5.5.57-winx64\bin:: Configure SQL Backup path set  DIR=D:\backup\db::  Create backup directory to back up MySQL if not exist %dir%  (     Mkdir %dir% 2>nul) if not exist %dir%  (    echo  backup path: %dir% not exists, create dir failed.     Goto exit) echo start mysqldump ...for %%i in  (%databasename%)  do  (    %mysql%mysqlduMp -h%host% -p%port% -u%username% -p%password% %%i > %dir%%%i-%now%.sql  2>nul) Echo end mysqldump:: Record the status of the main library and log records%mysql%mysql -h%host% -p%port% -u% username% -p%password% -bse  "Select now (); Show master status\g"  >  %dir%mysql_status-%now%.logecho delete files before 60 days:: Delete backup from 60 days ago forfiles / p  "D:\backup\db"  /m *.* /d -60 /c  "cmd /c del  @file  /f" : Exit

After the batch script is written, and then added to the taskschd.msc every day to trigger execution, the specific configuration method is as follows:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/9F/6A/wKioL1mcXffRxvKvAAHliq4qtMk081.png-wh_500x0-wm_ 3-wmp_4-s_2952152744.png "title=" General "alt=" Wkiol1mcxffrxvkvaahliq4qtmk081.png-wh_50 "/>

It is important to note here that it is important to check that it is running with the highest privileges in order to avoid running a failure.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/00/BA/wKiom1mcXtTRGRXAAAL8kKJ4-0w832.png-wh_500x0-wm_ 3-wmp_4-s_20486992.png "title=" trigger "alt=" Wkiom1mcxttrgrxaaal8kkj4-0w832.png-wh_50 "/>

Here the trigger conditions are selected once a day, depending on the actual production environment to judge, the start of the task is scheduled

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/9F/6A/wKioL1mcX4jzb4blAAHW4UENUBU925.png-wh_500x0-wm_ 3-wmp_4-s_3186311080.png "title=" action "alt=" Wkiol1mcx4jzb4blaahw4uenubu925.png-wh_50 "/>

Fill in the path of the batch script here and the initial directory, it is necessary to note that the initial directory must be configured, or do not add the batch script added to the PATH environment variable, the task will not find the batch script to execute the failure

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/00/BA/wKiom1mcYGfR_y3mAAIsbDnl2zc272.png-wh_500x0-wm_ 3-wmp_4-s_2197678179.png "title=" condition "alt=" wkiom1mcygfr_y3maaisbdnl2zc272.png-wh_50 "/>

Conditions here can be selected according to the actual situation

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/9F/6A/wKioL1mcYPWyBJADAAHz67d2CoQ590.png-wh_500x0-wm_ 3-wmp_4-s_3924522108.png "title=" Settings "alt=" Wkiol1mcypwybjadaahz67d2coq590.png-wh_50 "/>

There must be a tick to allow the task to run on demand, the rest of the time on the actual situation, and finally after the confirmation of saving input user password so that every day the system will automatically do mysqldump logical backup.

This article from "Jim's Technical Essay" blog, declined to reprint!

Batch script for automatic backup of MySQL under Windows