Master-Slave replication in mysql error connecting to master 'replicator@xxx:3306' Can't connect to server on 'xxx' (111 "Connection refused")
0
votes
0
answers
187
views
Hi i am trying to make a connection between my Primary Server and backup server to have Master-slave mysql connection
this is my Primary server ip = xxx
and this is my Backup server ip = yyy
both of servers are centos 7 and in master (primary) server mariadb version is:
Ver 15.1 Distrib 10.6.9-MariaDB, for Linux (x86_64) using readline 5.1
and in backup (slave) server version is:
Ver 15.1 Distrib 10.6.15-MariaDB, for Linux (x86_64) using readline 5.1
in Master server i did this to my.cnf:
[mysqld]
bind-address=0.0.0.0
server-id = 1
binlog-do-db=x_test
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
#log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin.log
local-infile = 0
innodb_file_per_table
max_allowed_packet = 256M
max_connections = 2000
tmpdir =/var/lib/mysqltmp
query_cache_size = 0M
join_buffer_size = 512K
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 4
innodb_buffer_pool_size = 4G
log-error = /var/log/mysqld.log
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:15G
sql_mode=""
#server-id=1
#log_bin=mysql-bin
[mysqldump]
max_allowed_packet = 512M
#log-error=/var/log/mysql/mysql_error.log
[client-server]
#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
and i allowed my Backup ip in iptables of Primary server to have connection in port 3306
sudo iptables -L -n | grep 3306
ACCEPT tcp -- [my backup ip] 0.0.0.0/0 tcp dpt:3306
and even i can connect to my user replicator in my Primary server from my backup server with this command:
-h xxx -u replicator -p
and when i enter password it connects successfully so the problem is not port or network
on Master server i executed this command in mysql (logged in with root user):
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
and then i used this:
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000020 | 342 | x_test | |
+------------------+----------+---------------+------------------+
1 row in set (0.000 sec)
on my slave server (backup) i did this:
i changed /etc/my.cnf to:
[mysqld]
server-id = 2
replicate_do_db = x_test
then i restarted with this:
restart mysqld
then in mysql (with root user) i executed this command:
CHANGE MASTER TO
MASTER_HOST='xxx', # The IP of the Master server
MASTER_USER='replicator',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000020', # The file you noted earlier
MASTER_LOG_POS=342; # The position you noted earlier
after that:
START SLAVE;
then when i executed this:
SHOW SLAVE STATUS\G
the result was this:
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: xxx
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 342
Relay_Log_File: server-51-210-183-10-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: x_test
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: 342
Relay_Log_Space: 256
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: 2003
Last_IO_Error: error connecting to master 'replicator@xxx:3306' - retry-time: 60 maximum-retries: 100000 message: Can't connect to server on 'xxx' (111 "Connection refused")
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: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
how can i solve this problem?
thanks in advance
Asked by Matthew
(1 rep)
Nov 4, 2023, 09:33 AM