If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump file
and the binary log file that was started by the 1st FLUSH LOGS command.
The recovered database wil be restored to the date time that the 2nd
FLUSH LOGS command was issued instead of the start time of the backup,
but you won't have to lock all of your tables and it wil give you a
consistent state across a mixed INNODB MyISAM environment.
The downside is
-you have 3 files to deal with
-you have to maintain the table names in the mysqldump commands
-you have a small risk of a change ocurring in the MyISAM
tables between time the 2nd FLUSH LOGS is executed and the 2nd
mysqldump command is executed
-----Original Message-----
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 10:21 AM
To: mysql@lists.mysql.com
Subject: Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both
MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use
mysqldump --opt, thus getting the --lock-tables option, but for the
InnoDB the --single-transaction is preferred. Since they are mutually
exclusive, is there a best practice to get consistent state of the
tables when the database dump is performed?
Would `mysqldump --opt --skip-lock-tables --single-transaction` be best
for a database that is mostly InnoDB tables, but does have a few MyISAM
tables?
WOuld I be better off locking the database from any updates/inserts, and
specifying particular commands for individual tables?
Any advice appreciated, including RTFMs with links.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
