Page 1 of 1

Slave connection to master hanging

Posted: Fri May 08, 2009 2:07 pm
by Benjamin
I've configured a slave server to connect to a master server, but it's not working.

I'm able to connect to the master server from the slave server via the command line, so I have ruled out a firewall issue.

Here's the output of show processlist;

Code: Select all

 
*************************** 2. row ***************************
     Id: 6067123
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 163141
  State: Connecting to master
   Info: NULL
*************************** 3. row ***************************
     Id: 6067124
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 25414
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 4. row ***************************
 
 
Now, if I restart the slave server, I'm able to view the following in the syslog:
May 8 15:01:41 adserver mysqld[4926]: 090508 15:01:41 [Note] Slave I/O thread killed while connecting to master
May 8 15:01:41 adserver mysqld[4926]: 090508 15:01:41 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000005', position 30173784
May 8 15:01:41 adserver mysqld[4926]: 090508 15:01:41 [Note] Error reading relay log event: slave SQL thread was killed
May 8 15:01:49 adserver mysqld[4926]: 090508 15:01:49 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005' at position 30173784, relay log './adserver-relay-bin.000003' position: 98
May 8 15:04:58 adserver mysqld[4926]: 090508 15:04:58 [ERROR] Slave I/O thread: error connecting to master 'pages_slave@xxx.xxx.xxx.xxx:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 110' errno: 2013 retry-time: 60 retries: 86400
So I'm not sure what the problem is. I followed the instructions for setting it up here:

http://www.howtoforge.com/mysql_database_replication

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 2:23 pm
by Eran
Did you assign the slave to the right bin-log and position?
What were the parameters of the CHANGE MASTER command you issued?

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 2:45 pm
by Benjamin
Yes, bin-log and position were set as output from the master.

Code: Select all

 
CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='pages_slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=30173784;
 

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 2:49 pm
by Eran
Could the master position changed before you started the slave on it? other than that I don't have a clue.. maybe vladsun would know

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 3:02 pm
by Benjamin
Yeah, the master position changes as soon as I unlock the tables on it. But as far as I know, the slave should start reading from the original position onward.

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 3:12 pm
by Eran
It could be a bug. What version of MySQL are you using? it's possible upgrading it would solve this
http://forums.mysql.com/read.php?26,109 ... msg-109677

Re: Slave connection to master hanging

Posted: Fri May 08, 2009 3:24 pm
by Benjamin
The slave:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
The master:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Re: Slave connection to master hanging

Posted: Mon May 11, 2009 1:00 pm
by Benjamin
Still having the problem. Can someone post a working my.cnf configuration for both the master and slave? This what I've got, with the syslog below. I'm wondering if I should use the hostname rather than IP?

Master my.cnf

Code: Select all

 
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = pages
expire_logs_days        = 10
max_binlog_size         = 300M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
 

Slave my.cnf

Code: Select all

 
server-id       =2
master-host     =xxx.xxx.xxx.xxx
master-user     =pages_slave
master-password =password
master-connect-retry =60
replicate-do-db =pages
 
syslog

Code: Select all

 
May 11 13:52:05 adserver mysqld[20491]: 090511 13:52:05 [Note] /usr/sbin/mysqld: Normal shutdown
May 11 13:54:04 adserver mysqld[20491]: 090511 13:54:04 [Note] Slave I/O thread killed while connecting to master
May 11 13:54:04 adserver mysqld[20491]: 090511 13:54:04 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000005', position 30173784
May 11 13:54:04 adserver mysqld[20491]: 090511 13:54:04 [Note] Error reading relay log event: slave SQL thread was killed
May 11 13:54:04 adserver mysqld[20491]: 090511 13:54:04  InnoDB: Starting shutdown...
May 11 13:54:06 adserver mysqld[20491]: 090511 13:54:06  InnoDB: Shutdown completed; log sequence number 0 154051721
May 11 13:54:06 adserver mysqld[20491]: 090511 13:54:06 [Note] /usr/sbin/mysqld: Shutdown complete
May 11 13:54:06 adserver mysqld_safe[19609]: ended
May 11 13:54:07 adserver mysqld_safe[19673]: started
May 11 13:54:07 adserver mysqld[19676]: 090511 13:54:07 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
May 11 13:54:07 adserver mysqld[19676]: 090511 13:54:07 [Warning] Changed limits: max_open_files: 1024  max_connections: 100  table_cache: 457
May 11 13:54:07 adserver mysqld[19676]: 090511 13:54:07  InnoDB: Started; log sequence number 0 154051721
May 11 13:54:08 adserver mysqld[19676]: 090511 13:54:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=adserver-relay-bin' to avoid this problem.
May 11 13:54:08 adserver mysqld[19676]: 090511 13:54:08 [Note] /usr/sbin/mysqld: ready for connections.
May 11 13:54:08 adserver mysqld[19676]: Version: '5.0.51a-3ubuntu5.4-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
May 11 13:54:08 adserver mysqld[19676]: 090511 13:54:08 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005' at position 30173784, relay log './adserver-relay-bin.000007' position: 98
May 11 13:54:08 adserver /etc/mysql/debian-start[19716]: Upgrading MySQL tables if necessary.
May 11 13:54:08 adserver /etc/mysql/debian-start[19720]: Looking for 'mysql' in: /usr/bin/mysql
May 11 13:54:08 adserver /etc/mysql/debian-start[19720]: Looking for 'mysqlcheck' in: /usr/bin/mysqlcheck
May 11 13:54:08 adserver /etc/mysql/debian-start[19720]: This installation of MySQL is already upgraded to 5.0.51a, use --force if you still need to run mysql_upgrade
May 11 13:54:08 adserver /etc/mysql/debian-start[19729]: Checking for insecure root accounts.
May 11 13:54:08 adserver /etc/mysql/debian-start[19733]: Checking for crashed MySQL tables.
May 11 13:57:17 adserver mysqld[19676]: 090511 13:57:17 [ERROR] Slave I/O thread: error connecting to master 'pages_slave@xxx.xxx.xxx.xxx:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 110'  errno: 2013  retry-time: 60  retries: 86400