| Bug #842 | relay logs are not cleaned out | ||
|---|---|---|---|
| Submitted: | 14 Jul 2003 6:05 | Modified: | 14 Jul 2003 6:24 |
| Reporter: | David Newcomb (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 4.0.13 | OS: | Windows (Windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[14 Jul 2003 6:24]
Guilhem Bichot
Hi, > Contents of Initial MySQL data direcory: > > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:31 <DIR> . > 14/07/2003 13:31 <DIR> .. > 01/07/2003 12:37 <DIR> mysql > 0 File(s) 0 bytes > 3 Dir(s) 11,521,646,592 bytes free > > Startup MySQL: > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:31 <DIR> . > 14/07/2003 13:31 <DIR> .. > 14/07/2003 13:31 925 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 01/07/2003 12:37 <DIR> mysql > 8 File(s) 20,997,752 bytes > 3 Dir(s) 11,500,642,304 bytes free > > mysql> change master to master_host='stour'; > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:32 <DIR> . > 14/07/2003 13:32 <DIR> .. > 14/07/2003 13:31 925 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:32 4 Gamlan-relay-bin.001 > 14/07/2003 13:32 23 Gamlan-relay-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 14/07/2003 13:32 23 master.info > 01/07/2003 12:37 <DIR> mysql > 14/07/2003 13:32 28 relay-log.info > 12 File(s) 20,997,830 bytes > 3 Dir(s) 11,500,642,304 bytes free > > mysql> start slave; > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:32 <DIR> . > 14/07/2003 13:32 <DIR> .. > 14/07/2003 13:33 1,197 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:33 119 Gamlan-relay-bin.001 > 14/07/2003 13:32 23 Gamlan-relay-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 14/07/2003 13:33 37 master.info > 01/07/2003 12:37 <DIR> mysql > 14/07/2003 13:33 44 relay-log.info > 12 File(s) 20,998,247 bytes > 3 Dir(s) 11,500,642,304 bytes free After START SLAVE, the slave writes to the relay log. > mysql> stop slave; > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:32 <DIR> . > 14/07/2003 13:32 <DIR> .. > 14/07/2003 13:33 1,541 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:33 119 Gamlan-relay-bin.001 > 14/07/2003 13:32 23 Gamlan-relay-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 14/07/2003 13:33 37 master.info > 01/07/2003 12:37 <DIR> mysql > 14/07/2003 13:33 44 relay-log.info > 12 File(s) 20,998,591 bytes > 3 Dir(s) 11,500,642,304 bytes free Yes, the relay log is not deleted. If you do START SLAVE now, the slave will write at the end of this relay log. Until it exceeds max_binlog_size, in that case a new relay log will be created, and the old one deleted as soon as the SQL slave thread has executed it. > mysql> reset slave; > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:36 <DIR> . > 14/07/2003 13:36 <DIR> .. > 14/07/2003 13:33 1,541 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:36 4 Gamlan-relay-bin.001 > 14/07/2003 13:36 23 Gamlan-relay-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 01/07/2003 12:37 <DIR> mysql > 10 File(s) 20,998,395 bytes > 3 Dir(s) 11,500,642,304 bytes free The slave has deleted the previous Gamlan-relay-bin.001 and created a new empty one (see the size: 4). RESET SLAVE deletes all existing relay logs and creates a new empty one. It also deletes master.info and relay-log.info. > mysql> show slave status\G > *************************** 1. row *************************** > Master_Host: stour > Master_User: qrepl > Master_Port: 3306 > Connect_retry: 2 > Master_Log_File: > Read_Master_Log_Pos: 4 > Relay_Log_File: Gamlan-relay-bin.001 > Relay_Log_Pos: 4 > Relay_Master_Log_File: > Slave_IO_Running: No > Slave_SQL_Running: No > Replicate_do_db: quantel > Replicate_ignore_db: > Last_errno: 0 > Last_error: > Skip_counter: 0 > Exec_master_log_pos: 0 > Relay_log_space: 4 > 1 row in set (0.00 sec) > > mysql> change master to master_host=''; > C:\mysql\data>dir /on > Volume in drive C has no label. > Volume Serial Number is 283E-C3F4 > > Directory of C:\mysql\data > > 14/07/2003 13:37 <DIR> . > 14/07/2003 13:37 <DIR> .. > 14/07/2003 13:33 1,541 Gamlan.err > 14/07/2003 13:31 79 Gamlan-bin.001 > 14/07/2003 13:31 17 Gamlan-bin.index > 14/07/2003 13:36 4 Gamlan-relay-bin.001 > 14/07/2003 13:37 4 Gamlan-relay-bin.002 > 14/07/2003 13:37 23 Gamlan-relay-bin.index > 14/07/2003 13:31 123 Gamlan-slow.log > 14/07/2003 13:31 25,088 ib_arch_log_0000000000 > 14/07/2003 13:31 5,242,880 ib_logfile0 > 14/07/2003 13:31 5,242,880 ib_logfile1 > 14/07/2003 13:31 10,485,760 ibdata1 > 14/07/2003 13:37 23 master.info > 01/07/2003 12:37 <DIR> mysql > 14/07/2003 13:37 28 relay-log.info > 13 File(s) 20,998,450 bytes > 3 Dir(s) 11,500,642,304 bytes free CHANGE MASTER TO MASTER_HOST='' should not work. The way to make a slave forget it is a slave is with RESET SLAVE. But what is confusing you is that after RESET SLAVE, a small empty relay log exists, and SHOW SLAVE STATUS returns a row (not "empty set"). But the important thing is that RESET SLAVE has deleted master.info and relay-log.info. So when the slave mysqld will restart, it will not find any *.info files, and then SHOW SLAVE STATUS will return "empty set". Yes, this small 4-byte relay log will still exist and has to be deleted by hand. > > type relay-log.info: > ------start------ > .\Gamlan-relay-bin.001 > 4 > > 0 > ------end------ > > > Suggested fix: > When they issue a change master to master_host='' then clean all the relay > logs, as it says in the manual. It clears all relay logs but starts a new empty one, to prepare for future replication. > There should be a way of completely stopping replication so that it can > not remember anything about what is was replicating from. RESET SLAVE is intended for that. I agree total cleaning (no 4-byte relay log, empty set in SHOW SLAVE STATUS) could be on our TODO, but this does not seem very urgent, as it is, to me, no strong annoyance.
[14 Jul 2003 7:28]
David Newcomb
Fair enough. It's more of an annoyance than anything else. http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html says "CHANGE MASTER TO deletes all relay logs and starts a new one,..." which is currently not true as the relay logs will build up in the directory on each change master.
[14 Jul 2003 7:46]
Guilhem Bichot
> http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html says "CHANGE MASTER > TO deletes all relay logs and starts a new one,..." which is currently > not true as the relay logs will build up in the directory on each > change master. Yes, sometimes doing CHANGE MASTER you will end up with 2 4-byte relay logs. This is because we have some redundant functions in code (functions which handle relay logs, which we call twice at different places; not very efficient). But the first 4-byte relay log will be deleted as soon as you do START SLAVE. Still, I checked our code and didn't find any possibility for more than 2 4-bytes relays logs when calling CHANGE MASTER. So not more than two relay logs *should* build up.
Description: When starting and stopping slaving, the relay-bin logs are not deleted. The "change master to ..." closes and re-opens the relay logs, even if master_host=''; If you are switching between replication masters, the relay-bin logs just fill up the directory. How to repeat: Contents of Initial MySQL data direcory: C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:31 <DIR> . 14/07/2003 13:31 <DIR> .. 01/07/2003 12:37 <DIR> mysql 0 File(s) 0 bytes 3 Dir(s) 11,521,646,592 bytes free Startup MySQL: C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:31 <DIR> . 14/07/2003 13:31 <DIR> .. 14/07/2003 13:31 925 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 01/07/2003 12:37 <DIR> mysql 8 File(s) 20,997,752 bytes 3 Dir(s) 11,500,642,304 bytes free mysql> change master to master_host='stour'; C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:32 <DIR> . 14/07/2003 13:32 <DIR> .. 14/07/2003 13:31 925 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:32 4 Gamlan-relay-bin.001 14/07/2003 13:32 23 Gamlan-relay-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 14/07/2003 13:32 23 master.info 01/07/2003 12:37 <DIR> mysql 14/07/2003 13:32 28 relay-log.info 12 File(s) 20,997,830 bytes 3 Dir(s) 11,500,642,304 bytes free mysql> start slave; C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:32 <DIR> . 14/07/2003 13:32 <DIR> .. 14/07/2003 13:33 1,197 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:33 119 Gamlan-relay-bin.001 14/07/2003 13:32 23 Gamlan-relay-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 14/07/2003 13:33 37 master.info 01/07/2003 12:37 <DIR> mysql 14/07/2003 13:33 44 relay-log.info 12 File(s) 20,998,247 bytes 3 Dir(s) 11,500,642,304 bytes free mysql> stop slave; C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:32 <DIR> . 14/07/2003 13:32 <DIR> .. 14/07/2003 13:33 1,541 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:33 119 Gamlan-relay-bin.001 14/07/2003 13:32 23 Gamlan-relay-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 14/07/2003 13:33 37 master.info 01/07/2003 12:37 <DIR> mysql 14/07/2003 13:33 44 relay-log.info 12 File(s) 20,998,591 bytes 3 Dir(s) 11,500,642,304 bytes free mysql> reset slave; C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:36 <DIR> . 14/07/2003 13:36 <DIR> .. 14/07/2003 13:33 1,541 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:36 4 Gamlan-relay-bin.001 14/07/2003 13:36 23 Gamlan-relay-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 01/07/2003 12:37 <DIR> mysql 10 File(s) 20,998,395 bytes 3 Dir(s) 11,500,642,304 bytes free mysql> show slave status\G *************************** 1. row *************************** Master_Host: stour Master_User: qrepl Master_Port: 3306 Connect_retry: 2 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: Gamlan-relay-bin.001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: quantel Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 0 Relay_log_space: 4 1 row in set (0.00 sec) mysql> change master to master_host=''; C:\mysql\data>dir /on Volume in drive C has no label. Volume Serial Number is 283E-C3F4 Directory of C:\mysql\data 14/07/2003 13:37 <DIR> . 14/07/2003 13:37 <DIR> .. 14/07/2003 13:33 1,541 Gamlan.err 14/07/2003 13:31 79 Gamlan-bin.001 14/07/2003 13:31 17 Gamlan-bin.index 14/07/2003 13:36 4 Gamlan-relay-bin.001 14/07/2003 13:37 4 Gamlan-relay-bin.002 14/07/2003 13:37 23 Gamlan-relay-bin.index 14/07/2003 13:31 123 Gamlan-slow.log 14/07/2003 13:31 25,088 ib_arch_log_0000000000 14/07/2003 13:31 5,242,880 ib_logfile0 14/07/2003 13:31 5,242,880 ib_logfile1 14/07/2003 13:31 10,485,760 ibdata1 14/07/2003 13:37 23 master.info 01/07/2003 12:37 <DIR> mysql 14/07/2003 13:37 28 relay-log.info 13 File(s) 20,998,450 bytes 3 Dir(s) 11,500,642,304 bytes free type relay-log.info: ------start------ .\Gamlan-relay-bin.001 4 0 ------end------ Suggested fix: When they issue a change master to master_host='' then clean all the relay logs, as it says in the manual. There should be a way of completely stopping replication so that it can not remember anything about what is was replicating from. Also you must be able to do this remotely. Shutting down MySQL and deleting the logs by hand is not a good enough solution.