Posted: 2016-02-10 02:44:58
The best way to create and automate backups of MySQL databases is to:
1. Use the Windows Task Scheduler to automatically run a backup task every day or week.
2. Have the task run a BATCH file containing the “mysqldump” and “makecab” commands to export and compress the databases.
3. For additional recovery, use MySQL’s Binary Log files to record transactions between backup jobs (to rebuild the database to the last transaction recorded).
This solution will work for everything from Windows 10, down to XP, and Server 2003. No external tools are required.
Create a mysql-backup.bat file to export all the databases (or only select databases), using a DATE-TIME file-name stamp, and compress the SQL file…
@ECHO OFF set TIMESTAMP=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2% REM Export all databases into file C:\path\backup\databases.[year][month][day].sql "C:\path-to\mysql\bin\mysqldump.exe" --all-databases --result-file="C:\path-to\backup\databases.%TIMESTAMP%.sql" --user=username --password=password REM Change working directory to the location of the DB dump file. C: CD \path-to\backup\ REM Compress DB dump file into CAB file (use "EXPAND file.cab" to decompress). MAKECAB "databases.%TIMESTAMP%.sql" "databases.%TIMESTAMP%.sql.cab" REM Delete uncompressed DB dump file. DEL /q /f "databases.%TIMESTAMP%.sql"
Make sure to update all the paths used, and the MySQL’s username (root) and password (if no password is used, leave the “
--password” switch out).
Why use CAB instead of ZIP?…
MS-CAB files have almost 50% better compression ratios over ZIP (especially for single files), and the MAKECAB/EXPAND commands exist on all Windows versions.
Create a Windows Task to run the above BATCH file every day or every week.
Make sure that:
1. The user has rights to Log on as a batch job.
2. If the drive/path you are exporting to is a mapped drive, to use the UNC path.
3. If the drive/path is a shared folder, the user the task is ran on has the correct permissions.
Verify that my.ini has the Binary Log enabled, set to either a MIXED or ROW mode, and does not expire between backup tasks (make it 2x the frequency of the backup task schedule +1 day).
log-bin=mysql-bin binlog-format=MIXED expire_logs_days=15
This way you can restore to the last transaction recorded by replaying the log over the last backup job.