Mysql – Master – master replication not working

MySQLreplicationwamp

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        |
 +------------------+---------------+

Best Answer

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.