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.
Comments
Ron
Fri, 2013/02/01 - 07:09
Permalink
INFORMATION_SCHEMA is the likely answer
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
Ron
Wed, 2013/02/06 - 09:58
Permalink
INFORMATION_SCHEMA is the WRONG answer
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