MariaDB

安装

参考 How To Install MariaDB on Ubuntu 22.04

sudo apt update
sudo apt upgrade
sudo apt install mariadb-server
sudo mysql_secure_installation

VPS开放3306端口并执行 sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf,注释掉 bind-address = 127.0.0.1, 更新数据库表：

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 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
MariaDB [mysql]> select host,user from user where user = 'root';
+-----------+------+
| Host      | User |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.001 sec)
MariaDB [mysql]> UPDATE mysql.global_priv SET Host='%' WHERE User='root';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> select host,user from user where user = 'root';
+------+------+
| Host | User |
+------+------+
| %    | root |
+------+------+
1 row in set (0.001 sec)

重启数据库 sudo service mariadb restart

WSL安装客户端： sudo apt install mariadb-client

shell下测试连接： mysql -u root -h VPS_ip -D mysql -p

python驱动，参考： https://mariadb.com/resources/blog/how-to-connect-python-programs-to-mariadb/

pip install mariadb

测试脚本：

import mariadb
import sys

db_user='root'
db_user_passwd = 'db_password'
db_host_ip = "db_ip"

# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user=db_user,
        password=db_user_passwd,
        host=db_host_ip,
        port=3306,
        database="mysql"

    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor
cur = conn.cursor()

cur.execute(
    "SELECT host,user FROM user WHERE user=?", ('root',))

# Print Result-set
for (host, user) in cur:
    print(f"Host: {host}, User: {user}") 

# Close Connection
conn.close()

输出：Host: %, User: root

新建数据库用户并赋权

VPS上登录到root，更新root仅允许本地访问，创建新用户test_user_name可以从VPS外访问，将数据库db_name的权限赋予该用户

mysql -u root -p
UPDATE mysql.global_priv SET Host='localhost' WHERE User='root';
CREATE USER 'test_user_name'@'%' IDENTIFIED BY 'test_user_name_password';
GRANT ALL ON db_name.* to test_user_name@'%' IDENTIFIED BY 'test_user_name_password';
FLUSH privileges;

pycharm 客户端

视图，工具窗口，数据库，添加数据库

使用ORM： SQLAlchemy

1.数据库驱动
参考：https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-1/ ，安装 MariaDB Connector/C 参考：https://mariadb.com/docs/skysql/connect/programming-languages/c/install/

2.安装sqlalchemy： conda install sqlalchemy