Description:
If you run RESET SLAVE ALL while the server is in SUPER_READ_ONLY mode, you get:
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
However, the relay logs are gone and show slave status is also empty, but the mysql slave_master_info table is not.
How to repeat:
Environment:
- MySQL 5.7.15 GR 090
- Asynchronous Replication
- GTID enabled
- super_read_only=on
my.cnf:
```
[root@mysql2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
server_id=2
master_info_repository=TABLE
relay_log_info_repository=TABLE
#transaction-write-set-extraction=MURMUR32
#binlog-checksum=NONE
#validate_password_policy=0
#validate_password_length=0
log-bin=mysql-bin
super_read_only
[mysql]
prompt="mysql2 mysql> "
```
### This works:
```
mysql2 mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: repl_async
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 141155316
Relay_Log_File: mysql2-relay-bin.000007
Relay_Log_Pos: 141155529
Relay_Master_Log_File: mysql-bin.000005
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: 141155316
Relay_Log_Space: 141155777
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: 1
Master_UUID: 3cd84735-87ef-11e6-a6da-08002718d305
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968
Executed_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
[root@mysql2 ~]# ls -alhs /var/lib/mysql
total 715M
4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 19:50 .
4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:49 ..
4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf
4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool
48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:51 ib_logfile0
48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:51 ib_logfile1
76M -rw-r-----. 1 mysql mysql 76M Oct 1 19:51 ibdata1
12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:50 ibtmp1
4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql
4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001
19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004
257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:51 mysql-bin.000005
4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:50 mysql-bin.index
0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:50 mysql.sock
4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:50 mysql.sock.lock
4.0K -rw-r-----. 1 mysql mysql 248 Oct 1 19:50 mysql2-relay-bin.000006
256M -rw-r-----. 1 mysql mysql 135M Oct 1 19:50 mysql2-relay-bin.000007
4.0K -rw-r-----. 1 mysql mysql 52 Oct 1 19:50 mysql2-relay-bin.index
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema
0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys
mysql2 mysql> set global super_read_only=off;
Query OK, 0 rows affected (0.02 sec)
mysql2 mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql2 mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
```
No problem. we first disabled super_read_only and then did reset slave all.
### Now the version that breaks:
```
mysql2 mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: repl_async
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 141155316
Relay_Log_File: mysql2-relay-bin.000007
Relay_Log_Pos: 141155529
Relay_Master_Log_File: mysql-bin.000005
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: 141155316
Relay_Log_Space: 141155777
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: 1
Master_UUID: 3cd84735-87ef-11e6-a6da-08002718d305
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968
Executed_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
[root@mysql2 ~]# ls -alhs /var/lib/mysql
total 715M
4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 19:58 .
4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:58 ..
4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf
4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool
48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:59 ib_logfile0
48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:59 ib_logfile1
76M -rw-r-----. 1 mysql mysql 76M Oct 1 19:59 ibdata1
12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:58 ibtmp1
4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql
4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001
19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004
257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:59 mysql-bin.000005
4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:58 mysql-bin.index
0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:58 mysql.sock
4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:58 mysql.sock.lock
4.0K -rw-r-----. 1 mysql mysql 248 Oct 1 19:58 mysql2-relay-bin.000006
256M -rw-r-----. 1 mysql mysql 135M Oct 1 19:58 mysql2-relay-bin.000007
4.0K -rw-r-----. 1 mysql mysql 52 Oct 1 19:58 mysql2-relay-bin.index
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema
0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys
mysql2 mysql> reset slave all;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
Error log:
2016-10-01T20:00:32.833484Z 5 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-10-01T20:00:32.833502Z 5 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2016-10-01T20:00:32.833509Z 5 [ERROR] Error creating master info: Error checking repositories.
[root@mysql2 ~]# ls -alhs /var/lib/mysql
total 459M
4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 20:00 .
4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:58 ..
4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf
4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool
48M -rw-r-----. 1 mysql mysql 48M Oct 1 20:00 ib_logfile0
48M -rw-r-----. 1 mysql mysql 48M Oct 1 20:00 ib_logfile1
76M -rw-r-----. 1 mysql mysql 76M Oct 1 20:00 ibdata1
12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:58 ibtmp1
4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql
4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001
19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004
257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:59 mysql-bin.000005
4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:58 mysql-bin.index
0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:58 mysql.sock
4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:58 mysql.sock.lock
4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 20:00 mysql2-relay-bin.000001
4.0K -rw-r-----. 1 mysql mysql 26 Oct 1 20:00 mysql2-relay-bin.index
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema
0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest
12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys
mysql2 mysql> select * from slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000005
Master_log_pos: 141155316
Host: mysql1
User_name: repl_async
User_password: Xslave
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: 3cd84735-87ef-11e6-a6da-08002718d305
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)
mysql2 mysql> show slave status\G
Empty set (0.00 sec)
mysql2 mysql> reset slave all;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
mysql2 mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
[root@mysql2 ~]# systemctl restart mysqld
error log:
2016-10-01T20:02:41.694225Z 0 [ERROR] Failed to open the relay log './mysql2-relay-bin.000007' (relay_log_pos 141155529).
2016-10-01T20:02:41.694235Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './mysql2-relay-bin.index' during relay log initialization.
2016-10-01T20:02:41.695015Z 0 [ERROR] Failed to initialize the master info structure
2016-10-01T20:02:41.695031Z 0 [ERROR] Failed to create or recover replication info repositories.
2016-10-01T20:02:41.695035Z 0 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.
```
- I did reset slave all in super_read_only mode, it gave an error saying that I can't do that because of super_read_only
- the relay logs were gone
- show slave status is gone
- reset slave all is now broken
- there are errors in the log
- restarting mysql gives errors now
Suggested fix:
Avoid a state where replication is half removed.
If RESET SLAVE ALL is not allowed in SUPER_READ_ONLY mode then do nothing (except displaying an error)
If it is allowed, do everything
Description: If you run RESET SLAVE ALL while the server is in SUPER_READ_ONLY mode, you get: ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement However, the relay logs are gone and show slave status is also empty, but the mysql slave_master_info table is not. How to repeat: Environment: - MySQL 5.7.15 GR 090 - Asynchronous Replication - GTID enabled - super_read_only=on my.cnf: ``` [root@mysql2 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid gtid-mode=on enforce-gtid-consistency log-slave-updates server_id=2 master_info_repository=TABLE relay_log_info_repository=TABLE #transaction-write-set-extraction=MURMUR32 #binlog-checksum=NONE #validate_password_policy=0 #validate_password_length=0 log-bin=mysql-bin super_read_only [mysql] prompt="mysql2 mysql> " ``` ### This works: ``` mysql2 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql1 Master_User: repl_async Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 141155316 Relay_Log_File: mysql2-relay-bin.000007 Relay_Log_Pos: 141155529 Relay_Master_Log_File: mysql-bin.000005 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: 141155316 Relay_Log_Space: 141155777 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: 1 Master_UUID: 3cd84735-87ef-11e6-a6da-08002718d305 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968 Executed_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) [root@mysql2 ~]# ls -alhs /var/lib/mysql total 715M 4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 19:50 . 4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:49 .. 4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf 4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool 48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:51 ib_logfile0 48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:51 ib_logfile1 76M -rw-r-----. 1 mysql mysql 76M Oct 1 19:51 ibdata1 12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:50 ibtmp1 4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql 4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001 19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004 257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:51 mysql-bin.000005 4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:50 mysql-bin.index 0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:50 mysql.sock 4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:50 mysql.sock.lock 4.0K -rw-r-----. 1 mysql mysql 248 Oct 1 19:50 mysql2-relay-bin.000006 256M -rw-r-----. 1 mysql mysql 135M Oct 1 19:50 mysql2-relay-bin.000007 4.0K -rw-r-----. 1 mysql mysql 52 Oct 1 19:50 mysql2-relay-bin.index 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema 0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys mysql2 mysql> set global super_read_only=off; Query OK, 0 rows affected (0.02 sec) mysql2 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql2 mysql> reset slave all; Query OK, 0 rows affected (0.01 sec) ``` No problem. we first disabled super_read_only and then did reset slave all. ### Now the version that breaks: ``` mysql2 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql1 Master_User: repl_async Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 141155316 Relay_Log_File: mysql2-relay-bin.000007 Relay_Log_Pos: 141155529 Relay_Master_Log_File: mysql-bin.000005 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: 141155316 Relay_Log_Space: 141155777 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: 1 Master_UUID: 3cd84735-87ef-11e6-a6da-08002718d305 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968 Executed_Gtid_Set: 3cd84735-87ef-11e6-a6da-08002718d305:1-83968 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) [root@mysql2 ~]# ls -alhs /var/lib/mysql total 715M 4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 19:58 . 4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:58 .. 4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf 4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool 48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:59 ib_logfile0 48M -rw-r-----. 1 mysql mysql 48M Oct 1 19:59 ib_logfile1 76M -rw-r-----. 1 mysql mysql 76M Oct 1 19:59 ibdata1 12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:58 ibtmp1 4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql 4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001 19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004 257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:59 mysql-bin.000005 4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:58 mysql-bin.index 0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:58 mysql.sock 4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:58 mysql.sock.lock 4.0K -rw-r-----. 1 mysql mysql 248 Oct 1 19:58 mysql2-relay-bin.000006 256M -rw-r-----. 1 mysql mysql 135M Oct 1 19:58 mysql2-relay-bin.000007 4.0K -rw-r-----. 1 mysql mysql 52 Oct 1 19:58 mysql2-relay-bin.index 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema 0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys mysql2 mysql> reset slave all; ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement Error log: 2016-10-01T20:00:32.833484Z 5 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2016-10-01T20:00:32.833502Z 5 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE. 2016-10-01T20:00:32.833509Z 5 [ERROR] Error creating master info: Error checking repositories. [root@mysql2 ~]# ls -alhs /var/lib/mysql total 459M 4.0K drwxr-x--x. 6 mysql mysql 4.0K Oct 1 20:00 . 4.0K drwxr-xr-x. 33 root root 4.0K Oct 1 19:58 .. 4.0K -rw-r-----. 1 mysql mysql 56 Oct 1 15:53 auto.cnf 4.0K -rw-r-----. 1 mysql mysql 664 Oct 1 19:40 ib_buffer_pool 48M -rw-r-----. 1 mysql mysql 48M Oct 1 20:00 ib_logfile0 48M -rw-r-----. 1 mysql mysql 48M Oct 1 20:00 ib_logfile1 76M -rw-r-----. 1 mysql mysql 76M Oct 1 20:00 ibdata1 12M -rw-r-----. 1 mysql mysql 12M Oct 1 19:58 ibtmp1 4.0K drwxr-x---. 2 mysql mysql 4.0K Oct 1 15:53 mysql 4.0K -rw-r-----. 1 mysql mysql 177 Oct 1 15:53 mysql-bin.000001 19M -rw-r-----. 1 mysql mysql 19M Oct 1 15:58 mysql-bin.000002 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 16:40 mysql-bin.000003 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 19:40 mysql-bin.000004 257M -rw-r-----. 1 mysql mysql 134M Oct 1 19:59 mysql-bin.000005 4.0K -rw-r-----. 1 mysql mysql 95 Oct 1 19:58 mysql-bin.index 0 srwxrwxrwx. 1 mysql mysql 0 Oct 1 19:58 mysql.sock 4.0K -rw-------. 1 mysql mysql 5 Oct 1 19:58 mysql.sock.lock 4.0K -rw-r-----. 1 mysql mysql 217 Oct 1 20:00 mysql2-relay-bin.000001 4.0K -rw-r-----. 1 mysql mysql 26 Oct 1 20:00 mysql2-relay-bin.index 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 performance_schema 0 drwxr-x---. 2 mysql mysql 55 Oct 1 15:54 sbtest 12K drwxr-x---. 2 mysql mysql 8.0K Oct 1 15:53 sys mysql2 mysql> select * from slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: mysql-bin.000005 Master_log_pos: 141155316 Host: mysql1 User_name: repl_async User_password: Xslave Port: 3306 Connect_retry: 60 Enabled_ssl: 0 Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0 Heartbeat: 30 Bind: Ignored_server_ids: 0 Uuid: 3cd84735-87ef-11e6-a6da-08002718d305 Retry_count: 86400 Ssl_crl: Ssl_crlpath: Enabled_auto_position: 1 Channel_name: Tls_version: 1 row in set (0.00 sec) mysql2 mysql> show slave status\G Empty set (0.00 sec) mysql2 mysql> reset slave all; ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log. mysql2 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) [root@mysql2 ~]# systemctl restart mysqld error log: 2016-10-01T20:02:41.694225Z 0 [ERROR] Failed to open the relay log './mysql2-relay-bin.000007' (relay_log_pos 141155529). 2016-10-01T20:02:41.694235Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './mysql2-relay-bin.index' during relay log initialization. 2016-10-01T20:02:41.695015Z 0 [ERROR] Failed to initialize the master info structure 2016-10-01T20:02:41.695031Z 0 [ERROR] Failed to create or recover replication info repositories. 2016-10-01T20:02:41.695035Z 0 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. ``` - I did reset slave all in super_read_only mode, it gave an error saying that I can't do that because of super_read_only - the relay logs were gone - show slave status is gone - reset slave all is now broken - there are errors in the log - restarting mysql gives errors now Suggested fix: Avoid a state where replication is half removed. If RESET SLAVE ALL is not allowed in SUPER_READ_ONLY mode then do nothing (except displaying an error) If it is allowed, do everything