• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: [OT] Frontbase Export/Dump
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [OT] Frontbase Export/Dump


  • Subject: Re: [OT] Frontbase Export/Dump
  • From: Mike Schrag <email@hidden>
  • Date: Mon, 12 May 2008 18:44:28 -0400

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@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


References: 
 >[OT] Frontbase Export/Dump (From: Joshua Paul <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: David Holt <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: William Hatch <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: Guido Neitzer <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: Chuck Hill <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: Kieran Kelleher <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: Mike Schrag <email@hidden>)
 >Re: [OT] Frontbase Export/Dump (From: Kieran Kelleher <email@hidden>)

  • Prev by Date: Re: [OT] Frontbase Export/Dump
  • Next by Date: Re: That dreaded eclipse NPE again
  • Previous by thread: Re: [OT] Frontbase Export/Dump
  • Next by thread: Re: [OT] Frontbase Export/Dump
  • Index(es):
    • Date
    • Thread