We help IT Professionals succeed at work.

MySQL replication not working.

Medium Priority
921 Views
Last Modified: 2012-05-06
Hi
I did setup replication as I did before many times. I did setup the master then I did setup the slave, I did LOAD DATA FROM MASTER and it did fetch the database however new records are not added to the slave, if I do "LOAD DATA FROM MASTER" it does fetch the data again, however as said it does not fetch new records. As you can see in the logs below the slave is connected as it should to the server.

Please advice.

090217 19:11:31 [Note] Slave I/O thread: connected to master 'replication@xxxxxxxx:3306',  replication started in log 'mysql-bin.000002'at position 98


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |       98 | mya2billing  | mysql,test       |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Not enough information.  Is the secondary server reporting any errors?
show slave status.

Which mysql version are you using? select @@version;
Top Expert 2008

Author

Commented:
No not reporting any errors

mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: xxxxxxxxxxxx                
                Master_User: xxxxxxxxxxxxx
                Master_Port: 3306                            
              Connect_Retry: 60                              
            Master_Log_File: mysql-bin.000002                
        Read_Master_Log_Pos: 105707                          
             Relay_Log_File: mysql-relay-bin.000002          
              Relay_Log_Pos: 235                              
      Relay_Master_Log_File: mysql-bin.000002                
           Slave_IO_Running: Yes                              
          Slave_SQL_Running: Yes
            Replicate_Do_DB: xxxxxxxxx;
        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: 105707
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)



server/client 5.2
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I think you have a position mismatch between the slave that has 105707 for the position and the master that has the position as 98.
Try resetting the position count.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Compare the equivalent
show master status \G
show slave status \G
Compare the results.
Top Expert 2008

Author

Commented:
Well that position is after several restarts ...at current the status is :

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: xxxxx            
                Master_User: xxxxxxx                
                Master_Port: 3306                            
              Connect_Retry: 60                              
            Master_Log_File: mysql-bin.000002                
        Read_Master_Log_Pos: 116282                          
             Relay_Log_File: mysql-relay-bin.000002          
              Relay_Log_Pos: 235                              
      Relay_Master_Log_File: mysql-bin.000002                
           Slave_IO_Running: Yes                              
          Slave_SQL_Running: Yes
            Replicate_Do_DB: mya2billing;
        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: 116282
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |   116282 | xxxxxxx  | mysql,test       |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Now they appear to be in sync.  Are you saying if you insert a record on the master in the database you are replicating it does not show up on the slave?
Top Expert 2008

Author

Commented:
yes exactly
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Perform a mysqldump on the master server. reset the master
Restore the data on the slave.
And reinitialize the slave.

Something is not right.
Run show processlist on the master.

Rotate the binlog on the master and see whether the change propagates to the slave.  
Top Expert 2008

Author

Commented:
I tried this already

Perform a mysqldump on the master server. reset the master
Restore the data on the slave.
And reinitialize the slave.

"I did not lock the tables though..but I dont think that would cause the problem at hand"

The master is a slave for another different server..replication is working fine, show process list shows :

mysql> show processlist;
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host                | db   | Command     | Time | State                                                                 | Info             |
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  1 | system user |                     | NULL | Connect     | 1005 | Waiting for master to send event                                      | NULL             |
|  2 | system user |                     | NULL | Connect     |    2 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  4 | root        | localhost           | NULL | Query       |    0 | NULL                                                                  | show processlist |
| 38 | replication | THECLIENT:54932 | NULL | Binlog Dump |  709 | Has sent all binlog to slave; waiting for binlog to be updated        | NULL             |
+----+-------------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

How can I rotate the bin log ? If you are refering to the fact that the log position is changing...that is propagating.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Double check the server ID.
You are chaining.  Double check that the transaction from the first master are actually being added into the binlog that is being passed to the slave.

I think you are missing the log_slave_updates in the my.cnf on the intermediate system which would explain everything.
http://mysqlha.blogspot.com/2008/01/how-big-is-your-replication-circlechain.html

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.