3

My problem is that all setup is done for MySQL replication but the slave is unable to sync the master's data. To understand I am sharing link below; please visit.

mysql>  SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.110
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000033
          Read_Master_Log_Pos: 402
               Relay_Log_File: VoltyLinux-relay-bin.000046
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000033
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replica
          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: 402
              Relay_Log_Space: 692
              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: 1
                  Master_UUID: f1739fcc-0d2d-11e6-a8cc-c03fd56585b5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 60
                  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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

https://stackoverflow.com/q/36929641/2644613

  • Just to clarify: with "sync" you mean that changes on master are not propagated to the slave, right? What are the server_ids of the servers? (run: show global variables like 'server_id' on both the slave and the master). Please also include the master status from the master host. Do you have any replication filters enabled? – Károly Nagy May 2 '16 at 8:05
0

From your Show slave status \G output, the following things can be understood:

  1. Slave_IO_Running: Yes It means, Input Output thread is running - this thread is responsible for downloading data (binlog) from master server to slave server.
  2. Slave_SQL_Running: Yes It means, SQL thread is running - this thread is responsible for executing SQL from relay log (from binlog).
  3. Read_Master_Log_Pos: 402, Exec_Master_Log_Pos = 402, It means, the IO thread reading from the binary log position 402 and the SQL thread executing the same query.
  4. Seconds_Behind_Master: 0 It means, both the master and slave is in sync and slave is in up-to the point, no delay.
  5. Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates. It means slave is up to the point.

It seems, slave is getting data from master and there is no issues in your settings.

FYI:

  1. Run the Show Slave Status \G command continuously, you will see the following variable values will change, every time.
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 402
Relay_Log_File: VoltyLinux-relay-bin.000046
Relay_Log_Pos: 317
Exec_Master_Log_Pos: 402
Relay_Log_Space: 692

In case, if your slave stopped getting data from master server, you will see the No value for Slave_IO Running variable in your Show Slave Status \G command output as follows.

Slave_IO_Running: NO

I hope this answer will help you. Thanks.

  • "change every time"? I don't think so. – Rick James May 2 '16 at 19:54
  • @RickJames If master data replicated to slave, the values will change. However, we can define the maximum size limit for log files (binary log & relay log), once the max limit reached, log files names will change. I tried to answer in simple language, so may be few things change a bit. Thank you. – rathishDBA May 3 '16 at 6:53
  • In non-busy systems, Log_files may change only once a week. A busy system might see them change once an hour. But not "every time you run SHOW SLAVE STATUS". – Rick James May 3 '16 at 23:37
  • Simple way just create a test database on master server and see if it got created on slave server (assuming no time lagging). – rathishDBA May 17 '17 at 14:12

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.