I smell a Kieran blog post in the making :)
ms
On May 12, 2008, at 6:39 PM, Kieran Kelleher wrote:
Yes, I did not want to cloud my simple command line example by
discussing any of the various and plentiful command line options to
mysqldump.
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
So for a live production database, you could use the --single-
transaction option for innodb databasess.
Full server (all databases in one dump) backups are done using the --
lock-all-tables option which locks everything in all databases.....
not good for a live master server, so ideally you should have one or
more replication slaves and do transactional daily/weekly backups to
SQL dump files on a slave server.
For individual database dumps the --lock-tables will lock the whole
database, whether myisam or innodb AFAIK.
However, be aware that there is an option --opt which is a shorthand
for a bunch of common generally desirable mysqldump options
including --lock-tables. the --opt is ON by default if not specified
to ensure those who don't read manuals get the best results :-). So
if you use no options, you should get a transactional snapshot in
time of the database.... and like you said for MyISAM, there is no
transactions so NEVER use MyISAM for WebObjects applications that
require transactional integrity!
So, if you really want a transactional dump for an innodb database,
then you need --single-transaction --skip-opt ..... The --skip-opt
turns off the --opt group ... so you then can add back the specific
ones you need. You don't need --lock-tables and --single-transaction
to be used together.
Another interesting thing is the --master-data option which records
the binary log name and position at the time of a full dump in the
beginning of the dump file. MySQL has a feature where you can have
"binary logging" on and it records every transaction to log files.
If you have to restore your database and you used the master-data
option, you can import and then "play back" the binary logs from the
exact moment of the backup dump. It is a good disk-failure strategy
to have the SQL binary log files written to a different physical
hard disk.
Kieran
On May 12, 2008, at 4:53 PM, Mike Schrag wrote:
# To dump a database to a SQL file
$ mysqldump mydatabase > mydatabasedump.sql
# To import a database form an SQL file
$ mysql myremotedatabase < mydatabasedump.sql
Out of scientific curiosity, I went digging -- if you're using
InnoDB, these appears to not be transactionally safe(much like FB's
ascii dump). If you're using MyISAM, you have no transactions, so
your database is already broken :). For InnoDB, though, you can
use mysqldump --single-transaction dbname which will give you a
transactionally safe dump. MySQL is not, however, transactionally
safe for alter table statements. For most cases this won't be a
problem, but if you try to dump the db while running migrations,
you'll get corrupted results. If you're using MyISAM, you should
do a --lock-tables, which has the obvious downside of .... locking
the tables ... but if you're using this for backup, you really
should be doing it.
ms
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/kieran_lists%40mac.com
This email sent to [EMAIL PROTECTED]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
