Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I configured two WAMP servers running on different machines for master-master replication. I am using a linksys router for the network. I created a wireless network, but one of the server boxes doesn't have a WIFI so I used cable to connect it to the router. After the configuration, everything seem to be okay, but the servers don't seem to connect with each other as slaves. Below is the server status.

Variable               Value
Slave_IO_State:        Connecting to master
Master_Host:           192.168.1.100
Master_User:           master
Master_Port:           3306
Connect_Retry:         60
Master_Log_File:       mysql-bin.000001
Read_Master_Log_Pos:   95849
Relay_Log_File:        Chibuzo-PC-relay-bin.000001
Relay_Log_Pos :        4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running:      Connecting
Slave_SQL_Running:     Yes
Replicate_Do_DB:       ticket
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:    95849
Relay_Log_Space:    107
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:  

Running the following query on server 1 with IP address 192.168.1.102, I got the following result

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';

+-----------+-------------------------+
| User      | host                    |
+-----------+-------------------------+
| root      | localhost               |
| root      | 127.0.0.1               |
| root      | ::1                     |
| master    | 192.168.1.100           |
+-----------+-------------------------+

For server 2 with IP 192.168.1.100

 +-----------+-------------------------+
 | User      | host                    |
 +-----------+-------------------------+
 | root      | localhost               |
 | root      | 127.0.0.1               |
 | root      | ::1                     |
 | master    | 192.168.1.102           |
 +-----------+-------------------------+

Here is part of the error log on 192.168.1.102

121230  1:23:29 [Note] Plugin 'FEDERATED' is disabled.
121230  1:23:29 InnoDB: The InnoDB memory heap is disabled
121230  1:23:29 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121230  1:23:29 InnoDB: Compressed tables use zlib 1.2.3
121230  1:23:29 InnoDB: Initializing buffer pool, size = 16.0M
121230  1:23:29 InnoDB: Completed initialization of buffer pool
121230  1:23:29 InnoDB: highest supported file format is Barracuda.
121230  1:23:30 InnoDB: Waiting for the background threads to start
121230  1:23:31 InnoDB: 1.1.8 started; log sequence number 5483034
121230  1:23:31 [Note] Server hostname (bind-address): '(null)'; port: 3306
121230  1:23:31 [Note]   - '(null)' resolves to '::';
121230  1:23:31 [Note]   - '(null)' resolves to '0.0.0.0';
121230  1:23:31 [Note] Server socket created on IP: '0.0.0.0'.
121230  1:23:31 [Note] Event Scheduler: Loaded 0 events
121230  1:23:31 [Note] wampmysqld: ready for connections.
Version: '5.5.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
121230  2:33:12 [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=Chibuzo-PC-relay-bin' to avoid this problem.
121230  2:33:12 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.100', master_port='3306', master_log_file='mysql-bin.000001', master_log_pos='95849'.
121230  2:38:50 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 95849, relay log '.\Chibuzo-PC-relay-bin.000001' position: 4
121230  2:39:11 [ERROR] Slave I/O: error connecting to master 'master@192.168.1.100:3306' - retry-time: 60  retries: 86400, Error_code: 2003
130101  2:56:57 [Note] wampmysqld: Normal shutdown

130101  2:56:57 [Note] Event Scheduler: Purging the queue. 0 events
130101  2:56:58 [Note] Error reading relay log event: slave SQL thread was killed
130101  2:56:58 [Note] Slave I/O thread killed while connecting to master
130101  2:56:58 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 95849
130101  2:57:00 [Warning] wampmysqld: Forcing close of thread 1011  user: 'root'

SHOW binary LOGS;

On 192.168.1.102

 +------------------+---------------+
 | Log_name         | File_size     |
 +------------------+---------------+
 | mysql-bin.000001 |  1455         |
 | mysql-bin.000002 |   236         |
 | mysql-bin.000003 |  6417         |
 | mysql-bin.000004 | 12755         |
 +-----------+----------------------+

On 192.168.1.100

 +------------------+---------------+
 | Log_name         | File_size     |
 +------------------+---------------+
 | mysql-bin.000001 |  96096        |
 | mysql-bin.000002 |  91695        |
 | mysql-bin.000003 |  48773        |
 +------------------+---------------+
share|improve this question
1  
Seems like a network connection problem. Perhaps firewall is blocking connections? Are you able to connect to MySQL from one server to another? –  redguy Dec 31 '12 at 12:19
    
@redguy With the same network, I can use a web application on either WAMP servers from other workstations on the network. –  Chibuzo Dec 31 '12 at 14:53
    
Whenever you start replication and get Slave_IO_Running: Connecting and Slave_SQL_Running: Yes, that quickly indicates a connectivity issue. The only way to know what is to check the error log. Please run SELECT user,host FROM mysql.user WHERE Repl_slave_priv='Y'; and post it in the question (not as a comment). –  RolandoMySQLDBA Dec 31 '12 at 18:50
    
Since you are using WAMP, MySQL for Windows may not allow you to print or copy the error log. Please run net stop mysql, copy and paste the last 20 lines of the error log into the question (not as a comment), then net start mysql . –  RolandoMySQLDBA Dec 31 '12 at 18:52
    
@RolandoMySQLDBA I've edited the question as you suggested. Thank you. –  Chibuzo Jan 1 '13 at 2:04
show 6 more comments

3 Answers 3

In your Error Log :

[ERROR] Slave I/O: error connecting to master 'master@192.168.1.100:3306' - retry-time: 60  retries: 86400, Error_code: 2003

Error_code: 2003 : The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.

Try to connect from one server to the other with your "master" account :

On 192.168.1.100 :

mysql -h 192.168.1.102 -u master -p

On 192.168.1.102 :

mysql -h 192.168.1.100 -u master -p

Check, your firewall, try to ping and other stuff in relation to network communication...

Max.

share|improve this answer
    
Thanks for the answer, I tried connecting as you suggested, I got the error 'ERROR 2003'. MySQL server is running and the port is the usual 3306. I checked my firewall, but WAMP/MySQL wasn't among the list. –  Chibuzo Jan 2 '13 at 12:24
    
From Bill Karwin, "90% of the time, this error is due either to the MySQL Server not running, or else firewall configuration on the Windows server blocking access on port 3306 (or whatever port your MySQL instance is configured to use)." –  Seksi DBA Jan 2 '13 at 13:56
    
Thank you. Please how can I unblock access on port 3306? –  Chibuzo Jan 2 '13 at 14:10
add comment

I have a suggestion

On the Slave run the following:

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=95849;
START SLAVE;
SELECT SLEEP(15);
SHOW SLAVE STATUS\G

This will reset all relay logs and attempt to connect from the log file and position from the error message. If you get Slave_IO_Running: Connecting, there are two scenarios to investigate

SCENARIO #1 : The binlog mysql-bin.000001 on 192.168.1.100 might be corrupt.

You could verify this by running

mysqlbinlog --start-position=95849 mysql-bin.000001 > binlog_stuff.txt

Examine binlog_stuff.txt to see if contains clean statements or errors from the the binlog dump.

SCENARIO #2 : The binlog mysql-bin.000001 on 192.168.1.100 is from a newer version of MySQL

  • Replication from MySQL 5.0 to MySQL 5.1 is allowed
  • Replication from MySQL 5.0 to MySQL 5.5 is allowed
  • Replication from MySQL 5.1 to MySQL 5.5 is allowed
  • Replication from MySQL 5.5 to MySQL 5.1 is not allowed
  • Replication from MySQL 5.5 to MySQL 5.0 is not allowed
  • Replication from MySQL 5.1 to MySQL 5.0 is not allowed
  • I have discussed this before

ALTERNATIVE

You could see if skipping to the next binlog would help

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=107;
START SLAVE;
SELECT SLEEP(15);
SHOW SLAVE STATUS\G

If you get the same errors, you can undo it with

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=95849;
START SLAVE;
SELECT SLEEP(15);
SHOW SLAVE STATUS\G
share|improve this answer
    
Thank you, I'll do that now, but just to remind you, it's a master-master replication. I'll give you feed back soon. –  Chibuzo Jan 2 '13 at 13:49
add comment

Sometimes your master server blocks any incomming connections after many unsuccessful attempts. It happens often after testing for other connection related issues.

Anyway try flush-hosts on your master server:

mysqladmin -u root -p flush-hosts

then restart slave:

service mysql restart

and check if connected:

tail -30f /var/log/mysql/error.log
share|improve this answer
add comment

protected by Community Feb 13 at 1:12

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.