0

My production database has all MyISAM tables which are replicated to a slave server. When I need to perform a backup, I simply stop the mysql server on the slave, copy the table files, and then start the server again. This has been working well for several years now. However, I now need to convert a few tables on my master to InnoDB because I am having some locking problems. I don't think I'll be able to just copy the table files on the slave anymore. I have a few questions:

  1. Is it a good idea to mix MyISAM and InnoDB tables in the same database? Tables of different types will be used in joins and other operations.

  2. Should I have 2 backup processes on the slave - 1 to backup MyISAM and the other for InnoDB tables? Seems like a mess to me.

  3. Can I have the InnoDB tables converted to MyISAM tables on the slave and keep my existing backup process? Can a table that is InnoDB on the master replicated to the same table in MyISAM format on the slave?

Improve this question

1 Answer 1

Reset to default
1

  1. Mixed tables types are fine, although you may want to check the performance difference between mixed and all InnoDB tables for your particular application.

  2. Even without mixing tables, I suggest backing up by performing a dump, which is far preferable to copying the files/folders, as it simplifies moving to another server or version of MySQL.

  3. There is no reason to split this into two separate backups.

I would strongly discourage you from changing the table format on the slave, especially if you are using transactions. By retaining the InnoDB tables the slave will correctly process the transactions. By changing the tables to MyISAM you may well cause problems by including partial transactions within the backup, which is likely to be bad news when you have to do a restore.

Improve this answer
2
  • Your answers make a lot of sense, thanks. If I make a dump of the full database into 1 SQL file (via mysqldump), is there a way to selectively import certain tables?
    – smusumeche
    Jan 18, 2010 at 14:59
  • I don't think you can selectively load tables from a dump file, although I'm not completely sure of it. Of course you can just dump each table to a separate file. I've done that in the past and then tar or zip them together for simpler file management.
    – John Gardeniers
    Jan 18, 2010 at 17:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .