MySQL主从复制连接失败 [英] MySQL master-slave replication connection failure
问题描述
我正在尝试在运行 Scientific Linux 6.10 (Carbon) 版的计算机上的 MySQL8.0 安装上设置一个非常简单的数据库的复制.从机在 CentOS Linux 7.8.2003 (Core) 版本上有 Mysql 5.7.18.
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).
从计算机无法直接访问主计算机，因此我使用 ssh 端口转发来解决这个问题.下面的mysql命令在从机上工作:
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 |
+----+--------------------+---------------------+-----------+
但是，slave 无法使用相同的端口连接到 master:
However, the slave cannot connect to the master using the same port:
mysql> show slave status\G
*************************** 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 'repl_user@127.0.0.1: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)
master上的my.cnf:
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
slave 上的 my.cnf:
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 repl_user@127.0.0.1 |
+------------------------------------------------------------------+
| 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)
推荐答案
您需要通过 SSH tunel 实现 MySQL 复制.这是一个 不错-to-read 关于如何实现这一目标的指南.它指出了复制所需的步骤、如何在两台服务器上设置用户、配置 MySQL 等.
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.
要创建 SSH 隧道，请使用以下命令.(奴隶)
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
编辑
或者您可以尝试以下更改/添加:
Or you could try the following changes/additions:
像这样创建一个repl_user:
mysql> CREATE USER repl_user@X.X.X.X; // where X.X.X.x is the external ip of the master server
授予复制权限
mysql > GRANT REPLICATION SLAVE ON *.* TO repl_user@X.X.X.X IDENTIFIED BY 'mysecurepass';
要在防火墙规则中允许端口 3306，您需要这样的东西.(你可以先试试这个...)
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:
这篇关于MySQL主从复制连接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
