Below I will explain how to take nightly backups of your MySQL database
Download and Install:
- MYSQL GUI Tools
- 7zip
- Windows Server 2003 Resource Kit Tools to obtain robocopy
After you install the tools bring up a command prompt and run these commands to copy robocopy to our working folder (1BACKUP_MYSQL):
cd \ cd .. cd \ c: mkdir 1BACKUP_MYSQL copy "C:\Program Files\Windows Resource Kits\Tools\robocopy.exe" C:\1BACKUP_MYSQL
Creating a MySQL backup plan
1. Run MYSQL Administrator (Start -> Programs -> My SQL)
Connect with using the following…
Serverhost: localhost
Port: 3306
Username: root
Password: (whatever pw you created when you installed the bitnami stack)
If you forgot your password you can find it by looking in this file: C:\Program Files\BitNami WAMPStack\mysql\my.ini
2. In MYSQL Administrator click “Backup”
3. Click button “New Project”
4. Select your databases in the left listbox and click the arrow button to place them in right list box
5. Fill out the “Project Name” textbox with something meaningful like “backup plan”
6. Click “Advanced Options” tab
7. Under “backup execution method” select “Lock all tables”
8. Click “Schedule” tab and schedule your backup for 1AM (or whatever time you want) daily
9. Type in “C:\1BACKUP_MYSQL\” for your backup folder
10. Select execute backup daily
11. Click button “Save Project”
12. Click “Execute Backup Now”
Creating the backup .bat file
Open up notepad and paste this in:
Set DD_MM_YYYY=%DATE:~4,2%_%DATE:~7,2%_%DATE:~10,4% cd \ cd c: cd 1BACKUP_MYSQL "c:\Program Files\7-Zip\7z" a -t7z %DD_MM_YYYY%_MYSQL_BACKUP.7z *.sql -r -mx9 DEL /S *.sql /F /Q robocopy C:\1BACKUP_MYSQL "C:\My Dropbox\backup\brakertech" /E /R:2 /XF "mysql_backup.bat" /XF "robocopy.exe" /TEE exit
You will probably need to modify “C:\My Dropbox\backup\brakertech” to whatever path you would like to copy your compressed sql backups to.
Create a scheduled task for backup
Follow Instructions here to create a scheduled task. Don’t forget to have the task run as a user that has the proper permissions to wherever you are writing the robocopied files to