远程连接MySQL数据库（多种方法）
1、授权连接到MySql
确保电脑上有mysql服务器，才可以进行如下操作
步骤1：配置服务器
配置MySQL服务器允许远程连接：
1、修改服务器配置文件：
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
2、注释或修改 bind-address 行：
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
3、重启 MySQL 服务：
sudo systemctl restart mysql
创建用户授权访问：
1、连接到 MySQL 服务器：
mysql -u root -p
2、创建用户并授予权限：
CREATE USER 'yourusername'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON yourdatabase.* TO 'yourusername'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
-- 创建一个新的 MySQL 用户
CREATE USER 'xiaozhou'@'%' IDENTIFIED BY 'securepassword';
-- 授予新用户对 car_door 数据库的所有权限
GRANT ALL PRIVILEGES ON car_door.* TO 'xiaozhou'@'%' WITH GRANT OPTION;
-- 刷新权限使更改生效
FLUSH PRIVILEGES;
3、防火墙设置：允许流量通过端口3306（一般都为3306）
sudo ufw allow 3306/tcp
sudo ufw reload
步骤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
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" // 用户在指定数据库上的权限
}
]
}
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')
3、执行脚本
在每次更新 JSON 文件后，可以运行这个脚本来生成新的 grant_privileges.sql 文件。然后，将生成的 SQL 文件导入 MySQL 服务器：
python3 generate_sql_from_json.py
mysql -u root -p < grant_privileges.sql
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
赋予脚本权限：
chmod +x /path/to/update_mysql_users.sh
运行脚本：
/path/to/update_mysql_users.sh
5、定时任务自动运行：
编辑crontab
crontab -e
添加以下行代码，确保每天运行脚本：
0 0 * * * /path/to/update_mysql_users.sh