On 7 Mar 2007, at 08:44, Ian P. Christian wrote:
mysqldump --master-data --single-transaction database > dump.sql
This database I'm dumping has something like 17 million rows, all
but 1 table (which uses FULLTEXT, and only has 3-4k rows) run
innodb. There is only one table of any real size, and this table
has all but about 100k of the total rows in. My understanding of
this command is that the database should not be locked whilst this
command is running.
--single-transaction doesn't _do_ the dump as a transaction, it
simply wraps the dump in begin/commit statements so it's atomic when
restoring.
If the dump is to preserve relational integrity then it has to lock
tables or disable access (or writes/deletes can happen during the
dump). There are two alternatives: One is to use innoDB's commercial
hotbackup utility (which I've not used, but it's apparently 'the
way'). The other is one of the reasons for using a slave - stop the
slave, do the dump, restart the slave and it will catch up anything
it missed. It helps if you can dedicate a slave for this.
I reserve the right to be completely wrong though ;^)
Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
[EMAIL PROTECTED] | http://www.synchromedia.co.uk/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]