List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 26 2010 7:00pm
Subject:RE: master-slave replication sync problems.
View as plain text  
ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost

show slave status\G

If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...

*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 10.10.10.45
                Master_User: slave
                Master_Port: 3306
                          ...
           Slave_IO_Running: No
          Slave_SQL_Running: Yes

start slave; show slave status\G

Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.10.10.41
                Master_User: slave
                Master_Port: 3306
                          ...
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: agis_core_2008
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1061
                 Last_Error: Error 'Duplicate key name 'id_operator'' on
query. ....

The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.

mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Repeat the above statements over and over until you see two YES rows.

*************************** 1. row ***************************

           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

> -----Original Message-----
> From: Norman Khine [mailto:norman@stripped] 
> Sent: Thursday, August 26, 2010 6:05 AM
> To: mysql@stripped
> Subject: master-slave replication sync problems.
> 
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
> 
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:
> 
> [MASTER]
> mysql> show master status;
> 
> +------------------+----------+----------------------+--------
> ------------------+
> | mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
> mysql,information_schema |
> +------------------+----------+----------------------+--------
> ------------------+
> 1 row in set (0.00 sec)
> 
> [SLAVE]
> mysql> stop slave;
> mysql> change master to MASTER_HOST='master.domain.com',
> MASTER_USER='replicator', MASTER_PASSWORD='xxx',
> MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=13301215;
> mysql> start slave;
> 
> is this correct or is there a better way to do this?
> 
> thanks

Thread
master-slave replication sync problems.Norman Khine26 Aug
  • Re: master-slave replication sync problems.Ananda Kumar26 Aug
  • Re: master-slave replication sync problems.a.smith26 Aug
    • Re: master-slave replication sync problems.Ananda Kumar26 Aug
    • Re: master-slave replication sync problems.Norman Khine26 Aug
      • Re: master-slave replication sync problems.Ananda Kumar26 Aug
      • Re: master-slave replication sync problems.a.smith26 Aug
        • Re: master-slave replication sync problems.Ananda Kumar26 Aug
          • Re: master-slave replication sync problems.a.smith26 Aug
  • Re: master-slave replication sync problems.jitendra ranjan26 Aug
    • Re: master-slave replication sync problems.a.smith26 Aug
  • RE: master-slave replication sync problems.Daevid Vincent26 Aug
    • Re: master-slave replication sync problems.MySQL)27 Aug
  • Re: master-slave replication sync problems.Todd Lyons1 Sep