我正试图在运行Scientific Linux 6.10版(Carbon(的计算机上安装MySQL8.0,设置一个非常简单的数据库的复制。从属计算机在CentOS Linux 7.8.2003版(Core(上具有Mysql 5.7.18。
从计算机无法直接访问主计算机,所以我使用ssh端口转发来绕过这一点。下面在从属计算机上的mysql命令有效:
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 |
+----+--------------------+---------------------+-----------+
但是,从设备不能使用相同的端口连接到主设备:
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 '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)
主机上的my.cnf:
[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在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
如果能理解奴隶为什么不与主人说话,我们将不胜感激。我花了几个小时寻找所有类似的问题,但似乎没有一个问题能提供帮助。
以下是主数据库上的授权状态:
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 replication
。这里有一个很好阅读的指南,介绍如何实现这一点。它指出了复制所需的步骤,如何在两台服务器上设置用户,配置MySQL
等等。
要创建SSH隧道,请使用以下命令。(从(
ssh -L 8899:127.0.0.1:3306 ssh_tunnel_user@master_server_ip_address -f -N
编辑
或者您可以尝试以下更改/添加:
创建这样的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
,您需要这样的东西。(你可以先试试这个…(
# 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
但在您的情况下,您还需要端口转发,它是由您的防火墙规则为您的特定区域创建的,如上所述。
因此,我认为最有可能需要的是以下防火墙规则。
# 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"'
你可以从获得更多想法
- 用";丰富的语言;语法
- 根据来源使用区域管理传入流量
- CentOS中的白名单源IP地址7
我不确定,但要么我不理解你的设置,要么这个:
ssh -g -R 8899:127.0.0.1:3306 username@intermediate_computer
ssh -g -R 8899:127.0.0.1:8899 username@slave_computer
意味着您尝试在两个不同的输出3306
和8899
上绑定相同的内部端口8899
。这对我来说似乎有问题,你试过两个不同的端口吗?
根据我使用mysql复制的经验,我可以告诉您,从mysql版本必须与master或更高版本相同。正如我所看到的,你在master上使用8.0,在slave上使用5.7。
在您的情况下,master可能会传递slave无法理解的binlog。