Howto: Backup your Bitnami Stack’s MySQL DB

June 26, 2010

Below I will explain how to take nightly backups of your MySQL database

Download and Install:

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