如何在python里登录测试mysql数据库

首先学会使用python登录mysql数据库

安装相关库

首先需要安装python库pymysql

pip instlal pymysql

还需要安装cryptography库

对Ubuntu平台来说，直接pip安装即可：

pip install cryptography

对于测试用的FreeBSD平台来说，稍微有些麻烦，需要使用pkg安装包：

pkg install py311-cryptography

由于是安装的py311-cryptography库，因此测试时需要执行python3.11进入python3.11环境才可以使用cryptography这个库。也就是只有python3.11这个版本下才可用。

在python中登录测试mysql服务器

一个测试例子

mysql服务器在192.168.1.12 ，内网通过192.168.1.2 nat出去，尝试登录mysql，代码如下：

import pymysql  
  
# 配置数据库连接  
config = {  
    'host': '192.168.1.12',  
    'user': 'trinity',  
    'password': 'trinity',  
    'database': 'auth',  
    'charset': 'utf8mb4',  
    'cursorclass': pymysql.cursors.DictCursor  
}  
  
try:  
    # 建立数据库连接  
    connection = pymysql.connect(**config)  
  
    # 使用cursor()方法获取操作游标  
    with connection.cursor() as cursor:  
        # 执行SQL查询  
        sql = "SELECT * FROM realmlist"  
        cursor.execute(sql)  
  
        # 获取所有记录列表  
        results = cursor.fetchall()  
        for row in results:  
            print(row)  
  
except pymysql.MySQLError as e:  
    print(f"Error: {e}")  
  
finally:  
    # 关闭数据库连接  
    connection.close()

换一个网络拓扑，mysql数据库是192.168.0.12

import pymysql  
  
# 配置数据库连接  
config = {  
    'host': '192.168.0.12',  
    'user': 'trinity',  
    'password': 'trinity',  
    'database': 'auth',  
    'charset': 'utf8mb4',  
    'cursorclass': pymysql.cursors.DictCursor  
}  
  
try:  
    # 建立数据库连接  
    connection = pymysql.connect(**config)  
  
    # 使用cursor()方法获取操作游标  
    with connection.cursor() as cursor:  
        # 执行SQL查询  
        sql = "SELECT * FROM realmlist"  
        cursor.execute(sql)  
  
        # 获取所有记录列表  
        results = cursor.fetchall()  
        for row in results:  
            print(row)  
  
except pymysql.MySQLError as e:  
    print(f"Error: {e}")  
  
finally:  
    # 关闭数据库连接  
    connection.close()

如果数据库配置一切正常，那么就会输出auth这个库realmlist这个表的信息。输出类似：

{'id': 1, 'name': 'Trinity', 'address': '114.115.136.249', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 0, 'timezone': 1, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}
{'id': 2, 'name': 'NewRealm', 'address': '192.168.1.12', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 2, 'timezone': 1, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}
{'id': 6, 'name': 'test', 'address': '127.0.0.1', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 2, 'timezone': 0, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}

在实际实践中，没有这么顺利，因为默认mysql服务器是不会开远程访问权限的。需要执行两步：

1 mysql服务器打开远程访问权限

2 登录的用户给它远程访问权限

如果两步没有做，那么就无法登录。比如在做完第一步之后，没有做第二步，那么就可能会报错：

报错信息：

Error: (1130, "Host '192.168.0.107' is not allowed to connect to this MySQL server")
证明还要给用户加ip地址权限。 

如何为mysql添加远程访问权限

1 mysql服务器打开远程访问权限

具体为在/etc/mysql/mysql.conf.d/mysqld.conf文件中，修改

# bind-address          = 127.0.0.1
bind-address            = 0.0.0.0

将绑定ip从127.0.0.1修改成0.0.0.0,即可放开服务器远程访问权限。 

2 登录的用户给它远程访问ip权限

具体为创建跟访问源ip绑定的用户：

CREATE USER 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity';


GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.12' WITH GRANT OPTION;

先创建了用户，然后赋予它访问auth库的权限。

为ip地址访问加上权限实践操作

首先看一下数据库中账户的权限

mysql> use 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 WHERE user='trinity';
+-----------+---------+
| host      | user    |
+-----------+---------+
| localhost | trinity |
+-----------+---------+
1 row in set (0.01 sec)

证明只能从localhost登录

使用命令：

GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity' WITH GRANT OPTION;

报错：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'trinity' WITH GRANT OPTION' at line 1

提示

说明不能同时改ip 和密码

只改ip范围

GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;

还是报错，原来需要使用新增用户的方式

新增远程用户'trinity'@'192.168.1.2'

mysql> CREATE USER 'trinity'@'192.168.1.2' IDENTIFIED BY 'trinity';
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT host, user FROM user WHERE user='trinity';
+-------------+---------+
| host        | user    |
+-------------+---------+
| 192.168.1.2 | trinity |
| localhost   | trinity |
+-------------+---------+
2 rows in set (0.00 sec)

为新增用户加上auth库的权限

GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
 

进行测试

import pymysql  
  
# 配置数据库连接  
config = {  
    'host': '192.168.1.12',  
    'user': 'trinity',  
    'password': 'trinity',  
    'database': 'auth',  
    'charset': 'utf8mb4',  
    'cursorclass': pymysql.cursors.DictCursor  
}  
  
try:  
    # 建立数据库连接  
    connection = pymysql.connect(**config)  

报错还是没权限。仔细检查，发现前面新增用户和权限都写在了另一台服务器，1.12的权限还是没有放开。

为1.12服务器添加权限：

mysql> CREATE USER 'trinity'@'192.168.1.2' IDENTIFIED BY 'trinity';
Query OK, 0 rows affected (0.27 sec)

mysql> GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

测试1.12服务器

报错

RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods

安装上cryptography库即可。

再进行测试，测试通过：

{'id': 1, 'name': 'Trinity', 'address': '114.115.136.249', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 0, 'timezone': 1, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}
{'id': 2, 'name': 'NewRealm', 'address': '192.168.1.12', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 2, 'timezone': 1, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}
{'id': 6, 'name': 'test', 'address': '127.0.0.1', 'localAddress': '127.0.0.1', 'localSubnetMask': '255.255.255.0', 'port': 8085, 'icon': 0, 'flag': 2, 'timezone': 0, 'allowedSecurityLevel': 0, 'population': 0.0, 'gamebuild': 12340}

为实际机器0.12服务器加权限（其实不是）

创建一个源ip为192.168.1.12的mysql用户并赋予权限

mysql> CREATE USER 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity';
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> 
mysql> GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.12' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

然后确认一下这句是否加上

mysql> use 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 WHERE user='trinity';
+--------------+---------+
| host         | user    |
+--------------+---------+
| %            | trinity |
| 192.168.1.12 | trinity |
| 192.168.1.2  | trinity |
+--------------+---------+
3 rows in set (0.00 sec)

可以看到这句加上了。这里有三句，其中第一句是使用updata，设置全网可以用

mysql> use 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> update user set host = '%' where user = 'trinity';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第二句1.12就是刚才加的。第三句1.2是前面登错了服务器加的。 

测试：

直接生产环境测试，测试没通过。说1.2还是没权限。仔细看，又登错了服务器.....真实的服务器权限：

mysql> use 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 WHERE user='trinity';
+-------------+---------+
| host        | user    |
+-------------+---------+
| 192.168.1.2 | trinity |
| localhost   | trinity |
+-------------+---------+
2 rows in set (0.01 sec)

所以并没有为实际0.12机器加权限 

终极操作再为0.12服务器加权限

为0.12服务器加上1.12的访问权限：

CREATE USER 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity';


GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.12' WITH GRANT OPTION;

 确认加上1.12

SELECT host, user FROM user WHERE user='trinity';
+--------------+---------+
| host         | user    |
+--------------+---------+
| 192.168.1.12 | trinity |
| 192.168.1.2  | trinity |
| localhost    | trinity |
+--------------+---------+

生产环境测试，测试通过！

 总结

python登录调试mysql，重点不在于python代码，而在于mysql放开权限，步骤为：

1 mysql服务器打开远程访问权限

具体为在/etc/mysql/mysql.conf.d/mysqld.conf文件中，修改

# bind-address          = 127.0.0.1
bind-address            = 0.0.0.0

修改完之后重启服务service mysqld restart 

2 给mysql用户远程访问ip权限

具体为创建跟访问源ip绑定的用户：

CREATE USER 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity';


GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.12' WITH GRANT OPTION;

权限加上，pymysql客户端才能登录mysql服务器。

调试

mysql登录提示ip地址没有权限

Error: (1130, "Host '192.168.1.2' is not allowed to connect to this MySQL server")
为服务器的账户添加权限

mysql> CREATE USER 'trinity'@'192.168.1.2' IDENTIFIED BY 'trinity';
Query OK, 0 rows affected (0.27 sec)

mysql> GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql登录提示'cryptography' package is required

RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods

对Ubuntu平台来说，直接pip安装即可：

pip install cryptography

对于测试用的FreeBSD平台来说，稍微有些麻烦，需要使用pkg安装包：

pkg install py311-cryptography
Updating FreeBSD repository catalogue...
pkg: No SRV record found for the repo 'FreeBSD'
Fetching meta.conf:   0%
FreeBSD repository is up to date.
All repositories are up to date.
The following 4 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	py311-cffi: 1.16.0
	py311-cryptography: 42.0.8_1,1
	py311-pycparser: 2.22

Installed packages to be REINSTALLED:
	liblz4-1.10.0,1

Number of packages to be installed: 3
Number of packages to be reinstalled: 1

The process will require 8 MiB more space.
1 MiB to be downloaded.

Proceed with this action? [y/N]: y

并且需要执行python3.11进入（也就是只有python3.11这个版本的环境下），然后才可以使用cryptography这个库。

为mysql增加ip访问用户报错

使用命令：

GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity' WITH GRANT OPTION;

报错：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'trinity' WITH GRANT OPTION' at line 1

说明不能同时改ip 和密码

只改ip范围

GRANT ALL PRIVILEGES ON *.* TO 'trinity'@'192.168.1.2' WITH GRANT OPTION;

还是报错，原来需要使用新增用户的方式。即新增用户的时候就加上ip地址，然后再给用户赋予权限：

CREATE USER 'trinity'@'192.168.1.12' IDENTIFIED BY 'trinity';


GRANT ALL PRIVILEGES ON auth.* TO 'trinity'@'192.168.1.12' WITH GRANT OPTION;