2020-01-09
總結(jié)自菜鳥教程
以下所有操作基于pymysql庫(kù)
數(shù)據(jù)庫(kù)連接
使用pymysql.connect方法
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pymysql
#import MySQLdb
# connect方法 連接數(shù)據(jù)庫(kù)
db = pymysql.connect("192.168.24.144","chy","123456","mysql")
# cursor() 創(chuàng)建游標(biāo)對(duì)象
cursor = db.cursor()
# 使用 execute() 方法執(zhí)行 SQL 查詢
cursor.execute("SELECT VERSION()")
# fetchone() 方法獲取單條數(shù)據(jù)
data = cursor.fetchone()
print ("Database version : %s " % data)
執(zhí)行后報(bào)錯(cuò)
(1045, u"Access denied for user 'chy'@'192.168.24.123' (using password: YES)")
連接失敗,在服務(wù)端查看用戶chy的相關(guān)情報(bào)
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)
目測(cè)是host需要修改,和上午記載的問題基本一致
使用上午的方法進(jìn)行修改
update user set host = '%' where user ='chy';
修改后問題依然存在
(1044, u"Access denied for user 'chy'@'%' to database 'mysql'")
再查發(fā)現(xiàn)SELECT host ,user , authentication_string FROM user WHERE user = 'chy';不太夠用,需要查看一些別的權(quán)限,使用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)
修改權(quán)限Grant_priv 和 Super_priv 等會(huì)查查這些權(quán)限都是什么意思
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)
解決以上問題,執(zhí)行上面的代碼腳本,終端輸出如下:
chy:~/code_bak/MY_SQL$ python mysql.py
Database version : 5.7.28-0ubuntu0.16.04.2
