Binlog not consistent between slave and master

Initially I don't see binlog on slave is updating. Today I discovered and add log-slave-updates option to slave, now it is updating. However, the filename does not match between slave and master.

Please check below,

Slave status,

slave> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.192.1.100
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
              Master_Log_File: mysql-bin.000417
          Read_Master_Log_Pos: 103252997
               Relay_Log_File: mysqld-relay-bin.000027
                Relay_Log_Pos: 1035475
        Relay_Master_Log_File: mysql-bin.000417
             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: 103252997
              Relay_Log_Space: 1035632
              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: 3
1 row in set (0.00 sec)

binlog directory content

Slave

```
root@slave:/vol/log/mysql# ll -h
total 9.9G
drwxr-xr-x 2 mysql mysql 4.0K Sep 23 02:23 ./
drwxr-xr-x 3 mysql mysql 18 Sep 4 20:51 ../
-

The MySQL replication process works like follow :

• Transactions are executed on the Master

• All transactions are written in Binary Logs files (mysql-bin.xxxxx) as "event" on the Master

• On a Slave, two threads are dedicated to the replication process

• The I/O thread reads the Master Binary Logs events and writes them to the relay log in local (mysqld-relay-bin.xxxxx)

• The SQL thread reads the Relay Log events and executes them on the Slave

In the SHOW SLAVE STATUS\G output the important info are:

What is the current Master position?

• Master_Log_File: The current Binary Log of the Master

• Read_Master_Log_Pos: The current Binary Log position of the Master

What is the current Slave position?

• Relay_Log_File: The current Relay Log of the Slave

• Relay_Log_Pos: The current Relay Log position of the Slave

What is the current Slave position regarding to the Master's Binary Logs?

• Relay_Master_Log_File: The Binary Log (on the master) which corresponding to the actual position of the slave

• Exec_Master_Log_Pos: The Binary Log position (on the master) which corresponding to the actual position of the slave

Now, if you don't use the Binary Logs on Slaves (for point in time recovery or chained replication) you can disable it and remove them:

log_bin = 0

To purge your binary logs, use:

PURGE BINARY LOGS;

Note regarding log-slave-updates: by default if you enable Binary Logs on Slave, the Slave will only writes events executed on the Slave directly, none of the events coming from its Master will be written in the Slave's Binary Logs. If you want to setup a chained replication (M -> S/M -> S), you need to tell the Slave to logs the Master events on its Binary Logs to replicate them on its own Slaves. This options is log-slave-updates.

If you need to enable Binary Logs on Slave the command to see the curent position of the Slave´s Binary Logs is SHOW MATSER STATUS; you will see the position coresponding to your files on your directory (on slave).

Note on Binary Logs managment: Do not forget to set a "purge strategy" for your Binary Logs if you don't want to saturate your disks. The simplest way is to use the expire_logs_days variable which tell to MySQL to purge its Binary Logs older than this variable.

I hope I was clear...

Best Regards

StackExchange Database Administrators Q#77346, answer score: 2