From: Igor Shevtsov Date: December 6 2012 10:26pm Subject: Re: MySQL Replication Error List-Archive: http://lists.mysql.com/mysql/228777 Message-Id: <50C11B7B.3030201@googlemail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------070804040006030800040406" --------------070804040006030800040406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 wrote: > >> >> 2012/11/30 Néstor >> >>> I am trying to set up mysql replication on 2 systems but Once I get it >>> going, I get the following an error 1062, >>> >> >> >>> 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 >> --------------070804040006030800040406--