Using MySQL in Windows: automatic and scheduled backup, windowsmysql

Using MySQL in Windows: automatic and scheduled backup, windowsmysql

I. Write backup scripts

rem auther:www.yumi-info.comrem date:20171222rem ******MySQL backup start********@echo offforfiles /p "E:\mysql\MySQL BackUp" /m backup_*.sql -d -30 /c "cmd /c del /f @path"set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%""E:\mysql\MySQL Server 5.6\bin\mysqldump" --opt --single-transaction=TRUE --user=root --password=123456 --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "yumi_website" > "E:\mysql\MySQL BackUp\backup_%Ymd%.sql"@echo onrem ******MySQL backup end********

This is a common windows batch processing script file. Let me briefly explain the key parts:

forfiles /p "E:\mysql\MySQL BackUp" /m backup_*.sql -d -30 /c "cmd /c del /f @path" 

Forfiles is used to delete expired backups under the backup directory. "E: \ mysql \ MySQL BackUp" is the path of the BackUp file, which can be modified by yourself. "Backup _ *. SQL" indicates all database backup files starting with "backup _" and suffixed with ". SQL" in this path. The subsequent number "30" indicates that it expires in 30 days.

set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%" 

Use the set command to define a variable named "Ymd". The value of this variable is a series of rules that follow, simply put, the current date and time.

"E:\mysql\MySQL Server 5.6\bin\mysqldump" 

This line of command uses the MySQL tool to write this script into the path of your MySQL installation path, which is usually in the/bin directory of the MySQL installation path. This line of command is followed by a large string of parameters. Let's pick a few important explanations:

--user=root 

The account connecting to the MySQL database service. Generally, this account must have the permission to perform database backup operations. We have used root for ease of use, but we do not recommend using the root account in the actual production environment, to avoid account and password leakage, resulting in unnecessary trouble.

--password=123456 

This is the password used to connect to the MySQL database service.

--host=127.0.0.1 

This is the IP address of the server where the Database Service is located.

--port=3306 

This is the port number of the server where the Database Service is located.

--events "yumi_website" > "E:\mysql\MySQL BackUp\backup_%Ymd%.sql" 

The events parameter backs up a database to a specified file. "Yumi_website" is the database to be backed up, and the server directory and file name stored in the backup file are on the right of the database larger than ">.

2. Set Windows tasks

After completing the preceding steps, we need to add Windows scheduled tasks.

In Windows Server2012, go to the server management panel, click "Tools" in the menu bar in the upper right corner, and select "Task Scheduler ":

After opening the task scheduler, click "create basic task" on the right side ":

Then, we need to enter the Task Name and description:

After clicking Next, we need to set the task execution frequency. I chose "Daily ":

Click "Next" again to set the task execution time. I chose the quiet one point:

In "Next", select "Start Program ":

In the subsequent dialog box, select the batch file you just compiled:

After completing these steps, windows will show us the overview of the entire task:

Click "finish. In this case, a new task is added to the Windows Task List:

Now, all the settings for automatic MySQL backup in Windows are complete.

MySQL is used in the Windows environment: automatic and scheduled backup is all the content shared by Alibaba Cloud xiaobian. I hope to give you a reference and support for the customer's home.