Anyone please give detailed documentation on MySQL Replication on Windows system. I replicated master to slave, but it is showing 'connecting to master'. Error number showing as 1045 and 2003 it's varying.
MySQL replication ‘connecting to master’
MySQLreplicationwindows
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.
You do not want writes to be replicated from A to B then A better be slave of B, master.
again, to complicate things you can use master-master with replication filters but let's not get there if you do-not wish to.
That said, writing on slave is sort of risky business where you're going to "accidentally" write on databases being replicated from master and that's not good! As long as you're making sure you're only writing to slave-only databases it should be fine. You should choose to create dedicated users for such activity. You might also want to wonder: - What about backups of master data? - What about backups of slave data?
just saying... but yea to answer your question go with master-slave.
Related Question
- MySQL Replication – Handling Checksum Mismatch in Slave
- Mysql – Slave_IO_Running: Connecting in Master-Slave Replication
- MySQL Replication With single Master DB to multiple Slave DBs
- MySQL Replication – Slave Can’t Connect to Master
- MySQL Replication ERROR 1130: error conecting to master
- Mysql – configure MySQL Replication as Master – Master – Slave
Best Answer
If you have validated the credentials you are using for replication and they work connecting through another client, your 1045 error may lie with the new default authentication plugin in MySQL8. When trying to connect with an insecure connection and an account created with the caching_sha2_password, it will continuously attempt the connection without being successful.
Try connecting using the same replication credentials from the slave to the master through a MySQL client. This will ensure your networking and credentials are both valid. If you cannot do this, check your network/firewall and your replication credentials.