Best Practices for Sharing Data Across MySQL and MariaDB Databases

mariadbMySQLreplicationview

I have app that uses DB with 50+ tables. Now i'm in situation that i need to install another instance of app. Problem is that i would like to use some tables as "common" data, e.g. "brands" or "cities" or "countries" in both (for now there are only 2, but it might soon be more) apps.

I searched and find that i can make "common" DB with such tables, and have views in each DB instance that points to corresponding table.

Main app queries are heavily relying on that common tables, so i'm concerned if that will slow down my queries since views don't have indexes?

Are there some better practices? I'm looking now for replication in mysql manual. Is that way to go? Replicate tables from common DB to app instance DB's? Can this be one-direction replication? (only tables in "common" DB can be changed and then replicated to other DB's)?

thanx for advice Y

Best Answer

If the "common" tables on the secondary "app instances" are being accessed read only, then your easiest option will be doing a traditional master slave(s) configuration with a replicate_ignore_table = ... line put into all slaves for all tables except the "common" ones.

If you need to have identical behavior between all app instances (meaning you're not using app logic to isolate writes to the master only), then I would seriously considering using a galera cluster product of some sort.

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

Mysql – Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

USE db_99;
do something with db_1 or 2 or 3;

will not replicate because of your binlog-do-db and replicate-do-db. (The latter is redundant in your case.)

This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement.

Is it a "bug"? Well, a lot of people get burned by it.

Best Answer

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

Mysql – Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

Related Question