List:General Discussion« Previous MessageNext Message »
From:Igor Shevtsov Date:December 6 2012 11:07pm
Subject:Re: MySQL Replication Error
View as plain text  
In regards to the second part of your email.
You don't have errors in your mysql.err log. Those are notes saying that 
you started IO and SQL replication threads after you skipped an 
replication error and run start slave command.

I wouldn't run  SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; command either as 
it makes your tables inconsistent.
I'd rather deleted offending row on the slave and started replication. 
In this case insert would've succeed and tables would become consistent 
again.





On 06/12/12 21:59, Néstor wrote:
> I spoke to soon!!!
> Here is the error about 1.5 hours after replication  has started.
>       121205 16:39:51 [ERROR] Slave: Error 'Duplicate entry '3468897' for
> key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog
>      (uid, type, message, variables, severity, link, location, referer,
> hostname, timestamp)
>      VALUES
>      (0, 'page not found', 'images/internet_explorer/borderTopLeft.png',
> 'N;', 4, '','
> http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '',
> '10.20.141.24', 1354754352)', Error_code: 1062
> 121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We
> stopped at log 'mysql-bin.000289' position 86451409
>
> --------------------------------------------
> Sorry for the long message, below are the steps use to create my
> replication.
>
>
> When I created the replication, I followed the steps here :
>
> http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
>       I am on a red hat server.
> -------
> I set my firewall to accept info from server1 on server2
> SERVER1:
> tcp        0      0 wahoo.sdcwa.org:mysql       wahooesc.sdcwa.org:52131
>   ESTABLISHED 30145/mysqld
> SERVER2:
> tcp        0      0 wahooesc.sdcwa.org:52131    www.sdcwa.org:mysql
> ESTABLISHED 30875/mysqld
> -------------
>
> I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
>
> I have also done these steps where in SERVER1
> FLUSH TABLES WITH READ LOCK;
> SHOW MASTER STATUS;
> +------------------+----------+--------------+------------------+
> | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> +------------------+----------+--------------+------------------+
> | mysql-bin.000289 | 42394063 |              |                  |
> +------------------+----------+--------------+------------------+
>
> Then Dump SERVER1 database then you unlock SERVER1 database
> and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK;
>
> I proceed to STOP SLAVE then add the database to SERVER2 then
> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289',
> MASTER_LOG_POS=42394063; to synch with the SERVER1
> Then I start SERVER2 and the output of  slave status is:
>
> mysql> SHOW SLAVE STATUS\G
> *************************** 1. row ***************************
>               Slave_IO_State: Waiting for master to send event
>                  Master_Host: 10.168.1.21
>                  Master_User: sdcwa_slave
>                  Master_Port: 3306
>                Connect_Retry: 60
>              Master_Log_File: mysql-bin.000289
>          Read_Master_Log_Pos: 55848766
>               Relay_Log_File: mysql-relay-bin.000002
>                Relay_Log_Pos: 13454938
>        Relay_Master_Log_File: mysql-bin.000289
>             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: 55848766
>              Relay_Log_Space: 13454938
>              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)
> -----------------------
>
> I get the folllowing on my mysql.err:
> 121205 15:09:56 [Note] Slave SQL thread initialized, starting replication
> in log 'mysql-bin.000289' at position 42394063, relay log
> '/var/log/mysql/mysql-relay-bin.000001' position: 4
> 121205 15:09:56 [Note] Slave I/O thread: connected to master '
> sdcwa_slave@stripped:3306',  replication started in log
> 'mysql-bin.000289' at position 42394063
>
>
> Everything is GOOD, for a little while and then I get an error on mysql.err:
>
> ---------------------
>
> Now if I do the SKIP FLAG many times, I will error after error...
> I do not see how replication works so well for others if I am using the
> steps in the link
> on top of this message.
>
> THANKS!!!
>
>
> On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui <manuel@stripped> wrote:
>
>>
>> 2012/11/30 Néstor <rotsen@stripped>
>>
>>> I am trying to set up mysql replication on 2 systems but Once I get it
>>> going, I get the following an error 1062,
>>>
>> <skip>
>>
>>> I have re-installed the database on the slave also to see if this fixes
>>> the
>>> problem
>>> but after a few minutes I get the same error.  I have repeated the
>>> replication from the
>>> beginning but I get the same problem after a while.
>>>
>>> Does anyone know a way to fix this?
>>>
>>>
>> Hello,
>>
>> How did you build the slave from the master? How did you decide in which
>> position the slave should start replicating from?
>> You might want to try pt-table-checksum (
>> http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html) to
>> see what else do you have broken.
>>
>> Manuel.
>>
>>
>> --
>> Manuel Aróstegui
>> Systems Team
>> tuenti.com
>>

Thread
MySQL Replication ErrorNéstor30 Nov
  • RE: MySQL Replication ErrorRick James30 Nov
    • Re: MySQL Replication ErrorReindl Harald30 Nov
      • Re: MySQL Replication Errordivesh kamra5 Dec
        • Re: MySQL Replication ErrorJohan De Meersman5 Dec
          • Re: MySQL Replication ErrorNéstor6 Dec
  • Re: MySQL Replication ErrorManuel Arostegui5 Dec
    • Re: MySQL Replication ErrorNéstor6 Dec
      • Re: MySQL Replication ErrorIgor Shevtsov6 Dec
      • Re: MySQL Replication ErrorIgor Shevtsov6 Dec
      • Re: MySQL Replication ErrorJohan De Meersman10 Dec
Re: MySQL Replication ErrorNéstor7 Dec
  • Re: MySQL Replication ErrorIgor Shevtsov7 Dec