[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

MySQL replication not working.

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)
0
http:// thevpn.guru
Asked:
http:// thevpn.guru
  • 6
  • 4
1 Solution
 
arnoldCommented:
Not enough information.  Is the secondary server reporting any errors?
show slave status.

Which mysql version are you using? select @@version;
0
 
http:// thevpn.guruAuthor 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
0
 
arnoldCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arnoldCommented:
Compare the equivalent
show master status \G
show slave status \G
Compare the results.
0
 
http:// thevpn.guruAuthor 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)


0
 
arnoldCommented:
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?
0
 
http:// thevpn.guruAuthor Commented:
yes exactly
0
 
arnoldCommented:
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.  
0
 
http:// thevpn.guruAuthor 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.
0
 
arnoldCommented:
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
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now