CyberNotes
Tutorial Thursday
 

One concern that many Website owners have is that their site will get hacked into and they will lose everything. That is why backup software normally costs an arm and a leg because people are willing to pay for a peace of mind.




That peace of mind no longer has to break your pocket book, in fact, you don’t even have to open your pocket book! The software I am about to discuss will help you setup a batch file to perfom a MySQL database backup. Then I will show you how to schedule the backup along with a few other details. Okay, I’ll go ahead and walk you through the steps.

–Getting Started–

  1. First you will need to go ahead and download the FREE software from WFF Systems.
  2. You can start the installation process by executing the file that you downloaded above.
  3. After you have completed the installation go ahead and run the software.

 

Create A Batch File

  1. Now that you have the software up and running you need to enter in the directory that you would like the backup to be stored. Enter this into the textbox located on the right-side of the program.
  2. Select the Add Database option and enter in the information for your database. Press OK after you have filled out all of the fields.
    Database Backup
  3. Select Backup and make sure the software is able to connect and backup your database properly. If it is successful you should see a backup file in the directory that you designated.
    Database Backup
  4. If you were able to successfully run the backup then you can close the software. Otherwise you will have to troubleshoot to figure out what part of your information is not correct.

 

–Edit The Batch File (Optional)–
This section will explain how you can add the date and time to the backup’s filename. If you do not perform these steps the backup will automatically overwrite the previous backup.

  1. Browse to the directory that you installed the software.
  2. Find the file named tmp.bat and right-click on it, then select Edit.
  3. Your file should be similar to this:
    "C:\Backup Software\mysqldump.exe" example_database -hexample.com -uexample_user -pexample_password >"c:\example_database.sql"
  4. In order to have it generate the date and time for the file name we need to add a little code to the batch file. Place this code before the text above:
    for /f "tokens=1,2" %%u in ('date /t') do set d=%%v for /f "tokens=1" %%u in ('time /t') do set t=%%u if "%t:~1,1%"==":" set t=0%t% set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2% echo %timestr%
  5. Now change the output of your file name to include the date/time variable. That means I would change "c:\example_database.sql" to "c:\example_database-%timestr%.sql".
  6. My final batch file looks like this:
    for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
    for /f "tokens=1" %%u in ('time /t') do set t=%%u
    if "%t:~1,1%"==":" set t=0%t%
    set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2%
    echo %timestr%
    "C:\Backup Software\mysqldump.exe" example_database -hexample.com -uexample_user -pexample_password >"c:\example_database-%timestr%.sql"
  7. Now each time you run the batch file it will generate a file name that looks like "example_database-200612071200.sql". The time is formatted as year/day/month/time.

 

–Schedule The Batch File–
Now we can schedule the file so that it will automatically perform the backups each day.

  1. Goto the Control Panel-> Scheduled Tasks-> Add Scheduled Task.
  2. Select Next to begin the wizard. It will take a little while for it to get to the next screen.
  3. Select Browse. Browse for the batch file that you created above. It is located in the program’s installation directory and it is called tmp.bat.
  4. Just complete the rest of the details about when you want to execute the backup…daily, monthly, etc.

 

–Overview–
I hope that this will help you out because I know this is a big time saver for me! The biggest issue that some people may have is finding their database’s name and the username/password.

Update:
DavidC commented below recommending the MySQL Administrator software. I haven’t used this before but it does look very powerful.