第 22 章 · MySQL 与 MariaDB

一句话：用得最广的开源关系数据库，默认存储引擎 InnoDB 带事务和行锁；MariaDB 是它的分支，命令和 SQL 大部分相同。 用它还是用别的：团队、托管环境或老系统已经定了 MySQL 就用它，PHP、WordPress 一类程序默认也是它；新项目没有这层约束时选 PostgreSQL（第 21 章）；单机小工具、测试用的库用 SQLite（第 23 章）。 核对版本：MySQL 8.4（随书 compose 镜像）、MariaDB 11.4，2026-10

22.1 安装与版本

本书用 Docker 跑 MySQL。在随书的 compose/ 目录下启动，Compose 的用法详见第 13 章：

docker compose --profile db up -d

这一条同时起 PostgreSQL、MySQL、MongoDB。MySQL 的宿主机端口是 3306，超级用户 root，口令 dev，数据在命名卷 mysqldata 里。

进容器里自带的命令行客户端 mysql，提示输入口令时输 dev，出现 mysql> 提示符就是连上了。本章 22.3 和 22.4 的 SQL 都在这个提示符下输入，exit 退出。要输入中文时加 --default-character-set=utf8mb4，原因见 22.4 排查乱码一节：

docker compose exec mysql mysql -uroot -p

宿主机上装了 mysql 客户端（装法见第 20 章 20.1）的，也可以从宿主机连。主机写 127.0.0.1 而不是 localhost：Linux 和 macOS 上的 mysql 客户端把 localhost 当成本机的套接字文件，不走 TCP 端口，连不到容器：

mysql -h 127.0.0.1 -P 3306 -uroot -p

查版本，前一条查本机客户端，输出形如 mysql Ver 8.4.6 for Linux on x86_64；后一条在 mysql 里查服务端，返回 8.4. 开头的版本号：

mysql --version

SELECT VERSION();

不想用 Docker、要在本机安装服务的，见 22.7 的“下载”。

MariaDB 是从 MySQL 分出来的另一个数据库，网络协议和大部分 SQL 相同，本章的命令多数照用，客户端也连得上对方。几处要注意：命令行程序的名字以 mariadb、mariadb-dump 为准，mysql、mysqldump 这些旧名字在有的环境里已经没有；版本号各走各的，MariaDB 11.4 不对应 MySQL 8.4 的功能；Linux 包装好后 root 用系统账号认证，sudo mariadb 不要口令；MySQL 8 导出的文件里常有排序规则 utf8mb4_0900_ai_ci，MariaDB 11.4.5 之前的版本不认识，导入就报错，见 22.6；JSON 类型在 MariaDB 里只是长文本的别名；两者的复制机制不通用，不能随意互相做主从。Docker 里跑 MariaDB 用官方镜像 mariadb:11.4，口令的环境变量叫 MARIADB_ROOT_PASSWORD。

22.2 五分钟概念

• 实例与库：一个 mysqld 服务进程叫一个实例（Instance），管一个数据目录，随书镜像里是 /var/lib/mysql，存在卷 mysqldata 里。实例里有多个库（Database）。MySQL 里库和模式（Schema）是同一个东西，CREATE SCHEMA 和 CREATE DATABASE 同义；同一个实例里能用 库名.表名 跨库查询和联表，这一点和 PostgreSQL 不同。
• 账号是 'user'@'host'：账号由用户名和来源主机两部分组成，'app'@'localhost' 和 'app'@'%' 是两个账号，口令和权限各管各的，% 匹配任何来源。连接时服务端按来源地址找最具体的那条账号，找不到就报 Access denied。MySQL 8.4 的默认认证插件是 caching_sha2_password，旧客户端和驱动要升级才连得上。
• 存储引擎（Storage Engine）InnoDB：表的数据怎么存、怎么加锁由存储引擎决定，默认是 InnoDB，支持事务、行锁、外键和崩溃恢复。老系统里可能还有 MyISAM 表：不支持事务，写入时锁住整张表，mysqldump --single-transaction 对它不起作用；SHOW CREATE TABLE 的 ENGINE= 能看出是哪种。
• 字符集（Character Set）与排序规则（Collation）：字符集决定能存哪些字符，排序规则决定怎么比较和排序。MySQL 的 utf8 是 utf8mb3 的别名，每个字符最多 3 个字节，存不了表情符号和部分生僻字；完整的 UTF-8 叫 utf8mb4。8.0 起服务端默认 utf8mb4 和 utf8mb4_0900_ai_ci，ai_ci 表示不区分重音和大小写，WHERE name = 'tom' 能查到 Tom。字符集分服务端、库、表、列、连接几层，表和列的建表时就定下了，事后改服务端默认值不影响已有的表。
• 二进制日志（binlog）：按顺序记下每一次改动，主从复制和按时间点恢复都靠它。8.0 起默认开启，默认保留 30 天，文件在数据目录下，名为 binlog.000001 之类；写入多的库要留意它占的磁盘，SHOW BINARY LOGS; 列出各个文件和大小。

随书的容器里，账号的“来源主机”和直觉不一样：容器里的 mysql 客户端走套接字，来源是 localhost；宿主机上的程序连 127.0.0.1:3306，经 Docker 端口映射进到容器，服务端看到的来源是 Docker 网关的地址，只有 'app'@'%' 这类账号能匹配上，'app'@'localhost' 不行：

字符集出问题多在连接这一层。客户端连上时声明自己发的是哪种编码（character_set_client），服务端按声明解码，再转成列的字符集存下；取数据时按 character_set_results 编码后送回。声明和实际发出的字节不一致，存进去的就是错的字节；列的字符集装不下的字符，变成问号或者直接报错：

22.3 常用命令

SQL 以分号结尾；\G、\s 这类以 \ 开头的命令和 SOURCE、exit 不用分号，只在 mysql 客户端里有效。备份恢复一组是命令行程序，在容器里执行时前面加 docker compose exec mysql，文件也在容器里；进出容器的文件用 22.4 的 -T 写法。示例库叫 app，表 orders，reader 是只读用户。

任务	命令	说明
客户端命令
列出数据库	SHOW DATABASES;	含 mysql、information_schema 等系统库
换库	USE app;	之后不写库名的表都指 app 里的；也可以连接时写库名 mysql -uroot -p app
列出表	SHOW TABLES;	只列当前库；看别的库写 SHOW TABLES FROM shop;
看表结构	DESCRIBE orders;	列、类型、能否为空、键、默认值
看建表语句	SHOW CREATE TABLE orders\G	含引擎、字符集、排序规则和全部索引，排查乱码先看它
竖着显示结果	SELECT * FROM orders WHERE id = 1\G	用 \G 代替分号结尾，每列占一行，看宽表好用
执行 SQL 文件	SOURCE init.sql	路径相对于启动 mysql 的目录，在容器里就是容器里的路径
不进提示符直接执行	mysql -uroot -p -e "SHOW DATABASES"	写脚本时用
看连接信息	\s	服务端版本、当前用户、连接用的字符集、是否加密
退出	exit	也可以 \q 或按 Ctrl+D
用户与权限
建用户	CREATE USER 'app'@'%' IDENTIFIED BY 'app_pw';	从宿主机经 Docker 连进来要用 %，见 22.2
给一个库的全部权限	GRANT ALL PRIVILEGES ON app.* TO 'app'@'%';	只限 app 库，不能再授权给别人
给只读权限	GRANT SELECT ON app.* TO 'reader'@'%';	8.0 起 GRANT 不再顺带建用户，要先 CREATE USER
看权限	SHOW GRANTS FOR 'app'@'%';	不写 FOR … 看当前用户的
改口令	ALTER USER 'app'@'%' IDENTIFIED BY 'new_pw';	已连上的会话不受影响，下次连接起用新口令
收回权限	REVOKE DELETE ON app.* FROM 'app'@'%';	写法和 GRANT 对称
删用户	DROP USER 'reader'@'%';	它已建立的连接不会立刻断开
列出所有账号	SELECT user, host, plugin FROM mysql.user;	plugin 是认证插件，同名不同 host 的是不同账号
备份恢复
导出一个库	mysqldump -uroot -p --single-transaction app > app.sql	InnoDB 表在一致的快照里导出，不锁表；文件里不含建库语句
连同存储过程和事件	mysqldump -uroot -p --single-transaction --routines --events app > app.sql	默认只带触发器
导出并带建库语句	mysqldump -uroot -p --single-transaction --databases app shop > two.sql	恢复时自动建库并切过去，不能换库名
只导几张表	mysqldump -uroot -p --single-transaction app orders users > part.sql	库名后跟表名
只导表结构	mysqldump -uroot -p --no-data app > schema.sql	建库建表脚本，不含数据
导出全部库	mysqldump -uroot -p --single-transaction --all-databases > all.sql	含系统库 mysql，账号和权限也在里面
恢复	mysql -uroot -p app_copy < app.sql	目标库要先建好；遇到错误就停，之前执行过的不回滚
运维
看当前连接	SHOW FULL PROCESSLIST;	Time 是当前状态持续的秒数，Info 是正在执行的语句
结束一个连接	KILL 42;	参数是上一条查到的 Id
只取消正在跑的语句	KILL QUERY 42;	连接保留，程序不用重连
查配置项	SHOW VARIABLES LIKE 'max_conn%';	% 是通配符
看当前连接数	SHOW GLOBAL STATUS LIKE 'Threads_connected';	和 max_connections 比，接近了就要查谁占着连接
改配置并保留	SET PERSIST max_connections = 300;	立即生效，写进数据目录的 mysqld-auto.cnf，重启后仍在；SET GLOBAL 重启就丢
打开慢查询日志	SET GLOBAL slow_query_log = ON;	超过 long_query_time 秒的语句写进日志，默认 10 秒
慢查询日志在哪	SHOW VARIABLES LIKE 'slow_query_log_file';	默认在数据目录，名为 主机名-slow.log
看执行计划	EXPLAIN SELECT * FROM orders WHERE user_id = 42;	type 列为 ALL 是全表扫描，key 列是用到的索引
各表大小	SELECT table_name, ROUND((data_length + index_length) / 1048576) AS mb FROM information_schema.tables WHERE table_schema = 'app' ORDER BY mb DESC;	单位 MB；InnoDB 给的是估算值
看最近一次死锁	SHOW ENGINE INNODB STATUS\G	看 LATEST DETECTED DEADLOCK 一段，两边各是哪条语句

22.4 典型工作流

给应用建库和用户

程序不该用 root 连库：它能删任何库、改任何账号。给每个应用建一个 utf8mb4 的库和一个只管这个库的用户：

1. 起数据库：docker compose --profile db up -d。
2. 以 root 进 mysql，口令 dev：docker compose exec mysql mysql -uroot -p --default-character-set=utf8mb4。
3. 建库，写明字符集和排序规则，换版本也不会退回 latin1（要导到 MariaDB 11.4.5 之前的版本，排序规则改用 utf8mb4_unicode_ci，见 22.6）：CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;。
4. 建用户，来源写 %，宿主机上的程序才连得上，口令换成自己的：CREATE USER 'app'@'%' IDENTIFIED BY 'app_pw';。
5. 只给这个库的权限：GRANT ALL PRIVILEGES ON app.* TO 'app'@'%';。
6. 核对，应看到 app.* 上的 ALL PRIVILEGES：SHOW GRANTS FOR 'app'@'%';。
7. 退出：exit。
8. 以新用户进新库，口令 app_pw：docker compose exec mysql mysql -uapp -p --default-character-set=utf8mb4 app。
9. 建表并存一行带表情的中文，不报错就说明整条链路都是 utf8mb4：CREATE TABLE notes (id BIGINT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200) NOT NULL);，再 INSERT INTO notes (body) VALUES ('你好😀');。

程序里的连接串写 mysql://app:[email protected]:3306/app?charset=utf8mb4，主机写 127.0.0.1 的原因见 22.1，各语言驱动的写法见第 20 章 20.2、20.5。

导出一个库，导入成另一个库

经管道或重定向进出容器时一律加 -T，不让 docker compose exec 分配终端，原因见第 21 章 21.4。-T 时没法交互输入口令，口令只好写在 -p 后面，屏幕上会多一行 Using a password on the command line interface can be insecure 的警告，本机练习可以忽略。下面的命令在随书 compose/ 目录下执行：

1. 把 app 库导出到宿主机的 app.sql：docker compose exec -T mysql mysqldump -uroot -pdev --single-transaction --routines --events app > app.sql。
2. 看一眼文件头，有 SET NAMES utf8mb4 一行，导入时会按它设置连接字符集（PowerShell：Get-Content app.sql -TotalCount 20）：head -n 20 app.sql。
3. 建一个空库作为导入目标：docker compose exec mysql mysql -uroot -pdev -e "CREATE DATABASE app_copy CHARACTER SET utf8mb4"。
4. 把宿主机上的文件送进容器导入，这是 bash 的写法，PowerShell 见下面的块：docker compose exec -T mysql mysql -uroot -pdev app_copy < app.sql。
5. 核对行数和原库一致：docker compose exec mysql mysql -uroot -pdev -e "SELECT COUNT(*) FROM app_copy.notes"。
6. 演练完删掉：docker compose exec mysql mysql -uroot -pdev -e "DROP DATABASE app_copy"。

Get-Content app.sql -AsByteStream -Raw | docker compose exec -T mysql mysql -uroot -pdev app_copy

PowerShell 不支持 < 重定向，所以用管道；第 1 步的 > 和上面的管道都要在 PowerShell 7.4 以上用，Windows PowerShell 5.1 会把文件存成 UTF-16，导入时报语法错误。因为导出时没加 --databases，文件里没有建库和 USE 语句，才能导进另一个名字的库。定期备份、异地保存和恢复演练的做法见第 20 章 20.4。

排查中文乱码

页面上的中文显示成 中文，或者存进去变成 ???，按下面的顺序从连接查到表，再看库里实际存的字节：

1. 进 app 库：docker compose exec mysql mysql -uroot -p --default-character-set=utf8mb4 app。
2. 看连接这一层，character_set_client、character_set_connection、character_set_results 三项都应是 utf8mb4：SHOW VARIABLES LIKE 'character_set%';。
3. 看表和列的字符集，DEFAULT CHARSET=utf8mb3 或 latin1 就是问题所在：SHOW CREATE TABLE notes\G。
4. 看实际存的字节，“中”的 UTF-8 是 E4B8AD；是 3F 说明存成了问号，是 C3A4C2B8C2AD 说明被编码了两次：SELECT body, HEX(body) FROM notes WHERE id = 1;。
5. 表或列不是 utf8mb4 的，整表转换，大表会锁表较久，先在副本上试：ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;。
6. 程序那一端声明字符集，连接串加 charset=utf8mb4，JDBC 加 characterEncoding=UTF-8：code .env。
7. 再存一行，回到第 4 步看字节是 E4B8AD 开头：INSERT INTO notes (body) VALUES ('中文');。

已经变成 3F 的数据信息丢了，只能从原始数据重新导入。编码了两次的数据在旧程序里看着正常，换个客户端才乱，修复前先导出备份，再用 UPDATE notes SET body = CONVERT(CAST(CONVERT(body USING latin1) AS BINARY) USING utf8mb4); 还原。容器里的 mysql 客户端每次都加 --default-character-set=utf8mb4，是因为容器没设语言环境，客户端可能按 latin1 声明，正是第 2 步要查的情况。

22.5 配置要点

MySQL 的配置文件叫选项文件，格式是 [段名] 下面一行一个 键 = 值，服务端读 [mysqld] 段，客户端读 [client] 段。位置：

范围	路径
配置文件（Linux，Debian/Ubuntu 包）	/etc/mysql/my.cnf，它再读 /etc/mysql/conf.d/ 和 /etc/mysql/mysql.conf.d/ 下的文件
配置文件（Windows 安装包）	my.ini，默认在 C:\ProgramData\MySQL\MySQL Server 8.4\
配置文件（Docker 镜像内）	/etc/my.cnf，它再读 /etc/mysql/conf.d/ 下的 .cnf 文件
SET PERSIST 写的	数据目录下的 mysqld-auto.cnf，最后读，优先于上面的文件
查读取顺序	mysql --help | grep -A1 "Default options"

改法有三种。编辑文件后重启；SET PERSIST 配置项 = 值; 立即生效并保存，只读的项（如 bind-address）要用 SET PERSIST_ONLY 再重启；Docker 里把自己写的 .cnf 挂载到 /etc/mysql/conf.d/，或者在 compose 的 command: 里写成启动参数，如 command: --max-connections=300，改完 docker compose up -d 重建容器。

值得改的项：

配置项	建议值	效果
character-set-server	utf8mb4	新建库的默认字符集；8.0 起默认就是它，写明是为了换版本或换 MariaDB 时不变
collation-server	utf8mb4_0900_ai_ci	默认排序规则；要和 MariaDB 11.4.5 之前的版本互相导数据，改用 utf8mb4_unicode_ci
max_connections	300	最大连接数，默认 151，超出报 Too many connections
slow_query_log	ON	打开慢查询日志，配合 long_query_time = 1 记下超过 1 秒的语句
bind-address	0.0.0.0	监听哪个地址；官方默认 * 是所有地址，Ubuntu 包改成了 127.0.0.1，别的机器连不进来；要重启
default-time-zone	'+00:00'	服务端默认时区，影响 NOW() 和 TIMESTAMP 列的显示；默认跟随系统，官方镜像里是 UTC

my.cnf 的最小片段，# 后面是注释，可以写在行尾：

[mysqld]
character-set-server = utf8mb4          # 新建库默认 utf8mb4
collation-server = utf8mb4_0900_ai_ci   # 默认排序规则，不区分大小写和重音
max_connections = 300                   # 最大连接数，默认 151
slow_query_log = ON                     # 打开慢查询日志
long_query_time = 1                     # 超过 1 秒算慢查询，默认 10
bind-address = 0.0.0.0                  # 监听所有 IPv4 地址，别的机器才连得进来
default-time-zone = '+00:00'            # 服务端默认时区 UTC

[client]
default-character-set = utf8mb4         # 本机 mysql 客户端连接时声明 utf8mb4

22.6 注意事项

• ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)。 原因：口令错或卷里是改 MYSQL_ROOT_PASSWORD 前的口令；Ubuntu 的 root 只认 sudo。处理：旧口令登录再 ALTER USER；Ubuntu 用 sudo mysql。
• 容器里能连，宿主机上的程序报 Access denied for user 'app'@'172.18.0.1'。 原因：只建了 'app'@'localhost'，从宿主机经端口映射进来的来源是 Docker 网关地址。处理：CREATE USER 'app'@'%' IDENTIFIED BY '口令'; 再 GRANT，见 22.2 的图。
• 中文或表情变成 ?，或报 Incorrect string value: '\xF0\x9F\x98\x80'。 原因：列是 utf8（即 utf8mb3）或 latin1，或连接没声明 utf8mb4。处理：ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4;，连接串加 charset=utf8mb4。
• Java 或 DBeaver 连库报 Public Key Retrieval is not allowed。 原因：默认认证插件 caching_sha2_password 在不加密的连接上要先取服务端公钥，驱动默认不许。处理：本机开发在 JDBC URL 加 allowPublicKeyRetrieval=true，DBeaver 在驱动属性里设；生产用加密连接。
• ERROR 1040 (HY000): Too many connections。 原因：连接用完没关，或进程数乘连接池大小超过 max_connections（默认 151）。处理：SHOW FULL PROCESSLIST; 找出谁占着连接；临时 SET PERSIST max_connections = 300;，根本办法见第 20 章 20.5 的连接池。
• mysqldump 导出期间，应用的写入全部卡住，导完才恢复。 原因：没加 --single-transaction 时它默认锁表再导出；MyISAM 表加了也要锁。处理：InnoDB 的库一律加 --single-transaction；把 MyISAM 表转成 InnoDB：ALTER TABLE t ENGINE=InnoDB;。
• 导入旧数据（CSV 或程序写入）报 Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at'。 原因：默认 sql_mode 含 NO_ZERO_DATE 等严格模式，不收全零日期。处理：全零日期改成 NULL；只导一次就 SET SESSION sql_mode = ''; 再导。
• Windows 上好好的，部署到 Linux 报 Table 'app.Orders' doesn't exist。 原因：lower_case_table_names 在 Linux 默认 0，表名区分大小写，Windows 默认 1。处理：代码里的表名和建表时完全一致，统一用小写；这个设置只能在初始化空数据目录时定。
• MySQL 8 的导出导进 MariaDB 报 Unknown collation: 'utf8mb4_0900_ai_ci'。 原因：MariaDB 11.4.5 之前没有它。处理：升级 MariaDB，或替换：sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' app.sql（macOS 写 sed -i ''）。

22.7 深入阅读

• MySQL 8.4 参考手册：官方手册，SQL 语句、数据类型、服务端配置项、账号与权限、复制都在这里。
• mysql 命令行客户端：mysql 客户端的命令行参数、\G \s 等内置命令，以及怎么从文件执行 SQL。
• mysqldump：导出一个库、几张表或全部库的写法，–single-transaction 与锁表的关系。
• 字符集、排序规则与 Unicode：服务端、库、表、列、连接各层字符集怎么定，utf8mb3 与 utf8mb4 的区别。
• MariaDB 文档（原知识库）：MariaDB 的官方文档，含与 MySQL 的兼容性和差异说明。
• 下载：不想用 Docker 时，MySQL 社区版在 Windows、macOS、各 Linux 发行版的官方安装包。