如何在Windows下通过批处理文件用mysqldump备份恢复MySQL数据库？

Got it, let's walk through setting up automated MySQL backups on Windows—this involves creating a batch file with the backup command, scheduling it via Windows Task Scheduler, and covering restore steps too.

First, you need a batch file that runs the mysqldump command to back up your databases. Here's how to put it together:

• Open Notepad or any text editor.
• Paste in a command like this (adjust the values to match your setup):

  @echo off
  REM Set your MySQL installation path, backup save path, credentials, and database name
  set MYSQL_PATH="C:\Program Files\MySQL\MySQL Server 8.0\bin"
  set BACKUP_PATH="D:\MySQL_Backups"
  set DB_USER=root
  set DB_PASS=your_password_here
  set DB_NAME=your_database_name
  
  REM Create backup directory if it doesn't exist
  if not exist %BACKUP_PATH% mkdir %BACKUP_PATH%
  
  REM Generate a timestamp for the backup file (so each backup has a unique name)
  set TIMESTAMP=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
  set TIMESTAMP=%TIMESTAMP: =0%  REM Replace spaces in timestamp with 0
  
  REM Run the mysqldump command to create the backup
  %MYSQL_PATH%\mysqldump -u%DB_USER% -p%DB_PASS% %DB_NAME% > %BACKUP_PATH%\%DB_NAME%_%TIMESTAMP%.sql
  
  REM Optional: Add a success message (you can check this in the task history later)
  echo Backup completed successfully at %TIMESTAMP% >> %BACKUP_PATH%\backup_log.txt
  

• Save the file with a .bat extension (e.g., mysql_backup.bat). Make sure to choose "All Files" as the save type to avoid saving it as a .txt file.

Now we'll set up Windows to run this batch file automatically:

• Press Win + R, type taskschd.msc, and hit Enter to open Task Scheduler.
• On the right-hand pane, click Create Task (not "Create Basic Task"—this gives you more control).
• In the General tab:
  • Give your task a name (e.g., "Daily MySQL Backup") and a description.
  • Check "Run whether user is logged on or not" if you want backups to run even when you're not signed in.
  • Check "Run with highest privileges" (this helps avoid permission issues with writing to the backup folder).
• Go to the Triggers tab:
  • Click New to set when the task runs (e.g., daily at 2 AM). Adjust the schedule to fit your needs.
• Go to the Actions tab:
  • Click New, set "Action" to "Start a program".
  • In "Program/script", browse to select your mysql_backup.bat file.
  • Leave "Add arguments" and "Start in" blank unless you need to specify a working directory.
• Go to the Settings tab:
  • Check "Allow task to be run on demand" so you can manually trigger backups too.
  • Adjust other settings like "Stop the task if it runs longer than" (e.g., 1 hour) as needed.
• Click OK and enter your Windows credentials when prompted—this is needed for the task to run in the background.

You can tweak a few more settings to make the backup more reliable:

• In the Settings tab, check "Run task as soon as possible after a scheduled start is missed" to catch up if the computer was off during the scheduled time.
• Set up email notifications (via a separate script in the batch file) if you want alerts when backups succeed or fail.
• Compress the backup SQL file to save space—you can add a command like 7z a %BACKUP_PATH%\%DB_NAME%_%TIMESTAMP%.zip %BACKUP_PATH%\%DB_NAME%_%TIMESTAMP%.sql (you'll need 7-Zip installed and added to your PATH).

If you ever need to restore from a backup, here's how:

• Open Command Prompt (as Administrator).
• Navigate to your MySQL bin directory (e.g., cd "C:\Program Files\MySQL\MySQL Server 8.0\bin").
• Run the restore command (replace placeholders with your details):

  mysql -u root -p your_database_name < "D:\MySQL_Backups\your_backup_file.sql"
  

• Enter your MySQL password when prompted, and the database will be restored from the backup file.

内容的提问来源于stack exchange，提问作者danny