Thursday, January 27, 2011

Prevent non-replication writes to MySQL slave?

We have some MySQL database servers set up with row-based replication, for performance. The software writes to the master, and reads from either the master or the slave. Everything's working great, for the most part.

It's my understanding that MySQL will allow writes to the slave, even though it knows it's a MySQL slave. Ideally, I'd kind of like to close this, so even if somebody writes some bad code that gets a read-connection and does an UPDATE, it will throw an error rather than put data on the slave.

Is there a way to do this in MySQL? Obviously we'd like to make this impossible from our software, as well, but like a firewall on our servers, I'd like to be as defensive as possible.

Thanks!

  • Enable the read-only option in my.cnf. It can also be specified as a flag on the command line using --read-only with mysqld.

    vmfarms : Note that this will not work for superusers (ie: root user in MySQL) as it doesn't obey read-only.
    From Warner
  • Only give replication related rights to the users on the slave. You still have the issue of root user rights, but you can remove remote root access to the DB server.

    From Craig

0 comments:

Post a Comment