0

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. Basically this is a database to store local session variables, so I won't have to run MySQL on the application server in this region.

So my questions are:

  1. Can I do this?
  2. If I can, is it a good idea, or should I keep the slave strictly to read only databases (beyond practical considerations)?

Any answer much appreciated, technical explanations and links to more reading would be great. I've found it difficult to Google much on this topic.

1

You can safely write to a schema on the slave if that schema does not exist on the master or if you correctly use replication filters to prevent its replication to the slave.

"Correctly" is the important word in that sentence. Refer to this page:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

Check the --replicate-do-db and other filter settings, and note that the behavior changes based on whether binlog events are formatted as rows or as statements. When statement-based replication is being used, replication filters only apply to the database in use as the current database. When row-based replication is used, the filters are applied regardless of current database.

Be sure you get this right, otherwise, you could have a situation like this:

replicate-ignore-db=monkey
binlog_format=STATEMENT

Then you do this:

USE zebra;
TRUNCATE monkey.tablex;

Guess what? tablex just got truncated on both master and slave. However, if you had this instead:

replicate-ignore-db=monkey
binlog_format=ROW

and did the same TRUNCATE on the master, monkey.tablex would not be truncated on the slave.

Note that this all applies according the binlog_format in use at the time the event is logged, and since binlog_format can be changed on a per-session basis, you can get some surprises unless you know exactly what's going on.

Having said all that, if the schema you're writing to does not even exist on the master, then you have nothing to worry about, other than the fact that you can't have read_only or super_read_only set on the slave if you plan on writing to it, so you can't count on it preventing write activity.

| improve this answer | |
0

Yes, in general you can safely write to a table/tables on the slaves that is not being written on the master.
You should configure those tables to be excluded from replication process entirely.
Unless you have a Master - Master replication i won't suddgest using writes on your slaves with a default configuration.
Master-master replication is actually two master-slave replications. This allows to make read and write transactions on both servers, as data propagation from master to slave goes very fast oposit to data propagation from slave to master which requires much more time.

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision.
Simple trick will allow to avoid such collisions on MySQL server.

On the Master 1/Slave 2 add to /etc/my.cnf:

auto_increment_increment= 2
auto_increment_offset   = 1

On the Master 2/Slave 1 add to /etc/my.cnf:

auto_increment_increment= 2
auto_increment_offset   = 2
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.