Percona Server 5.6.21+ and MySQL 5.7.8+ offer the super_read_only option that was first implemented in WebscaleSQL. Unlike read_only, this option prevents all users from running writes (even those with the SUPER privilege). Sure enough, this is a great feature, but what’s the relation with GTID? Read on!
TL;DR
Enabling super_read_only on all slaves when using GTID replication makes your topology far less sensitive to errant transactions. Failover is then easier and safer because creating errant transactions is much harder.
GTID replication is awesome…
For years, all MySQL DBAs in the world have been fighting with positioning when working with replication. Each time you move a slave from one master to another, you must be very careful to start replicating at the correct position. That was boring and error-prone.
GTID replication is a revolution because it allows auto-positioning: when you configure server B to replicate from A, both servers will automatically negociate which events should be sent by the master. Of course this assumes the master has all missing events in its binlogs. Otherwise the slave will complain that it can’t get all the events it needs and you will see an error 1236.
… but there’s a catch
Actually GTID replication has several issues, the main one in MySQL 5.6 being the inability to switch from position-based to GTID-based replication without downtime. This has been fixed since then fortunately.
The issue I was thinking of is errant transactions. Not familiar with this term? Let me clarify.
Say you have a slave (B) replicating from a master (A) using the traditional position-based replication. Now you want to create a new database. This is easy: just connect to B and run:
1 | mysql> CREATE DATABASE new_db; |
Ooops! You’ve just made a big mistake: instead of creating the table on the master, you’ve just created it on the slave. But the change is easy to undo: run DROP DATABASE on B, followed by CREATE DATABASE on A.
Nobody will ever known your mistake and next time you’ll be more careful.
However with GTID-replication, this is another story: when you run a write statement on B, you create an associated GTID. And this associated GTID will be recorded forever (even if the binlog containing the transaction is purged at some point).
Now you can still undo the transaction but there is no way to undo the GTID. What you’ve created is called an errant transaction.
This minor mistake can have catastrophic consequences: say that 6 months later, B is promoted as the new master. Because of auto-positioning, the errant transaction will be sent to all slaves. But it’s very likely that the corresponding binlog has been purged, so B will be unable to send the errant transaction. As a result replication will be broken everywhere. Not nice…
super_read_only can help
Enter super_read_only. If it is enabled on all slaves, the above scenario won’t happen because the write on B will trigger an error and no GTID will be created.
With super_read_only, tools that were not reliable with GTID replication become reliable enough to be used again. For instance, MHA supports failover in a GTID-based setup but it doesn’t check errant transactions when failing over, making it risky to use with GTID replication. super_read_only makes MHA attractive again with GTID.
However note that super_read_only can’t prevent all errant transactions. The setting is dynamic so if you have privileged access, you can still disable super_read_only, create an errant transaction and enable it back. But at least it should avoid errant transactions that are created by accident.
After an erroneous CREATE DATABASE + DROP on the slave, the GTID is not preserved forever.
If you run ‘RESET MASTER’ in the slave, the odd GTID disappears. This is not desirable if the slave needs to become a master immediately, but provided that the other nodes are up to date when the reset happens, the slave becomes again a good candidate for promotion.
What happens with MariaDB’s flavor of GTID?
Same story with MariaDB. Unless you run a RESET MASTER, the odd transaction is kept in the permanent memory of the server, and used when the slave becomes a master.
Yes, this can really help but if this work as expected. For now (tested on newest Percona Server 5.6) this only works if you set this using CLI/mysql client (not from my.cnf or startup options). After some maintance (mysqld restart) we should turn this by hand (or add
$ vim /etc/my.cnd
$ service mysql restart
$ mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show variables like ‘super_read_only’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| super_read_only | ON |
+—————–+——-+
1 row in set (0.00 sec)
mysql> create table test (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test (id int);
ERROR 1290 (HY000): The MySQL server is running with the –read-only (super) option so it cannot execute this statement
mysql>
my.cnf setting will work in the upcoming Percona Server release.