On one of the node I setup master with following:
CHANGE MASTER TO MASTER_HOST = '192.168.1.11', MASTER_USER = 'repl_user', MASTER_PASSWORD = 'repl123', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 842;
this worked in the past, but now I am getting this strange error:
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.11
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 842
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
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: 842
Relay_Log_Space: 249
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1698
Last_IO_Error: error connecting to master 'repl_user@192.168.1.11:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'repl_user'@'10.0.0.5'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
The Slave status shows:
Last_IO_Error: error connecting to master 'repl_user@192.168.1.11:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'repl_user'@'10.0.0.5'
but where is this 'repl_user'@'10.0.0.5' coming from ?
I have this user setup as :
MariaDB [(none)]> select user,host from mysql.user where user = 'repl_user';
+-----------+------+
| user | host |
+-----------+------+
| repl_user | % |
+-----------+------+
I do not have any node on any subnet 10.0.x.x .
The reason of the error is straightforward: the primary host sees the replica connection as coming from 10.0.0.5.
Why this happens, I can't say. One distinct possibility is that your replica is on a subnet of its own - say, 192.168.7.0/24 - that the master; and the connection is made through a NAT (or a VPN of some kind):
192.168.77.77 (your PC) --- 192.168.77.1 (local gateway)
|
|
10.0.0.5 (remote gateway)
|
192.168.1.11
To check this out, on the replica machine, run:
traceroute -n 192.168.1.11
and see what that tells you.
Verify that the replica machine only has the one interface 192.168.1.x and there is no 10.0.0.x interface lurking.
Then, verify the routing tables to ensure they're correct (I'd be surprised if they weren't, but I have seen things happen).
If nothing avails, I must conclude that technically the server is on the same network segment as the replica, as you say, but actually it is not: it could be, for example, inside a virtual machine environment, or protected by a firewall. An "address cloning" setup like this is very unusual, and prone to problems, but not impossible:
192.168.1.77 (replica) ----- 192.168.1.11 (VM Host)
|
10.0.0.5 (internal dispatcher)
|
10.0.0.4 (MariaDB primary)
192.168.1.77 (replica) ----- 192.168.1.11 (Firewall external)
|
10.0.0.5 (Firewall internal)
|
192.168.1.11 (MariaDB primary)
These kind of setups might even be detectable (or not) only with tools like hping and careful tracerouting (so-called "firewalking"), assuming no great care has been taken to hide the fact that the server addressing isn't what it seems to be. For example, TTL measurements to port 3306 of 192.168.1.11 might be off and reveal that the packets actually passed through one or two extra nodes that shouldn't have been there.
All this, however, could be more easily discovered by asking the NetOps guys, and is just a curiosity - what matters to you is making the replica work, and for that, as things stand, you need to authorize 10.0.0.5.
===
For example, my own PC here is 192.168.100.76. Were I to connect to a primary on 192.168.200.233, that would happen through a NATting gateway for the 192.168.200.x subnet, which is 192.168.100.1 (on my side) and 192.168.200.1 (on the other side). So, the primary would see my replica request coming from 192.168.200.1.
And that would be the address I'd have to authorize if I wanted the replica to work.
By the same token, you need to authorize replica from 10.0.0.5.
And yes, that means that any PC in my 192.168.100.x network would then be authorized to connect to that master. To organize things differently, the network routing must be changed.
I could for example ask for a VPN connection so that my PC receives an address in the 192.168.200.x range. Packets would be encapsulated, sent to 192.168.100.1 (obviously), arrive at 192.168.200.1 on port 1194, and there "reinflated" and given source address 192.168.200.137 maybe. Then the primary would see "me" as 192.168.200.137 and I would need to authorize that address, reserved for my personal use.