Description:
Many of the FLUSH commands are bin-logged (but not all), and if GTID mode enabled, adding GTID sequence with local UUID.
These commands also do not respect super_read_only=1.
An example ones affected:
FLUSH SLOW LOGS
FLUSH HOSTS
FLUSH STATUS
FLUSH PRIVILEGES
FLUSH USER_RESOURCES
etc.
Not affected:
FLUSH LOGS
FLUSH ENGINE LOGS
FLUSH BINARY LOGS
This may cause replication problems later as the cluster becomes inconsistent.
Also tested on 8.0.3-rc
Somewhat related bug: https://bugs.mysql.com/bug.php?id=83232
How to repeat:
slave1 [localhost] {msandbox} ((none)) > select @@super_read_only\G
*************************** 1. row ***************************
@@super_read_only: 1
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
Value: 00012411-1111-1111-1111-111111111111:1-3
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > flush logs;
Query OK, 0 rows affected (0.08 sec)
slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
Value: 00012411-1111-1111-1111-111111111111:1-3
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > flush slow logs;
Query OK, 0 rows affected (0.00 sec)
etc
slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G *************************** 1. row ***************************
Variable_name: gtid_executed
Value: 00012411-1111-1111-1111-111111111111:1-3,
00012412-2222-2222-2222-222222222222:1-6
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 101 | 124 | Server ver: 8.0.3-rc-log, Binlog ver: 4 |
| mysql-bin.000002 | 124 | Previous_gtids | 101 | 195 | 00012411-1111-1111-1111-111111111111:1-3 |
| mysql-bin.000002 | 195 | Gtid | 101 | 268 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:1' |
| mysql-bin.000002 | 268 | Query | 101 | 346 | flush slow logs |
| mysql-bin.000002 | 346 | Gtid | 101 | 419 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:2' |
| mysql-bin.000002 | 419 | Query | 101 | 502 | flush user_resources |
| mysql-bin.000002 | 502 | Gtid | 101 | 575 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:3' |
| mysql-bin.000002 | 575 | Query | 101 | 649 | flush hosts |
| mysql-bin.000002 | 649 | Gtid | 101 | 722 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:4' |
| mysql-bin.000002 | 722 | Query | 101 | 797 | flush status |
| mysql-bin.000002 | 797 | Gtid | 101 | 870 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:5' |
| mysql-bin.000002 | 870 | Query | 101 | 957 | flush privileges |
| mysql-bin.000002 | 957 | Rotate | 101 | 1004 | mysql-bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)
Suggested fix:
I don't see much sense in actually replicating these FLUSH commands at all, especially that most of them are related to local resources, which can be different on the slave side.
If though any of these has to be replicated/bin-logged, it should respect the suer_read_only setting.
