1

I've just taken over the responsibility of managing our MySQL databases. We're running Percona Server (GPL), Release rel24.1, Revision 217. Our setup involves two servers both running as master and slave at the same time. However, the functional slave has read_only enabled and all client connections go to the functional master.

If I ever need to swap roles during maintenance, how might this change the typical role swapping process? Is it safe to just switch read_only and direct all client connections to the new functional master? This is of course assuming functional slave has caught up to functional master, I've stopped all client connections, etc. Since replication isn't normally used on slave even though it's running, is it smart enough to know how to pick up where the old master left off? The last time I tried to do this they fell out of sync with each other and it needed to be resolved by using rsync, causing us almost an entire day of downtime.

On mysql07:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql08
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.017456
          Read_Master_Log_Pos: 29738626
               Relay_Log_File: relay.006949
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.017456
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 29738626
              Relay_Log_Space: 442
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101215970
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.020222
        Position: 59745708
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On mysql08 (has read_only set to 1):

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql07
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.020222
          Read_Master_Log_Pos: 59883146
               Relay_Log_File: relay.010757
                Relay_Log_Pos: 59883292
        Relay_Master_Log_File: mysql-bin.020222
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 59883146
              Relay_Log_Space: 59883481
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101215972
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.017456
        Position: 30086889
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
  • Sure, I edited my post with output from SHOW SLAVE STATUS and SHOW MASTER STATUS on both servers. Hope that clarifies things a bit. Are there any other details I should post? – Eric R. Oct 4 '14 at 18:17
3

Since replication isn't normally used on slave even though it's running

That statement doesn't quite make sense.

In master/master, both servers are each other's master and slave and there is no real distinction between them -- only an administrative distinction, as you seem to understand.

If set up correctly, then yes, SET GLOBAL READ_ONLY = 0; on the former functional slave will allow direct writes by nonprivileged users.

However ... if your servers are set up correctly, then you should be able to make a "test" change directly to the data on the machine you call the slave, using any account with the SUPER privilege (which is immune to the global READ_ONLY setting) and it should replicate to the master. If that isn't what happens, then your setup is not correct and you need to understand why.

You should also be able to SHOW SLAVE STATUS; on each machine, and see data that matches SHOW MASTER STATUS when run on the opposite machine.

Until these things are true, then the answer to the question is no, it absolutely is not safe, because your system isn't working the way you believe it is.

Once you can see replication events going both directions and being executed no matter which direction they originate from, then yes, disconnecting the clients from the former functional master and connecting them the the former functional slave should be a relatively painless transition.

You may have missed an opportunity during your previous experience to fully understand what went wrong, and why.

  • I've checked that both of these things are true. I'm now wondering if perhaps replication wasn't configured properly at the time by our previous engineer. I wasn't the one to fix it when it broke. Error logs show the old slave attempting to connect to an invalid hostname. – Eric R. Oct 4 '14 at 19:18
0

You asked the same question on Reddit. Here is a copy&paste of my answer there (without the self-promotion link):

Replication is tricky. This is not enough space to tell you everything that can be problematic with it and what you should be aware of, but in summary it is:

  • Asynchronous
  • Single-master
  • Statement-based
  • No automatic consistency checking
  • Easily breaking
  • Can lead to inconsistent states/drifting
  • Easily lagging behind
  • Not consistent with disk by default
  • Difficulty on comparing node states

Some of these are fixable with configuration (e.g. STATEMENT based replication vs. ROW based replication) or can be solved by upgrading to GTID replication/semisync replication/will be solved on in-development 5.7 multi-master synchronous replication.

So aside from upgrading/changing configuration, you can either "patch" or monitor for those problems manually or use a middle layer. I recommend you to have a look at MHA, which allows performing failovers and switchovers in a controlled way (making sure that everything is consistent).

MHA is a good tool to do that semi-automatically. If you intend to perform automatic failover, we are more fans of not doing failover (which is trickier than it seems), but doing active proxying to avoid consistency problems (like split brain). HA proxy is a very common way to do that.

Please make sure that, in most cases, traditional MySQL replication is not 100% consistent, and it cannot guarantee that you will not loose any transaction if the master fails (unless you "patch" it with semi-sync replication + extras, like Facebook does, and you assume the performance penalty).

If you intend to do things manually, broadly speaking, you need to:

  1. Make sure that master and slave are in an eventually consistent state (pt-table-checksum)
  2. Stop client connections from master (e.g. set master as read_only). You can continue reading or not, depending on your application consistency requirements.
  3. Wait while your slave keeps up to date with the master (SHOW SLAVE STATUS on the slave == SHOW MASTER STATUS on the master) and make sure that there are no longer running write transactions on the master. This is your unavailability time- it can be from 1 second to 6 hours.
  4. Set slave as read_only=0. You can at this point reverse the replication direction, if you want. Many people set a master-master replication to avoid this when doing manual fail-over. But be careful to only write to one node at a time.
  5. Set slave as the new "active" node, for both reads and writes on your application. Please note that this may not be direct if you are using a pool of connections.
  6. At this point, you can continue with this setup, or perform the equivalent reverse operation after maintenance, as you want.

This seems easy, but there are may gotchas; specially for preparation- buffer pool tends to be cold on the slave, lag can be reduced with some tricks, some people think that the slave has to run on worse hardware, etc. HA is not free, it requires having at least some idle resources.

If you are applying too many "patches" to make your setup work, maybe replication is not the right technology for you, and you need to evaluate alternatives like Semisync plugin, Galera, NDB Cluster or Tungsten.

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.