MySQL Backup v3.4

MySQL Backup is written in Perl. It uses mysqlshow to grab the database names and "show tables" to grab the table names for a user's account, and then uses mysqldump to save the data in a subdirectory named in the script. It then tars and gzips the files, using the date and time for the file name. It can be run from cron on a daily basis. It now removes old files and has an option to email the gzip file to an admin and/or ftp the files to a remote server. It also has options to use "select data into outfile" or a regular "select" for users who can't use mysqldump. (Some hosts don't allow that.)

New Features in 3.3:

• Added code to make sure remote directory to delete ftp files matches setup variable for remote directory when using "pwd".
  
  If it doesn't match, the 'ls' command returns zero files, so the remote list of files never gets deleted. One doesn't want one's remote server to fill up!
  
  Ftp 'ls' requires the beginning slash to read the file list. If it doesn't have the beginning slash, it would assume the directory is a subdirectory of the current directory.
  
  'pwd' produces a trailing slash, so both slashes are required in the setup ftp directory variable.
  
  
• Added code to remove '.' and '..' from remote ftp list of files.

New Features in 3.2:

• Added code to not break if the remote ftp dir didn't have any files in it. Thanks to a number of users for pointing this out.
  
  
• Added code to delete remote ftp files, based on the variable $number_of_files_to_save (see comments under variable, in set up section)

Version History - I've placed the complete Version History in a separate file due to size.

Note: If you're using Mysql-4.0.21, there's a bug with mysqldump.
- http://bugs.mysql.com/bug.php?id=4047
- http://bugs.mysql.com/bug.php?id=5538
Quote: "Workaround: do not use mysqldump with the -l option. Use the --single-transaction option. Actually, contrary to what is said in the manual, the -l option does NOT guarantee a consistent snapshot of all InnoDB tables is one database. You must use the --single-transaction option to get a consistent snapshot. The fix will appear in 4.0.22."

New Features in 3.0:

I've skipped from 2.7 to 3.0 because I consider this a major feature release . It has many, many improvements in the code, including:

• options to refine the tar/gzip functions (-z switch, piped or two step method, intermediate text file deletion and support for bzip2);
• mysqldump now uses --result-file (check your version of mysqldump!);
• the script can now be run from the web, with password protection;
• the script now works on both Linux and Windows (including email and ftp functions);
• fixed a much requested bug with the parsing of whereis output;
• added a significant amount of error checking;
• cleaned up the reporting method;
• rewrote the subroutine that deletes text files;
• added a switch to disable reporting to stdout;
• created a method for selecting method of output (screen, email or both) for all print commands;
• many other small fixes and features. (see History Notes and comments in file).

Additional Features

• saves backup files, then has option to email file and/or ftp file to remote server
• supports large sets of databases and tables (tested with over 3,000 tables)
• uses .cnf files, or user/password in the file, or web login
• removes old files, based on time criteria
• backup can be done with mysqldump, select into outfile, or sql select (some may not have permission for select into outfile, and some don't have access to mysqldump

Note to Programmers:
If you're a programmer, and are interested in issues such as
'use strict', 'my' and other programming constructs, you may want to read
a note about my programming methods.

=> I suppose the script could be rewritten, but it's more flexible the way it is, considering that one can use the Linux shell prompt to do the same thing. Assuming that the database name is 'test', and that the files have the extension of '.txt', you can use this command:

find . -name "*.txt" -print | xargs -t --replace cat {} | mysql test

That parses through all the files and reads the contents using 'cat' and then pipes the contents one by one to the mysql monitor with the database 'test'. The '-t' shows you the file names as it works, so you're not staring at a blank screen. If you change the '-t' to a '-p', it will prompt you at each file. Try it on a test database first, to be safe :-) Note: I think the xargs version above is better than:

cat *.txt | mysql test

... because of the limitations of the command line size, assuming one has many files. Also, the xargs version shows you the filenames as they process. Of course, there are probably many other ways to accomplish the above, given the eclectic nature of Unix.

- a method to email each database to a different person

- a configuration option to allow running the script in
  different subdirectories, with different config files,
  for different users, all using the same copy of the
  master script.