MySQLdump: beware storage engine

Been replicating MySQL databases from master to slave computers and encountered a frustrating, and potentially major problem:

The storage engine is not preserved by mysqldump command. So if you require transactions, which depend on InnoDB storage engine, but your slave uses default storage engine of MyISAM, you could have problems if restoring slave data to the master.

Of course, one could use InnoDB as the default storage engine (set in my.cnf startup configuration file), but that's a slower storage method. That may not impact performance of slaves, depending on if you query against slaves and write against masters...

This is pissing me off a fair bit and really makes me want to look more into (finally) making the switch to PostgreSQL for once and all.

I'll try to update this with potential consequences and any fixes / scripts I come up with to address the issue, but be forewarned.

Tags:

Comments

So, if one exports & then imports the INFORMATION_SCHEMA database, particularly the TABLES table, that ought to solve the problem.

I haven't tested this, as the first ~40 entries concern the slave server's tables: might not be a good idea to overwrite them.

But if one could import all entries where TABLE_SCHEMA != 'information_schema', one should be in good shape (assuming one has no tables on slave server independent of master server tables).

R o n
- - -
Vancouver

Okay, I was kinda dumb on this one.

The correct answer when dumping databases using mysqldump is to NOT use --compatibility=ansi. Since storage engines are a MySQL extension they are not part of the ANSI standard.

So obvious, yet still overlooked.

A quick re-dump and reload onto slave and things have been running perfectly...

R o n
- - -
Vancouver

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.