Using MySQL in Windows: Implementing Automatic Scheduled Backups

1. Write a backup script

rem auther:www.yumi-info.com
rem date:20171222
rem ******MySQL backup start********
@echo off
forfiles /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 on
rem ******MySQL backup end********

This is a very common Windows batch 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 in the backup directory. "E:\mysql\MySQL BackUp" is the path where the backup file is located, which can be modified by yourself. "backup_*.sql" refers to all database backup files in this path that start with "backup_" and end with ".sql". The number "30" at the end means it will expire 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 the following string of rules, which is simply the current date and time.

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

This line of command calls the backup tool that comes with MySQL. Note that this path must be written as the path where your own "mysqldump.exe" is located, which is usually in the /bin directory of the MySQL installation path. This command line is followed by a long list of parameters. Let's pick out a few important ones to explain:

--user=root

The account used to connect to the MySQL database service. Usually, this account must have the permission to perform database backup operations. For the sake of simplicity, we used root, but it is not recommended to use the root account in an actual production environment to avoid account and password leakage, which would cause unnecessary trouble.

--password=123456

This is the password 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 implements the operation of backing up the database to a specified file. "yumi_website" is the database that needs to be backed up, and the part to the right of the greater than sign ">" is the server directory and file name where our backup file is saved.

2. Set up Windows tasks

After completing the above steps, we need to add a Windows scheduled task.

In Windows Server 2012, we enter the server management panel, click "Tools" in the upper right menu bar, and select "Task Scheduler":

After opening the Task Scheduler, we click "Create Basic Task" on the right:

Then, we need to fill in the name of the task and description:

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

Click "Next" again and set the time for the task to be executed. I chose 1 o'clock in the middle of the night:

In "Next", we select "Start a program":

In the subsequent dialog box, we need to select the batch file we just wrote:

After completing these steps, Windows will give us an overview of the entire task:

After confirming that everything is correct, click "Finish". At this point we will see a new task in the Windows task list:

At this point, the settings for automatic MySQL backup in Windows environment are complete.

The above article "Using MySQL in Windows environment: Implementing automatic scheduled backup" is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.