Sample Header Ad - 728x90

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