1

We have a system where our biz people have notebooks that they take on business trips. They are all mysql slaves and they plug them in every other day to sync them up but sometimes a person might not sync up for several days. We used to just remove bin logs older than X days but we had issues with that when a sales person was on an extended trip and tried to sync up after the threshold. What we need is to automate with a script, the process that removes bin logs on the master once it's ok that they have been applied on all slave. Slaves could be disconnected when the script runs.

How could I know which is the Exec_Master_Log_Pos for the most unsynced slave in my topology? Or the Master_Log_File of it. If I knew that values I could purge the binary logs without any problem.

1

MySQL Utilities 1.6.2 (Beta) has mysqlrplcheck utility and when used with --show-slave-status option will provide you with the slave's status even if the slave's replication thread is stopped.

 % mysqlrplcheck --master=root:msandbox@127.0.0.1:21891 --slave=root:msandbox@127.0.0.1:21892 -v -s
WARNING: Using a password on the command line interface can be insecure.
# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]

 master id = 1
  slave id = 101

Checking server_uuid values                                          [pass]

 master uuid = 00021891-1111-1111-1111-111111111111
  slave uuid = 00021892-2222-2222-2222-222222222222

Is slave connected to master?                                        [WARN]
Check master information file                                        [FAIL]

Slave is stopped.

Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]

  Master lower_case_table_names: 0
   Slave lower_case_table_names: 0

Checking slave delay (seconds behind master)                         [FAIL]

Slave is stopped.


#
# Slave status: 
#
                Slave_IO_State : 
                   Master_Host : 127.0.0.1
                   Master_User : rsandbox
                   Master_Port : 21891
                 Connect_Retry : 60
               Master_Log_File : mysql-bin.000001
           Read_Master_Log_Pos : 151
                Relay_Log_File : mysql-relay.000003
                 Relay_Log_Pos : 314
         Relay_Master_Log_File : mysql-bin.000001
              Slave_IO_Running : No
             Slave_SQL_Running : No
               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 : 151
               Relay_Log_Space : 724
               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 : None
 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 : 1
                   Master_UUID : 00021891-1111-1111-1111-111111111111
              Master_Info_File : mysql.slave_master_info
                     SQL_Delay : 0
           SQL_Remaining_Delay : None
       Slave_SQL_Running_State : 
            Master_Retry_Count : 86400
                   Master_Bind : 
       Last_IO_Error_Timestamp : 
      Last_SQL_Error_Timestamp : 
                Master_SSL_Crl : 
            Master_SSL_Crlpath : 
            Retrieved_Gtid_Set : 
             Executed_Gtid_Set : 
                 Auto_Position : 0
# ...done.

However, this does not work if the slave server is shutdown which in your case is true. The master node does not store slave replication information but you can probably automate something that would allow your biz people to 'click' on a link to execute this script and store the info on a db or a file and you can retrieve this information to decide on which binlog to purge.

1
  • ok. So what I need is the latest binlog file replicated by all slaves. 1 - run mysqlrplcheck and dump the Master_Log_File value and update it in a db. 2 - run a purge until the lowest value of Master_log_file. – johnsout Dec 31 '15 at 21:27

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.