List:Replication« Previous MessageNext Message »
From:Andrew Coder Date:March 12 2010 8:08am
Subject:Replication stuck
View as plain text  
Hello,

I have a problem replicating two servers.

Server A: Server version: 5.1.33-log FreeBSD port: mysql-server-5.1.33
Server B: Server version: 5.1.44-3-log (Debian)

Everything worked fine in the past. There are 3 DBs they replicate.
Replication is Master-Master.
Database structure is identical on both servers:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| database1            |
| database2            |
| database3            |
| mysql              |
+--------------------+
5 rows in set (0.00 sec)

But some time ago (I just can't find when - version upgrade? etc) they
stopped normally.
Server A is OK - when I start slave, it works like a charm - any changes
made on server B is replicated to this server.
But when I start slave on server B, it works max 5 minutes (the first 5
minutes I can make `show slave status\G`).
Then, if I try to make show slave status\G it stucks.

mysql> show processlist;
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
| Id  | User             | Host           | db   | Command     | Time    |
State                                                          |
Info                                                                 |
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
|  13 | debian-sys-maint | localhost      | NULL | Query       |    1559 |
checking permissions                                           | select
count(*) into @discard from `information_schema`.`PARTITIONS` |
|  42 | repl             | 10.9.0.1:62340 | NULL | Binlog Dump |    1520 |
Has sent all binlog to slave; waiting for binlog to be updated |
NULL                                                                 |
|  43 | root             | localhost      | NULL | Killed      |    1374 |
NULL                                                           | show slave
status                                                    |
|  48 | system user      |                | NULL | Connect     |    1481 |
Queueing master event to the relay log                         |
NULL                                                                 |
|  49 | system user      |                | NULL | Connect     | 2589939 |
Reading event from the relay log                               |
NULL                                                                 |
| 127 | root             | localhost      | NULL | Query       |     200 |
NULL                                                           | show slave
status                                                    |
| 128 | root             | localhost      | NULL | Query       |       0 |
NULL                                                           | show
processlist                                                     |
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
7 rows in set (0.00 sec)

show slave status is running for more than 200 seconds.

mysql> kill 127;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
| Id  | User             | Host           | db   | Command     | Time    |
State                                                          |
Info                                                                 |
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
|  13 | debian-sys-maint | localhost      | NULL | Query       |    1670 |
checking permissions                                           | select
count(*) into @discard from `information_schema`.`PARTITIONS` |
|  42 | repl             | 10.9.0.1:62340 | NULL | Binlog Dump |    1631 |
Has sent all binlog to slave; waiting for binlog to be updated |
NULL                                                                 |
|  43 | root             | localhost      | NULL | Killed      |    1485 |
NULL                                                           | show slave
status                                                    |
|  48 | system user      |                | NULL | Connect     |    1592 |
Queueing master event to the relay log                         |
NULL                                                                 |
|  49 | system user      |                | NULL | Connect     | 2590050 |
Reading event from the relay log                               |
NULL                                                                 |
| 127 | root             | localhost      | NULL | Killed      |     311 |
NULL                                                           | show slave
status                                                    |
| 128 | root             | localhost      | NULL | Query       |       0 |
NULL                                                           | show
processlist                                                     |
+-----+------------------+----------------+------+-------------+---------+----------------------------------------------------------------+----------------------------------------------------------------------+
7 rows in set (0.00 sec)

Its killed, but its still running like a zombie.

P.S. The filesystem is OK. I ran fsck several times. I have a lot of data on
same partition with MySQL server and its ok.

Any suggestions? Please.



Andrew Coder

Thread
Replication stuckAndrew Coder12 Mar
  • Re: Replication stuckKrishna Chandra Prajapati12 Mar
    • Re: Replication stuckAndrew Coder12 Mar
Re: Replication stuckAndrew Coder12 Mar