Mysql – Binlog not consistent between slave and master

binlogMySQLreplication

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 ../
-rw-rw---- 1 mysql mysql 101M Sep 18 03:56 mysql-bin.000417
-rw-rw---- 1 mysql mysql 102M Sep 18 03:57 mysql-bin.000418
-rw-rw---- 1 mysql mysql 102M Sep 18 03:58 mysql-bin.000419
-rw-rw---- 1 mysql mysql 103M Sep 18 03:59 mysql-bin.000420
-rw-rw---- 1 mysql mysql 101M Sep 18 04:00 mysql-bin.000421
-rw-rw---- 1 mysql mysql 102M Sep 18 04:01 mysql-bin.000422
-rw-rw---- 1 mysql mysql 101M Sep 18 04:03 mysql-bin.000423
-rw-rw---- 1 mysql mysql 101M Sep 18 04:04 mysql-bin.000424
-rw-rw---- 1 mysql mysql 102M Sep 18 04:05 mysql-bin.000425
-rw-rw---- 1 mysql mysql 102M Sep 18 04:07 mysql-bin.000426
-rw-rw---- 1 mysql mysql 102M Sep 18 04:08 mysql-bin.000427
-rw-rw---- 1 mysql mysql 102M Sep 18 04:10 mysql-bin.000428
-rw-rw---- 1 mysql mysql 101M Sep 18 04:12 mysql-bin.000429
-rw-rw---- 1 mysql mysql 102M Sep 18 04:13 mysql-bin.000430
-rw-rw---- 1 mysql mysql 102M Sep 18 04:15 mysql-bin.000431
-rw-rw---- 1 mysql mysql 101M Sep 18 04:17 mysql-bin.000432
-rw-rw---- 1 mysql mysql 101M Sep 18 04:19 mysql-bin.000433
-rw-rw---- 1 mysql mysql 101M Sep 18 04:21 mysql-bin.000434
-rw-rw---- 1 mysql mysql 103M Sep 18 04:23 mysql-bin.000435
-rw-rw---- 1 mysql mysql 101M Sep 18 04:25 mysql-bin.000436
-rw-rw---- 1 mysql mysql 104M Sep 18 04:27 mysql-bin.000437
-rw-rw---- 1 mysql mysql 101M Sep 18 04:30 mysql-bin.000438
-rw-rw---- 1 mysql mysql 101M Sep 18 04:32 mysql-bin.000439
-rw-rw---- 1 mysql mysql 102M Sep 18 04:35 mysql-bin.000440
-rw-rw---- 1 mysql mysql 101M Sep 18 04:37 mysql-bin.000441
-rw-rw---- 1 mysql mysql 103M Sep 18 04:40 mysql-bin.000442
-rw-rw---- 1 mysql mysql 103M Sep 18 04:42 mysql-bin.000443
-rw-rw---- 1 mysql mysql 101M Sep 18 04:45 mysql-bin.000444
-rw-rw---- 1 mysql mysql 101M Sep 18 04:48 mysql-bin.000445
-rw-rw---- 1 mysql mysql 103M Sep 18 04:51 mysql-bin.000446
-rw-rw---- 1 mysql mysql 101M Sep 18 04:54 mysql-bin.000447
-rw-rw---- 1 mysql mysql 101M Sep 18 04:57 mysql-bin.000448
-rw-rw---- 1 mysql mysql 101M Sep 18 04:59 mysql-bin.000449
-rw-rw---- 1 mysql mysql 101M Sep 18 05:02 mysql-bin.000450
-rw-rw---- 1 mysql mysql 101M Sep 18 05:06 mysql-bin.000451
-rw-rw---- 1 mysql mysql 101M Sep 18 05:09 mysql-bin.000452
-rw-rw---- 1 mysql mysql 101M Sep 18 05:12 mysql-bin.000453
-rw-rw---- 1 mysql mysql 102M Sep 18 05:15 mysql-bin.000454
-rw-rw---- 1 mysql mysql 101M Sep 18 05:19 mysql-bin.000455
-rw-rw---- 1 mysql mysql 102M Sep 18 05:22 mysql-bin.000456
-rw-rw---- 1 mysql mysql 101M Sep 18 05:26 mysql-bin.000457
-rw-rw---- 1 mysql mysql 103M Sep 18 05:29 mysql-bin.000458
-rw-rw---- 1 mysql mysql 104M Sep 18 05:33 mysql-bin.000459
-rw-rw---- 1 mysql mysql 102M Sep 18 05:37 mysql-bin.000460
-rw-rw---- 1 mysql mysql 102M Sep 18 05:41 mysql-bin.000461
-rw-rw---- 1 mysql mysql 102M Sep 18 05:45 mysql-bin.000462
-rw-rw---- 1 mysql mysql 102M Sep 18 05:49 mysql-bin.000463
-rw-rw---- 1 mysql mysql 101M Sep 18 05:53 mysql-bin.000464
-rw-rw---- 1 mysql mysql 103M Sep 19 07:43 mysql-bin.000465
-rw-rw---- 1 mysql mysql 101M Sep 19 07:44 mysql-bin.000466
-rw-rw---- 1 mysql mysql 101M Sep 19 07:45 mysql-bin.000467
-rw-rw---- 1 mysql mysql 102M Sep 19 07:46 mysql-bin.000468
-rw-rw---- 1 mysql mysql 102M Sep 19 07:47 mysql-bin.000469
-rw-rw---- 1 mysql mysql 103M Sep 19 07:48 mysql-bin.000470
-rw-rw---- 1 mysql mysql 102M Sep 19 07:49 mysql-bin.000471
-rw-rw---- 1 mysql mysql 103M Sep 19 07:50 mysql-bin.000472
-rw-rw---- 1 mysql mysql 101M Sep 19 07:51 mysql-bin.000473
-rw-rw---- 1 mysql mysql 102M Sep 19 07:53 mysql-bin.000474
-rw-rw---- 1 mysql mysql 101M Sep 19 07:54 mysql-bin.000475
-rw-rw---- 1 mysql mysql 103M Sep 19 07:55 mysql-bin.000476
-rw-rw---- 1 mysql mysql 103M Sep 19 07:57 mysql-bin.000477
-rw-rw---- 1 mysql mysql 103M Sep 19 07:58 mysql-bin.000478
-rw-rw---- 1 mysql mysql 102M Sep 19 08:00 mysql-bin.000479
-rw-rw---- 1 mysql mysql 103M Sep 19 08:01 mysql-bin.000480
-rw-rw---- 1 mysql mysql 103M Sep 19 08:03 mysql-bin.000481
-rw-rw---- 1 mysql mysql 101M Sep 19 08:05 mysql-bin.000482
-rw-rw---- 1 mysql mysql 101M Sep 19 08:07 mysql-bin.000483
-rw-rw---- 1 mysql mysql 102M Sep 19 08:09 mysql-bin.000484
-rw-rw---- 1 mysql mysql 102M Sep 19 08:11 mysql-bin.000485
-rw-rw---- 1 mysql mysql 101M Sep 19 08:13 mysql-bin.000486
-rw-rw---- 1 mysql mysql 101M Sep 19 08:15 mysql-bin.000487
-rw-rw---- 1 mysql mysql 101M Sep 19 08:17 mysql-bin.000488
-rw-rw---- 1 mysql mysql 101M Sep 19 08:19 mysql-bin.000489
-rw-rw---- 1 mysql mysql 101M Sep 19 08:21 mysql-bin.000490
-rw-rw---- 1 mysql mysql 102M Sep 19 08:24 mysql-bin.000491
-rw-rw---- 1 mysql mysql 104M Sep 19 08:26 mysql-bin.000492
-rw-rw---- 1 mysql mysql 101M Sep 19 08:29 mysql-bin.000493
-rw-rw---- 1 mysql mysql 102M Sep 19 08:32 mysql-bin.000494
-rw-rw---- 1 mysql mysql 103M Sep 19 08:34 mysql-bin.000495
-rw-rw---- 1 mysql mysql 102M Sep 19 08:37 mysql-bin.000496
-rw-rw---- 1 mysql mysql 101M Sep 19 08:40 mysql-bin.000497
-rw-rw---- 1 mysql mysql 101M Sep 19 08:43 mysql-bin.000498
-rw-rw---- 1 mysql mysql 102M Sep 19 08:46 mysql-bin.000499
-rw-rw---- 1 mysql mysql 101M Sep 19 08:49 mysql-bin.000500
-rw-rw---- 1 mysql mysql 101M Sep 19 08:52 mysql-bin.000501
-rw-rw---- 1 mysql mysql 103M Sep 19 08:55 mysql-bin.000502
-rw-rw---- 1 mysql mysql 102M Sep 19 08:58 mysql-bin.000503
-rw-rw---- 1 mysql mysql 101M Sep 19 09:01 mysql-bin.000504
-rw-rw---- 1 mysql mysql 102M Sep 19 09:05 mysql-bin.000505
-rw-rw---- 1 mysql mysql 104M Sep 19 09:08 mysql-bin.000506
-rw-rw---- 1 mysql mysql 103M Sep 19 09:12 mysql-bin.000507
-rw-rw---- 1 mysql mysql 101M Sep 19 09:15 mysql-bin.000508
-rw-rw---- 1 mysql mysql 103M Sep 19 09:19 mysql-bin.000509
-rw-rw---- 1 mysql mysql 103M Sep 19 09:22 mysql-bin.000510
-rw-rw---- 1 mysql mysql 104M Sep 19 09:26 mysql-bin.000511
-rw-rw---- 1 mysql mysql 102M Sep 19 09:30 mysql-bin.000512
-rw-rw---- 1 mysql mysql 102M Sep 19 09:34 mysql-bin.000513
-rw-rw---- 1 mysql mysql 102M Sep 19 09:38 mysql-bin.000514
-rw-rw---- 1 mysql mysql 102M Sep 19 09:42 mysql-bin.000515
-rw-rw---- 1 mysql mysql  48M Sep 23 01:50 mysql-bin.000516
-rw-rw---- 1 mysql mysql 1.2M Sep 23 02:44 mysql-bin.000517
-rw-rw---- 1 mysql adm   3.2K Sep 23 02:23 mysql-bin.index

Master

root@prod-dbm:/vol/log/mysql# ll -h
total 394M
drwxr-s--- 2 mysql adm   134 2014-09-18 21:40 ./
drwxr-xr-x 3 root  root   18 2012-12-29 18:52 ../
-rw-rw---- 1 mysql adm  197K 2014-07-21 18:41 error.log
-rw-rw---- 1 mysql adm  101M 2014-09-14 01:50 mysql-bin.000415
-rw-rw---- 1 mysql adm  101M 2014-09-18 21:40 mysql-bin.000416
-rw-rw---- 1 mysql adm   99M 2014-09-23 02:46 mysql-bin.000417
-rw-rw---- 1 mysql adm    96 2014-09-18 21:40 mysql-bin.index
-rw-rw---- 1 mysql adm   31M 2014-09-23 01:53 slow_query.log

Questions:

1, After the slave was created, we were running some analytic program and insert the result back to the salve (a different DB though), so I assume all the binlog from mysql-bin.000417 to mysql-bin.000515 on slave was the operations of analytic program. Now I enabled log-slave-updates, will the new binlog has mixed content of operations from replication as well as analytic program?

2, In "slave status", Relay_Master_Log_File is pointing to mysql-bin.000417, while on slave harddrive, the file mysql-bin.000417 ~ mysql-bin.000515 has not been updated since 2014-9-18. After I enabled log-slave-updates, file mysql-bin.000516 begins to be updated. So if I want to release some disk space, is it safe to purge file prior to mysql-bin.000516?

3, Generally, if Seconds_Behind_Master is 0, is it safe to remove all the previous binlogs? (Since the replication is in sync, and assuming analytic program is finished)

Thanks.

Best Answer

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

Related Question