List:General Discussion« Previous MessageNext Message »
From:Igor Shevtsov Date:December 6 2012 10:26pm
Subject:Re: MySQL Replication Error
View as plain text  
Hi Néstor,
You might want to put those lines into your my.cnf under replication 
section and restart the slave:
replicate-wild-ignore-table=dbname%.watchdog%

and may be this as well:
replicate-wild-ignore-table=dbname%.cache%

Do you really need to replicate cache and session tables?

Cheers,
Igor


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