MariaDB 数据库

数据库介绍

**数据库，是一个存放计算机数据的仓库。**这个仓库是按照一定的数据结构来对数据进行组织和存储的，我们可以通过数据库提供的多种方法来管理其中的数据。

MariaDB 介绍

MariaDB数据库管理系统是MySQL数据库的一个分支，主要由开源社区维护，采用GPL授权许可。开发这个MariaDB数据库分支的可能原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此MySQL开源社区采用分支的方式来避开这个风险。

MariaDB数据库完全兼容MySQL数据库，包括API和命令行，使之能轻松的成为MySQL的代替品。

MariaDB 部署

安装数据库

# 安装服务端
[root@server ~ 09:50:04]# yum install -y mariadb-server

# 安装客户端
[root@server ~]# yum install -y mariadb

# 启用并启动服务
[root@server ~ 09:50:51]# systemctl enable --now mariadb

# 配置防火墙
[root@server ~ 09:50:51]# firewall-cmd --permanent --add-service=mysql
[root@server ~ 09:50:51]# firewall-cmd --reload

数据库进程

MariaDB 启动后同时出现 mysqld_safe 和 mysqld 两个进程是完全正常的，这是 MariaDB（以及 MySQL）的标准运行机制。

核心进程

mariadb 服务有两个进程：

  1. mysqld_safe 是一个监控和守护程序，主要负责：

    • 安全启动 mysqld 核心进程，处理启动前的环境检查（比如配置文件加载、权限验证）；

    • 实时监控 mysqld 进程状态，如果 mysqld 意外崩溃 / 退出，mysqld_safe 会自动重启它，保证服务不中断；

    • 统一管理日志输出（把 mysqld 的日志重定向到指定日志文件，比如 error.log）；

    • 处理一些启动参数的兼容转换，简化用户的启动配置。

  2. mysqld 是 MariaDB 的核心进程，数据库的所有核心功能都由它实现：

    • 处理客户端的连接请求（比如 SQL 查询、数据写入）；

    • 管理内存、磁盘数据（表、索引、事务等）；

    • 执行 SQL 语句、维护数据一致性；

    • 负责锁机制、事务处理、复制等核心功能。

进程关系

两者是父进程 - 子进程的关系：mysqld_safe 是父进程，mysqld 是它启动的子进程。通过以下命令验证：

# 查看进程的父子关系
ps -ef | grep -E 'mysqld_safe|mysqld'

# 或用 pstree 查看进程树（更直观）
pstree | grep mysqld

输出示例（核心部分）：

root      1234     1  0 10:00 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock
mysql     1456  1234  0 10:00 ?        00:00:05 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql ...

可以看到：

  • mysqld_safe 由 root 用户运行（需要更高权限做环境准备）；
  • mysqld 由 mysql 普通用户运行（安全原则），且父进程 ID 是 mysqld_safe 的进程 ID（1234）。

加固数据库

MariaDB数据库默认具有test数据库和一些不太安全的配置。运行mysql_secure_installation修改这些配置。

[root@server ~ 10:03:53]# mysql_secure_installation

交互式提示您进行更改，包括：

  • 为 root 帐户设置密码。
  • 禁止 root 帐户从本地主机外部访问数据库。
  • 删除 匿名 用户帐户。
  • 删除用于演示的 test 数据库。

配置数据库

默认情况下，MariaDB 侦听系统中所有网络地址上3306/TCP端口。

在 CentOS 7 中，MariaDB 的配置文件采用 “主配置文件 + 细分配置文件” 的分层结构，核心文件集中在 /etc/my.cnf 和 /etc/my.cnf.d/ 目录下。

主配置文件

/etc/my.cnf，MariaDB 的全局主配置文件，是服务启动时优先读取的配置入口。

  • 本身可直接写入所有配置参数（覆盖客户端、服务器端配置）；
  • 默认会通过 !includedir /etc/my.cnf.d/ 指令，加载 /etc/my.cnf.d/ 目录下的所有 .cnf 细分配置文件；
  • 优先级最高：若细分配置文件与主文件参数冲突，以 my.cnf 中的配置为准。
细分配置文件

/etc/my.cnf.d/ 目录下文件是细分配置文件。

  • /etc/my.cnf.d/server.cnf，专门配置 MariaDB 服务器端（mysqld 进程）的参数，仅对数据库服务本身生效。

    参数需放在 [mysqld]、[mysqld_safe] 等服务器端专属段落中。

    常见配置项：

    • 端口（port = 3306）、监听地址（bind-address = 0.0.0.0）；
    • 数据目录（datadir = /var/lib/mysql）、缓存大小（innodb_buffer_pool_size）；
    • 禁用网络连接（skip-networking = 1），禁用网络连接，客户端只能使用套接字文件与MariaDB通信。 如果您设置skip-networking = 0（默认值），MariaDB会侦听网络连接。
    • 存储引擎（default-storage-engine = InnoDB）、日志配置（log_error、slow_query_log）。

  • /etc/my.cnf.d/client.cnf，配置所有 MariaDB 客户端工具的通用参数（如 mysql、mysqldump、mysqladmin 等），简化客户端连接操作。

  • /etc/my.cnf.d/mysql-clients.cnf，与 client.cnf 功能类似，也是配置客户端工具，更偏向于 mysql 命令行客户端的专属优化（部分系统中与 client.cnf 功能重叠，可互补使用）。

连接数据库

连接本地数据库

mysql命令可以通过本地特殊的套接字文件(socket)与数据库通信。 这种方式更安全，因为MariaDB不需要侦听来自网络客户端的连接。 但是使用该数据库的服务都需要在MariaDB服务器上运行，会影响性能。

[root@server ~ 10:22:41]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

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)]>
连接远端数据库

客户端可以使用TCP/IP网络连接到MariaDB服务，此时服务器需要配置侦听端口3306/TCP上的连接。

环境准备

# 数据库服务器本机创建连接用户
[root@server ~ 10:17:34]# mysql -u root -p
MariaDB [(none)]> create user laoma identified by '123';
MariaDB [(none)]> grant all privileges on *.* to ggg;
MariaDB [(none)]> flush privileges;

# 客户端测试
[root@client ~ 10:22:35]# mysql -u ggg -p123 -h server
非交互方式连接数据库

mysql命令可以通过-e选项，以非交互方式连接数据库。

[root@server ~ 10:21:55]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

配置客户端

配置文件

  • /etc/my.cnf.d/client.cnf，配置所有 MariaDB 客户端工具的通用参数（如 mysql、mysqldump、mysqladmin 等），简化客户端连接操作。

    参数需放在 [client] 通用段落中（所有客户端工具都会读取）。

    常见配置项：

    • 默认用户名（user = root）、默认密码（password = 123456，不推荐明文存储）；
    • 默认主机（host = localhost）、默认端口（port = 3306）；
    • 字符集（default-character-set = utf8mb4）。

  • /etc/my.cnf.d/mysql-clients.cnf，与 client.cnf 功能类似，也是配置客户端工具，更偏向于 mysql 命令行客户端的专属优化（部分系统中与 client.cnf 功能重叠，可互补使用）。

    优先级低于 client.cnf，若两者配置冲突，以 client.cnf 为准。

配置登录信息
[root@client ~ 10:24:41]# vim /etc/my.cnf.d/client.cnf
[client]
user=ggg
password=123
host=server
port=3306
database=mysql

# 输入mysql命令可以直接登录数据库
[root@client ~ 10:26:57]# mysql
MariaDB [(mysql)]>
配置登录提示符
[root@client ~ 10:28:14]# vim /etc/my.cnf.d/client.cnf
[client]
......
prompt="\\u@\\h [\\d]> "

# 登录效果如下
[root@client ~ 10:31:35]# mysql -u ggg -p123 -h 10.1.8.10 mysql
......
ggg@10.1.8.10 [mysql]>

结构化查询语言

结构化查询语言

SQL(Structured Query Language)，结构化查询语言，是1986年10 月由美国国家标准局颁布的数据库语言。国际标准化组织（ISO）也颁布了SQL国际标准。

结构化查询语言是关系数据库最重要的操作语言，它的影响已经超出数据库领域，得到其他领域的重视和采用，如人工智能领域的数据检索，第四代软件开发工具中嵌入SQL的语言等。

SQL语句可简单分为如下几类：

  • 数据查询语言（DQL：Data Query Language）：也称为“数据检索语句”，用以从表中获得数据，保留字SELECT WHERE，ORDER BY，GROUP BY和HAVING。
  • 数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT，UPDATE和DELETE。
  • 数据定义语言（DDL）：其语句包括动词CREATE和DROP。例如，在数据库中创建新表（CREATE TABLE）、删除表（DROP TABLE），为表添加索引等。
  • 事务处理语言（TPL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
  • 数据控制语言（DCL）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。

数据库操作

查询数据库列表
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.019 sec)

数据库说明：

  • mysql 数据库，是一个系统数据库，保存数据库用户及其访问权限等信息。
  • INFORMATION_SCHEMA 数据库，保存关于数据库或者数据表的元数据信息。
  • PERFORMANCE_SCHEMA 数据库，保存数据库服务器性能信息。
使用数据库

您可以使用 USE 语句选择数据库之一，例如：USE mysql;，后续默认操作的表属于mysql数据库。

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]>

SQL语句不区分大小写，但数据库名称区分大小写。 通常的做法是数据库名称全为小写字母，SQL语句全为大写字母，以区分SQL语句与语句的目标或参数。

创建数据库
MariaDB [mysql]> CREATE DATABASE ggg;
Query OK, 1 row affected (0.010 sec)

MariaDB [mysql]> USE ggg;
Database changed
删除数据库

DROP DATABASE语句删除数据库中的所有表并删除数据库。 这将破坏数据库中的所有数据。 只有对该数据库具有DROP特权的用户才能运行此语句。 这不会更改数据库的用户特权。 如果重新创建具有该名称的数据库，则为旧数据库设置的用户权限仍然有效。

MariaDB [inventory]> DROP DATABASE ggg;
Query OK, 0 rows affected (0.006 sec)

表操作

SQL CRUD操作（create 添加数据 read读取数据 update 修改数据delete删除数据）。

环境准备
# 导入备份
[root@client ~ 11:10:42]# wget http://192.168.46.200/course-materials/softwares/stage01/inventory.middle.sql
[root@client ~ 11:11:00]# mysql -uggg -p123 -h server inventory <inventory.middle.sql
查询表
查询表列表
# 查询表列表
[root@client ~ 11:11:32]# mysql -uggg -p123 -h server inventory -e 'show tables;' 
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
+---------------------+
查询表结构
MariaDB [inventory]> DESCRIBE product;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(100) | NO   |     | NULL    |                |
| price           | double       | NO   |     | NULL    |                |
| stock           | int(11)      | NO   |     | NULL    |                |
| id_category     | int(11)      | NO   |     | NULL    |                |
| id_manufacturer | int(11)      | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.008 sec)

MariaDB [inventory]>
查询表中数据

查询表中所有记录所有字段

MariaDB [inventory]> SELECT * FROM product;
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
|  1 | ThinkServer TS140 |  539.88 |    20 |           2 |               4 |
|  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 |
|  3 | RT-AC68U          |  219.99 |    10 |           1 |               3 |
|  4 | X110 64GB         |   73.84 |   100 |           3 |               1 |
+----+-------------------+---------+-------+-------------+-----------------+
4 rows in set (0.004 sec)

MariaDB [inventory]> SELECT * FROM category;
+----+------------+
| id | name       |
+----+------------+
|  1 | Networking |
|  2 | Servers    |
|  3 | Ssd        |
+----+------------+
3 rows in set (0.001 sec)

MariaDB [inventory]> SELECT * FROM manufacturer;
+----+----------+----------------+-------------------+
| id | name     | seller         | phone_number      |
+----+----------+----------------+-------------------+
|  1 | SanDisk  | John Miller    | +1 (941) 329-8855 |
|  2 | Kingston | Mike Taylor    | +1 (341) 375-9999 |
|  3 | Asus     | Wilson Jackson | +1 (432) 367-8899 |
|  4 | Lenovo   | Allen Scott    | +1 (876) 213-4439 |
+----+----------+----------------+-------------------+
4 rows in set (0.001 sec)

MariaDB [inventory]>

查询表中所有记录特定字段

MariaDB [inventory]> SELECT name,price,stock FROM product;
+-------------------+---------+-------+
| name              | price   | stock |
+-------------------+---------+-------+
| ThinkServer TS140 |  539.88 |    20 |
| ThinkServer RD630 | 2379.14 |    20 |
| RT-AC68U          |  219.99 |    10 |
| X110 64GB         |   73.84 |   100 |
+-------------------+---------+-------+
4 rows in set (0.001 sec)
WHERE 子句
MariaDB [inventory]> SELECT * FROM product WHERE price > 100;
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
|  1 | ThinkServer TS140 |  539.88 |    20 |           2 |               4 |
|  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 |
|  3 | RT-AC68U          |  219.99 |    10 |           1 |               3 |
+----+-------------------+---------+-------+-------------+-----------------+
3 rows in set (0.020 sec)

条件操作数

Operator Description
= Equal
<> Not equal. Note: In some versions of SQL, this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN Specify multiple possible values for a column 
# 条件操作符包括：=、<>、>、<、>=、<=
MariaDB [inventory]> SELECT * FROM product WHERE price > 100;

# BETWEEN，匹配2个数字之间（包括数字本身）的记录。
MariaDB [inventory]> SELECT * FROM product WHERE id BETWEEN 1 AND 3;

# IN，匹配列表中记录。
MariaDB [inventory]> SELECT * FROM product WHERE id IN (1,3);
MariaDB [inventory]> SELECT * FROM category WHERE name IN ('Servers','Ssd');

# LIKE，用于匹配字符串。%表示一个或多个字符，_表示一个字符，[charlist]表示字符列中的任何单一字符，[^charlist]或者[!charlist]不在字符列中的任何单一字符。
MariaDB [inventory]> SELECT * FROM product WHERE name like '%Server%';

# 逻辑与AND
MariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' AND price>1000;

# 逻辑或or
MariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' OR price>500;

# ORDER BY 关键字用于对结果集进行排序。
MariaDB [inventory]> SELECT * FROM product ORDER BY price;
MariaDB [inventory]> SELECT * FROM product ORDER BY price desc;
多表查询
# 示例1：产品类型是Servers的产品名称和价格
MariaDB [inventory]> SELECT product.name,product.price
FROM product,category
WHERE product.id_category = category.id 
AND category.name='Servers';

# 示例2：查询厂商是Lenovo的产品名称和价格
MariaDB [inventory]> SELECT product.name,product.price
FROM product,manufacturer 
WHERE product.id_manufacturer = manufacturer.id
AND manufacturer.name='Lenovo';
函数
# 示例1：查询产品价格平均值
MariaDB [inventory]> SELECT avg(price) FROM product;

# 示例2：查询产品价格最大值
MariaDB [inventory]> SELECT max(price) FROM product;

# 示例3：查询产品价格最小值
MariaDB [inventory]> SELECT min(price) FROM product;

# 示例4：查询产品存量
MariaDB [inventory]> SELECT sum(stock) FROM product;

# 示例5：查询产品价格最小值的那个产品信息
MariaDB [inventory]> SELECT min(price) FROM product;
MariaDB [inventory]> SELECT * FROM product WHERE price=73.84;
或者
MariaDB [inventory]> SELECT * FROM product WHERE price=(SELECT min(price) FROM product);

# 示例6：查询Lenovo厂商提供了几种产品
MariaDB [inventory]> SELECT count(product.name)
FROM product,manufacturer 
WHERE product.id_manufacturer = manufacturer.id
AND manufacturer.name='Lenovo';

# GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
MariaDB [inventory]> SELECT id_category,sum(stock) FROM product GROUP BY id_category;
创建表
MariaDB [inventory]> CREATE TABLE staff(
id INT(11) NOT NULL,
name VARCHAR(100) NOT NULL,
age INT(11)  DEFAULT 10,
id_department INT(11) 
);
Query OK, 0 rows affected (0.017 sec)
MariaDB [inventory]> SHOW TABLES;
插入记录
MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department)
VALUES (1,'g1',28,10);
MariaDB [inventory]> INSERT INTO staff (id,name,age) VALUES (2,'g2',20);
MariaDB [inventory]> INSERT INTO staff (id,name) VALUES (3,'g3');
更新记录
MariaDB [inventory]> UPDATE staff SET age=30 WHERE id=3;
MariaDB [inventory]> UPDATE staff SET age=30

如果使用不带WHERE子句的UPDATE，则表中的所有记录都会更新。

删除记录
MariaDB [inventory]> DELETE FROM staff WHERE id=3 ;
MariaDB [inventory]> DELETE FROM staff ;

如果使用不带WHERE子句的DELETE子句，则表中的所有记录都会删除。

删除表
MariaDB [inventory]> DROP TABLE staff ;

管理数据库用户

创建用户账户

MariaDB [mysql]> create user ggg@'%' identified by '123';

当前，ggg帐户可以使用密码123从**%**连接。

用户的密码，会被加密存在在mysql.user表：

MariaDB [mysql]> select host,user,password from user where user = 'ggg';
+------+------+-------------------------------------------+
| host | user | password                                  |
+------+------+-------------------------------------------+
| %    | ggg  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)

默认情况下，新帐户被授予最小特权。 在不授予其他特权的情况下ggg用户只能访问最少的帐户信息，大多数其他操作均被拒绝。

查询有哪些用户

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| ggg  | %         |
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

授予用户权限

GRANT [权限1, 权限2, ...]
ON [作用范围]
TO '用户名'@'主机地址'
[IDENTIFIED BY '密码'];

权限可以是单权限，也可以是逗号分隔的多权限组合。

作用范围 授权对象格式 适用场景 核心特点
全局范围 *.* 管理员/超级用户权限 覆盖整个 MySQL 实例所有资源
数据库范围 数据库名.* 业务库专属权限（如电商库） 仅覆盖指定数据库的所有对象
表范围 数据库名.表名 单表专属权限（如订单表） 仅覆盖指定库的指定表
列范围 数据库名.表名(列1,列2) 敏感列管控（如手机号/密码） 仅覆盖表的指定列
1. 全局范围

管理员级全局权限是指作用于 *.*（所有数据库、所有表）的最高级别权限，通常只授予数据库管理员（DBA）。

常用权限

权限名称 作用
CREATE USER 创建、删除、重命名其他用户，以及为其他用户授权（这是用户管理的核心权限）
SUPER 执行超级管理员操作，例如终止任意连接、修改全局系统变量、主从复制管理等
PROCESS 查看所有用户的连接进程（SHOW PROCESSLIST），用于排查问题
RELOAD 执行 FLUSH 系列命令（如 FLUSH PRIVILEGES、FLUSH LOGS），使配置生效
SHUTDOWN 关闭 MariaDB 服务
FILE 读写服务器上的文件（如 LOAD DATA INFILE），需谨慎授予
CREATE 创建数据库、表、视图等对象
ALTER 修改数据库、表的结构
DROP 删除数据库、表等对象
INSERT/UPDATE/DELETE/SELECT 对所有表进行增删改查操作

示例

-- 示例1：超级管理员（完全控制）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

-- 示例2：用户管理管理员
GRANT CREATE USER, RELOAD ON *.* TO 'user_admin'@'localhost';

-- 示例3：数据库管理员
GRANT CREATE, DROP  ON *.* TO 'db_admin'@'localhost';

-- 示例4：运维监控管理员
GRANT PROCESS, RELOAD, SUPER ON *.* TO 'ops_admin'@'localhost';

-- 示例5：授予只读管理员全局查询权限（监控/报表）
GRANT SELECT ON *.* TO 'monitor'@'%';
2. 数据库范围

数据库级权限作用于 数据库名.* 范围，仅对指定数据库生效，无法跨库操作；所有权限授权后都需执行 FLUSH PRIVILEGES; 生效。

数据操作类（增删改查），这类权限控制对数据库内表数据的读写操作，是最基础的业务权限：

权限名称 作用 授权示例
SELECT 允许查询数据库内所有表的数据（读权限） GRANT SELECT ON app_db.* TO 'laowang'@'localhost';
INSERT 允许向数据库内所有表插入数据（写权限） GRANT INSERT ON app_db.* TO 'laowang'@'localhost';
UPDATE 允许修改数据库内所有表的数据 GRANT UPDATE ON app_db.* TO 'laowang'@'localhost';
DELETE 允许删除数据库内所有表的数据 GRANT DELETE ON app_db.* TO 'laowang'@'localhost';

结构管理类（表 / 索引 / 视图管理），这类权限控制数据库内表、索引、视图等对象的创建 / 修改 / 删除，属于「结构层」权限：

权限名称 作用 授权示例
CREATE 允许在数据库内创建表、视图、索引 GRANT CREATE ON app_db.* TO 'laowang'@'localhost';
ALTER 允许修改数据库内表的结构（如 ALTER TABLE） GRANT ALTER ON app_db.* TO 'laowang'@'localhost';
DROP 允许删除数据库内的表、视图 GRANT DROP ON app_db.* TO 'laowang'@'localhost';
INDEX 允许为数据库内的表创建 / 删除索引（注：CREATE 权限已包含此能力） GRANT INDEX ON app_db.* TO 'laowang'@'localhost';
CREATE VIEW 允许在数据库内创建视图 GRANT CREATE VIEW ON app_db.* TO 'laowang'@'localhost';
SHOW VIEW 允许查看数据库内视图的定义（如 SHOW CREATE VIEW） GRANT SHOW VIEW ON app_db.* TO 'laowang'@'localhost';

**特殊操作类，**这类权限针对数据库内的特殊操作，如存储过程、锁表等：

权限名称 作用 授权示例
CREATE ROUTINE 允许在数据库内创建存储过程 / 函数 GRANT CREATE ROUTINE ON app_db.* TO 'laowang'@'localhost';
ALTER ROUTINE 允许修改 / 删除数据库内的存储过程 / 函数 GRANT ALTER ROUTINE ON app_db.* TO 'laowang'@'localhost';
EXECUTE 允许执行数据库内的存储过程 / 函数 GRANT EXECUTE ON app_db.* TO 'laowang'@'localhost';
LOCK TABLES 允许锁定数据库内的表（如 LOCK TABLES） GRANT LOCK TABLES ON app_db.* TO 'laowang'@'localhost';

示例

-- 示例1：业务全权限（增删改查 + 结构管理）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP 
ON app_db.* 
TO 'laowang'@'localhost';

-- 示例2：只读权限（仅查询）
GRANT SELECT, SHOW VIEW 
ON app_db.* 
TO 'report_user'@'localhost';

-- 示例3： 仅结构管理权限（无数据操作）
GRANT CREATE, ALTER, DROP, INDEX 
ON app_db.* 
TO 'dba_user'@'localhost';
3. 表范围

表级权限作用于 数据库名.表名 范围，仅对指定数据库的指定表生效，不会影响同库其他表；所有授权后需执行 FLUSH PRIVILEGES; 使权限立即生效。

核心数据操作类（最常用），这类权限控制对单表数据的读写，是业务场景中最基础的表级权限：

权限名称 作用 授权示例（以 app_db.users 表为例）
SELECT 允许查询该表的所有列数据（读权限） GRANT SELECT ON app_db.users TO 'laowang'@'localhost';
INSERT 允许向该表插入新数据（写权限） GRANT INSERT ON app_db.users TO 'laowang'@'localhost';
UPDATE 允许修改该表的现有数据 GRANT UPDATE ON app_db.users TO 'laowang'@'localhost';
DELETE 允许删除该表的现有数据 GRANT DELETE ON app_db.users TO 'laowang'@'localhost';

表结构管理类，这类权限控制单表的结构修改、索引管理等，属于「结构层」权限：

权限名称 作用 授权示例
ALTER 允许修改该表的结构（如添加 / 删除列、修改列类型） GRANT ALTER ON app_db.users TO 'laowang'@'localhost';
DROP 允许删除该表（慎用） GRANT DROP ON app_db.users TO 'laowang'@'localhost';
INDEX 允许为该表创建 / 删除索引（注：MariaDB 5.5 中 ALTER 权限已包含此能力） GRANT INDEX ON app_db.users TO 'laowang'@'localhost';
CREATE VIEW 基于该表创建视图（需同时有该表的 SELECT 权限） GRANT CREATE VIEW ON app_db.users TO 'laowang'@'localhost';
SHOW VIEW 查看基于该表创建的视图定义（如 SHOW CREATE VIEW） GRANT SHOW VIEW ON app_db.users TO 'laowang'@'localhost';

特殊操作类，这类权限针对单表的特殊操作，如锁表、触发器管理等：

权限名称 作用 授权示例
LOCK TABLES 允许锁定该表（用于并发控制） GRANT LOCK TABLES ON app_db.users TO 'laowang'@'localhost';
CREATE TRIGGER 允许为该表创建触发器 GRANT CREATE TRIGGER ON app_db.users TO 'laowang'@'localhost';
ALTER TRIGGER 允许修改 / 删除该表的触发器 GRANT ALTER TRIGGER ON app_db.users TO 'laowang'@'localhost';
EXECUTE 允许执行该表关联的存储过程 / 函数（若有） GRANT EXECUTE ON app_db.users TO 'laowang'@'localhost';

示例

-- 示例1：业务读写权限（最常用）
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'laowang'@'localhost';
FLUSH PRIVILEGES;

-- 示例2：表结构管理权限
GRANT ALTER, INDEX ON app_db.users TO 'laowang'@'localhost';
FLUSH PRIVILEGES;

-- 示例3：只读权限（报表 / 分析用）
GRANT SELECT, SHOW VIEW ON app_db.users TO 'report_user'@'localhost';
FLUSH PRIVILEGES;
4. 列范围

列级权限仅作用于「数据库名.表名.列名」，是表级权限的精细化补充；MariaDB 5.5 仅支持对 SELECT/INSERT/UPDATE 这 3 类操作做列级限制，其他操作（如 DELETE）无列级权限控制。所有授权后需执行 FLUSH PRIVILEGES; 生效。

列级权限（仅 3 类）

权限名称 作用 授权语法 实操示例（以 app_db.users 表为例）
SELECT 允许查询指定列的数据（控制「读哪些列」） GRANT SELECT(列1,列2) ON 库.表 TO 用户@主机; GRANT SELECT(name, phone) ON app_db.users TO 'laowang'@'localhost';
INSERT 允许向指定列插入数据（控制「写哪些列」） GRANT INSERT(列1,列2) ON 库.表 TO 用户@主机; GRANT INSERT(name, email) ON app_db.users TO 'laowang'@'localhost';
UPDATE 允许修改指定列的数据（控制「改哪些列」） GRANT UPDATE(列1,列2) ON 库.表 TO 用户@主机; GRANT UPDATE(phone) ON app_db.users TO 'laowang'@'localhost';

示例

注意：列级授权需显式指定列名，且仅支持 SELECT/INSERT/UPDATE/REFERENCES 权限。

-- 示例1：授予 user_op 仅能查询 `shop_db.user` 表的 `id`、`name` 列（隐藏手机号/密码）
CREATE USER IF NOT EXISTS 'user_op'@'%' IDENTIFIED BY 'UserOp@123';
GRANT SELECT (id, name) ON shop_db.user TO 'user_op'@'%';

-- 示例2：授予 user_admin 仅能修改 `shop_db.user` 表的 `status` 列（账号状态）
GRANT UPDATE (status) ON shop_db.user TO 'user_admin'@'%';

-- 示例3：组合授权：查询基础列 + 修改手机号列
GRANT SELECT (id, name), UPDATE (phone) ON shop_db.user TO 'user_mgr'@'%';

查询用户权限

MariaDB [mysql]> show grants for ggg@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for ggg@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggg'@'%' IDENTIFIED BY PASSWORD '*531E182E2F72080AB0740FE2F2D689DBE0146E04' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

关键注意事项

  1. 最小权限原则：优先授予“刚好够用”的权限（如业务开发仅授予数据库级权限，而非全局），避免过度授权。

  2. 权限生效：所有授权后需执行 FLUSH PRIVILEGES;。

  3. 用户范围限制：生产环境避免 @'%'（任意主机），替换为具体 IP（如 @'192.168.1.100'）或网段（@'192.168.1.%'）。

  4. 查看权限：验证授权结果，避免配置错误：

    -- 查看指定用户的所有权限
SHOW GRANTS FOR 'laoma'@'%';
-- 查看当前登录用户的权限
SHOW GRANTS;

  5. 权限回收：如需撤销权限，格式与授权对应（替换 GRANT 为 REVOKE）：

    -- 撤销 laoma 的全局创建用户权限
REVOKE CREATE USER ON *.* FROM 'laoma'@'%';

常见场景权限参考

角色 权限范围 推荐权限组合
超级管理员 *.* ALL PRIVILEGES WITH GRANT OPTION
业务库开发 业务库.* CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE
运营只读 业务库.* SELECT
订单表操作 业务库.订单表 SELECT, INSERT, UPDATE
敏感列管控 业务库.用户表 SELECT(id,name), UPDATE(status)

回收用户权限

REVOKE语句从帐户中删除特权。 连接的用户必须具有GRANT OPTION特权，并且必须具有被撤消的特定特权。

#添加create权限
MariaDB [(none)]> grant create on *.* to ggg@'%';
Query OK, 0 rows affected (0.00 sec)

#查看ggg用户权限
MariaDB [(none)]> show grants for ggg@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for ggg@%                                                                                    |
+-----------------------------------------------------------------------------------------------------+
| GRANT CREATE ON *.* TO 'ggg'@'%' IDENTIFIED BY PASSWORD '*531E182E2F72080AB0740FE2F2D689DBE0146E04' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#移除权限
MariaDB [(none)]> revoke create on *.*  from ggg@'%';
Query OK, 0 rows affected (0.00 sec)
#刷新权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#再次查看发现权限已移除
MariaDB [(none)]> show grants for ggg@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for ggg@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggg'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除用户

当不再需要特定的用户帐户时，可以使用DROP USER将其从数据库中删除。

用户名使用**‘user’@‘host’**格式。

MariaDB [mysql]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| ggg  | %         |
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

MariaDB [mysql]> drop user ggg@'%';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

如果删除了当前连接的帐户，则在关闭连接之前不会删除该帐户，关闭连接之后才会删除该帐户。

更改用户密码

# root用户修改普通用户账户密码
MariaDB [mysql]> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| %         | ggg  | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> update user set password=password('123') where user='ggg' and host='%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| %         | ggg  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)


# 或者
MariaDB [(none)]> SET PASSWORD FOR 'laoma'@'localhost' = PASSWORD('mypass');

# 普通用户修改自己账户密码
MariaDB [(none)]> SET PASSWORD = PASSWORD('mypass');
MariaDB [(none)]> FLUSH PRIVILEGES;

排故数据库访问 ⭐️

下表总结了用户在身份验证和访问方面可能遇到的一些问题，以及可能的原因。

问题 解决方法
已授予用户从任何主机进行连接的访问权限，但只能使用数据库服务器本地mysql命令进行连接。 如果在配置文件/etc/my.cnf.d/server.cnf中设置了skip-networking，请删除该指令并重新启动服务。
用户可以与localhost上的任何应用程序连接，但不能远程连接。 确保/etc/my.cnf.d/server.cnf中的bind-address配置正确。 确保用户表中包含用户要尝试与其连接的主机的条目。
用户可以连接，但看不到除information_schema以外的任何数据库。 确保已授予用户访问其数据库的权限。 刚创建的用户具有最小权限，会遇到这个问题。
用户可以连接，但不能创建任何数据库。 考虑向用户授予全局CREATE特权（这也授予DROP特权）。
用户可以连接，但不能读取或写入任何数据。 向用户授予他们打算使用的数据库的CRUD特权。

思考

1. 忘记 root 用户密码 ⭐️

  1. 编辑**/etc/my.cnf.d/server.cnf**配置文件，在[mysqld]下添加skip-grant-tables=1。

  2. 重启 mariadb 服务。

  3. 执行mysql -u root命令进入mysql命令行，修改root用户密码。

    [root@server ~ 15:01:25]# mysql -u root
#原先密码为123456
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('123') where USER='root';
MariaDB [(none)]> exit

  4. 把**/etc/my.cnf.d/server.cnf配置文件中的skip-grant-tables=1注释掉，然后重启mariadb**服务。

2. 无意回收了 root 账户权限

当 MySQL 的 root 账户所有权限被误回收时，核心解决思路是绕过权限验证启动 MySQL，直接修改权限表恢复 root 权限，以下是适配 MySQL 5.7/8.0 版本的完整恢复步骤（CentOS 7/8、Ubuntu 通用）：

  • 步骤 1：停止 MySQL 服务

  • 步骤 2：跳过权限表启动 MySQL

  • 步骤 3：无密码登录 MySQL

  • 步骤 4：恢复 root 权限

    场景 1：MySQL 5.7 版本

    -- 1. 切换到 mysql 系统库
USE mysql;

-- 2. 重置 root 账户的所有权限（设置为 Y 表示开启）
UPDATE user SET 
  Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y',
  Create_priv='Y', Drop_priv='Y', Reload_priv='Y', Shutdown_priv='Y',
  Process_priv='Y', File_priv='Y', Grant_priv='Y', References_priv='Y',
  Index_priv='Y', Alter_priv='Y', Show_db_priv='Y', Super_priv='Y',
  Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y',
  Repl_slave_priv='Y', Repl_client_priv='Y', Create_view_priv='Y',
  Show_view_priv='Y', Create_routine_priv='Y', Alter_routine_priv='Y',
  Create_user_priv='Y', Event_priv='Y', Trigger_priv='Y',
  Create_tablespace_priv='Y'
WHERE User='root' AND Host='localhost';  -- 若需恢复远程 root（%），替换 Host='%'

-- 3. 刷新权限（使修改生效）
FLUSH PRIVILEGES;

-- 4. 验证权限（可选）
SELECT User, Host, Grant_priv FROM mysql.user WHERE User='root';

    场景 2：MySQL 8.0 版本

    -- 1. 先刷新权限（启用权限校验，否则 GRANT 命令失效）
FLUSH PRIVILEGES;

-- 2. 重新授予 root 全局所有权限（含授权权限）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

-- 若需恢复远程 root（%），补充执行：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 3. 刷新权限
FLUSH PRIVILEGES;

-- 4. 验证权限（可选）
SHOW GRANTS FOR 'root'@'localhost';

  • 步骤 5：重启 MySQL 服务

3. 无意删除了 root 账户

当 MySQL 中所有 root 账户（包括 root@localhost、root@% 等）被误删除时，核心思路是跳过权限验证启动 MySQL，直接重建 root 账户并赋予全权限，以下是适配 MySQL 5.7/8.0 版本的完整恢复步骤（CentOS/Ubuntu 通用）：

  • 步骤 1：停止 MySQL 服务

  • 步骤 2：跳过权限表启动 MySQL

  • 步骤 3：无密码登录 MySQL

  • 步骤 4：重建 root 账户

    场景 1：MySQL 5.7 版本

    -- 1. 切换到 mysql 系统库
USE mysql;

-- 2. 插入 root 账户（本地登录）
INSERT INTO user (
  Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv,
  Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
  Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
  Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv,
  Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv,
  Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv,
  Create_tablespace_priv
) VALUES (
  'localhost', 'root', PASSWORD('你的新密码'), 'Y', 'Y', 'Y', 'Y',
  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
);

-- （可选）若需要远程 root 账户（%），补充插入
INSERT INTO user (Host, User, Password, Grant_priv, Super_priv)
VALUES ('%', 'root', PASSWORD('你的新密码'), 'Y', 'Y');

-- 3. 刷新权限（使账户生效）
FLUSH PRIVILEGES;

-- 4. 验证 root 账户是否创建成功
SELECT User, Host FROM mysql.user WHERE User='root';

    场景2：MySQL 8.0 版本

    -- 1. 先刷新权限（启用权限校验，否则 CREATE USER 可能失效）
FLUSH PRIVILEGES;

-- 2. 创建本地 root 账户（设置密码，替换为你的密码）
CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED BY '你的新密码';

-- （可选）创建远程 root 账户（生产环境不推荐）
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '你的新密码';

-- 3. 授予 root 全局所有权限（含授权权限）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 4. 刷新权限
FLUSH PRIVILEGES;

-- 5. 验证账户
SHOW GRANTS FOR 'root'@'localhost';

  • 步骤 5：重启 MySQL 服务（恢复正常）

备份和恢复

备份方式

逻辑备份

逻辑备份，将数据库导出为包含重新创建数据库所需的SQL命令的文本文件。

  • 通过查询数据库，检索数据库结构。
  • 备份速度较慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。
  • 逻辑备份具有高度的可移植性，在某些情况下可以还原到其他数据库提供程序（例如PostgreSQL）。
  • 在服务器联机时执行。
  • 备份不包括日志或配置文件。
物理备份

物理备份，复制原始数据库目录和包含数据库内容的文件。

  • 备份可以包括日志和配置文件。
  • 兼容性差：仅可移植到具有类似硬件和软件的其他计算机上。
  • 比逻辑备份更快。
  • 在服务器脱机或数据库中的所有表均已锁定时执行，以防止在备份期间进行更改。

物理备份和恢复

执行物理备份

提示：备份前需要停止服务。

# 停止
[root@server ~]# systemctl stop mariadb

# 执行备份
[root@server ~]# cp -a /var/lib/mysql /backup/mysql
恢复物理备份

提示：恢复前需要停止服务。

# 停止服务
[root@server ~]# systemctl stop mariadb

# 模拟删除原数据
[root@server ~]# rm -fr /var/lib/mysql

# 恢复数据
[root@server ~]# cp -a /backup/mysql /var/lib/mysql
[root@server ~]# chown -R mysql:mysql /var/lib/mysql/

# 确认目录中内容
[root@server ~]# ls /var/lib/mysql

# 启动服务
[root@server ~]# systemctl start mariadb

逻辑备份和恢复

执行逻辑备份

mysqldump命令可用于逻辑备份。使用mysqldump命令连的MariaDB，用户至少需要对转储表具有SELECT特权，对转储视图至少需要SHOW VIEW，对于转储触发器至少需要TRIGGER特权。

语法：

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

常用选项：

  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
  --ignore-database=name 
                      Do not dump the specified database. To specify more than
                      one database to ignore, use the directive multiple times,
                      once for each database. Only takes effect when used
                      together with --all-databases|-A
  -B, --databases     Dump several databases. Note the difference in usage; in
                      this case no tables are given. All name arguments are
                      regarded as database names. 'USE db_name;' will be
                      included in the output.

  -Y, --all-tablespaces 
                      Dump all the tablespaces.
  --ignore-table=name Do not dump the specified table. To specify more than one
                      table to ignore, use the directive multiple times, once
                      for each table.  Each table must be specified with both
                      database and table names, e.g.,
                      --ignore-table=database.table.
  -y, --no-tablespaces 
                      Do not dump any tablespace information.

  --add-drop-database Add a DROP DATABASE before each create.
  --add-drop-table    Add a DROP TABLE before each create.

-x, --lock-all-tables 
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off.

示例：

# 备份方式1：不包含创建数据库语句
[root@server ~]# mysqldump -u root -p inventory > /backup/inventory.dump.sql-1

# 备份方式2：包含创建数据库语句
[root@server ~]# mysqldump -u root -p --databases inventory > /backup/inventory.dump.sql-2

# 备份方式3：备份所有数据库
[root@server ~]# mysqldump -u root -p --all-databases > /backup/all-databases.dump.sql

inventory.dump.sql-2 文本文件部分内容：

--
-- Current Database: `inventory`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `inventory` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `inventory`;

--
-- Table structure for table `category`
--

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `category`
--

LOCK TABLES `category` WRITE;
/*!40000 ALTER TABLE `category` DISABLE KEYS */;
INSERT INTO `category` VALUES (1,'Networking'),(2,'Servers'),(3,'Ssd');
/*!40000 ALTER TABLE `category` ENABLE KEYS */;
UNLOCK TABLES;

还原备份时，它将用备份的内容覆盖数据库服务器的内容。 如果数据库中的数据比备份中的数据新，该部分数据将会丢失。

恢复逻辑备份

模拟故障：删除数据库

[root@server ~]# mysql -u root -p -e 'drop database inventory;'

恢复

[root@server ~]# mysql -u root -p < /backup/inventory.dump.sql-2
# 或者
MariaDB [(none)]> source /backup/inventory.dump.sql

提示：如果使用 /backup/inventory.dump.sql-1 恢复数据，则需要提前创建inventory数据库，并使用以下命令恢复：

[root@server ~]# mysql -u root -p inventory < /backup/inventory.dump.sql-1
# 或者
MariaDB [(none)]> use inventory;
MariaDB [(inventory)]> source /backup/inventory.dump.sql-1

MySQL 集群架构概述

MySQL 集群架构类别

1️⃣ 普通主从架构

1主 + N 从

  • 主：写
  • 从：读、备份、报表
  • 异步复制 / 半同步复制

特点

  • 最简单、最稳定、成本最低
  • 故障需要手动切换
  • 无法自动故障转移

适用

  • 小型企业、测试环境、非核心业务
2️⃣ 主从 + Keepalived / VIP

1主 + 1从 + VIP

  • 主挂了，Keepalived 自动把 VIP 飘到从库
  • 简单实现自动切换

特点

  • 部署简单
  • 无复制状态判断，容易脑裂
  • 不判断复制延迟，直接切

适用

  • 中小企业、追求低成本高可用
3️⃣ 主从 + MHA

1主 + N从 + MHA管理

  • 业界最经典、最成熟的高可用方案
  • MHA 自动检测主库故障
  • 自动补齐数据、切换、提升新主

特点

  • 最稳定、最成熟
  • 切换速度快（秒级）
  • 不会丢数据（半同步+日志补偿）
  • 配置复杂一点

适用

  • 中大规模企业主流
  • 电商、金融、生产核心库 90% 在用
4️⃣ 主从 + Orchestrator

自动化主从拓扑管理 + 故障自愈

  • 开源、可视化、自动切换
  • 可以跨机房、自动修复复制

特点

  • 比 MHA 更现代化
  • 自动化程度更高
  • 适合大规模集群

适用

  • 中大型企业、云厂商、多实例集群
5️⃣ MySQL MGR

MySQL Group Replication 官方组团复制。

单主模式 / 多主模式

  • MySQL 官方原生高可用
  • 基于 Paxos 一致性协议
  • 多节点自动选主、强一致

特点

  • 强一致，不丢数据
  • 无需第三方工具
  • 单主模式非常稳定
  • 多主模式不推荐生产

适用

  • 新一代主流架构
  • 金融、政企、云平台、新系统首选
6️⃣ 主主架构（双主）

双主互相同步

  • 可以双写（危险）
  • 企业基本只做一主一备，不双写

特点

  • 切换快
  • 双写会冲突、死锁、数据不一致
  • 几乎不用在核心业务
总结
  1. 基础主从：简单但不自动切换
  2. Keepalived 主从：简单高可用，但脑裂风险
  3. MHA：经典稳定、企业主流
  4. Orchestrator：自动化集群管理
  5. MGR：官方强一致，新一代主流
  6. 主主：不用在生产双写

MySQL 集群架构-企业主流

🔥 第一名：MGR 单主模式（新系统首选）

🔥 第二名：MHA（老系统、传统架构主流）

🔥 第三名：Orchestrator（大规模集群）

🔥 第四名：简单主从（小型业务）

为什么 MGR 单主 + MHA是企业主流架构？

  1. MGR（官方、强一致、不丢数据）
  • MySQL 官方亲生
  • 分布式一致性协议（Paxos）
  • 自动选主、自动故障转移
  • 金融级安全
  • 云原生、好维护

→ 新系统 90% 直接上 MGR

  1. MHA（最成熟、最稳定、最靠谱）
  • 线上运行 10 年+
  • 百万级核心库验证
  • 几乎不出问题
  • 切换可靠、不丢数据

→ 老系统、核心业务依然大量用

电商平台数据库架构

1. MySQL 单机写入能力有天花板

京东、拼多多、淘宝这种顶级电商，根本不靠“单主MySQL”扛流量，单主绝对扛不住。

原因很简单：MySQL 单机写入能力有天花板。

  • 普通云服务器：2000 ~ 4000 TPS
  • 高性能物理机：5000 ~ 8000 TPS
  • 优化到极限：顶多 1 万 TPS

电商真实流量（秒杀/大促）

  • 拼多多：几十万 TPS
  • 京东：几十万 ~ 上百万 TPS
  • 淘宝：上百万 TPS

→ 差 10～100 倍，单主根本接不住。

2. 那京东、拼多多、淘宝用什么扛？

不是 MySQL 单主，而是 一套分布式数据库体系。

① 分库分表

把一个库 → 拆成 16、32、64、128 个库，每个库都是独立的 MySQL 集群。

例如：

  • 用户库 32 组
  • 订单库 64 组
  • 商品库 64 组
  • 库存库 32 组
  • 支付库 16 组

每组都是 高可用集群（主从/MGR），靠水平拆分撑住海量写入。

② MySQL 只做“存储引擎”，前面全是缓存
  • Redis 集群扛90% 读流量
  • 本地缓存 + 分布式锁
  • 消息队列削峰
    MySQL 只干最核心的落盘。
③ 读写极端分离
  • 读请求 100% 走从库 / 只读实例
  • 主库只承担写入+实时读
  • 从库可以扩展到 N 个
④ 不用普通 MySQL，用“云数据库/分布式数据库”
  • 京东：TDSQL（腾讯）+ JDB + 自研分库分表
  • 拼多多：阿里云 PolarDB + 分库分表
  • 淘宝：OceanBase（完全自研）

这些本质都是：分布式数据库 = 无限水平扩展

3. 那他们还用不用“主从/MGR”？

用！但不是一个主，而是：几百套、几千套 主从/MGR 集群

每套负责一小块业务，互不干扰。

4. 总结

京东、拼多多这种电商：

  1. 单主 MySQL 绝对撑不住，写入有天花板。
  2. 真实架构是：分库分表 + 读写分离 + 缓存 + 分布式数据库。
  3. MySQL 只是其中一环，而且是成百上千套集群一起扛。
  4. 高可用用 MGR 或 优化版主从（自己封装）。

MySQL 主从同步解决方案

MySQL 主从同步本质是主库将数据变更以日志形式传输给从库，从库重放日志实现数据一致。

MySQL 主从同步架构

  1. 一主一从：一个主库对应一个从库，适合小型业务。

  2. 一主多从：一个主库对应多个从库，可实现读写分离（主库写，多个从库读），提升读性能。

  3. 级联复制（主 - 从 - 从）：从库不再直接连接主库，而是连接另一个从库（中间层从库），减轻主库的复制压力。

    例如：Master → Slave1 → Slave2（Slave1 既是从库，也是 Slave2 的主库）。

  4. 双主复制（互为主从）：两个库互为主从，A 是 B 的主库，B 也是 A 的主库，通常用于高可用切换（如主库宕机后，从库快速切换为主库）。

MySQL 主从同步流程⭐️⭐️

1.发送写请求（INSERT/UPDATE/DELETE）

2.执行SQL语句，更新数据

3.数据更新完成

4.将SQL变更写入binlog

5.立即向客户端返回执行成功（不等待从库）

6.binlog日志就绪

7.主动拉取主库binlog

8.将binlog写入从库relay log（中继日志）

9.读取relay log中的变更指令

10.执行SQL指令，更新从库数据

11.从库数据更新完成

客户端

主库（Master）

主库存储引擎

主库binlog日志

从库I/O线程

从库relay log

从库SQL线程

从库存储引擎

从库数据与主库最终一致
  1. 客户端程序（如Java应用、Navicat等）向主库（Master）的MySQL服务进程发送数据写请求（如INSERT/UPDATE/DELETE等SQL指令）。
  2. 主库MySQL服务进程接收客户端请求后，解析并执行对应的SQL语句，调用自身存储引擎（如InnoDB）完成数据更新。
  3. 主库存储引擎（如InnoDB）完成数据页写入、事务提交等操作，向主库MySQL服务进程确认数据更新完成。
  4. 主库MySQL服务进程的binlog写入线程，将本次数据变更的完整信息写入主库本地的二进制日志（binlog）文件。
  5. 主库MySQL服务进程完成binlog写入后，立即向客户端程序返回“执行成功”的响应（不等待从库反馈，异步核心特征）。
  6. 主库MySQL服务进程的binlog守护线程，将binlog日志持久化到磁盘并标记为“可读取状态”，等待从库I/O线程连接拉取。
  7. 从库（Slave）MySQL服务进程的I/O线程（Slave_IO_Running）主动建立与主库的TCP连接，请求拉取主库最新的binlog日志片段。
  8. 从库MySQL服务进程的I/O线程，将从主库拉取到的binlog内容，按顺序写入从库本地的中继日志（relay log）文件。
  9. 从库MySQL服务进程的SQL线程（Slave_SQL_Running）持续读取中继日志中的数据变更指令，按顺序解析成可执行的SQL逻辑。
  10. 从库MySQL服务进程的SQL线程执行解析后的SQL指令，调用从库存储引擎（如InnoDB）完成本地数据的更新。
  11. 从库存储引擎（如InnoDB）确认数据更新完成，从库数据与主库的变更内容最终保持一致。

MySQL 主从同步方式

1. 异步复制（Asynchronous Replication）
  • 核心原理：主库执行完 SQL 并写入 binlog 后，立即向客户端返回执行成功，不等待从库接收或执行 binlog。
  • 特点：
    • 性能最好（主库无额外等待），是 MySQL 默认的复制方式。
    • 数据一致性风险最高：如果主库宕机，可能存在部分 binlog 未传输到从库，导致主从数据不一致。
  • 适用场景：对性能要求高、对数据一致性要求较低的场景（如普通业务的读写分离）。
2. 半同步复制（Semi-synchronous Replication）
  • 核心原理：主库执行完 SQL 并写入 binlog 后，会等待至少一个从库接收并确认（将 binlog 写入中继日志 relay log），再向客户端返回成功；如果超时（默认 10 秒），则降级为异步复制。
  • 特点：
    • 性能略低于异步复制，但数据一致性大幅提升（至少保证 binlog 已传输到一个从库）。
    • 需要手动开启（依赖 plugin/semisync_master.so 和 semisync_slave.so 插件）。
  • 适用场景：对数据一致性有一定要求，且能接受少量性能损耗的场景（如核心业务的主从架构）。
3. 全同步复制（Fully Synchronous Replication）
  • 核心原理：主库执行完 SQL 后，必须等待所有从库接收并执行完 binlog，才向客户端返回成功。
  • 特点：
    • 数据一致性最高（主从实时一致）。
    • 性能极差（主库需等待所有从库执行完成），MySQL 原生不支持，需通过 MGR（MySQL Group Replication）或第三方插件实现。
  • 适用场景：对数据一致性要求极高的核心金融场景（极少使用）。

MySQL 主从异步复制延迟

MySQL 异步复制的时间差（通常称为 “主从延迟”）没有固定值，它不是由 MySQL 内置的参数决定的固定间隔，而是由多种因素共同影响的动态值 —— 理论上可以是毫秒级，也可能达到秒级、分钟级甚至更长。

异步复制的延迟本质

异步复制过程中，主库完全不等待从库，所以延迟的大小完全取决于从库 “拉取 + 执行” 的速度，以及主库产生 binlog 的速度。

影响延迟的核心因素
网络延迟

主从库之间的网络带宽、时延直接影响 binlog 的传输速度：

  • 同机房 / 同可用区：网络延迟通常毫秒级（1-10ms），几乎可以忽略。
  • 跨机房 / 跨地域：网络延迟可能达到几十毫秒甚至上百毫秒，是延迟的基础值。
主库写入压力

主库如果高频执行大批量写入（如批量 INSERT/UPDATE、大事务），会快速产生大量 binlog，从库可能 “追不上”：

  • 小事务（单条 INSERT/UPDATE）：从库能实时跟进，延迟通常 < 100ms。
  • 大事务（如一次性更新 10 万行数据）：主库一次性写入大量 binlog，从库 SQL 线程执行需要时间，延迟可能达到秒级 / 分钟级。
从库性能瓶颈
  • 从库硬件配置（CPU / 内存 / 磁盘）低于主库：比如从库用机械硬盘（HDD），主库用固态硬盘（SSD），从库执行 SQL 的速度会远慢于主库，导致延迟累积。
  • 从库开启了大量慢查询、索引维护、报表统计等额外操作：占用从库资源，导致 SQL 线程执行 binlog 的速度下降。
  • 从库 SQL 线程是单线程（MySQL 5.6 及以下默认）：无法并行执行 binlog，主库并发写入时，从库会出现明显延迟；MySQL 5.7 + 支持并行复制，可大幅缓解。
复制格式的影响

基于行的复制（RBR）：binlog 体积更大，传输和解析耗时略高于基于语句的复制（SBR），但一致性更好，是权衡后的最优选择。

实际场景中的延迟范围
场景 典型延迟 核心原因
小事务 + 同机房 + 高性能从库 0-100ms 网络 / 执行耗时可忽略
中等事务 + 跨机房 100ms-1s 网络延迟 + 少量 binlog 堆积
大批量写入 / 大事务 1s - 数分钟 从库 SQL 线程执行速度跟不上
从库性能瓶颈 / 慢查询 数分钟 - 甚至小时 资源耗尽，延迟持续累积
如何监控和优化延迟
1. 监控延迟

可以通过从库执行以下命令查看实时延迟：

-- 查看从库状态，Seconds_Behind_Master 即为延迟秒数（NULL表示复制异常）
SHOW SLAVE STATUS\G;

-- 关键字段说明：
-- Seconds_Behind_Master：从库落后主库的秒数
-- Slave_IO_Running：I/O线程是否正常（负责拉取binlog）
-- Slave_SQL_Running：SQL线程是否正常（负责执行relay log）
2. 常见优化手段
  1. 提升从库硬件配置（CPU / 内存 / SSD），至少不低于主库。
  2. 开启 MySQL 并行复制（MySQL 5.7+：slave_parallel_workers = 4，根据 CPU 核数调整）。
  3. 拆分大事务，避免主库一次性产生大量 binlog。
  4. 从库关闭不必要的操作（如慢查询日志、非必要的索引、报表任务）。
  5. 跨地域场景可使用级联复制，先在主库同机房部署一个从库，再同步到异地从库。

MySQL 主从同步数据丢失问题

数据丢失的核心场景：主库写入 binlog 后立即返回业务，主库内存中的 binlog 未写入磁盘，从库还没来得及同步，主库宕机（磁盘损坏等），则未同步的 binlog 丢失；

应对策略：

  1. 开启半同步复制（主库需等待至少 1 个从库确认接收 binlog）
  2. 开启 sync_binlog=1（每次事务刷盘 binlog）
  3. innodb_flush_log_at_trx_commit=1（事务提交立即刷 redo log）
安装半同步插件

主库安装半同步插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库安装半同步插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
配置半同步

主库 my.cnf 中额外配置：

[mysqld]
# ========== 半同步复制核心参数 ==========
# 开启半同步主库插件（永久生效）
plugin-load-add = rpl_semi_sync_master.so

# 启用主库半同步复制（1=开启，0=关闭）
rpl_semi_sync_master_enabled = 1

# 半同步超时时间（毫秒），超时后降级为异步复制
rpl_semi_sync_master_timeout = 1000

# 至少等待1个从库确认
rpl_semi_sync_master_wait_for_slave_count = 1

# 无可用从库时，是否等待（1=等待，直到超时）
rpl_semi_sync_master_wait_no_slave = 1

# 等待时机：AFTER_SYNC（推荐，主库刷binlog到磁盘后等待）
rpl_semi_sync_master_wait_point = AFTER_SYNC
# ========== 半同步复制核心参数 ==========

# 每次事务刷盘binlog（降低数据丢失风险）
sync_binlog = 1

# 事务提交立即刷redo log（ACID强一致性）
innodb_flush_log_at_trx_commit = 1

从库 my.cnf 中额外配置：

[mysqld]
# ========== 半同步复制核心参数 ==========
# 开启半同步从库插件（永久生效）
plugin-load-add = rpl_semi_sync_slave.so
# 启用从库半同步复制
rpl_semi_sync_slave_enabled = 1
# ========== 半同步复制核心参数 ==========

Mariadb 主从同步实践

实验环境

主机名 IP 地址 角色
db1.laoma.cloud 10.1.8.11 主库
db2.laoma.cloud 10.1.8.12 从库

环境准备

所有数据库节点安装好 Mariadb 数据库，安全初始化。

配置主库

编辑 /etc/my.cnf.d/master.cnf 并重启服务，确保主库满足同步要求：

[root@db1 ~ 15:52:11]# cat > /etc/my.cnf.d/master.cnf <<'EOF'
[mysqld]
# 主从唯一ID，主库建议设1，从库设2/3等（必填，不能重复）
server_id = 1

# 开启二进制日志，指定日志前缀（必填）
log_bin = mysql-bin
# binlog格式：ROW（行级复制，最安全，5.7默认可能是STATEMENT，需手动改）
binlog_format = ROW

# 开启中继日志
relay_log = mysql-relay-bin

# 忽略同步的系统库（无实际业务意义）
binlog-ignore-db = information_schema  # 忽略虚拟库
binlog-ignore-db = performance_schema  # 忽略性能监控库
binlog-ignore-db = sys                 # 忽略简化监控库
EOF

# 重启服务使配置生效
[root@db1 ~ 16:05:42]# systemctl restart mariadb
# 预期输出：无报错即重启成功

配置从库

编辑 /etc/my.cnf.d/master.cnf 并重启服务，确保主库满足同步要求：

[root@db2 ~ 15:52:17]# cat > /etc/my.cnf.d/master.cnf <<'EOF'
[mysqld]
# 主从唯一ID，主库建议设1，从库设2/3等（必填，不能重复）
server_id = 2

# 开启二进制日志，指定日志前缀（必填）
log_bin = mysql-bin
# binlog格式：ROW（行级复制，最安全，5.7默认可能是STATEMENT，需手动改）
binlog_format = ROW

# 开启中继日志
relay_log = mysql-relay-bin

# 忽略同步的系统库（无实际业务意义）
binlog-ignore-db = information_schema  # 忽略虚拟库
binlog-ignore-db = performance_schema  # 忽略性能监控库
binlog-ignore-db = sys                 # 忽略简化监控库
EOF

# 重启服务使配置生效
[root@db2 ~ 16:06:13]# systemctl restart mariadb
# 预期输出：无报错即重启成功

主从同步

# 配置主数据库
[root@db1 ~ 16:06:46]# mysql -uroot -p123
MariaDB [(none)]> grant replication slave, replication client on *.* to 'repl'@'10.1.8.12' identified by '123';
MariaDB [(none)]> flush privileges;

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 3089
    Binlog_Do_DB: 
Binlog_Ignore_DB: information_schema,performance_schema,sys
1 row in set (0.00 sec)


# 配置从数据库
[root@db2 ~ 16:09:34]# mysql -uroot -p123
MariaDB [(none)]> change master to master_host='10.1.8.11',
    ->     master_user='repl',
    ->     master_password='123',
    ->     master_port=3306,
    ->     master_log_file='mysql-bin.000001',
    ->     master_log_pos=3089,
    ->     master_connect_retry=30;
Query OK, 0 rows affected (0.00 sec)

#当前两个程序都未启动
[root@db2 ~ 16:13:15]# mysql -uroot -p123 -e 'show slave status\G' |grep 'Slave.*Running'
             Slave_IO_Running: No
            Slave_SQL_Running: No
#重启服务
[root@db2 ~ 16:14:06]# systemctl restart mariadb.service
#均已启动
# 确保 Slave_IO_Running 和 Slave_SQL_Running 状态为Y es。
[root@db2 ~ 16:14:53]# mysql -uroot -p123 -e 'show slave status\G' |grep 'Slave.*Running'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# 主库写入数据
[root@db1 ~ 16:09:23]# mysql -uroot -pLaoma@123
create database test;
use test; 
create table linux(username varchar(15) not null,password varchar(15) not null);
insert into linux values ('g1', 'g1@123');
insert into linux values ('g2', 'g2@123');
insert into linux values ('g3', 'g3@123');
commit;
select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| g1       | g1@123   |
| g2       | g2@123   |
| g3       | g3@123   |
+----------+----------+
3 rows in set (0.00 sec)

# 从库查询
[root@db1 ~ 16:09:23]# mysql -uroot -pLaoma@123
MariaDB [(none)]> select * from test.linux;
+----------+----------+
| username | password |
+----------+----------+
| g1       | g1@123   |
| g2       | g2@123   |
| g3       | g3@123   |
+----------+----------+
3 rows in set (0.00 sec)
# 数据库 # mariadb # linux
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

海外动态代理行业彻底换代，AI 数据时代新标准已到来

avatar AtomGit开源社区
cover

2026年SEO+GEO优化指南：搜索排名机制解析与实用工具推荐

avatar AtomGit开源社区
cover

2026年论文写作AI哪个好用？7款工具实测对比，轻松拿捏

avatar AtomGit开源社区