远程连接MySQL数据库（多种方法）

远程连接MySQL数据库（多种方法）

1、授权连接到MySql

确保电脑上有mysql服务器，才可以进行如下操作

步骤1：配置服务器

配置MySQL服务器允许远程连接：

1、修改服务器配置文件：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
1

2、注释或修改 bind-address 行：

# bind-address = 127.0.0.1
bind-address = 0.0.0.0
1
2

3、重启 MySQL 服务：

sudo systemctl restart mysql
1

创建用户授权访问：

1、连接到 MySQL 服务器：

mysql -u root -p
1

2、创建用户并授予权限：

CREATE USER 'yourusername'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON yourdatabase.* TO 'yourusername'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
1
2
3

-- 创建一个新的 MySQL 用户
CREATE USER 'xiaozhou'@'%' IDENTIFIED BY 'securepassword';

-- 授予新用户对 car_door 数据库的所有权限
GRANT ALL PRIVILEGES ON car_door.* TO 'xiaozhou'@'%' WITH GRANT OPTION;

-- 刷新权限使更改生效
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9

3、防火墙设置：允许流量通过端口3306（一般都为3306）

sudo ufw allow 3306/tcp
sudo ufw reload
1
2

步骤2：配置应用程序：

在应用程序中，使用新创建的用户和密码进行数据库连接：

config = {
    'user': 'xiaozhou',
    'password': 'securepassword',
    'host': 'your_server_ip_or_hostname',
    'database': 'car_door'
}

def create_connection():
    try:
        cnx = mysql.connector.connect(**config)
        return cnx
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("用户名或密码错误")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("数据库未创建")
        else:
            print(err)
        return None

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2、通过脚本去配置远程访问

第一种方法的缺点在于每次添加一个访问授权就要整体操作一遍，这里提出用脚本在主机上更新授权用户，注意：进行的授权只能在选定的服务器上运行，脚本也只能设置在服务器上运行

1、JSON文件准备

准备一个JSON文件，用于填写用户信息，以后每次修改直接修改JSON文件即可，或者以后可以使用一些方法远程修改文件。这里我的JSON文件命名为：db_users.json

{
    "users": [
        {
            "username": "user_name",       // 要创建的用户名
            "password": "securepassword",      // 用户的密码
            "host": "%",                       // 用户可以从哪些主机连接到 MySQL 服务器
            "database": "database",            // 用户有权限访问的数据库
            "privileges": "ALL PRIVILEGES"     // 用户在指定数据库上的权限
        },
        {
            "username": "another_user",        // 另一个要创建的用户名
            "password": "anotherpassword",     // 用户的密码
            "host": "%",                       // 用户可以从哪些主机连接到 MySQL 服务器
            "database": "another_database",    // 用户有权限访问的数据库
            "privileges": "SELECT, INSERT"     // 用户在指定数据库上的权限
        }
    ]
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2、通过python脚本对JSON文件进行读取

编写一个 Python 脚本 generate_sql_from_json.py 来读取 JSON 文件，并生成包含 SQL 语句的 grant_privileges.sql 文件，因为SQL更新需要用到MySQL文件，不能直接读取JSON，这里去进行转化：

import json
import os

def generate_sql_from_json(json_file, output_sql_file):
    json_file = os.path.join(os.path.dirname(__file__), json_file)  # 使用绝对路径
    with open(json_file, 'r') as file:
        user_data = json.load(file)
    
    sql_statements = []
    for user in user_data['users']:
        create_user = f"CREATE USER IF NOT EXISTS '{user['username']}'@'{user['host']}' IDENTIFIED BY '{user['password']}';"
        grant_privileges = f"GRANT {user['privileges']} ON {user['database']}.* TO '{user['username']}'@'{user['host']}' WITH GRANT OPTION;"
        sql_statements.append(create_user)
        sql_statements.append(grant_privileges)
    
    sql_statements.append("FLUSH PRIVILEGES;")
    
    with open(output_sql_file, 'w') as file:
        for statement in sql_statements:
            file.write(statement + '\n')
    
    print(f"SQL statements written to {output_sql_file}")

if __name__ == "__main__":
    generate_sql_from_json('db_users.json', 'grant_privileges.sql')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3、执行脚本

在每次更新 JSON 文件后，可以运行这个脚本来生成新的 grant_privileges.sql 文件。然后，将生成的 SQL 文件导入 MySQL 服务器：

python3 generate_sql_from_json.py
mysql -u root -p < grant_privileges.sql
1
2

4、自动运行脚本

如果还是感觉麻烦，可以创建一个自动运行代码的脚本，创建名为：update_sql_user.sh的脚本然后设置脚本定期运行：

#!/bin/bash

# 设置路径
JSON_FILE="/path/to/db_users.json"
PYTHON_SCRIPT="/path/to/generate_sql_from_json.py"
SQL_FILE="/path/to/grant_privileges.sql"

# MySQL 配置
MYSQL_USER="root"
MYSQL_PASSWORD="your_root_password"
MYSQL_HOST="localhost"

# 运行 Python 脚本生成 SQL 文件
/usr/bin/python3 $PYTHON_SCRIPT

# 检查 SQL 文件是否生成成功
if [ -f "$SQL_FILE" ]; then
    # 导入生成的 SQL 文件到 MySQL 服务器
    mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST < $SQL_FILE
    if [ $? -eq 0 ]; then
        echo "MySQL 用户和权限更新成功。"
    else
        echo "MySQL 用户和权限更新失败。"
    fi
else
    echo "SQL 文件生成失败。"
fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

赋予脚本权限：

chmod +x /path/to/update_mysql_users.sh
1

运行脚本：

/path/to/update_mysql_users.sh
1

5、定时任务自动运行：

编辑crontab

crontab -e
1

添加以下行代码，确保每天运行脚本：

0 0 * * * /path/to/update_mysql_users.sh
1