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!
[ 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
|
