2020-01-09
总结自菜鸟教程
以下所有操作基于pymysql库
数据库连接
使用pymysql.connect方法
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pymysql
#import MySQLdb
# connect方法 连接数据库
db = pymysql.connect("192.168.24.144","chy","123456","mysql")
# cursor() 创建游标对象
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# fetchone() 方法获取单条数据
data = cursor.fetchone()
print ("Database version : %s " % data)
执行后报错
(1045, u"Access denied for user 'chy'@'192.168.24.123' (using password: YES)")
连接失败,在服务端查看用户chy
的相关情报
mysql> SELECT host ,user , authentication_string FROM user WHERE user = 'chy';
+-----------+------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------+-------------------------------------------+
| localhost | chy | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)
目测是host
需要修改,和上午记载的问题基本一致
使用上午的方法进行修改
update user set host = '%' where user ='chy';
修改后问题依然存在
(1044, u"Access denied for user 'chy'@'%' to database 'mysql'")
再查发现SELECT host ,user , authentication_string FROM user WHERE user = 'chy';
不太够用,需要查看一些别的权限,使用SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+------------+------------+
| host | user | Grant_priv | Super_priv |
+-----------+------------------+------------+------------+
| % | root | Y | Y |
| localhost | mysql.session | N | Y |
| localhost | mysql.sys | N | N |
| localhost | debian-sys-maint | Y | Y |
| % | chy | N | N |
+-----------+------------------+------------+------------+
5 rows in set (0.00 sec)
修改权限Grant_priv
和 Super_priv
等会查查这些权限都是什么意思
mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='chy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
解决以上问题,执行上面的代码脚本,终端输出如下:
chy:~/code_bak/MY_SQL$ python mysql.py
Database version : 5.7.28-0ubuntu0.16.04.2
网友评论