I am trying to run the following.
mysql > purge binary logs to 'mysqld-relay-bin.000075';
ERROR 1373 (HY000): Target log not found in binlog index
# cat ./mysqld-relay-bin.index
/srv/mysql/logs/mysqld-relay-bin.000010
....
/srv/mysql/logs/mysqld-relay-bin.000075
/srv/mysql/logs/mysqld-relay-bin.000076
/srv/mysql/logs/mysqld-relay-bin.000077
/srv/mysql/logs/mysqld-relay-bin.000078
/srv/mysql/logs/mysqld-relay-bin.000079
What can I do to manually clear remove these relay logs.
MariaDB [(none)]> show slave status;
Empty set (0.00 sec)
MariaDB [(none)]> show master status;
+---------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+--------------+------------------+
| binary.000141 | 487953618 | | |
+---------------+-----------+--------------+------------------+
ls -lrt /var/lib/mysql/logs/
total 4616604
-rw-rw---- 1 mysql mysql 299 Jun 7 15:04 mysqld-relay-bin.000010
-rw-rw---- 1 mysql mysql 299 Jun 7 15:19 mysqld-relay-bin.000011
-rw-rw---- 1 mysql mysql 299 Jun 7 15:21 mysqld-relay-bin.000012
....
-rw-rw---- 1 mysql mysql 299 Jul 23 01:15 mysqld-relay-bin.000075
-rw-rw---- 1 mysql mysql 299 Jul 23 01:15 mysqld-relay-bin.000076
-rw-rw---- 1 mysql mysql 268 Jul 24 09:17 mysqld-relay-bin.000077
As part of a backup script that I run.
I flush logs every night, could that have an effect.
Maser server where the replication logs are piling up.
MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1000 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%relay%';
+-----------------------+----------------------------------------+
| Variable_name | Value |
+-----------------------+----------------------------------------+
| max_relay_log_size | 1073741824 |
| relay_log | /srv/mysql/logs/mysqld-relay-bin |
| relay_log_basename | /srv/mysql/logs/mysqld-relay-bin |
| relay_log_index | /srv/mysql/logs/mysqld-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+-----------------------+----------------------------------------+
Slave that connects to the master.
MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1002 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.21.228.81
Master_User: db.replicator
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binary.000141
Read_Master_Log_Pos: 540479720
Relay_Log_File: mysqld-relay-bin.000358
Relay_Log_Pos: 540480005
Relay_Master_Log_File: binary.000141
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: 540479720
Relay_Log_Space: 540480344
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: 1000
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
ERROR: No query specified
Best Answer
You are mixing up two things: "binlogs" and "relaylogs"
The Master writes to a sequence of binlogs on the Master. These are purged by any of () expire_logs_days, () explicit PURGE, (*) OS delete command (not recommended, but possible).
The Slave receives data from the Master's binlogs and immediately writes the data to a sequence of "relay logs" that live on the Slave. The "IO Thread" does that. Meanwhile, the "SQL thread" is reading the relaylogs and performing the actions. As it finishes a relaylog, it automatically deletes the file. No manual action should ever be needed on the relay logs.
What you did was to try a binlog action on the relaylogs. Don't do that.
Normally, there will be one, occasionally two, relay logs visible on the Slave. Since you are seeing dozens, the Slave must be terribly far behind on replication. This probably means that it is stuck. Perform
SHOW SLAVE STATUS;
to see why. After fixing the problem, the relay logs will cleanup (as mentioned above).If you do manage to remove/purge/whatever those relay logs, you will permanently break replication. You will have to rebuild the Slave from scratch. So, do not delete those files. (Unless you are rebuilding the Slave, at which point they are ancient history.)
But it is the Master??
server_id
gets the servers into a messy loop.What's in one of those relaylogs?