牧樵工坊

mysql基础知识

初始mysql

MySQL 配置文件结构（Ubuntu）

在 Ubuntu 系统中，MySQL（数据库系统）的配置采用“模块化分层结构”。

一、主配置目录

/etc/mysql/

二、整体结构（核心理解）

MySQL 配置由以下几部分组成：

  • 入口文件：my.cnf
  • 核心配置目录：mysql.conf.d/
  • 自定义扩展目录：conf.d/
  • 系统内部配置：debian.cnf

本质：配置分散 + 按顺序加载 + 后者覆盖前者

三、核心文件详解

1️ my.cnf（入口文件）

/etc/mysql/my.cnf

特点：

  • 实际是一个 软链接
  • 不写具体配置
  • 用来“引入”其他配置目录

查看内容：

cat /etc/mysql/my.cnf

典型内容：

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

my.cnf 本质是 配置调度中心（入口）

2️ mysql.conf.d/（核心配置目录）⭐⭐⭐

/etc/mysql/mysql.conf.d/

重点：

绝大多数 MySQL 配置都在这里修改

🔹 mysqld.cnf（服务端配置）

/etc/mysql/mysql.conf.d/mysqld.cnf

作用：

  • 控制 MySQL 服务行为

常见配置：

[mysqld]
bind-address = 127.0.0.1
port = 3306
datadir = /var/lib/mysql

高频修改项：

  • bind-address 👉 是否允许远程连接
  • port 👉 端口
  • datadir 👉 数据目录
  • max_connections 👉 最大连接数
  • character-set-server 👉 字符集

重点：

这是最重要的配置文件（必会）

🔹 mysql.cnf（客户端配置）⭐

/etc/mysql/mysql.conf.d/mysql.cnf

作用：

  • 控制 mysql 命令行工具行为

例如：

  • 默认字符集
  • 自动补全

📌 重点：

影响客户端，不影响服务端

3️⃣ conf.d/（自定义配置目录）⭐⭐

/etc/mysql/conf.d/

作用：

  • 放自定义 .cnf 文件

特点：

  • 自动加载
  • 不影响原文件
  • 推荐使用

示例：

sudo nano /etc/mysql/conf.d/custom.cnf

[mysqld]
max_connections = 200

推荐把自定义配置写这里（避免改系统文件）

4️⃣ debian.cnf（系统内部配置）

/etc/mysql/debian.cnf

作用：

  • Ubuntu 系统脚本连接 MySQL 使用

特点：

  • 包含内部账号密码
  • 权限：600

5️⃣ debian-start（启动脚本）

/etc/mysql/debian-start

作用：

  • MySQL 启动时执行
  • 进行初始化/检查

6️⃣ my.cnf.fallback（备用配置）

/etc/mysql/my.cnf.fallback

作用：

  • 主配置失效时备用

四、配置加载顺序（重要）

加载顺序：

1. /etc/mysql/my.cnf
2. /etc/mysql/conf.d/*.cnf
3. /etc/mysql/mysql.conf.d/*.cnf

规则：

后加载的配置会覆盖前面的配置

五、查看实际加载配置

mysql --help | grep "Default options"

或：

mysqld --verbose --help | grep -A 1 "Default options"

⚙ 六、修改配置标准流程

1️⃣ 修改配置文件

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

或：

sudo nano /etc/mysql/conf.d/custom.cnf

2️⃣ 写入配置

[mysqld]
bind-address = 0.0.0.0
max_connections = 200

3️⃣ 重启服务

sudo systemctl restart mysql

新手建议重点练习：

  • 远程连接配置

    bind-address = 0.0.0.0

  • 字符集配置

    character-set-server = utf8mb4

  • 连接数配置

    max_connections = 200

总结表

文件/目录重要性作用
my.cnf⭐配置入口
mysqld.cnf⭐⭐⭐核心服务端配置
mysql.cnf⭐客户端配置
conf.d/⭐⭐自定义配置
debian.cnf⚠️系统内部使用

《MySQL是怎样运行的：从根儿上理解MySQL》第一章：重新认识MySQL 笔记总结

一、 MySQL 的客户端/服务器架构

MySQL 采用类似于微信的客户端/服务器（C/S）架构：

  1. 服务器程序：负责直接与存储的数据打交道，响应各个客户端发来的增删改查请求并操作数据。
  2. 客户端程序：负责与用户交互，用户通过客户端输入 SQL 命令发送给服务器，并接收服务器返回的操作结果。
    计算机上的这些运行的程序都称为进程。代表 MySQL 服务器程序的进程被称为 MySQL数据库实例（简称数据库实例），默认名称为 mysqld。而常用的 MySQL 客户端进程默认名称为 mysql。

二、 MySQL 的安装与可执行文件

MySQL 安装后（不论是哪种操作系统或安装方式），一定要记住其安装目录。
在安装目录下的 bin 目录中，存放着大量重要的可执行文件，这些文件与服务器程序和客户端程序密切相关（例如 mysqld、mysql、mysqld_safe 等）。

  • 启动方式：可以通过相对路径、绝对路径执行这些文件。为了方便，通常建议将 bin 目录的路径添加到操作系统的环境变量 PATH 中，这样就可以在任何目录下直接输入命令启动程序 。

三、 启动 MySQL 服务器程序

  1. 在类 UNIX 系统中的启动方式 ：
  • mysqld：直接启动一个服务器进程（不常用）。
  • mysqld_safe：启动脚本，会间接调用 mysqld，并启动一个监控进程以在服务器挂掉时重启它；还会将出错诊断信息输出到错误日志中。
  • mysql.server：也是启动脚本，间接调用 mysqld_safe。通过 mysql.server start 启动，mysql.server stop 关闭服务器。
  • mysqld_multi：用于监控和管理运行在同一台计算机上的多个 MySQL 服务器实例。
  1. 在 Windows 系统中的启动方式 ：
  • mysqld：在命令行直接输入或双击运行。
  • 以服务的方式运行：通过命令 "完整可执行文件路径" --install [服务名] 注册为 Windows 服务。注册后可使用 net start MySQL 启动服务，net stop MySQL 停止服务。

四、 启动 MySQL 客户端程序

使用 mysql 可执行文件可以与服务器进行交互。启动客户端时常用的参数格式为：mysql -h主机名 -u用户名 -p密码。

  • -h：指定服务器所在的主机名或 IP（本机的可以省略或用 localhost / 127.0.0.1）。
  • -u：指定用户名。
  • -p：指定密码（建议单独使用 -p 并在提示后隐藏输入，以防密码泄露。若写在同一行，-p 与密码间不能有空格）。
  • 断开连接可输入：quit、exit 或 \q。

五、 客户端与服务器的通信方式

本质上是进程间通信，MySQL 支持以下三种方式：

  1. TCP/IP：最通用的网络通信方式，跨主机及本机均适用。MySQL 服务器默认监听 3306 端口 。客户端启动时可用 -P（大写P）参数指定非默认端口 。
  2. 命名管道和共享内存：专为 Windows 系统用户提供的本地通信方式。需在启动服务器和客户端时添加相应参数（如 --shared-memory）。
  3. Unix 域套接字文件：在 类 Unix 系统下，如果客户端和服务器同在一台机器，可以使用此方式。默认套接字文件路径为 /tmp/mysql.sock。

六、 服务器处理客户端请求的流程

当服务器收到客户端发送的文本（MySQL 语句）请求后，大致经过三个部分进行处理并返回结果 ：

  1. 连接管理：
  • 服务器会为每个连接进来的客户端分配一个专门的线程进行交互，并在客户端退出时缓存该线程以便复用，从而节省创建和销毁线程的开销 。
  • 进行身份认证及支持 SSL 加密通信 。
  1. 解析与优化：包含三个重要阶段 ：
  • 查询缓存（MySQL 8.0 中已删除）：如果查询请求与之前完全一致且数据未被修改，则直接从缓存返回结果。任何不同（如空格、大小写）或包含动态函数（如 NOW()），甚至表数据/结构被修改，都会导致缓存不命中或失效 。
  • 语法解析：对请求的文本进行词法和语法分析，提取表名、列名、查询条件等，放入内部数据结构中 。
  • 查询优化：对 SQL 语句进行优化（如外连接转内连接、表达式简化等），生成包含索引使用和表连接顺序的执行计划（可用 EXPLAIN 查看）。
  1. 存储引擎：
  • 负责表数据的物理存储和提取。为了方便管理，MySQL 把“连接管理、解析与优化”这些功能归为 MySQL server 层，把数据存取功能归为 存储引擎层。
  • server 层优化完毕后，只需调用底层存储引擎提供的统一 API 接口即可获取数据 。

七、 常用存储引擎

MySQL 提供了多种存储引擎以应对不同场景（如 InnoDB、MyISAM、Memory 等），其中最常用的是 InnoDB（MySQL 默认的存储引擎，支持事务和外键）和 MyISAM。

  • 查看服务器支持的引擎：SHOW ENGINES;。
  • 创建表时指定引擎：在建表语句后添加 ENGINE = 引擎名称（例如 ENGINE = MyISAM）。
  • 修改已有表的引擎：ALTER TABLE 表名 ENGINE = 引擎名称;。

第二章：MySQL的调控按钮-启动选项和系统变量 学习笔记

一、 启动选项的设置方式

启动选项用于在MySQL程序启动时修改其默认设置，控制程序的行为。主要有两种设置方式：在命令行中设置和在配置文件中设置。
1. 在命令行上使用选项

  • 基本格式：--启动选项1[=值1] --启动选项2[=值2] ...

  • 长形式与短形式：

    • 长形式：如 --skip-networking、--default-storage-engine=MyISAM。可以使用短划线 - 或下划线 _ 连接单词。不需要值的选项直接写名字即可；需要值的选项，选项名、=、选项值之间不能有空白字符。
    • 短形式：如 -h (等同于 --host)、-P (等同于 --port)、-p (等同于 --password)、-u (等同于 --user)。使用时前面只加一个短划线 -，且选项名和选项值之间可以没有间隙或用空格隔开（注意：-p 和密码值之间不能有空格）。选项名区分大小写。
      2. 在配置文件中使用选项（推荐）
      为了避免每次启动都在命令行输入一长串选项，可以将它们写入配置文件（也称选项文件）。

  • 配置文件的路径：

    • MySQL启动时会在固定的多个路径下搜索配置文件（例如Windows下的 C:\my.ini，Linux下的 /etc/my.cnf、~/.my.cnf 等）。
    • 可在启动命令中使用 --defaults-extra-file=路径 指定额外的配置文件，或者用 --defaults-file=路径 让MySQL只在该路径下搜索配置文件。

  • 配置文件的语法与内容：

    • 按选项组（Group）划分，用中括号包围组名，如 [server]、[mysqld]、[client]、[mysql] 等。
    • [server] 组的选项作用于所有服务器程序；[client] 组的作用于所有客户端程序。特定组的选项只对其对应的程序生效（例如 [mysqld] 组专供 mysqld 服务器程序使用）。
    • 配置文件中只能使用长形式选项，不需要加 -- 前缀，每行只指定一个选项，= 周围可以有空格，使用 # 作为注释。

  • 多版本专属选项组：支持像 [mysqld-5.7] 这种带版本号的选项组，只有版本号为 5.7 的 mysqld 程序才会读取。

  • 优先级规则：

    • 文件之间：如果多个配置文件中设置了相同的启动选项，以后读取的配置文件（排在搜索路径后面的）为准。
    • 组之间：如果同一个配置文件中的多个组出现了同名选项，以最后一个出现的组为准。
    • 命令行与配置文件之间：如果同一个选项既在配置文件中，又在命令行中，以命令行中的启动选项为准。

二、 系统变量

MySQL 服务器程序运行过程中用到的一系列影响其行为的变量，称为系统变量（如 max_connections、default_storage_engine 等）。
1. 查看系统变量

  • 语法：SHOW VARIABLES [LIKE 匹配的模式]; （例如：SHOW VARIABLES LIKE 'default%';）
    2. 设置系统变量

  • 通过启动选项设置：在程序启动时通过命令行或配置文件传递。注意：作为启动选项时可以用横划线 - 也可以用下划线 _，但作为系统变量名时必须使用下划线 _。

  • 服务器运行过程中动态修改：大部分系统变量无需重启服务器即可在运行中修改。
    3. 系统变量的作用范围 (GLOBAL 和 SESSION)
    为了解决“影响全局”与“只影响某个特定客户端”的冲突，系统变量分为两种作用范围：

  • GLOBAL（全局变量）：影响服务器的整体操作。通过启动选项设置的变量作用范围都是 GLOBAL。

  • SESSION（会话变量）：当客户端连接到服务器时，服务器会为该客户端分配一组专属的 SESSION 变量，其初始值根据当时的 GLOBAL 变量值进行初始化。

  • 动态修改语法：

    • 全局：SET GLOBAL 系统变量名 = 值; 或 SET @@GLOBAL.系统变量名 = 值;
    • 会话：SET SESSION 系统变量名 = 值; 或 SET @@SESSION.系统变量名 = 值; 或直接 SET 系统变量名 = 值;（省略作用范围默认就是 SESSION）。

  • 查看语法：SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];（省略作用范围默认查看的是 SESSION）。

  • 注意事项：

    • 并不是所有系统变量都同时拥有 GLOBAL 和 SESSION 范围。有些只有 GLOBAL（如 max_connections），有些只有 SESSION（如 insert_id）。
    • 有些系统变量是只读的（如 version 版本号），不允许修改。
      4. 启动选项与系统变量的区别

  • 启动选项是在程序启动时传递的参数，而系统变量是影响服务器程序运行行为的变量。

  • 大部分系统变量可以作为启动选项传入；但有些启动选项不是系统变量（如 --defaults-file）；有些系统变量在程序运行过程中自动生成，不能作为启动选项设置。

三、 状态变量

为了让我们更好地了解服务器程序的运行情况，MySQL 维护了许多运行状态信息，称为状态变量（如 Threads_connected 当前连接数）。

  • 特点：状态变量只能由服务器程序自己设置和更新，用户是只读的，不能使用 SET 语句修改。
  • 作用范围：状态变量也分为 GLOBAL 和 SESSION 两个作用范围。
  • 查看语法：SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];（如果不写明作用范围，默认是 SESSION 范围）。

总结概括：
本章的核心在于掌握“如何对MySQL下达指令来改变它的行为”。启动前可以通过命令行或配置文件（推荐） 下达启动选项；启动后可以通过 系统变量（GLOBAL/SESSION） 动态调整其运行行为，同时可以通过 状态变量 来实时监控数据库的健康和运行状态。理解 GLOBAL 和 SESSION 的隔离机制是日常进行数据库调优和排错的基础。

第三章：乱码的前世今生-字符集和比较规则 学习笔记

一、 字符集和比较规则的基础概念

  1. 字符集（Character Set）：计算机中只能存储二进制数据，将字符映射成二进制数据的过程称为“编码”，将二进制数据映射回字符的过程称为“解码”。描述某个字符范围及其编码规则的集合就是字符集。
  2. 比较规则（Collation）：针对某个特定字符集中的字符，规定它们如何比较大小和排序的规则。一种字符集可以对应多种比较规则，但一种比较规则只能依附于某一个具体的字符集。每种字符集都会有一个默认的比较规则。

二、 MySQL中的字符集与比较规则

  1. utf8 与 utf8mb4 的坑：
    • 在 MySQL 中，普通的 utf8 其实是 utf8mb3 的别名，它是个“阉割版”，最多只能使用 1~3 个字节表示一个字符。
    • 这意味着 MySQL 的 utf8 无法存储一些生僻字或者 Emoji 表情（这类字符需要 4 个字节）。如果需要存储这些字符，必须使用真正的 UTF-8，也就是 MySQL 中的 utf8mb4。
  2. 查看字符集：
    • 命令：SHOW CHARSET; 或 SHOW CHARACTER SET;
    • 查询结果中的 Maxlen 列非常重要，它代表该字符集表示一个字符最多需要的字节数（例如 ascii 是 1，gbk 是 2，utf8 是 3，utf8mb4 是 4）。
  3. 查看比较规则：
    • 命令：SHOW COLLATION;
    • 命名规律：通常是字符集名称_语言_后缀。
    • 常见后缀：_ci（不区分大小写）、_cs（区分大小写）、_bin（以二进制方式直接比较）。

三、 字符集和比较规则的四个级别

MySQL 提供了非常灵活的字符集和比较规则设置，分为 4 个级别，且具备向下继承的特性：

  1. 服务器级别：系统变量 character_set_server 和 collation_server 决定了服务器的默认字符集和比较规则。
  2. 数据库级别：创建或修改数据库时可指定。若不指定，默认继承服务器级别。可通过系统变量 character_set_database 和 collation_database 查看当前数据库的设置（这两个变量是只读的，不能直接修改）。
  3. 表级别：创建或修改表时可指定。若不指定，默认继承表所在数据库的级别。
  4. 列级别：针对具体的字符串类型的列可单独指定。若不指定，默认继承该列所在表的级别。
  • 联动规则：在任何一个级别中，如果只设置了字符集，比较规则会自动变为该字符集的默认规则；如果只设置了比较规则，字符集也会自动变为该比较规则对应的字符集。

四、 客户端与服务器通信中的字符集转换（解决乱码的核心）

charset.png
MySQL 客户端向服务器发送请求以及服务器返回结果时，本质上发送和接收的都是字节串。在这个过程中，MySQL 会进行多次字符集转换，这由三个核心系统变量控制：

  1. character_set_client：服务器认为客户端发送过来的请求是采用这个字符集编码的，并据此进行解码。
  2. character_set_connection：MySQL 内部解析 SQL 时使用的编码。服务器解码请求后，会再将其转换为该系统变量指定的字符集进行内部处理。（注：如果具体操作的列使用了不同的字符集，此时还会再进行一次转换）。
  3. character_set_database = utf8mb4：当前数据库默认字符集，新建表默认继承它
  4. character_set_results：服务器处理完毕后，会将查询到的结果从列使用的字符集转换成该系统变量指定的字符集，然后再把字节串发送给客户端。
  • 避免乱码的最佳实践：为了防止在发送和接收过程中出现乱码，通常应当保持 character_set_client、character_set_connection、character_set_results 这三个变量的值与客户端实际使用的字符集完全一致。
  • 快捷命令：MySQL 提供了 SET NAMES 字符集名称; 命令，执行它等同于同时将上述三个系统变量设置为指定的字符集。

五、 比较规则的作用场景

比较规则主要应用在两个场景：

  1. 比较字符串大小的表达式（如 WHERE col = 'A'）。
  2. 对字符串列进行排序（如 ORDER BY col）。
    如果发现数据库在进行字符串排序或匹配时没有区分大小写（或者预想中不该区分却区分了），绝大多数情况下是因为比较规则设置不当。例如，列使用了以 _ci 结尾的比较规则，那么查询时无论是大写还是小写，都会被视为相同。

utf8mb3 vs utf8mb4（MySQL 深度解析）

utf8mb3 = 阉割版 UTF-8
utf8mb4 = 标准 UTF-8

UTF-8 是变长编码：

字节数可表示内容
1 字节ASCII（英文）
2 字节常见语言
3 字节中文（大部分）
4 字节emoji / 生僻字

示例：

VARCHAR(255)
字符集占用是否超限
utf8mb3255 × 3 = 765✅ OK
utf8mb4255 × 4 = 1020⚠️ 可能超限

site logo

Hi,MuQiao

© 2026 牧樵工坊