MySQL – Read-Write Master with Many Read-Only Slaves Over Shared Storage

MySQLreplicationstorage

I've asked the same question on the MySQL forum but have also seen lots of great responses here as well so thought I would try my luck here too.

We have a relatively large MySQL environment where we are currently using MySQL Master-Slave replication to achieve our performance requirements. We typically have multiple clusters of 10 hosts, where one is read-write and the others are read-only. We are also very performance sensitive so are currently using FusionIO cards in each host.

We are finding a few challenges with this architecture however, and are looking for other ideas – specifically though we would like to come back to a very high speed shared storage solution that would allow us to have a single copy of the DB, where the master can mount it as read-write and the slaves as read-only.

I understand that by using Clusterware this type of design is possible, however we would prefer to not to use that product as we are okay to use some manual failover processes if need be.

Has anyone ever done anything like this before? I know that MySQL supports mounting as read only, "–innodb-read-only=1", however what other implications are there if using this method compared to their existing Master-Slave configuration? Is it possible to have one as read-write and the others read-only if using this parameter?

Thank you in advance for any thoughts on this.

Best Answer

NO. Do not attempt to have a single copy of the data with multiple copies of mysqld accessing it. I don't know if that ever worked; if it did, I suspect it has atrophied over the years as changes have been made.

Think about it... Each copy of the server has lots of stuff cached in RAM. To coordinate that with other servers would require writing things to disk; this defeats the performance goal. When a write occurs; all traces of it would need to be immediately flushed to disk, and all the other servers would need to know to read it and replace whatever is cached in their RAMs.

With FusionIO, check to see that you can safely turn off innodb_doublewrite.

How many queries/second? Do you have any 'slow' queries? (That is, there are other ways to beef up performance, than just hardware.)

Related Solutions

Should MySQL Replication Slave Be Set to Read Only?

When a Slave is read-only, it is not 100% shielded from the world.

According to MySQL Documentation on read-only

This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

Thus, anyone with SUPER privilege can read and write at will to such a Slave...

Make sure all non-privileged users do not have the SUPER Privilege.

If you want to revoke all SUPER privileges in one shot, please run this on Master and Slave:

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

With reference to the Slave, this will reserve SUPER privilege to just root and prevent non-privileged from doing writes they would otherwise be restricted from.

UPDATE 2015-08-28 17:39 EDT

I just learned recently that MySQL 5.7 will introduce super_read_only.

This will stop SUPER users in their tracks because the 5.7 Docs say

If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.

Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.

super_read_only was added in MySQL 5.7.8.

MySQL Master-Master Replication – Setting Up MySQL Master-Master with Additional Slaves

What you want is server1 to binlog not only its own updates, but also those updates he received from server2, so that slave1 will get all updates.

You just need to tell server1 to do this, which can be accomplished by setting log-slave-updates to yes.

You might want to set this option on server2 too, if the server topology should change someday (as Rolando kindly remarked in the comments below).

This does not loop endlessly as the default setting for replicate-same-server-id is 0 (and cannot be changed as long as log-slave-updates is on). This setting set to 0 means the slave will skip statements he receives with his own server-id.

Your infrastructure decision in general seems fine to me.

Best Answer

Related Solutions

Should MySQL Replication Slave Be Set to Read Only?

UPDATE 2015-08-28 17:39 EDT

MySQL Master-Master Replication – Setting Up MySQL Master-Master with Additional Slaves

Related Question