[ Visit our Open Source Software page to obtain other Open Source programs ]

[ Back to the MySQL Backup Page ]

MySQL Backup Version History

v.3.1 - Nov. 21, 2003

- Added code to check if file has already been
uploaded to the ftp site -- it yes, aborts.

Note that if your filename has seconds
in the datetime string, you shouldn't run into
the ftp overwrite / bad file descriptor problem.
In any case, the script will abort if the file exists.

- Added code to check the filesize of the uploaded file,
and removed code that simply listed the name

- rearranged the order of the message/code lines
in the ftp section to be clearer
                           
- changed order of --result-file in mysqldump
to *before* the dbname 

v.3.0 - May 17, 2003

- Note that I skipped from 2.7 to 3.0
  because I consider this a 'major' upgrade.

- See the end of this v3.0 section for acknowledgements.

- NEW: MYSQLDUMP --RESULT-FILE FLAG!
  new method of invoking mysqldump: --result-file
  NOTE!! some earlier versions of mysqldump don't
  have the --result-file flag -- if your version
  doesn't, you may have to
  a) upgrade mysql or
  b) use the outfile or normal_select method
  I tested it on:
  - Linux with MySQL v3.23.54 - worked fine
  - Win98 with MySQL v3.23.33 - no result-file flag
  I searched and SEARCHED :-) through the History
  notes at mysql.com to find out when they introduced
  the result-file flag, but I haven't found it yet.
  If someone finds it, let me know.

  I use the --result-file=$file and system call method
  with mysqldump because I had problems with the
  backtick and > redirection symbol on Win98, running
  from the script. I'm still trying to figure out why.
  Also note that the system method with a list
  doesn't invoke the shell, so that we don't run into
  the 127 character command limit on Win98.

- I now longer parse the cnf file manually.
  (I actually didn't use the parsed data
  in 2.7, but I hadn't deleted the code.)
  If you select cnf_file, mysqlshow and mysqldump
  will use the cnf_group variable
  thus, your cnf file can include
  multiple groups if you wish.

- added the mysqlshow and mysqldump flag:
  --defaults-extra-file=$cnf_file
  so that cron will find the correct cnf file
  Thanks to Kaloyan Tenchov.

- added a note about the env var:
  $ENV{'MYSQL_UNIX_PORT'}.
  If your MySQL socket file is NOT
  in a default directory, such as:
  /var/lib/mysql/mysql.sock
  then you may need to use the above
  environment command.

- uncommented
  $dbh->{PrintError} = 1;
  $dbh->{RaiseError} = 0;
  and changed PrintError to 1
  and RaiseError to 0
  so that errors don't cause the
  script to die, but still provide output

- added variable for the command 'ls'

- removed the need for the utility commands
  find, rm, xargs, wc
  by recoding two subroutines in Perl.
  (delete_text_files and clean_old_files)
  (There's always more than one way to do it. :-)
  This was stimulated by the problems with
  backticks and pipes on Win98.
  I now use File::Path to remove directories
  and simple directory arrays to remove files,
  since the directories aren't nested.

- added note with urls to get windows
  versions of unix utilities used in the script

- updated automatic method of the whereis
  section that finds commands (cleaner)

- added chomp command to whereis output

- added routine to check for script utilities
  on the disk and abort with error if not found

- changed the zip filename in the MIME::Lite
  attachment section to not have the path

- added a bit of code to clear the screen when
  the script is run (from the shell)

- added option for bzip2
- added options for gzip and bzip2 parameters
- changed gzip output labels to include bzip2

- added a compress_method option for
  standard two step compression, or tar -z,
  or the tar gzip pipe method.

- changed code to delete text files in between
  tar and gzip if options are set.

- moved code to delete text files into subroutine

- modified some of the output

- added a variable $print_stdout
  so that if it's not set to 'yes', the messages
  will only get sent to the admin email, rather
  than printed to the stdout (screen)
  Thanks to Matthew Moffitt for the idea.

- create &message subroutine to make the print
  and $body_text email statements cleaner.
  (the &message subroutine allows selective
  output, using 's' for screen, 'e' for email,
  and 'b' for both)

- fixed mysql outfile method,
  by chmoding tar_dir to 777 so that mysql
  can write to that directory.
  Also added full path name to the outfile.

  (The last time I tested the outfile method,
  I was using a separate instance of mysql,
  running as my own user, rather than 'mysql',
  which is why it worked then, but not when
  it runs as mysql.)

- added code to chmod tar_dir to 700 if
  $chmod_backup_file is set to yes; otherwise
  it's chmoded to 755 (from 777 - see above)

- added error routines to check for the creation
  of tar_dir, the existence of mysql_backup_dir,
  the creation of the backup files, and the
  deletion of the files to be deleted.

- added a number of variables and code bits
  that allow one to run the script from the
  web, with a password protected login,
  for users that don't have shell access.

- re-arranged the layout of some of the setup
  variables, to be more convenient (some are
  closer to the top of the file).

- added notes about libintl-2.dll and libiconv-2.dll
  I needed to install these dlls in \windows\system
  under Win98.

- added the record delimiter :!: to the outfile and
  normal select method of backing up (with a \n)
  to accomodate multiline text records easily

- note about segmentation faults on startup:
  As far as I can tell, some versions of DBI/DBD::mysql
  caused a fault when the connect parameters
  'mysql_read_default_file=$cnf_file"' and
  'mysql_read_default_group=$cnf_group"' were used.
  The mysql site has notes about this.
  If you're experiencing this:
  1. try to use the 'this_file' method of connecting,
     and commment out the cnf file params, and
  2. check out the mysql site for a better version
     of DBI/DBD.

- modified the email commands to use Net::SMTP
  for Windows users (or for anyone)
  Note that to successfully email from Windows,
  with Net::SMTP, you need to connect to a running
  mailserver. I had good results with the Microsoft
  IIS mailserver on Win2000, although it was a pain
  to setup (I didn't set it up :-).

- placed die print commands in Mime::Lite routines,
  instead of &error_message, because &error_message
  invokes the email routine, which would cause a loop.

- confirmed that passive ftp with ssh tunneling works.

- moved the contents of $mysqldump_params into the
  subroutine do_backup, because of the system call syntax.

- added notes about strftime under WinX.

- moved the use Net::FTP; and use MIME::Lite;
  lines up to the associated vars for convenience.

- Acknowledgements:
  =================
  Thanks to all the suggestions, code ideas and
  patch contributions from our users!
  About patches: I usually "integrate" patches,
  or patch ideas, into the program within my own
  particular coding style. So, most patches won't
  appear exactly as submitted (often because I
  change things alot as I upgrade.)

  However, I deeply appreciate the patch efforts,
  and code suggestions. v3.0 is due to all of your
  comments and input! Thanks go to: (listed by alpha)

  Achim Dreyer, Alexander Klein, Andreas Gunleikskas,
  Artur Neumann, Bob Uehlein, Borracho, Carsten Grohmann,
  Cesar Mendoza, Chris Wright, Christian Mohn, Coffe,
  Craig Paterson, Daniel Myers, David Adams,
  Eric Eichberger, Erik Meusel, Frank Van Damme,
  Ilya Palagin, Jean-Francois Laflamme, Joe Claborn,
  Jonathan Hutchins, Joris, Kaloyan Tenchov, Ken Kirchner,
  Kenneth Kabagambe, Kevin Zembower, Manfred Larcher,
  Martin Kos, Matthew Boehm, Matthew Moffitt,
  Michael C. Neel, Michael Haydock, Michael Klug,
  Mike Yrabedra, Nate Parsons, Normand J. Charette,
  Paul Kremer, Russell Uman, Seth Rajkumar, Stephen Calia,
  The Sysadmin of Oceanet Technology, Vladyslav Shvedenko,
  Zak Zebrowski (I apologize if I forgot anyone! :-).

v2.7 - June 11, 2002

- Bug fix:
  moved the two 'use' statements for
  Net::FTP and MIME::Lite to the top of the file
  with instructions to comment out those two lines
  if the libraries aren't installed, since the
  'use' lines do get parsed.

v2.6 - June 10, 2002

- added the ftp_option
  The initial ftp code was contributed by
  Gil Hildebrand, Jr. (root@moflava.net)
  with additional code by myself.
  (added error checking, extra params, etc.)

- added code to skip loading
  Net::FTP and MIME::Lite if
  $ftp_backup and $email_backup are set to 'no'
  If you don't want to install them,
  you no longer have to comment out code sections.
  (see v2.7 above for bug fix)
  (the main code doesn't have to be commented out,
  but the 'use' lines do.)

- changed semi-colon ; to comma , after:
  attach $msg ....
         Path     =>"$backup_gzip_file",
  Thanks to Kenneth Martis for finding the bug.

- added a segment under $find_commands
  to configure the paths for the command line utils
  mysqldump, mysqlshow, nice, tar, gzip
  Thanks to Luc Schiltz for the concept.
  NOTE: If you use the automatic option, you
  should of course check the results.

- added --host=$db_host to
  mysqlshow and mysqldump commands
  so one can use a remote host.
  Thanks to Serge Colin and Gil Hildebrand, Jr.
  for the suggestion.

- separated Mandatory Variables from Optional Variables
  in the setup section, to make setup quicker.

v2.5 - March 1, 2002

- removed the password from the mysqldump and mysqlshow
  lines if .cnf files are being used, since using the
  user/pass on the command line shows up in 'ps'.
  (Thus, it's highly advisable to use a .cnf file!)

- added $show_file_list_in_email var, to trim large emails.
  The file list will not be included in the email unless
  the var is set to 'yes'.

- Added functionality to backup LARGE systems, i.e:
- changed the tar method to tar a subdirectory with all
  the files, so tar doesn't choke if there are too many
  files. The subdir is removed once the tar file is made,
  if $delete_text_files is set to yes. If not, the files
  in old tar_dirs are cleaned out later by clean_old_files.

- modified 'ls -la' to use xargs so that large directories
  don't choke.

v2.4 - June 20, 2001

- A bug fix of a bug fix. Oy.
  Changed 'w_user' and 'w_password'
  in the setup section back to 'user' and 'password'
  to make it more consistent with .cnf files, and to
  fix a bug I created when I changed it in v2.2
  (with mysqldump looking for $user)
  Now, we use $user and $password everywhere.
  Thanks to our sharp eyed users!

v2.3 - June 5, 2001

- Changed the ~/ for the home directory in the vars
   $cnf_file, $ENV{'MYSQL_UNIX_PORT'} and $mysql_backup_dir
   to use absolute paths instead of the ~/. The ~/ didn't
   work, and is a fine example of the need for testing :-)
   I thought I was being clever and convenient, but I
   actually didn't use it on my system (I used the absolute
   paths instead.) Thanks to Rick Morbey in London.

v2.2 - May 25, 2001

- Bug fix; a typo. Changed 'user' and 'password'
  in the setup section to 'w_user' and 'w_password'
  so that the connect_to_db routine works.
  Thanks to Glen Knoch.

v2.1 - May 23, 2001

- First public release of new Perl version
- bug fixes and added some options
  . changed 'mysqlshow db %' for tables, to 'show tables'
  . added prefixes to file deletes for safety
  . fixed error in regex that checked for text file delete
  . changed %e to %d in date string for file name
  . added vars for nice, tar, gzip paths
  . removed &error_message($error_text) from &mail_to,
    to avoid recursive loop, since &error_message calls &mail_to
  . fixed error in attached email file path
  . commented out $dbh->{PrintError} = 1; and
                  $dbh->{RaiseError} = 1; so that the script
                  wouldn't die before emailing the error.
  . made all vars in &do_backup 'my' to avoid conflicts

v2.0 - February 15, 2001

- completely rewritten as a Perl script
  . added all core options

v1.0 - January 2, 2000

- written as a simple shell script

- Back to the Top Open Source Page