Restore mySQL from dump file by accident - Added a verification to batch script

[DaveLembke]

Years ago I created some batch files to make backing up and restoring my databases simple. However I learned the hard way today after years of using these batches that my restore batch should have had a verification process before running as for I meant to run a backup and accidentally ran a restore, stomping out all the latest data on my database.

The good thing is that I didnt lose much work little changed between the prior day and today, BUT... I learned that I need to put a fail safe in the batches so that if I click to run the wrong one I have a means of stopping it vs it being an instant overwrite.

The restore batch now prompts warning 3x before running with 3 key presses. Just enough to catch your attention to exit the command shell window so that it doesnt run any further. I also added the color command to it to show in red vs green, whereas before my batches were minimalist and just black command shell background with white text.

The backup batch, I didnt add any prompts to because getting the latest backup isnt a bad thing. However if you want warnings before running a backup you can simply replace the line of:

mysql  -uroot -pYourDatabasePasswordHere< alldatabases.sql

with

mysqldump --all-databases> alldatabases.sql -uroot -pYourDatabasePasswordHere

In the batch that gives you 3 warnings before triggering and save that as a backup batch and chose a color combination that makes sense to warn you that its the backup and not the restore such as replacing red background with white text color cf with color e0 for a shell window that is black text on yellow background, so they dont look the same, you will know that yellow background is backup and red background is restore. You will also want to change the text of what is going on so that it doesnt say overwriting database when its actually backing it up if you chose to do this.

So just sharing these here in case anyone else dabbles with mySQL databases and might find them useful when performing database dumps and restore from dump files.

(Note: The restore batch which prompts 3 times before actually pushing the dump file to database could be cleaned up with a loop and count down before exiting loop and triggering but I just did it quickly copy/pasting in notepad redundant info vs reuse of the same messages.)

Just need to put your password to your mySQL server in place of YourDatabasePasswordHere within the batch scripts below. As well as user name in place of root if your not connecting as root. Passwords within batches can be a security issue, but only if others have access to the batch files. 

Additionally depending on how you have your mySQL database set up, if its configured to invoke mySQL instructions from any path on the system this can run anywhere on your system, however if not configured to invoke mySQL instructions from any path, you would need to then run this in the path that contains mysql.exe and mysqldump.exe, or these 2 files can be copied from your mySQL server such as on my system from c:\mySQL\bin\   to the location of your batch files and work fine communicating with your mySQL database at the default port 3306 without the batches having to be located at c:\mySQL\bin\

Here is the Backup Batch Script

Code: [Select]

color a0
echo off
cls
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo.     mySQL BACKUP - All Databases Version 2.0
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo. Backup Started at   %time% on %date%
@echo.
@echo. Backup Started at   %time% on %date%>>Backuplog.txt
mysqldump --all-databases> alldatabases.sql -uroot -pYourDatabasePasswordHere
@echo. Backup completed at %time% on %date%>>Backuplog.txt
@echo.
@echo. Backup Completed at %time% on %date%
@echo.
@echo.
@echo.
@echo.
@echo. ------------------------------------------------------>>Backuplog.txt
pause
Here is the Restore with accidental execution protection requiring you to press any key 3 times before it overwrites your database with an older database dump, if one exists.

Code: [Select]

color cf
echo off
cls
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo.        mySQL RESTORE - All Databases Version 2.0
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo.    WARNING - This will overwrite the current database
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo. Any Key 3x to continue -or- [x] out of Window to Cancel
@echo.
@echo.
@echo.                           [ 3 ]
@echo.
@echo.
@echo.
pause
cls
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo.        mySQL RESTORE - All Databases Version 2.0
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo.    WARNING - This will overwrite the current database
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo. Any Key 3x to continue -or- [x] out of Window to Cancel
@echo.
@echo.
@echo.                           [ 2 ]
@echo.
@echo.
@echo.
pause
cls
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo.        mySQL RESTORE - All Databases Version 2.0
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo.    WARNING - This will overwrite the current database
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo. Any Key 3x to continue -or- [x] out of Window to Cancel
@echo.
@echo.
@echo.                           [ 1 ]
@echo.
@echo.
@echo.
pause
cls
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo.        mySQL RESTORE - All Databases Version 2.0
@echo.
@echo. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo.     Please Wait --- Overwriting the current database
@echo.
@echo. ----------------------------------------------------------
@echo.
@echo. Restore Started at   %time% on %date%
@echo.
@echo. Restore Started at %time% on %date%>>RestoreLog.txt
mysql  -uroot -pYourDatabasePasswordHere< alldatabases.sql
@echo. Restore completed at %time% on %date%>>RestoreLog.txt
@echo.
@echo. Restore Completed at %time% on %date%
@echo.
@echo. ------------------------------------------------------>>RestoreLog.txt
@echo.
@echo.
@echo.
@echo.
pause