1

After adding:

expire_logs_days=7

is it necessary to change reset the master's position and file?

I had modified the my.cnf figuring that would delete the no longer needed binary logs older than 7 days on the server. (The Seconds_Behind_Master is never larger than 300 and we use flush-logs nightly.)

After stopping the mysql instance, adding that configuration change, and restarting I received:

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

My slave values were:

Master_Log_File: mysql-bin.000836    
Relay_Log_File: mysqld-relay-bin.001104
Relay_Log_File: mysqld-relay-bin.001104
Relay_Log_Pos: 59129553
Relay_Master_Log_File: mysql-bin.000834
Exec_Master_Log_Pos: 95093013
Relay_Log_Space: 824525831

When I looking at the storage on the server I saw:

-rw-rw----.  1 mysql mysql 242M May  8 02:00 mysqld-bin.000358
-rw-rw----.  1 mysql mysql 306M May  9 02:00 mysqld-bin.000359
-rw-rw----.  1 mysql mysql 237M May 10 02:00 mysqld-bin.000360
-rw-rw----.  1 mysql mysql 213M May 11 02:00 mysqld-bin.000361
-rw-rw----.  1 mysql mysql 192M May 12 02:00 mysqld-bin.000362
-rw-rw----.  1 mysql mysql  44G May 12 12:26 mysqld-bin.000363
-rw-rw----.  1 mysql mysql 926K May 12 13:01 mysqld-bin.000364
-rw-rw----.  1 mysql mysql 110M May 13 02:00 mysqld-bin.000365
-rw-rw----.  1 mysql mysql  53M May 13 07:54 mysqld-bin.000366
-rw-rw----.  1 mysql mysql 1.1G May 13 23:00 mysqld-bin.000367
-rw-rw----.  1 mysql mysql 155M May 14 02:00 mysqld-bin.000368
-rw-rw----.  1 mysql mysql 1.1G May 14 13:47 mysqld-bin.000369
-rw-rw----.  1 mysql mysql 299M May 15 02:00 mysqld-bin.000370
-rw-rw----.  1 mysql mysql 129M May 15 18:12 mysqld-bin.000371
-rw-rw----.  1 mysql mysql  35M May 13 02:00 mysqld-relay-bin.001103
-rw-rw----.  1 mysql mysql  57M May 13 07:53 mysqld-relay-bin.001104
-rw-rw----.  1 mysql mysql  174 May 13 10:04 mysqld-relay-bin.001105
-rw-rw----.  1 mysql mysql 133M May 13 22:01 mysqld-relay-bin.001106
-rw-rw----.  1 mysql mysql  221 May 13 22:01 mysqld-relay-bin.001107
-rw-rw----.  1 mysql mysql  71M May 14 02:00 mysqld-relay-bin.001108
-rw-rw----.  1 mysql mysql 242M May 14 22:01 mysqld-relay-bin.001109
-rw-rw----.  1 mysql mysql  221 May 14 22:01 mysqld-relay-bin.001110
-rw-rw----.  1 mysql mysql  35M May 15 02:00 mysqld-relay-bin.001111
-rw-rw----.  1 mysql mysql 214M May 15 18:12 mysqld-relay-bin.001112
-rw-rw----.  1 mysql mysql  260 May 15 02:00 mysqld-relay-bin.index

which is correct, the files are only the past 7 days. So I think I missed a step but didn't see that requirement on the mysql doc page (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_expire_logs_days) so wanted to verify.

I was able to get the slave functional again by using the following commands:

stop slave;
reset slave;
change master to master_log_file='mysql-bin.000834' , master_log_pos=95093013
start slave;

The drive now has:

-rw-rw----.  1 mysql mysql 242M May  8 02:00 mysqld-bin.000358
-rw-rw----.  1 mysql mysql 306M May  9 02:00 mysqld-bin.000359
-rw-rw----.  1 mysql mysql 237M May 10 02:00 mysqld-bin.000360
-rw-rw----.  1 mysql mysql 213M May 11 02:00 mysqld-bin.000361
-rw-rw----.  1 mysql mysql 192M May 12 02:00 mysqld-bin.000362
-rw-rw----.  1 mysql mysql  44G May 12 12:26 mysqld-bin.000363
-rw-rw----.  1 mysql mysql 926K May 12 13:01 mysqld-bin.000364
-rw-rw----.  1 mysql mysql 110M May 13 02:00 mysqld-bin.000365
-rw-rw----.  1 mysql mysql  53M May 13 07:54 mysqld-bin.000366
-rw-rw----.  1 mysql mysql 1.1G May 13 23:00 mysqld-bin.000367
-rw-rw----.  1 mysql mysql 155M May 14 02:00 mysqld-bin.000368
-rw-rw----.  1 mysql mysql 1.1G May 14 13:47 mysqld-bin.000369
-rw-rw----.  1 mysql mysql 299M May 15 02:00 mysqld-bin.000370
-rw-rw----.  1 mysql mysql 129M May 15 18:18 mysqld-bin.000371
-rw-rw----.  1 mysql mysql  61M May 15 18:51 mysqld-bin.000372
-rw-rw----.  1 mysql mysql  300 May 15 18:18 mysqld-bin.index
-rw-rw----.  1 mysql mysql  221 May 15 18:30 mysqld-relay-bin.000005
-rw-rw----.  1 mysql mysql 256M May 15 18:51 mysqld-relay-bin.000006
-rw-rw----.  1 mysql mysql   52 May 15 18:31 mysqld-relay-bin.index

So if I just missed a step great, but I'd like to avoid the server getting out of sync every time the log files change. The expire change freed 260 GBs so it was/is going to be needed. I currently have it commented out until I know the expected behavior.

  • CHANGE MASTER is not needed; it does not get out of sync. Old files are not needed; their deletion has no impact except on disk space. – Rick James May 27 '18 at 16:49
1

Changing the variable's value from mysql client should work:

slave2 [localhost] {msandbox} ((none)) > select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

slave2 [localhost] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.01 sec)

slave2 [localhost] {msandbox} ((none)) > set @@global.expire_logs_days=7;
Query OK, 0 rows affected (0.00 sec)

slave2 [localhost] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.00 sec)

slave2 [localhost] {msandbox} ((none)) > select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  7 |
+--------------------+
1 row in set (0.00 sec)

To answer you, it is not needed to reset master or execute CHANGE MASTER again. Make sure to add expire_logs_days=7 in your my.cnf, but you don't have to restart mysqld since the above changes will take in effect.

  • That is roughly what I did the first time though. Setting it global is the same as setting it in the my.cnf and restarting, isn't it? – user3783243 May 15 '18 at 23:58
  • Looks the same, but here we're making sure the slave replication is stopped while you change expire_logs_days var. If you want the restart method, edit my.cnf with expire_logs_days=7 and add skip-slave-start to manually start slave replication after mysqld restarts completely. – jerichorivera May 16 '18 at 0:07
  • @jerichorivera - Do you need STOP/START SLAVE?? – Rick James May 27 '18 at 16:45
  • @user3783243 - The SET is lost when the server stops. Changing my.cnf does not apply until after the server restarts. Doing both covers all cases, without the need for an extra restart. – Rick James May 27 '18 at 16:47

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.