Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I followed this tutorial for master-slave replication: http://www.howtoforge.com/mysql%5Fmaster%5Fmaster%5Freplication

Basically, this is what I did.

  1. Uninstalled mysql on both servers. Installed mysql on both servers.
  2. Loaded the data onto both servers.
  3. Followed the replication procedure.
  4. The replication deleted my tables, then created them again.
  5. Then, the replication started inserting rows into the table. Then deleting them after they reach 3 million rows...then inserting rows again.

Why can't it just insert all the rows, and make it exactly the same as my master? WHy is it deleting stuff and inserting over and over?

show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 
                Master_User: 
                Master_Port: 
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000025
        Read_Master_Log_Pos: 694442541
             Relay_Log_File: mysqld-relay-bin.000039
              Relay_Log_Pos: 201628150
      Relay_Master_Log_File: mysql-bin.000018
           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: 201628013
            Relay_Log_Space: 8213564485
            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: 6343
1 row in set (0.00 sec)

ERROR:
No query specified


 show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000033
        Position: 540194
    Binlog_Do_DB: fal
Binlog_Ignore_DB:
1 row in set (0.01 sec)
share|improve this question
    
I don't see any evidence of this purported insertion/deletion cycle. Also, the contents of the global my.cnf and the output of SHOW SLAVE STATUS on the other server might be handy, too. –  womble Dec 17 '09 at 2:43

1 Answer 1

up vote 2 down vote accepted

In MySQL replication, the master writes out "binlog" files, detailing only queries which change replicated databases (UPDATE, DELETE, INSERT, CREATE TABLE..., not SELECT etc...).

Starting from identical copies of a database, the master writes binlog files, and the slave reads them, and executes all the changes: Thus replication occurs.

Your status messages indicate the slave's replica of the master is not completely up to date (6343s = 105 minutes out), and there is a queue of SQL statements to run, sitting in the binlog files:

master:

File: mysql-bin.000033
Position: 540194

slave:

Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 694442541
...
Exec_Master_Log_Pos: 201628013
....
Seconds_Behind_Master: 6343

I suspect that some DELETE/INSERT type operation has occured on the master, and the slave is just replicating the changes, albeit with nearly a couple of hours of delay.

To identify which queries it is running/about to run, you can use mysqlbinlog to view the contents of the (binary) binlog files.

Lockie

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.