Posted on 3 Comments

Sharing datadir

Indeed, this is usually a big no-no. But I have a customer where this is used, validly. Except it wasn’t quite right.

Information from branches is replicated back to HQ, and since a slave can’t have multiple masters, they have one slave instance for each branch. Each branch uses its own database names and the mysql system db is not replicated, so no conflict resolution is necessary (which would be the other problem with multiple masters).
Log in to any instance, and you will see all databases. And yes you can access them. But in this case they didn’t always deliver the correct results…. lots of missing rows, generally. Cause? The instance configs didn’t contain the –external-locking option. Added that in, and now it’s working fine.

Note that this is unique to MyISAM, don’t ever ever try this with InnoDB or any other engine!!
Basically, the external locking option makes MySQL server use filesystem locks so that other instances will know about them, and also doesn’t presume that its internal caches are still valid later. So it’s not fast, but in this case that wasn’t an issue. It’s old code, and I wasn’t sure the query cache would be aware of this stuff also, so I made sure it was disabled. Just in case.

Sometimes it’s useful to know about “old stuff” 😉

Posted on 3 Comments

3 thoughts on “Sharing datadir

  1. Is there a way to “fake” multi masters with InnoDB tables?

  2. Sorry no. Don’t even try it. It’ll break your tablespace.

    You’d need some locking/cache-invalidation infrastructure to make that work (and at decent speed). For instance, Oracle *acquired* this technology for RAC. There are some free efforts available now, but it would have to get integrated into MySQL Server.

  3. From my research that’s what I figured. I was just hoping I wasn’t searching for the right stuff. 🙂

Comments are closed.