0

we have a master-slave mysql replication setup. The master has multiple databases and they get created and dropped, fairly often. I would like to restrict the slave to not drop the databases. I couldn't find any such option on the mysql help page. We provide a Saas model erp application and maintain each customer in a different database. After the trial period expires we delete databases from the master regularly(after backing up of course). Only lost a disk once and lost a few of those databases.

3
  • If you give more information on why you want to do this, you might get better answers too.
    – Olli
    Mar 22, 2011 at 7:40
  • Well, we have a seperate database for each client. And for each trial account too. We delete the trial databases at the end of the expiry period(Ofcourse, after backing up). But, since the slave is nothing more than just data replication, i would like the database to stay on the slave. Apr 22, 2011 at 18:25
  • setting the --read-only=True stops database level write commands too or not?? That is my core question.. Apr 22, 2011 at 18:42

2 Answers 2

1

You can prevent certain commands from being written to the binlog, and thus prevent them from being replicatd, by doing

SET sql_log_bin = 1

in the current session. See more at:

http://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html

Make sure the connection you do this on is not used for other purposes (eg from within a connection pool), since no commands in it will be written do the binlog until you do

SET sql_log_bin = 0

or the connection is reestablished.

1

--read-only will not serve this purpose. It causes the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .