Am trying to connect to a mysql master which is a server running LAMP. It replies with an error "cannot connect to mysql server". Have also tried to connect using mysql -h XXX -u -p from the slave server but still replies the same error. On the LAMP server have also checked the iptables and allowed mysql port to allow incoming connection but still have the same error. Have also allowed my slave public ip but the error is just the same.
Mysql – Cannot connect to thesql server error for master replication
MySQLreplication
Related Solutions
The basic problem stems from the Slave's IO Thread. Sometimes, it has the nasty habit of playing dumb and not properly checking or pinging the master.
MySQL 5.5's semisynchronous replication allows you to have the master time itself out and degrade back to asynchronous replication. Slaves running semisynchronous replication should have a more sensitive IO Thread now.
Concerning your particular situation, you are using MySQL 5.0.24 ??? That's a very old version. There are two bug reports (Bug1 and Bug2) discussing this.
It just dawned on me that you asked this question earlier and DTest answered it. Giving credit where credit is due, the bug reports came from his answer.
It would be nice to to be informed when the Public IP is being disabled from Slave access. That way, you could run STOP SLAVE;
on the slave in advance.
You also asked is there a way to come to know the master status from the slave, when this type of behavior occurs.
Here is a typical SHOW SLAVE STATUS\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.80.136
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019 <<------
Read_Master_Log_Pos: 277892198 <<------
Relay_Log_File: relay-bin.000058
Relay_Log_Pos: 37535484
Relay_Master_Log_File: mysql-bin.000019 <<------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 277892198 <<------
Relay_Log_Space: 277892637 <<------
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
Please note the following:
- Master_Log_File : Log file from Master whose latest SQL was last copied to relay logs
- Read_Master_Log_Pos : Log position within
Master_Log_File
whose latest SQL was last copied to relay logs - Relay_Master_Log_File : Log file from Master whose SQL was last executed from the relay logs
- Exec_Master_Log_Pos : Log position within
Relay_Master_Log_File
whose SQL was last executed from the relay logs
If none of these value are moving, that could mean the all SQL is processed or that the slave is currently processing an SQL statement in the SQL thread that came from position Exec_Master_Log_Pos
of the Master Log Relay_Master_Log_File
.
Now, look at Relay_Log_Space
. This number represents the sum total of all filesizes for all relay logs. If Slave_IO_Running
is Yes and Relay_Log_Space
is not changing, then go check the master by running SHOW MASTER STATUS;
. It should like something like this:
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000019 | 682563021 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
If the Master's (File,Position) of SHOW MASTER STATUS;
is beyond the Slave's (Master_Log_File,Read_Master_Log_Pos) of SHOW SLAVE STATUS\G
, then the IO Thread on the Slave is dead for intents and purposes, even if Slave_IO_Running is Yes.
Given the Following
+<------+
| ^
V |
M1 M2
| ^
V |
+-------+
and You Want the Following
+<------+
| ^
V |
S1<---M1 M2
| ^
V |
+-------+
Given Master IP is 10.1.2.30
Logon to the Slave and run the mysqldump like this
MAS_IP=10.1.2.30
SLV_IP=localhost
MYSQL_MAS_CONN="${MAS_IP} -uroot -ppassword"
MYSQL_SLV_CONN="${SLV_IP} -uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=1 --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database --all-databases"
mysql ${MYSQL_MAS_CONN} -e"FLUSH LOGS;"
echo "STOP SLAVE;" > MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysql ${MYSQL_MAS_CONN} < MySQLData.sql
If you have already performed this, let me address your comments.
But upon adding the third DB server and starting slave on it, it throws Cannot add or update a child row. foreign key constraint fails error.
Why would foreign key problems happen if you copied the data from a Master to a Slave? The auto_increment ids on the Slave simply did not match that of the Master. This is true even if you the Slave's Master is where you got the data from. Why?
auto_increment_increment is set to 2 in both masters. auto_increment_offset is set to 1 in Master 1 and 2 in Master 2. We have mostly innoDB tables and some MyISAM tables.
You have to make sure the Slave has the exact same auto_increment_increment
and auto_increment_offset
as the Master you are replicating from.
Goto the Master and run
SHOW VARIABLES LIKE 'auto_increment%';
Put those values into the Slave's my.cnf
and restart mysql.
REMEMBER : A Slave's auto_increment behavior needs to be identical to that of its master.
Give it a Try !!!
UPDATE 2013-04-06 17:26
Here is the problem: Let's say you have the setup
+<------+
| ^
V |
S1<---M1 M2
| ^
V |
+-------+
If you did the following:
- mysqldump data from M1
- load dump it S1
- enabled Replication from M1 to S1
- M1 and S1 have identical auto_increment_offset
- M1 and S1 have identical auto_increment_increment
there should never be foreign key violations.
What you may need to do is cleanup every server's view of the auto increment values
Here is something to try
- Set these values on M1's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1
- Set these values on M2's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1
- Set these values on S1's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1
Restart mysql on all three servers
Load Data like this:
- mysqldump from M1 to
- Run SET GLOBAL sql_log_bin = 0; on M1
- Load the mysqldump into M1
- Run SET GLOBAL sql_log_bin = 1; on M1
- Run SET GLOBAL sql_log_bin = 0; on M2
- Load the mysqldump into M2
- Run SET GLOBAL sql_log_bin = 1; on M2
- Load the mysqldump into S1
- Reset Replication amongst the threee server
If nothing breaks, you can then do this
Set these values on M2's /etc/my.cnf - auto_increment_increment = 10 - auto_increment_offset = 2
Restart mysql on M2
Everything should be clean now. From here, INSERTs should be properly handled.
Give it a Try !!1
Related Question
- Mysql – Several PC Cannot Connect To MySQL Server on Certain Time
- MySQL Replication – Using Non-Standard Port 3306
- Thesql ssl replication – fails while attempting to connect to master
- Mysql – Slave_IO_Running: Connecting in Master-Slave Replication
- Setting Up Master-Slave Replication in MySQL – Error Code 2003
- Mysql – error connecting to master ‘replica@192.168.57.10:3306’ mac-ubuntu
- Mysql – Troubleshooting “Can’t connect …” from MySQL slave to master
Best Answer
Using this mysql -h XXX -u -p you cannot connect to mysql remotely using root, if you want to connect with root remotely you have to configure it. For replication you need to create replication user on master and give it replication priviliges.
example;
mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'%.example.com';
start your slave and check what is the error on IO thread also check slave log for any errors.