>
> -----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.
>
>
> Gordon Bruce wrote:
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
Thanks for the tip. I haven't looked into binary logs too much, just
learned about them the other day when I had to correct my own mistake
and restore a table.
Is is best practice to go with tables of all one sort to allow for
consistent state when doing backups like this, or are mixed tables the
norm in most databases? I went with the InnoDB in order to not have to
do row level locking on the tables as transactions were performed and
I've been very pleased with the results. I might consider just going
with all InnoDB to make it easy, as those are the majority of my tables
in this case.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
