英文:
Unable to connect to a remote mysql server
问题
I am unable to connect to a remote mysql server. Both my remote and local machines are using ubuntu. The remote is a machine hosted in aws ec2 instance.
我无法连接到远程的MySQL服务器。我的远程和本地机器都在使用Ubuntu。远程机器托管在AWS EC2实例上。
I have changed the remote's bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf like so:
> bind-address=0.0.0.0
我已经像这样更改了远程机器的/etc/mysql/mysql.conf.d/mysqld.cnf中的绑定地址：
> bind-address=0.0.0.0
I have created a new remote user CREATE USER 'username'@'%' IDENTIFIED BY 'password'; and granted necessary permissions like so:
> GRANT ALL ON mydb.* TO 'username'@'%';
我创建了一个新的远程用户CREATE USER 'username'@'%' IDENTIFIED BY 'password';并授予了必要的权限，如下所示：
> GRANT ALL ON mydb.* TO 'username'@'%';
followed By sudo service mysql restart.
I have changed firewall settings like so:
> sudo ufw allow 3306
然后执行sudo service mysql restart。我也像下面这样更改了防火墙设置：
> sudo ufw allow 3306
But I am still unable to connect from my local ubuntu machine or other ec2 instances. I have done telnet 13.127.232.87 3306 but it results in timeout but I have also check remote with sudo systemctl start mysql.service which is showing to be running perfectly. There are no errors in log but says :
2023-05-21T07:18:42.452577Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-05-21T07:18:42.452831Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.33-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock'
但我仍然无法从我的本地Ubuntu机器或其他EC2实例连接。我已经执行了telnet 13.127.232.87 3306，但结果是超时，但我也使用sudo systemctl start mysql.service检查了远程机器，显示它正在正常运行。日志中没有错误，但显示如下：
2023-05-21T07:18:42.452577Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-05-21T07:18:42.452831Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.33-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock'
EDIT:
Also tried: sshd: <IP_addresses_to_whitelist> followed by sudo service ssh restart
编辑：
还尝试了：sshd: <IP_addresses_to_whitelist> 然后执行 sudo service ssh restart
英文:
I am unable to connect to a remote mysql server. Both my remote and local machines are using ubuntu. The remote is a machine hosted in aws ec2 instance.
I have changed the remote's bind-address in /etc/mysql/mysql.conf.d/mysqld.cnf like so:
> bind-address=0.0.0.0
I have created a new remote user CREATE USER 'username'@'%' IDENTIFIED BY 'password'; and granted necessary permissions like so:
> GRANT ALL ON mydb.* TO 'username'@'%';
followed By sudo service mysql restart.
I have changed firewall settings like so:
> sudo ufw allow 3306
But I am still unable to connect from my local ubuntu machine or other ec2 instances. I have done telnet 13.127.232.87 3306 but it results in timeout but I have also check remote with sudo systemctl start mysql.service which is showing to be running perfectly. There are no errors in log but says :
Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-05-21T07:18:42.452577Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-05-21T07:18:42.452831Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.33-0ubuntu0.22.04.2' socket: '/var/run/mysqld/mysqld.sock'
EDIT:
Also tried: sshd: <IP_addresses_to_whitelist> followed by sudo service ssh restart
答案1
得分: 0
以下是翻译好的部分：
"对于将来遇到相同问题的任何人，请一步一步按照以上所有步骤进行操作，并不要忘记在安全组上允许3306端口，但不要将类型设置为TCP，而应设置为MySQL。这是我犯的一种尴尬错误。 :)"
英文:
For anyone who has same problem in the future follow all the above steps one by one and don't forget to allow 3306 on security group not as type tcp but as mysql that was kind of embarrasing mistake on my part. 
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论