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

Last modified: November 1, 2010

Author

Comments

Write a Reply or Comment

Your email address will not be published.

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

This site uses Akismet to reduce spam. Learn how your comment data is processed.