2

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> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

mysql> 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

  • If everything is set up correctly then replication should continue to happen, even with a read lock in place. (also if anyone connects with the super privilege they can write to your master 2). That is exactly how our databases are set up. – IGGt Aug 26 '15 at 7:41
  • No I tried. I set read lock in my 2nd master. I created a new database in Master 1. And I checked slave status in Master 2, it says it is on read-lock and behind the master and it isn't getting updated. Did I miss something out?? – tesla747 Aug 26 '15 at 7:58
  • if you remove the read lock, does replication happen as you expect (from master 1 to Master 2)? – IGGt Aug 26 '15 at 8:07
1

1 Stop the slave on the first master

mysql> 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> 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> 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> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52
  • Thanks, I reinitialized by updating the log-position as per A,B and C. It works fine. But what is your opinion, Master-Master is better or Master-Slave? I feel Master-Master has lesser downtime than Master-Slave when the first Master goes down. It is easier to point the application to second master(running in read only) as primary master rather than promoting the slave to master and then proceeing. your inputs? – tesla747 Aug 26 '15 at 8:26
  • What is the purpose of each server in your configuration? – Craig Efrein Aug 26 '15 at 8:30
  • Purpose is that I need to run Master-Slave or Master-Master for my application. I chose Master-master instead of Master-slave because the downtime in switching the application pointer would be less in case of master-master since I would need to promote slave to master in master-slave which would take more time. So my question is that is Master-master with 2nd master being passive better or should I keep it as both active? – tesla747 Aug 26 '15 at 8:36
  • Stick with master-master then as you would eliminate the need to run the change master command on the first master. You can still keep replication going and lock down your second server by restricting login access to it. – Craig Efrein Aug 26 '15 at 8:40
  • Okay but keeping master master with both active.. Is it risky? Even if i set auto increment is it risky to have both active? – tesla747 Aug 26 '15 at 8:44

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.