NOTE: We are redesigning our site, and this page isn't finished yet.
  Click on the Significato Journal page to see the new design.


Top Page
of Site
Top Page
of Journal
Column
Syndication
Public
Speaking
Abstinence Curriculum Free
Software
Contact
Us

MySQL Backup

NEWS: A new version will be released by the end of Nov., 2006.

| PageMod | MySQL Backup | FutureSQL | WCN:Talk (formerly FutureForum) |
| Search and Replace | Selected Backup | Simple Form |


[ Visit our Open Source Software page or one of the links above to obtain other Open Source programs ]

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. 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.)

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.1:

  • fixed bug in mysqldump parameters with --result-file being in wrong order
  • added some code to the ftp upload feature

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

Open Source Support Forum: If you need technical help, you may also leave a post at our Open Source Support Forum. If others answer questions before I get to them, I don't mind at all :-)

Register your email to receive notification about updates to our Open Source software!
Current Version - 3.1
mysql_backup.cgi
(the primary script)
mysql_backup_login.cgi
(this login file is only necessary if you want to login via the web)



[ Quotes & Testimonials ]
MySQL Backup was featured as the
“Tool of the Month” for June, 2002
at UnixReview.com.

Peter,

I just wanted to say thanks for writing your sql backup script. We use it on all of our SQL servers here to make multiple backups per day, and have it tied into our tape backup libraries. Just wanted you to know we use it for thousands of databases multiple times per day, and your hard work is appreciated. Thanks,
Jordan

Jordan Lowe / Server Central Network

Dear Peter,

A while back you kindly advised me on the best way to restore my databases - your demo follows. Well, on Tuesday the worst did happen, and our server got hacked, losing the whole of MySQL and its records. But I had set up a cron job to run your backup script and email me the MySQL data file each day, and it has taken just a couple of hours to restore everything, entirely thanks to you! Thought you might like to know it is all worth while.

Very best regards,

Jeremy Rodwell
FlexiSites Ltd., England

[Note: Jeremy is referring to the text below about 'Restoring Many MySQLDump Text Files'. For all its impossibly arcane syntax, Unix really is powerful (once you figure it out :-). Thanks for the testimonial, Jeremy!!]

"In a word: awesome. This script (MYSQL Backup) lets me sleep at night while it makes precious backups of my data. Peter, thanks a million, the script is worth about that much!"
Ben Steed, Searcy, AR
"The MySQL Backup script has been my major back tool for our MySQL databases since day one. I think it is the best ever. If only we could do incremental back-up to save time and disk space..."
Ivan Mirisola, SysAdmin of Nucci Systems, São Paulo, SP, Brazil
"I think MySQL Backup is a powerfull and easy way to Backup MySQL Databases. Very Nice!"
Christian Weilacher, Germany

"You saved us reinventing the wheel with a few basic - but very effective - scripts. Thanks, probably some day, we can do something in return ..."
Thomas Weller, Manager La preveda, Germany

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
.

[ Issues, FAQ's, ToDo and Version History ]
Importing (Restoring) Many MySQLDump Text Files:
~ from a user (paraphrased): "If we have 100+ mysqldump *.txt files, how can we easily import them all, in order to restore a database, without typing each file name in by hand? (i.e. from the shell prompt, piped to the mysql monitor:
mysql mydb < mytable.txt). Wouldn't it be better for the script to write all the tables to one text sql file?"

=> 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.

ToDo List
- 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.

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

   

Return to Top Open Source Page

Click to view WCP's
Resource Catalogue
We invite you to exchange Links with us; for we all benefit from more traffic.
WCP does not have any control over the content of the Google Ads below, and does not necessarily endorse each ad.

Register
your email

for update notification
to the World Community Journal pages!
About WCN & Contact Us
Affiliated
Projects
The FutureWorld
Association
Replace
Property Tax
Campaign
Hawk
Mountain
Films
Do you need a
Public Speaker?
"Peter Brown did an excellent job with the keynote speech. This man is not only capable of speaking to a national audience but he has 'world-class' ability in public speaking ."
Senator Larry Pressler, former US Senator from South Dakota
Click here for information
about booking him as a speaker at your event!



Click on the PayPal buttons above to pay for WCN products or services with a Major Credit Card!