CentOS Question posted in
The official documentation can be found here.

MySQL master-slave replication connection failure – CentOS

I’m trying to setup replication of a very simple database located on a MySQL8.0 installation on a computer running Scientific Linux release 6.10 (Carbon). The slave computer has Mysql 5.7.18 on a CentOS Linux release 7.8.2003 (Core).

The slave computer has no direct access to the master computer, so I use ssh port forwarding to get around that. The mysql command below on the slave computer works:

 ssh -g -R 8899:127.0.0.1:3306 username@intermediate_computer
 ssh -g -R 8899:127.0.0.1:8899 username@slave_computer
 mysql -u repl_user -p -h 127.0.0.1 -P 8899
 connect testdb;
mysql> select * from table1;
+----+--------------------+---------------------+-----------+
| id | name               | tag                 | reference |
+----+--------------------+---------------------+-----------+
|  1 | apple              | good                |         4 |
|  2 | watermelon         | garden good         |         5 |
|  3 | early girl tomatos | red,excellent       |         8 |
|  4 | golden boy tomatos | yellow,excellent    |         8 |
|  5 | green beans        | green,tasty         |         7 |
|  6 | kale               | green,mildly bitter |         3 |
+----+--------------------+---------------------+-----------+

However, the slave cannot connect to the master using the same port:

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 8899
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: cdms-db-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          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: 0
              Relay_Log_Space: 154
              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: 2003
                Last_IO_Error: error connecting to master '[email protected]:8899' - retry-time: 60  retries: 28
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 201031 10:17:26
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: a1932c42-9d74-11e7-ba23-0015175696ac:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

my.cnf on the master:

[mysqld]
default_authentication_plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
relay_log_space_limit=8000000000
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
#log_warnings = 2

# Information added by to set this as a master for replication
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
binlog_format=row
gtid-mode=ON
enforce-gtid-consistency=ON

my.cnf on the slave:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#skip-grant-tables

max_allowed_packet = 64M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


# Information added to set this as a slave for test replication 
gtid_mode=ON
enforce-gtid-consistency=ON
server-id=3
skip-slave-start=ON
replicate-do-db=testdb
log-slave-updates=ON

Any help understanding why the slave appears to not talk to the master would be greatly appreciated. I spent a couple hours looking for all similar questions and none seem to be close enough to help.

Here is the grant status on the master database:

mysq>show grants;
+------------------------------------------------------------------+
| Grants for [email protected]                                   |
+------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl_user`@`127.0.0.1`        |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `repl_user`@`127.0.0.1` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
Tags:

3

Answers


  1. 0

    I’m not sure about it, but either I don’t understand you setting or this :

    ssh -g -R 8899:127.0.0.1:3306 username@intermediate_computer
ssh -g -R 8899:127.0.0.1:8899 username@slave_computer

    mean you try to bind the same internal port 8899 on two different output 3306 and 8899. This seem problematic to me, have you tried 2 different port ?

    Login or Signup to reply.
  2. 0

    You need to implement MySQL replication over SSH tunel. Here is a nice-to-read guide on how to achieve that. It points out the steps needed for the replication, how to set up users on both servers, configure MySQL and more.

    To create a SSH tunnel use the following command. (slave)

    ssh -L 8899:127.0.0.1:3306 ssh_tunnel_user@master_server_ip_address -f -N

    EDIT

    Or you could try the following changes/additions:

    Create a repl_user like this:

    mysql> CREATE USER [email protected]; // where X.X.X.x is the external ip of the master server

    Grant replication permission

    mysql > GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'mysecurepass';

    To allow port 3306 in your firewall rules you would need something like this. (You could try this first…)

    # firewall-cmd --new-zone=mysql_replication_access --permanent
# firewall-cmd --reload
# firewall-cmd --get-zones // check that your zone exists
# firewall-cmd --zone=mysql_replication_access --add-source=Y.Y.Y.Y/Y --permanent // Y.Y.Y.Y/Y client server ip address or range
# firewall-cmd --zone=mysql_replication_access --add-port=3306/tcp  --permanent
# firewall-cmd --reload

    But in your case you also need port forwarding which is created by your firewall rules for your specific zone as listed above.

    So i think something like the following firewall rule, is most probably needed.

    # firewall-cmd --permanent –zone=mysql_replication_access --add-rich-rule='rule family="ipv4" source address="X.X.X.X" forward-port to-addr="Y.Y.Y.Y" to-port="8899" protocol="tcp" port="3306"'

    Some more ideas you could get from:

    Login or Signup to reply.
  3. 0

    According to my experience with working with mysql replication I can tell you that slave mysql version must be the same as master or higher. As I can see you’re using 8.0 on master and 5.7 on slave one.

    In your case, the master may pass binlogs that slave cannot understand.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search