Re: [OT] Frontbase Export/Dump
Re: [OT] Frontbase Export/Dump
- Subject: Re: [OT] Frontbase Export/Dump
- From: Kieran Kelleher <email@hidden>
- Date: Mon, 12 May 2008 18:39:24 -0400
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@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden