mysql coneen refuse_远程连接mysql报错"Can't connect to MySQL server on 'x.x.x.x' ([Errno 61] Connection re...

环境信息

腾讯云服务器系统：liunx

服务器mysql版本：5.7.24‘

本地系统：win7

在服务器安装mysql

输入命令

sudo apt-get install mysql-server

apt-get install mysql-client

sudo apt-get install libmysqlclient-dev

登陆mysql

mysql -u root -p

问题详情

from pymysql import cursors, connect

# 连接数据库

conn = connect(

host='服务器ip',

user='root',

port=3306,

password='123456',

db='test',

charset='utf8mb4',

)

try:

with conn.cursor() as cursor:

# 查询

sql = "select * from test;"

cursor.execute(sql)

result = cursor.fetchone()

print(result)

except Exception as e:

print(e)

finally:

conn.close()

python连接mysql出错

pymysql.err.OperationalError:"Can't connect to MySQL server on 'x.x.x.x' ([Errno 61] Connection refused)")

意思是服务器拒绝访问。

解决办法

方法1

连接远程服务器输入"service mysqld status"查看mysql服务已启动

用"cat my.cnf"命令查看my.cnf文件目录：cat /etc/mysql/my.cof

image.png

用cd /etc/mysql/mysql.conf.d/进入该目录

用vim mysqld.cnf编辑文件

[mysqld]

bind-address = 0.0.0.0 # 表示允许任何主机登陆MySQL

port=3306 # 表示MySQL运行端口为3306

方法2

登陆mysql：mysql -u root -p

mysql> show global variables like 'port'; # 查看MySQL运行的实际端口

+---------------+-------

| Variable_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

1 row in set (0.01 sec)

mysql> use mysql; # 切换到mysql数据库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select host,user from user; # 查询user表

+-----------+---------------+

| host | user |

+-----------+---------------+

| % | root |

| localhost | mysql.session |

| localhost | mysql.sys |

+-----------+---------------+

5 rows in set (0.00 sec)

如果上述查询结果，root用户对应的host不为%，则修改用户权限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root密码' WITH GRANT OPTION; # 修改权限

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

方法3

关闭防火墙

systemctl start firewalld # 开启防火墙

systemctl stop firewalld # 关闭防火墙

systemctl status firewalld #检查防火墙状态

方法4

配置腾讯云服务器的安全组开放3306端口

连接成功