MySQL Forums - Replication

Master freezes when replication starts to write a relay-log (no replies)

Edward Lee — Thu, 23 May 2013 09:12:19 +0000

Hello,

I have a strange replication problem with a master and its slave.
Every time I import a big file into the database which should be replicated by the slave the master freezes when it has finished writing the “mysql-bin”-file and starts to transfer data to the replication-slave.

The import I do with a simple “CREATE TABLE … SELECT * FROM …”

The import works fine, i.e. the data will be replicated and the master doesn’t freeze, if the data size doesn’t exceed 400-500 MB. The import also works fine if the replication is not running, but the option “log_bin” is on. If the replication slave is running the import fails in case of data that exceeds 400-500 MB in eight of ten cases.

The master and slave are on two different servers and connected over 1 Gbit network. I use the 5.1.69 MySQL-Version and as option-file I took the standard huge-my.ini. The table engine is MyISAM. The master has 8 Gb RAM and about 800GB free disk space. The slave has also 8 GB RAM and about 2 TB free disk space.
The servers are also not much loaded. The master has about 20-30 connections which are mostly SELECTs. The slave is used as a backup storage and there is no traffic except the replication process.

Does anybody know where the problem is?!

I would be very grateful if somebody could help me! Thank you very much in advance!

Last_Errno: 1032, Could not execute Update_rows event on table jiradb.userhistoryitem; (1 reply)

Raja Tadi — Fri, 17 May 2013 04:26:30 +0000

MySql Replication is not working below is the error, This is very urjent to resolved kindly help me.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: RCOLNX89194.noam.corp.frk.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 32156874
Relay_Log_File: rcovlnx0124-relay-bin.000005
Relay_Log_Pos: 168612
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: jiradb,crowd
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table jiradb.userhistoryitem; Ca n't find record in 'userhistoryitem', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event' s master log mysql-bin.000009, end_log_pos 4190003
Skip_Counter: 0
Exec_Master_Log_Pos: 4189741
Relay_Log_Space: 28139415
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table jiradb.userhistoryitem; Ca n't find record in 'userhistoryitem', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event' s master log mysql-bin.000009, end_log_pos 4190003
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f5095cc4-ad21-11e2-a67e-3440b5e37058
Master_Info_File: /usr/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 130514 03:01:56
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

Replication error -Lost connection to MySQL server during query on query 0 (1 reply)

neelima pulikanti — Sun, 12 May 2013 18:32:36 +0000

Hi,
Intermittently I keep seeing this below error on our production servers for replication error and do not know what could be causing this one. And also do not understand why do I see "query on query 0" in the error. I do not see this error register in the error log so no clue whats causing it. ( mysql version:5.0.91-community-log/statement based replication). Please let me know if any of you have seen this error and what to do to fix it. Thanks in advance.

ERROR: extractor: Got error reading packet from server: Lost connection to MySQL server during query on query 0

Why my slave lost data occationally? (3 replies)

LIAO CHIHYUAN_LIAO — Sun, 12 May 2013 18:34:00 +0000

Hi,
I have a question about replication and need some suggestions.
We have many master-slave database groups in use now.
But, our user complian that the data was lost in slave side sometimes.
There is no error message and no crash before the data lost.
Is there any suggestion can let me find out the root cause of this??
Thanks!!

Foreign Key errors on slave after import from another slave (6 replies)

Alexander Spitzer — Sat, 18 May 2013 05:58:53 +0000

We have a master and a couple slaves. I was trying to refresh a slave that had been turned of a while ago. I took a backup of another slave and expected that just importing/setting binlog pos would work fine. Unfortunately it just seems to end in FK errors.

My procedure was as follows:

1) I stopped replication to one of the slaves (so no writes)

2) mysqldump -u root -pxxxx --single-transaction dbname > dump.sql

2) I took note of the binlog filename and file_pos.
"show slave status > slave.txt"

3) The import to the new slave went without errors. (no FK errors etc)
mysql -u root dbname < dump.sql

4) I set the binfilename and binlog pos etc to match where the other slave was.

Upon starting the slave I just ended up with constant FK errors.

How is it possible to dump/restore a slave with FKs?

Thanks!

Duplicating large database (4 replies)

Ron Bergin — Sun, 05 May 2013 20:46:58 +0000

Please let me know if this is not the correct area to post my question.

I need to make several copies of a large database on a test server so that I can test and benchmark different configurations of partitioning and indexes.

The db I'm working with is about 800GB. I've loaded one copy of it so far using the standard dump/restore process. The loading of the data alone took 46 hours and I'm looking for a better/faster method.

Master updates Slave but not Master itself (5 replies)

Mannoj Kumar — Wed, 08 May 2013 01:51:41 +0000

I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color.

After reboot I see the master went for recovery then it came up and application started to use the Master normally but still the err msg exist in Front indication panel. But now the slave had stuck out due to primary key constraint for a table.

The problem I face is master has a table data until what exist in binlog of master. But slave has the table data what is not in master binlog. Below is the table details when compared to Master table and Slave table.

Last info in binlog timestamp is 2013-04-29 02:13:11
System shut down timestamp 2013-04-29 02:54

mysql> select * from audit where id=11298907;
------------------------------------------------------------
| id | ipaddress | dated | msisdn |
+----------+----------------+-------------------------------
|11298907 | 82.25.226.183 | 2013-04-29 02:13:11 | 998282821|
-----------------------------------------------------------
mysql> select * from audit where id > 11298907 limit 1;

-----------------------------------------------------------
| id | ipaddress | dated |msisdn |
+----------+----------------+------------------------------
|11298908 | 82.25.226.183 | 2013-04-29 04:31:13 | 992828111|
------------------------------------------------------------

On Slave
==========

mysql> select * from audit where id=11298907;
------------------------------------------------------------
| id | ipaddress | dated | msisdn |
+----------+----------------+-------------------------------
|11298907 | 82.25.226.183 | 2013-04-29 02:13:11 | 998282821|
-----------------------------------------------------------

mysql> select * from audit where id > 11298907 limit 1;

-----------------------------------------------------------
| id | ipaddress | dated |msisdn |
+--------+----------------+---------------------------------
|11298908 | 82.25.226.183 | 2013-04-29 02:13:12 | 762616173|
------------------------------------------------------------
Both the servers are ntp synched with GST. And they remain same after Master reboot too. It took almost 2 hrs to bring up Master Server. How come only slave can have the data that Master is not aware of? Please have someone come accross such a thing?

Replication for Oracle and MySQL - Now 100% Open Source (no replies)

Petri Virsunen — Mon, 29 Apr 2013 18:43:29 +0000

Oracle is the most powerful DBMS in the world. However, Oracle's expensive and complex replication makes it difficult to build highly available applications or move data in real-time to data warehouses and popular databases like MySQL.

In this webinar you will learn how Continuent Tungsten solves problems with Oracle replication at a fraction of the cost of other solutions and with less management overhead too – picture Oracle GoldenGate without the price tag! We will demo constructing a highly available site using Oracle-to-Oracle replication. We will then show you how to replicate data in real time from Oracle to MySQL as well as load a data warehouse.

You may obtain open source Tungsten Replicator at http://code.google.com/p/tungsten-replicator. Alternatively you may also obtain Continuent Tungsten, full-featured solution with 24/7 support, from Continuent at sales@continuent.com

Register for a session now by clicking a date below:

• Thursday, May 2, 2013 @ 10:00 AM PDT/11 AM EDT, https://www1.gotomeeting.com/register/256313281
• Tuesday, May 7, 2013 @ 10:00 AM EDT/15:00 BST/16:00 CEST, https://www1.gotomeeting.com/register/394798433

Once registered you will receive an email c

adding db to an established master - master configuration. (1 reply)

kevin foote — Wed, 01 May 2013 04:29:35 +0000

I have a working master-master mysql environment. I would like to add two (2) more databases into the replication process, (or for that mater any db I add to the server). With no downtime to the db would be my preference.

RHEL 5
MySQL 5.1.41 community

Here is the binlog section of my.cnf that I currently have configured, I use the innodb engine..

## BINLOG SETTINGS
log-bin = /var/log/mysql/mydb1-bin
log-bin-index = /var/log/mysql/mydb1-bin.index
binlog-do-db = proddb
binlog-ignore-db = mysql
expire_logs_days = 4
sync_binlog = 1

One of the two databases I need added to the replication process has data and is in a production state while the other is yet to be created.

Ideally I'm hoping for some tool or if anyone has a devised set of steps to just add stuff into the replication mix. I'm hoping to do this with no downtime to the databases in question.

Thanks for any pointers... Kevin

binlog overflow (4 replies)

Alax James — Fri, 03 May 2013 08:13:36 +0000

Huge seconds_behind_master (3 replies)

sudhakar s — Wed, 01 May 2013 04:32:19 +0000

Hi,

I am using mysql 5.5.16 entp with Master Slave replication.
on slave i observe a strange behaviour for seconds_behind_master.
Values fluctuate from huge 10K sec's to Zero in the next second immediately
when i check the slave status.

Any idea of this intermittent values in second_behind_master???

5.6.x and show slave status (1 reply)

Eugene Zheganin — Tue, 23 Apr 2013 13:45:36 +0000

Hi.

I'm using 5.6.x as a slave to 5.5.x (it's a replication chain).
MTS is off:

mysql> show variables like '%work%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| skip_networking | OFF |
| slave_parallel_workers | 0 |
+------------------------+-------+
2 rows in set (0.01 sec)

However, when I ussue a 'show slave status' command repeatedly, in the situation when the slave has a lag (for example lost of network connectivity or slave was used for backing up a large database and some tables were locked) the seconds_behind_master field shows different numbers. For example it can show hundreds of seconds and then thousands of seconds, switching between them. Bot numbers decrease until they become 0 at some moment. No question about zero, but why there are two numbers when MTS is off ? I tried to find an answer in Mysql documentation, but didn't succeed.

Thanks.

Binlog Dump(Writing to net) is very Slow (8kb/s) (2 replies)

ferluszt ferluszt — Fri, 03 May 2013 03:03:02 +0000

Hi,
following Situation:
I have set up a Master-Slave Replication. When I insert/update/delete Rows on the Master the Binlog is written very slow to the Slave (Traffic 8kb/s).

Show Processlist (on Master) says Binlog Dump Writing to net
on CPU usage is 1%

Show Processlist (on Slave) says
IO Thread: "Queueing master event to relay log" and
SQL Thread: "Reading event from the relay log" (30%) and "Has read all relay log; waiting for the slave I/O thread to update it" (70%)

Master: 5.5.29 on a Windows Server 2008
Slave : 5.5.29 on Ubuntu Server

Master Configuration:
[...]
server-id = 5
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sysdate_is_now = 1
innodb = FORCE
# BINARY LOGGING #
log_bin = "E:/mysql/data/mysql-bin"
expire_logs_days = 14
sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
binlog_format = STATEMENT
[...]

Slave Configuration:
[...]
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1
# REPLICATION # Slave
server-id = 10
skip_slave_start = 1
log_slave_updates = 1
relay_log = /var/lib/mysql/relay-bin
slave_net_timeout = 60
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
binlog_format = MIXED
[...]

Got Fatal Error 1236 From Master Reading Data from binary log possibility to loose data/? (12 replies)

Frwa Onto — Mon, 22 Apr 2013 13:18:06 +0000

I got this Fatal Error 1236 I have tried to run this command first stop slave; then change master to master_log_file='mysql-bin.000003',master_log_pos=335782050; then start slave but my slave is still not working. So some post say just move to next log file in my case will be mysql-bin.000004 and pos=0 wouldnt I loose any data here?

Replication with two servers (5 replies)

elguesmi elguesmi — Fri, 12 Apr 2013 02:52:23 +0000

hi all,

I have two servers mysql and I want to configuer a replication between this two servers
but i want to configurer :

------------------------------------------
server 1 : Master server 2 : Slave

and

server 2 Master server 1 : slave
-------------------------------------------

for the same Data base

I ask if this is possible or not

thx for help

Failed to open the relay log on Master (1 reply)

Imran Akbar — Wed, 10 Apr 2013 03:55:33 +0000

Hi,

After upgrading to MySQL 5.6.10 on my master and slave machines, I see this error in my master's logs:

[ERROR] Failed to open the relay log './mysqld-relay-bin.000001' (relay_log_pos 4).
[ERROR] Could not find target log file mentioned in relay log info in the index file './sjvs6-cerberus-relay-bin.index' during relay log initialization.
[ERROR] Failed to initialize the master info structure

According to the blog post here, this happens when the hostname changes:
https://blogs.oracle.com/trentlloyd/entry/watch_out_for_hostname_changes
I think it's not the hostname that changed, but the settings in my.cnf, because I now have relay logs with 3 different prefixes in /usr/lib/mysql, like:

mysqld-relay-bin.000001
mysql-relay-bin.000001
sjvs6-cerberus-relay-bin.000001

The weird thing is, the files in the error messages do exist, and mysql has permissions to them.

Any ideas how to fix this?
I don't mind resetting my slaves - I'd just like to get replication working again.

the master's my.cnf is below:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
innodb_buffer_pool_size=256M
max_allowed_packet=2M
slow_query_log=1
slow_query_log_file=/var/log/mysqld-slow.log
default-storage-engine=InnoDB
skip_secure_auth
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# Configure and enable replication
log_bin=mysql-bin
server_id=36
binlog-format=mixed
report-host=cerberus

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

thanks,
imran

Too much CPU spend on replication (3 replies)

Alax James — Thu, 04 Apr 2013 05:01:14 +0000

Hi,

I am using binlog for replication between two servers.

We have huge amount of data need to be provisoned.And we use row-based binlog format as each row has a date-type colunm.

So the provisioning generates lots of binlog which lets mysqld overload.
How can I solve this overload issue?

Thanks

Alex

Problem in update command master to master replication (3 replies)

Junior Maia Junior — Thu, 04 Apr 2013 16:50:00 +0000

Hello, I need help again, I had a problem these days and managed to solve here in the forum, I hope you can help me solve this second problem. Come on, the problem is this: I have two servers running mysql server 5.6 on windows, setup a Master to Master replication on these servers, running, syncing properly, I'm doing some tests, but on a test problem. I have the following environment:

Code Name Phone
1 John 1111111
2 Maria 2222222
3 José 3333333
4 Junior 4444444

Now imagine that I lose the communication between these two servers, give the user performs an update command on server 1: UPDATE table SET name = 's1 maria', phone = '2 a2a2a2a 'where code = 2;
hence, another user on server 2, run this command: UPDATE table SET name = 'Mary s2', phone = '2 b2b2b2b 'where code = 2;

I did this test, in my case the servers sync normally again, but give a select * from table where code = 2 on server 1 and it returns me the data set:

Code Name Phone
2 maria s1 2a2a2a2a

I run the same command on server 2 and it returns me the following data set:

Code Name Phone
2 maria s2 2b2b2b2b

That is, he did not run the update, someone answer me know how to solve this problem?

How to get single slave table back in sync with master? (3 replies)

F Kemp — Wed, 03 Apr 2013 02:49:19 +0000

Hi we have one table that was updated on the slave and now it is out of sync with the master table and some updates failed to replicate from the master. I don't mind skipping those errors but I am worried this will keep happening with this table. Is it possible to resync that one table by truncating it and repopulating it to match production? Or by dropping and recreating the table with all the current production records? Thanks!

Master-Master Replication connection loss (1 reply)

Junior Maia Junior — Sat, 30 Mar 2013 04:25:43 +0000

Good day, I am a developer, I am Brazilian, I am in the company I work a problem, we have a mother and a branch in different cities, developed a system to manage the two companies (matrix and branch), installed the database at headquarters and branch access the base via remote access through the Internet, the problem is that the communication was slow on branch, al decided to use replication master to master mysql 5.6 for windows, with a database at headquarters and in the other branch, replication worked normally, the problem is as true a loss of connection between the servers, they do not return to replicate again, and I have no way of knowing what was recorded in a database or another, I can not just back up, as it happens loss of connection the two banks are now different. I wonder how to solve this problem, thank you.

Tungsten University: Geographically Distributed Clusters (no replies)

Petri Virsunen — Tue, 26 Mar 2013 18:41:53 +0000

Do you need run multi-master MySQL database clusters across sites? In this webinar we will build on the multi-master capabilities of Tungsten, covered in our advanced replication topologies course, to help you build and manage systems that spread data across dozens or even hundreds of sites. We will cover topics such as large scale topologies, transaction filtering, security, and moving replication from simple data movement to application messaging.

Course Dates:

- April 11th @ 10 am PDT/1 pm EDT, https://www1.gotomeeting.com/register/821737497
- April 16th @ 10 am EDT/15:00 BST/16:00 CEST, https://www1.gotomeeting.com/register/175986064

Course Topics:

- Review of multi-master replication topologies
- Setting up Tungsten clusters for local site HA
- Linking clusters across sites
- Security for cross-site replication
- Handling local as well as site-wide failures
- Off-the-shelf filters to improvement multi-master replication
- Custom filter development to implement business rules for application messaging

Load data infile for slave (3 replies)

Mannoj Kumar — Thu, 04 Apr 2013 17:01:02 +0000

Hi Folks,

What if suppose load data infile is issued in master and I see that it gets replicated to slave IO with the infile SQL_LOAD data file listings first then it triggers its SQLs. My question is, when will the tmpdir files that are created as SQL_LOAD_MB-1-0 , SQL_LOAD_MB-2-0 .. SQL_LOAD_MB-12-0 will it get removed on SLAVE? Or is it always check manually if it had triggered the SQLs by matching the master and slave table counts and then remove them? Please advise.

Regards,
Mannoj

Am I cutting bandwidth by skipping a table? (1 reply)

Robert Gunther — Tue, 26 Mar 2013 07:29:16 +0000

Lets say I have a database with 2 tables.

Customers
Orders

The customers table does not change much. The orders table changes a lot, 20gigs a day.

I want to replicate only the customers table, so I exclude the orders table from the replication (on the slave). Will the slave still download all the BIN logs from the master and apply only the changes to the customers table?

Or is it smart enough to only get changes to the customers table?

binlog_do_db not working? (MySQL 5.1.68) (3 replies)

Italo Tasso — Sat, 23 Mar 2013 18:59:11 +0000

I used the options:

binlog-format=ROW
binlog-do-db=cobrafix

They seem to be set ok:

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 13174
Binlog_Do_DB: cobrafix
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show variables\G;
*************************** 10. row ***************************
Variable_name: binlog_format
Value: ROW

But on the binary log, I have:

[root@cobrafix mysql]# mysqlbinlog mysql-bin.000002

/*!*/;
# at 1962
#130323 9:00:01 server id 1 end_log_pos 2077 Query thread_id=159 exec_time=0 error_code=0
use `cobrafix`/*!*/;
SET TIMESTAMP=1364040001/*!*/;
DROP TABLE IF EXISTS cobrafix_crystal.temp_c_328
/*!*/;
# at 2077
#130323 9:00:01 server id 1 end_log_pos 6098 Query thread_id=159 exec_time=0 error_code=0
SET TIMESTAMP=1364040001/*!*/;
CREATE TABLE cobrafix_crystal.temp_c_328 (

Why is MySQL logging database cobrafix_crystal, since it is not on the binlog_do_db?

replication with redhat clustering (4 replies)

zafar malik — Wed, 03 Apr 2013 11:48:17 +0000

Hi,

Please help me if replication is possible with redhat clustering.

Right now we have just implemented redhat clustering (bcoz we want to keep engine as innodb to take advantage of foreign keys) but as a backup I want to set replication.

If anyone have any idea then please share its process/document.

Thanks,
Zafar

InnoDB: Over 1000 columns (1 reply)

F Kemp — Thu, 21 Mar 2013 15:20:01 +0000

We receive "errno: 139" when we try to create an InnoDB table with over 1000 columns. Is there any setting we can change to get this to work?

Right now I think our only two options are:

1) Use MyISAM storage engine
2) Change table to have < 1000 columns

Is there a third option that will let this number of columns work with InnoDB?

Thanks!

Promoting slave as master (1 reply)

Vinayak Lokhande — Wed, 13 Mar 2013 03:03:28 +0000

Hi All,

I am bit new to mysql replication and need help regarding switching mysql master slave nodes.

Scenario:

We have a master slave setup as below:

servera --> Master
serverb --> Slave

We had some maintenance on servera and for which we want to make serverb as master and servera as slave.

This is what we have performed up to now:

1) Confirmed that slave is in sync with master.
2) Stopped mysql on servera (master)
3) On serverb(slave) recorded the log position:
Master_Log_File: mysql-bin.000043
Read_Master_Log_Pos: 290832543

4) On serverb (Slave) performed the following:

RESET SLAVE;
RESET MASTER;
Restarted mysql;

With this, we the serverb is now acting as master. However, I am not sure on how to add back servera as slave now.

Do the following steps look good:

On servera, run the follwoing command:

CHANGE MASTER TO MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_HOST='serverb', MASTER_LOG_FILE='mysql-bin.000043', MASTER_LOG_POS=290832543;

Will this ensure that the replication will start from where it left?

On the new master (serverb), when I run show master status, i see the following:

File: mysql-bin.000003
Position: 35738417

I am wondering how the old bin-log file and the position can be used here.

It will be really great if someone can advise here..

thanks in advance!!!

Thanks,
Vin

Slave I/O thread killed while reading event (no replies)

Imran Akbar — Mon, 11 Mar 2013 18:33:54 +0000

Hi,
I have 3 slaves connected to my master database. The slaves had been running ok, but then replication stopped a few days ago, so I restarted the replication. Today they've stopped again, with this appearing in my MySQL logs on the slaves:

130309 2:01:01 [Note] Slave I/O thread killed while reading event
130309 2:01:01 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000758', position 166668826
130309 2:01:01 [Note] Error reading relay log event: slave SQL thread was killed

That's all I see related to the error - I don't see any errors related to a specific query. There are no errors in the master's log. Any ideas how I can diagnose the problem?

thanks,
imran

Why variable log-bin is OFF (no replies)

Alax James — Mon, 11 Mar 2013 02:37:10 +0000

My slave just sits there... (1 reply)

Robert Gunther — Sun, 10 Mar 2013 22:36:30 +0000

I've got a setup with three servers

Server A <-> Server B

A & B are both masters, transactions are replicated in realtime and it works well.

Server B -> Server C

Server C is a slave off Server B (server B has log_slave_updates enabled, so Server C gets all).

So that is the setup in a nutshell. If I do show slave status on Server B I can see that the read_master_log_pos changes continuously, every 1 or 2 seconds that value changes which leads me to belive replication is happening - it is reading data in realtime.

If I do show slave status on Server C, the value of Read_Master_Log_Pos does not change often. It might change maybe every 20 minutes or more (I do not know exactly how often).

The Slave_IO_Running & Slave_SQL_Running are Yes on all servers.

Beyond that I have checked the servername.err file in the MySQL data folder. The log shows information about Slave SQL thread initialized, starting and general status information. like server starting, shutdown etc. There is nothing in the logs to indicate any type of error or lag condition.

If I check the process list on Server C, it will show a state of 'Waiting for master to send event' and 'Slave has read all relay log; waiting for the slave I/O thread to update it'

Any clues how to find out what is wrong with Server C? It seems to think there is nothing new to replicate. In reality I have seen it lag by hours or days behind the masters. He is like a lazy employee, only working when he wants to!

Robert G.