MariaDB 数据库管理手册

数据库介绍

什么是数据库

数据库，是一个存放计算机数据的仓库。这个仓库是按照一定的数据结构来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其中的数据。
数据结构是指数据的组织形式或数据之间的联系。

数据库种类

按照早期的数据库理论，比较流行的数据库模型有三种，分别为：
层次式数据库，以层次模型建立的数据库，例如文件系统、DNS。 网状数据库，以网状模型建立的数据库，例如网络通信。
关系型数据库，以二维表模型建议的数据库，例如图书馆管理系统。在当今的互联网企业中，最常用的数据库模式主要有两种：
关系型数据库，把复杂的数据结构归结为简单的二维表形式。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。例如：Oracle、MySql和PostgreSQL等
非关系型数据库，也被称为 NoSQL 数据库，本意是“Not Only SQL”，而非“NO SQL”的意 思，因此，NoSQL的产生并不是要彻底否定关系型数据库，而是作为传统数据库的一个有效 补充。典型产品有Redis（持久化缓存）、Mongodb、Memcached（纯内存）等

关系型数据库 (RDBMS)

关系数据库将结构化数据存储在一组相互关联的表中。这意味着一个表中的信息可以轻松地与另一表中的信息组合在一起，以处理和提供有关该数据的报告。
关系数据库管理系统（RDBMS-relational database management system）是用于管理关系数据库的软件。大多数关系数据库管理系统都允许 使用结构化查询语言（SQL）查找和管理数 据库中的数据。
许多组织使用关系数据库存储业务信息，例如：库存，销售和财务。关系数据库管理系统在许多应用中起着关键作用。例如，许多需要支持动态生成内容的Web应用程序都是围绕 LAMP解决方案构建：

LNMP 架构解决方案构建：
Linux操作系统提供基本的环境
Apache HTTPS Server或Nginx，提供Web服务器
MariaDB，MySQL或其他关系数据库如PostgreSQL，用于存储站点数据
由 Web 服务器运行的编程语言如 PHP，Python，Perl，Ruby，Java，服务器端JavaScript或其他，可以更新数据库中的数据并使用它为用户动态构建网页

2.1 MariaDB 介绍

MariaDB数据库管理系统是MySQL数据库的一个分支，主要由开源社区维护，采用GPL授权许可。开发这个MariaDB数据库分支的可能原因之一是甲骨文公司收购了MySQL后，有将MySQL 闭源的潜在风险，因此MySQL开源社区采用分支的方式来避开这个风险
MariaDB数据库完全兼容MySQL数据库，包括API和命令行，使之能轻松的成为MySQL的代替品。
MariaDB数据库管理系统可以包含多个database，每个database包涵多张表
关系数据库的表采用二维表格来存储数据，类似于Excle工作表。表中的一行即为一个元组，或称为一条记录
数据表中的每一列称为一个字段（属性），表是由其包含的各种字段定义，每个字段描述了
它所含有的数据意义，为每个字段分配一个数据类型，定义它们的数据长度和其他属性
行和列的交叉位置表示某个属性值

部署 MariaDB

安装 MariaDB

# 安装服务端
[root@server ~]# yum install -y mariadb-server
# 安装客户端
[root@client ~]# yum install -y mariadb
# 启用并启动服务
[root@server ~]# systemctl enable --now mariadb
# 配置防火墙
[root@server ~]# firewall-cmd --permanent --add-service=mysql
[root@server ~]# firewall-cmd --reload

MariaDB 进程

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

两个进程的核心作用

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

  1. mysqld_safe 并不是数据库的核心服务进程，而是一个监控和守护程序，主要负责：
  • 安全启动 mysqld 核心进程，处理启动前的环境检查（比如配置文件加载、权限验证）；
  • 实时监控 mysqld 进程状态，如果 mysqld 意外崩溃/退出， mysqld_safe 会自动重启它，保证服务不中断；
  • 统一管理日志输出（把 mysqld 的日志重定向到指定日志文件，比如 error.log ）；
  • 处理一些启动参数的兼容转换，简化用户的启动配置
  1. 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

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

[root@server ~]# mysql_secure_installation

交互式提示进行更改：

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

连接 MariaDB

MariaDB客户端可以通过两种方式连接到服务器：

  • 客户端与服务器在同一台计算机上运行，则它可以使用特殊的套接字文件与MariaDB通信,这种方式更安全，因为MariaDB不需要侦听来自网络客户端的连接。 但是使用该数据库的服务都需要在MariaDB服务器上运行，会影响性能
[root@server ~ ]# 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服务。 远程服务器与MariaDB服务器运行在不同主机。 此时服务器需要配置侦听端口3306/TCP上的连接。
#数据库服务器本机创建连接用户
[root@server ~ ]# mysql -u root -p
MariaDB [(none)]> create user  user4test@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 客户端测试
[root@client ~]# mysql -u  user4test -p123 -h server

配置 MariaDB

默认情况下，MariaDB 侦听系统中所有网络地址上3306/TCP端口。
在CentOS7中，配置文件采用主配置文件 + 细分配置文件的分层结构。

主配置文件：

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

  • 全局入口，优先读取。
  • 通过 !includedir /etc/my.cnf.d/ 指令加载细分文件。

细分配置文件：

/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 等），简化客户端连接操作
    参数需放在 [client] 通用段落中（所有客户端工具都会读取）
    常见配置项：
  • 默认用户名（ user = root ）、默认密码（ password = 123456 ，不推荐明文存储）
  • 默认主机（ host = localhost ）、默认端口（ port = 3306 ）
  • 字符集（ default-character-set = utf8mb4 ）

MariaDB 中的 SQL

什么是 SQL?

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

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

SQL 语句常见可分为以下几类：

  • DQL–查询：Data Query Language：用于从表中检索数据。常见关键字： SELECT、FROM、WHERE、ORDER BY、GROUP BY、HAVING

  • DML–增删改：Data Manipulation Language：用于对表中的数据进行增删改。常见语句包括 INSERT、UPDATE、DELETE

  • DDL–结构定义：Data Definition Language：用于定义或修改数据库对象（如库、表、索引）。常见语句包括 CREATE、ALTER、DROP、TRUNCATE，例如 CREATE TABLE/DROP TABLE/CREATE INDEX

  • TCL–事务控制：Transaction Control Language：用于控制事务的开始、提交与回滚，确保 DML 操作满足原子性、一致性等特性。常见语句包括 START TRANSACTION（或 BEGIN）、COMMIT、ROLLBACK、SAVEPOINT

  • DCL–权限控制：Data Control Language：用于权限控制，决定用户/用户组对数据库对象的访问权限。常见语句包括 GRANT、REVOKE

连接数据库

mariadb软件包提供命令mysql，该命令支持对MariaDB数据库的交互式和非交互式访问。

  • 交互式执行时，结果以ASCII表的格式显示。
  • 非交互执行时，结果以制表符分隔的格式显

示例：

[root@server ~]# mysql -u root -h localhost -p

首次安装时，MariaDB默认设置root用户帐户无需密码即可进行访问

[root@server ~]# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or  g.
Your MariaDB connection id is 8
Server version: 10.3.17-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)]> SELECT USER();
+------------------+
| USER() |
+------------------+
| root@localhost |
+------------------+
1 row in set (0.003 sec)

执行交互式SQL语句时，MariaDB提示符在方括号中显示当前选择的数据库

数据库操作

查询数据库列表
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 user4test;
Query OK, 1 row affected (0.010 sec)
MariaDB [mysql]> USE user4test;
Database changed

删除数据库

DROP DATABASE 语句删除数据库中的所有表并删除数据库，这将破坏数据库中的所有数据不可恢复（除非有备份！），只有对该数据库具有DROP权限的用户才能运行此语句

删除数据库时，不会自动删除用户权限记录不会更改数据库的用户特权。所以如果重新创建具有该名称的数据库，则为旧数据库设置的用户权限仍然有效

1.  MariaDB [inventory]> DROP DATABASE user4test;
2.  Query OK, 0 rows affected (0.006 sec)

表操作

SQL C.R.U.D. 语句

  • 创建：create
  • 读取：read
  • 更新：update
  • 删除：delete
环境
[root@server ~]# mysql -uroot -p123 -e 'create database inventory;'
[root@server ~]# mysql -uroot -p123 inventory < inventory.dump

查询表

查询表的列表
MariaDB [inventory]> SHOW TABLES;
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
+---------------------+
3 rows in set (0.001 sec)
查询表的结构
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)

输出显示，表格中有：

  • DESCRIBE命令返回的表头列：Field、Type、Null、Key、Default、Extras 即字段名、数据类型、是否为空、主键、默认值、额外信息
    • Field 列，显示该属性名称。
    • Type 列，显示该属性的数据必须采用的格式。例如，stock属性必须是最多11位数字的整数。
    • Null 列，指示此属性是否可以为null。
    • Default 列，指示如果未指定该属性，则是否设置了默认值。
    • Key 列，显示属性ID是primary key。 主键是表中一行的唯一标识符。 该属性的任何其他行都不能具有相同的值。
    • Extra列，提供该列额外信息，对于id字段标记为auto_increment。 这意味着每次将新项目插入表中时，该条目的属性值都会增加。 这样可以更轻松地使数字主键保持唯一。
  • 业务表的字段名：id、name、price、stock、id_category、id_manufacturer

字段（column）是数据库表中的，记录数据类型和含义
行（row）是数据库表中的，记录具体的数据

查询表中数据

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

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
= 等于
<> 不等于（注意：在某些 SQL 版本中也可写作 !=）
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在一个包含边界的范围内
LIKE 按模式匹配
IN 指定某列的多个可能值 
# 条件操作符包括：=、<>、>、<、>=、<=
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,'user1',28,10);
MariaDB [inventory]> INSERT INTO staff (id,name,age) VALUES (2,'user2',20);
MariaDB [inventory]> INSERT INTO staff (id,name) VALUES (3,'uesr3');
更新记录
1.  MariaDB [inventory]> UPDATE staff SET age=30 WHERE id=3;
2.  MariaDB [inventory]> UPDATE staff SET age=30;

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

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

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

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

管理 MariaDB 用户

创建用户账户

默认情况下，MariaDB有自己的用户和密码，与本地系统的用户和密码分开。这意味着MariaDB 数据库用户与服务器的Linux用户不同，即使用户帐户具有相同的名称。
为了控制用户对数据库服务器的访问级别，必须在MariaDB中设置数据库用户并授予他们在服务器及其数据上执行操作的权限。

可以配置MariaDB使用 pam 身份验证插件将系统用户帐户和密码集成为MariaDB数据库用户，我将不会介绍此配置，在大多数情况下，最好分开管理数据库和系统用户账户。

要创建新用户， 需要以下权限级别之一：

  • MariaDB的 root 用户。
  • 被授予全局 CREATE USER 特权的用户。
  • 被授予对mysql数据库的INSERT特权的用户。
用户帐户定义示例
帐户 说明
test 或 test@‘%’ 用户 test 可从任意主机连接。
test@‘localhost’ 用户 test 只能从本机（localhost）连接。
test@‘192.168.1.5’ 用户 test 只能从 IP 地址 192.168.1.5 连接。
test@‘192.168.1.%’ 用户 test 可从属于 192.168.1.0/24 网段的任意地址连接。
test@‘2001:db8:18:b51:c32:a21’ 用户 test 可从 IPv6 地址 2001:db8:18:b51:c32:a21 连接。

可以使用CREATE USER语句在mysql数据库的user表中创建一条新记录
用户名格式：user_name@host_name 这样就可以根据主机源，创建具有相同名称但具有不同特权的多个用户帐户。

MariaDB [(none)]> CREATE USER user4test@'%' IDENTIFIED BY '123';

当前，user4test 帐户可以使用密码 123 从任意主机连接，用户的密码会被加密存在在mysql.user表：

MariaDB [mysql]> SELECT host,user,password FROM user WHERE user = 'user4test';
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| %         | user4test | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+--------+-------------------------------------------+
1 row in set (0.000 sec)

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

授予用户权限

GRANT语句可用于向帐户授予特权。 要授予GRANT特权，连接的用户必须具有GRANT OPTION且必须具有他们所授予的特定特权。

Mariadb 按范围粒度划分授权，从全局即整个数据库实例，到列级即单表某列逐步缩小，不同范围对应不同的授权对象和业务场景。

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

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

管理员级全局权限是指作用于*.* 即所有数据库、所有表的最高级别权限，通常只授予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'@'%';
数据库范围

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

数据操作类（增删改查），这类权限控制对数据库内表数据的读写操作，是最基础的业务权限
权限名称 作用 授权示例
SELECT 允许查询数据库内所有表的数据（读权限） GRANT SELECT ON app_db. * TO ‘Rich’@‘localhost’;
INSERT 允许向数据库内所有表插入数据（写权限） GRANT INSERT ON app_db. * TO ‘Rich’@‘localhost’;
UPDATE 允许修改数据库内所有表的数据 GRANT UPDATE ON app_db. * TO ‘Rich’@‘localhost’;
DELETE 允许删除数据库内所有表的数据 GRANT DELETE ON app_db. * TO ‘Rich’@‘localhost’;

结构管理类（表/索引/视图管理），这类权限控制数据库内表、索引、视图等对象的创建/修改/删除，属于结构层权限：
权限名称 作用 授权示例
CREATE 允许在数据库内创建表、视图、索引 GRANT CREATE ON app_db. * TO ‘Rich’@‘localhost’;
ALTER 允许修改数据库内表的结构（ 如 ALTER TABLE ） GRANT ALTER ON app_db. * TO ‘Rich’@‘localhost’;
DROP 允许删除数据库内的表、视图 GRANT DROP ON app_db. * TO ‘Rich’@‘localhost’;
INDEX 允许为数据库内的表创建/删除索引（注： CREATE 权限已包含此能力） GRANT INDEX ON app_db. * TO ‘Rich’@‘localhost’;
CREATE VIEW 允许在数据库内创建视图 GRANT CREATE VIEW ON app_db. * TO’Rich’@‘localhost’;
SHOW VIEW 允许查看数据库内视图的定义（如 SHOW CREATE VIEW ） GRANT SHOW VIEW ON app_db. * TO’Rich’@‘localhost’;

特殊操作类，这类权限针对数据库内的特殊操作，如存储过程、锁表等：
权限名称 作用 授权示例
CREATE ROUTINE 允许在数据库内创建存储过程/函数 GRANT CREATE ROUTINE ON app_db. * TO’Rich’@‘localhost’;
ALTER ROUTINE 允许修改/删除数据库内的存储过程/函数 GRANT ALTER ROUTINE ON app_db. * TORich’@‘localhost’;
EXECUTE 允许执行数据库内的存储过程/函数 GRANT EXECUTE ON app_db. * TO’Rich’@‘localhost’;
LOCK TABLES 允许锁定数据库内的表（如 LOCK TABLES ） GRANT LOCK TABLES ON app_db. * TO’Rich’@‘localhost’;
示例： 
-- 例1：业务全权限（增删改查 + 结构管理）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
ON app_db.*
TO 'Rich'@'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';
表范围
  • 核心数据操作类（最常用），这类权限控制对单表数据的读写，是业务场景中最基础的表级权限：
权限名称 作用 授权示例（以 app_db.users 表为例）
SELECT 允许查询该表的所有列数据（读权限） GRANT SELECT ON app_db.users TO ‘Rich’@‘localhost’;
INSERT 允许向该表插入新数据写权限） GRANT INSERT ON app_db.users TO ‘Rich’@‘localhost’;
UPDATE 允许修改该表的现有数据 GRANT UPDATE ON app_db.users TO ‘Rich’@‘localhost’;
DELETE 允许删除该表的现有数据 GRANT DELETE ON app_db.users TO ‘Rich’@‘localhost’;
  • 表结构管理类，这类权限控制单表的结构修改、索引管理等，属于「结构层」权限：
权限名称 作用 授权示例
ALTER 允许修改该表的结构（如添加/删除列、修改列类型） GRANT ALTER ON app_db.users TO ‘Rich’@‘localhost’;
DROP 允许删除该表（慎用） GRANT DROP ON app_db.users TO ‘Rich’@‘localhost’;
INDEX 允许为该表创建/删除索引 （注：MariaDB 5.5 中 ALTER 权限已包含此能力） GRANT INDEX ON app_db.users TO ‘Rich’@‘localhost’;
CREATE VIEW 基于该表创建视图（需同时有该表的 SELECT 权限） GRANT CREATE VIEW ON app_db.users TO ‘Rich’@‘localhost’;
SHOW VIEW 查看基于该表创建的视图定义 （如 SHOW CREATE VIEW ） GRANT SHOW VIEW ON app_db.users TO ‘Rich’@‘localhost’;
  • 特殊操作类，这类权限针对单表的特殊操作，如锁表、触发器管理等：
权限名称 作用 授权示例
LOCK TABLES 允许锁定该表（用于并发控制） GRANT LOCK TABLES ON app_db.users TO ‘Rich’@‘localhost’;
CREATE TRIGGER 允许为该表创建触发器 GRANT CREATE TRIGGER ON app_db.users TO ‘Rich’@‘localhost’;
ALTER TRIGGER 允许修改/删除该表的触发器 GRANT ALTER TRIGGER ON app_db.users TO ‘Rich’@‘localhost’;
EXECUTE 允许执行该表关联的存储过程/函数（若有） GRANT EXECUTE ON app_db.users TO ‘Rich’@‘localhost’;

示例

-- 示例1：业务读写权限（最常用）
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'Rich'@'localhost';
FLUSH PRIVILEGES;
-- 示例2：表结构管理权限
GRANT ALTER, INDEX ON app_db.users TO 'Rich'@'localhost';
FLUSH PRIVILEGES;
-- 示例3：只读权限（报表/分析用）
GRANT SELECT, SHOW VIEW ON app_db.users TO 'report_user'@'localhost';
FLUSH PRIVILEGES;
列范围

列级权限仅作用于数据库名、表名、列名，是表级权限的精细化补充

MariaDB 5.5 仅支持对SELECT、INSERT、UPDATE 这 3 类操作做列级限制，其他操作如 DELETE 无列级权限控制。所有授权后需执行 FLUSH PRIVILEGES; 生效

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

示例

-- 示例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'@'%';

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

查询用户权限

MariaDB [(none)]> SHOW GRANTS FOR root@localhost;
+-----------------------------------------------------------------------
| Grants for root@localhost
|
+-----------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY
| PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' WITH GRANT OPTION 
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
|
+-----------------------------------------------------------------------
2 rows in set (0.006 sec)

关键注意事项

  1. 最小权限原则：优先授予“刚好够用”的权限（如业务开发仅授予数据库级权限，而非全局），避免过度授权
  2. 权限生效：所有授权后需执行 FLUSH PRIVILEGES;
  3. 用户范围限制：生产环境避免 @‘%’ （任意主机），替换为具体 IP（如@‘192.168.1.100’ ）或网段（ @‘192.168.1.%’ ）
  4. 查看权限：验证授权结果，避免配置错误
-- 查看指定用户的所有权限
SHOW GRANTS FOR 'Rich'@'%';
-- 查看当前登录用户的权限
SHOW GRANTS;

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

回收用户权限

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

#回收用户权限
MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT
-> ON inventory.category FROM Rich@localhost;
Query OK, 0 rows affected (0.011 sec)

删除用户

当不再需要特定的用户帐户时，可以使用DROP USER将其从数据库中删除
用户名使用 ‘user’@‘host’ 格式。

1.  MariaDB  [(none) ]> DROP USER user4test@localhost;
2.  Query OK, 0 rows affected (0.001 sec)

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

更改用户密码

# root用户修改普通用户账户密码
MariaDB [(none)]> USE mysql;
MariaDB [(mysql)]> UPDATE user SET password=PASSWORD('mypass') WHERE
user='Rich' and host=’localhost’;
# 或者
MariaDB [(none)]> SET PASSWORD FOR 'Rich'@'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特权。

意外场景

忘记 root 用户密码

  1. 编辑**/etc/my.cnf.d/server.cnf**配置文件，在 [mysqld ]下添加 skip-grant-tables=1 。
  2. 重启 mariadb 服务。
  3. 执行 mysql -u root 命令进入mysql命令行，修改root用户密码。
[root@server ~]# mysql -u root
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('新密码')
where USER='root';
MariaDB [(none)]> exit
  1. 把**/etc/my.cnf.d/server.cnf配置文件中的 skip-grant-tables=1 注释掉，然后重启mariadb**服务。
[root@server ~]# systemctl restart mariadb

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

  • step1: 停止 MySQL 服务
  • step2: 跳过权限表启动 MySQL
  • step3: 无密码登录 MySQL
  • step4: 重建 root 账户（分版本处理）
    • 场景 1：MySQL 5.7 版本
#切换到 mysql 系统库
USE mysql;

#重置 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='%'
#刷新权限（使账户生效）
FLUSH PRIVILEGES; 

#验证 root 账户是否创建成功
SELECT User, Host FROM mysql.user WHERE User='root';
  • 场景 2：MySQL 8.0 版本
#先刷新权限（启用权限校验，否则 GRANT 命令失效）
FLUSH PRIVILEGES;

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

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

#刷新权限
FLUSH PRIVILEGES;

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

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

#重启 MySQL 服务
systemctl restart mariadb

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

  • step1: 停止 MySQL 服务
  • step2: 跳过权限表启动 MySQL
  • step3: 无密码登录 MySQL
  • step4: 重建 root 账户（分版本处理）
    • 场景 1：MySQL 5.7 版本
#切换到 mysql 系统库
USE mysql;
#插入 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');
#刷新权限 使账户生效
FLUSH PRIVILEGES;
#验证 root 账户是否创建成功
SELECT User, Host FROM mysql.user WHERE User='root';

备份和恢复

备份方式

逻辑备份
# mariadb # 服务器 # 数据库
Logo
openEuler 社区

openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构

更多推荐

从“解释世界“到“让事情发生“：AI时代最该升级的不是工具，而是你的思维操作系统

在复杂系统中，允许"有效但暂时不可解释"的结果先行发生，再通过快速迭代逼近理解。① 概率思维替代因果思维不再追问"为什么A导致B"，而是评估"如果做A，B发生的概率是多少"。AI输出的置信度分数，比人类的因果叙事更接近真实的不确定性。② 快速验证替代完美论证用最小成本让假设"发生"——一个MVP、一次A/B测试、一轮AI辅助的模拟推演。在行动中收集反馈，而非在论证中消耗机会窗口。③ 事后解释替代事

avatar openEuler 社区
cover

【ftplib，一个FTP的 Python 库！】标准库玩转文件传输自动化

avatar openEuler 社区
cover

【FastAPI】 源码详解（一）：四层架构与请求生命周期——从 ASGI 到依赖注入

avatar openEuler 社区