Date-stamping a backup file

Ever since Tim and Ben have warned that the MySQL Administrator back up and restore functions as per my old tutorial are suspect and unreliable, I've been trying to do a back up script that means I just have to click something and the backup will run.

However my biggest challenge (probably because I'm not the sharpest tool in the IT shed) has been to add a date and timestamp to the backup. After help from google and *lots* of errors, I present the following code.

It's a Windows/DOS batch file.
Copy the contents of the code box below to a text file and save the text file with a name such as "ov_backup.bat" - the .bat suffix is important.
Doesn't really matter where you save it to.
Right-click on filename and "Send To Desktop (create shortcut)"

To run the backup, simply double-click on the shortcut.


set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
set year=%date:~-4%
set month=%date:~7,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
set day=%date:~4,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
set datetimef=%year%%month%%day%_%hour%%min%%secs%
mysqldump -u openvpms --password=openvpms --single-transaction openvpms > "C:\Backup_ov_files\ov17back_%datetimef%.sql"

This should give you a backup file that looks like this: "ov17back_20150316_114120.sql" which tells you that the backup was created on 16 Mar 2015 at 11:41:20h

My next step will be to create a scheduled task which will automate the whole process. If that works, I'll withdraw my previous tutorial andreplace it with this info.

Feedback welcome.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Date-stamping a backup file

Yuri - the Stack Overflow discussion at http://stackoverflow.com/questions/1192476/format-date-and-time-in-a-win... discusses the problems caused by different date/time formats.  Your code assumes a dd/mm/yyyy format.  If the user is in the US then the format is mm/dd/yyyy.

Initially I thought that your code was very 'wordy' but on reflection, it is easy to see where changes would be needed if one was running mm/dd/yyyy format.

The Stack Overflow article contains some very sexy recipes that are environment independent but I for one am hesitant to use them.

 

Note that the 1.8 help stuff contains http://www.openvpms.org/documentation/csh/1.8/topics/how/administering-o...

This has sections on backup and log housekeeping.  It might be an idea for you to either reference this stuff or to incorporate your date/time stamping code in the backup section.

I must admit that in Hong Kong where we do raw file backups, I just hold the current and previous 3 backups different folders using the following rename code:

set BKP=\\Eiah_nas\backups\OpvBackup\Data
if not exist %BKP% goto DoCopy
if exist %BKP%-3 rmdir /S /Q %BKP%-3
if exist %BKP%-2 ren %BKP%-2 Data-3
if exist %BKP%-1 ren %BKP%-1 Data-2
if exist %BKP% ren %BKP% Data-1
:DoCopy

The dates of the folders tell me when the backup were done.

Regards, Tim G

Re: Date-stamping a backup file

Hi Tim,

>Initially I thought that your code was very 'wordy' but on reflection,
>it is easy to see where changes would be needed if one was running mm/dd/yyyy format.

Correct, one simply swaps "4" for "7" and vice-versa in the "set month" and "set day" lines.   The StackOverflow site was where I found the code snippet I used (I stripped out all the echos once I'd  debugged my code), took me a bit of time to learn to count characters from zero, not 1, when setting up date selection (see, I didn't claim to be sharp smiley ! ).  So if I add a couple of rem lines, that should explain changes needed.  I confess I didn't think about making the batch file environment-independent - I was happy just getting it working reliably on my machines here in Australia.  (It's also why I placed the post in the Implementation rather than the User forum.)


rem on next line use 7 for AU date format or replace with 4 for US date format
set month=%date:~7,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
rem on next line use 4 for AU date format or replace with 7 for US date format
set day=%date:~4,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%

On my system, once the backup is done once daily (at 7am), I now copy the file (a command in the batch file) to both a local external hard drive and also to my dropbox folder.  Then at my offsite location, I schedule a copy at noon from my dropbox folder to a local hard drive folder.  (This in case I forget to changeover the external hard drive at work on a regular basis).

"Poor man's" automation, I know, but I'm performing and saving more backups now than I've ever done before: local, offsite and "cloud" copies. And all without purchasing / installing extra software.

Yuri.

Re: Date-stamping a backup file

http://mysqlbackupftp.com/download/

 

I have been using it for a few months now seems ok.

It Date / Time stamps schedules backups and emails them or posts them to cloud.

 

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Date-stamping a backup file

Yuri - I second Ben's suggestion. I just grabbed the free version and had a play.  Although it is called mysqlbackupftp it will happily backup to a local or network drive.  It also takes care of all the scheduling and purge old and datestamping. It even sent me an email to say what what happened.

The only problem I had was setting up the smtp - I could not get it to use my bigpond smtp service - but that to gmail worked.

My recommendation is to use this and don't worry about building your own bat files and setting up scheduled jobs.

Regards, Tim G

 

Re: Date-stamping a backup file

Sorry, duplicate entry when editing

Kind regards,

Anthony (ActiVet)

Re: Date-stamping a backup file

Thanks Ben for your suggestion, this software is exactly what I was looking for! But I do have a question with the created backup file. I thought the software utilised mysqldump to create the backup file but when I tried to restore the unzipped file using the normal mysql command line, it was not successful (while I had no problem restoring one created by mysqldump). Is there a special restore method that we'll have to use for backup files created by mysqlbackupftp? Any advice is much appreciated!

Kind regards,

Anthony (ActiVet)

Kind regards,

Anthony (ActiVet)

Re: Date-stamping a backup file

Anthony - you may have the same problem as I did. mysqlbackupftp does not by default set the drop tables option (whereas mysqldump's standards option settings do).  Hence when you do a restore to overwrite an existing database it fails because the tables do get dropped. In the Advanced settings, click More SQL options, and set as follows:

By the way, although I have this running on our system, I will probably drop it in favour of mysqldump.  The problem is that mysqlbackupftp is not very efficient in terms of how it generates the output.  There are two problems: a) it runs mysqldump piping the output rather than using the mysqldump results-file parameter; and b) if you turn off zip compression (sensible if the majority of you data is in jpegs images in the documents file) then it still copies the file from its temp folder. The end result is that the dump file gets copied twice and it does not need to be.

The above problems came to light setting things up for a practice whose database is around 30GB.  In their case I did not have to worry about date stamping - their backup software does that. ie mysqldump runs at 3am and at 4am their backup software puts a copy of the dump (and other stuff) in a date-stamped location on a removable disk.

Regards, Tim G

Re: Date-stamping a backup file

Hi Tim,

Thanks for your tip! I updated the setting as above in mysqlbackupftp and tried to restore the database on a backup machine for testing but I still encountered an error message as below:

"ERROR 2006 (HY000) at line 683: MySQL server has gone away"

I've selected no compression by the way. While there was no problem restoring a backup file using mysqldump. The file size is not too much of a problem for us at this stage because a mysqldump backup file is under 400Mb and the one produced by mysqlbackupftp is around 550Mb, but I can see why you said it is not ideal if the database is bigger.

I think mysqlbackupftp is great because it provides a local backup file and can upload to dropbox which is an excellent option for us. Would you have any suggestion to get it to work? Am I still missing something?

Kind regards,

Anthony (ActiVet)

Kind regards,

Anthony (ActiVet)

Re: Date-stamping a backup file

Anthony - try googling 'mysql error 2006 (hy000) at line'. There is a lot of stuff about this error.

One possibility is that you are hitting the Max Allowed Packet limit - see (for example) the last answer in http://stackoverflow.com/questions/8744813/mysql-error-2006-hy000-at-lin...

Note that (if you have a standard setup) you have max_allowed_packet set to 16M.  I wonder what happens when you have a large image (say 12MB) and you are running MySQLbackupFTP with the hex blob option set (which will expand the size of the image by (I think) a factor of 2) and hence 2x12MB > 16MB == fail.

Try turning off the hex blob option.  [I think this is a safe option for when you create the dump on one type of machine and restore on another - google 'little endian' for background.  However, since you are backing up and restoring on the same machine - or at least another with same endian'ness, then you can happily dump with hex blob turned off.]

Alternatively try pushing up max_allowed_packet - the system with the giant database that I was playing with used max_allowed_packet set to 64MB.

Regards, Tim G

 

Re: Date-stamping a backup file

Hi Tim,

Thanks again for your suggestions. I tried to increase both the working server and the backup server's max_allowed_packet size to 64Mb and was able to complete the restore command (backup and restore performed after restarting the computers following the change), but then OpenVPMS would not load up...... arrrg!

At this stage, I decided to revert back to Yuri's batch file and created a Windows schedule task to run the backup daily. Then I installed Dropbox for Windows to sync my backup file to cloud. I think that would be the best solution for us for now :) (I also set our max_allowed_packet size back to 16Mb)

Thanks Yuri for the original post!

Kind regards,

Anthony (ActiVet)

Kind regards,

Anthony (ActiVet)

Syndicate content