How to:: Master-Master replication I have setup Master-Master replication on two servers. Now, my question is that if I need to have the second master as only read-only, what should I do? I tried doing 

mysql&gt; flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

But since this creates a read-lock in my 2nd master (Slave 1). The changes that I make in Master 1 is not propagated to Master 2 (Read-only). 

What should I do such that - changes in Master 1 are getting updated to Master 2 but Master 2 should not be in write mode, Master-master(Active/Passive).
Is it possible to do this?

Regds
1 Stop the slave on the first master

mysql&gt; STOP SLAVE;


If the second master becomes a read only then the first master should no longer replicate from it. 

2 Change the 2nd master's configuration to read only.

mysql&gt; SET GLOBAL READ_ONLY=1;


Add read_only=1 to my.cnf for the next restart

Read Only variable 

3 Unlock the tables on the second master

UNLOCK TABLES;


You don't need to lock tables on the second master (now slave)

4 Secure privileges

Redefine privileges on the 2nd master, follow this answer here : Should a MySQL replication slave be set to read only?

5 Verify slave status on 2nd Master, now slave.

mysql&gt; show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52


If the second master is not replicating then you may have to reinitialize the replication to the first master.

A. On first master, identify current log and position

SHOW MASTER STATUS;

+------------------+----------+--------------------------+------------------+
| File             | Position | Binlog_Do_DB             | Binlog_Ignore_DB |
+------------------+----------+--------------------------+------------------+
| mysql-bin.000001 |      107 | base1, base2             |                  |
+------------------+----------+--------------------------+------------------+
1 row in set (0.00 sec)


B. On second master now slave, reinitalize replication

Stop SLAVE ;
CHANGE MASTER TO MASTER_HOST = '10.11.128.52', 
MASTER_USER = 'repli', MASTER_PASSWORD = 'repli', 
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
Start SLAVE;


C. Show status on second master(now slave)

mysql&gt; show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52