为了什么

・安装 mariadb10.5 到 Ubuntu 18.04
・安装演示数据库 nation
・尝试编写使用 python + mysql-connector-python 的示例代码

添加存储库

从https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/获取信息。

$ curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
[info] Running apt-get update...
[info] Done adding trusted package signing keys

Repositoryに以下の３サイトが登録される
※ワーニング表示抑制のため [arch=amd64] を追加している
$ sudo cat /etc/apt/sources.list.d/mariadb.list

# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu bionic main

# MariaDB MaxScale
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
deb [arch=amd64] http://downloads.mariadb.com/MaxScale/2.4/ubuntu bionic main

# MariaDB Tools
deb [arch=amd64] http://downloads.mariadb.com/Tools/ubuntu bionic main

安装Mariadb

$ sudo apt update
$ sudo apt install mariadb-server mariadb-client

※ 服务名称为 /lib/systemd/system/mariadb.service

设定文本编码

/etc/mysql/mariadb.conf.d/50-server.cnf

character-set-server  = utf8mb4
#collation-server     = utf8mb4_general_ci  修正前
collation-server      = utf8mb4_bin         修正後

#bind-address         = 127.0.0.1           修正前
bind-address          = 0.0.0.0             修正後

/etc/mysql/mariadb.conf.d/50-client.cnf
default-character-set = utf8mb4             コメントアウト

※修正後、再起動する
$ sudo systemctl restart mariadb

$ mysql -u root -p
Enter password:
～
MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

※アクセス用ユーザーを追加する
MariaDB [(none)]>GRANT ALL PRIVILEGES ON *.* TO demo IDENTIFIED BY 'passwd' WITH GRANT OPTION;

安装DemoDB国家

解压来自MariaDB示例数据库的nation.zip文件。

$ mysql -u root -p
Enter password:
～
MariaDB [(none)]> source /path/to/nation.sql
～
MariaDB [nation]> use nation;
Database changed

MariaDB [nation]> show tables;
+-------------------+
| Tables_in_nation  |
+-------------------+
| continents        |
| countries         |
| country_languages |
| country_stats     |
| guests            |
| languages         |
| region_areas      |
| regions           |
| vips              |
+-------------------+

安装mysql-connector-python

$ sudo pip3 install mysql-connector-python

样品代码

# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode
# coding:utf-8

import mysql.connector as mydb

# コネクションの作成
conn = mydb.connect(
    host='192.168.5.xxx',
    port='3306',
    user='demo',
    password='passwd',
    database='nation'
)

cur = conn.cursor()

query = "SELECT * FROM guests"
cur.execute(query)

print("all:")
rows = cur.fetchall()
for row in rows:
    print(row)
    
cur.close()
conn.close()

我参考的网站是这个。

在Ubuntu 18.04 LTS上安装MariaDB 10.4
解决Ubuntu 18.04上MariaDB 10.4的安装
关于MySQL的字符编码和排序规则
CentOS 7上安装和初始化MariaDB
创建用户
解决MySQL出现”ERROR 2003 (HY000): Can’t connect to MySQL server”错误的方法