date:20121206

Re: MySQL Replication Error

2012-12-06 Thread Néstor

I added those line to the slave's my.cnf and mysql would not start
replicate-wild-ignore-table=parallax%.watchdog%
replicate-wild-ignore-table=parallax%.cache%

Nestor


On Thu, Dec 6, 2012 at 4:39 PM, Néstor  wrote:

> HI Igor,
>
> Are you saying to add these lines to the my.cnf file:
> replicate-wild-ignore-table=dbname%.watchdog%
> replicate-wild-ignore-table=dbname%.cache%
>
> Correct?
>
> Thanks,
>
> Nestor :-)
>
>
>
> On Thu, Dec 6, 2012 at 3:07 PM, Igor Shevtsov wrote:
>
>> 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:
>>> tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
>>>   ESTABLISHED 30145/mysqld
>>> SERVER2:
>>> tcp0  0 wahooesc.sdcwa.org:52131www.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.02
>>>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_Ciph

Re: MySQL Replication Error

2012-12-06 Thread Igor Shevtsov


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:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
  ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.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.02
   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.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21: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

Re: MySQL Replication Error

2012-12-06 Thread Néstor

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:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
 ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.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.02
  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.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21: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

Re: MySQL Replication Error

2012-12-06 Thread Igor Shevtsov


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:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
  ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.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.02
   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.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21: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 y

Re: MySQL Replication Error

Re: MySQL Replication Error

Re: MySQL Replication Error

Re: MySQL Replication Error

4 matches

Site Navigation

Mail list logo

Footer information