最新资讯

MariaDB 数据库管理

介绍 MariaDB

数据库介绍

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

数据结构，是指数据的组织形式或数据之间的联系。

数据库种类

按照早期的数据库理论，比较流行的数据库模型有三种，分别为：

• 层次式数据库，以层次模型建立的数据库，例如文件系统、DNS。
• 网状数据库，以网状模型建立的数据库，例如网络通信。
• 关系型数据库，以二维表模型建议的数据库，例如图书馆管理系统。

在当今的互联网企业中，最常用的数据库模式主要有两种：

• 关系型数据库，把复杂的数据结构归结为简单的二维表形式。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。例如：Oracle、MySql和PostgreSQL等。
• 非关系型数据库，也被称为 NoSQL 数据库，本意是“Not Only SQL”，而非“NO SQL”的意思，因此，NoSQL的产生并不是要彻底否定关系型数据库，而是作为传统数据库的一个有效补充。典型产品有Redis（持久化缓存）、Mongodb、Memcached（纯内存）等。

关系数据库

关系数据库将结构化数据存储在一组相互关联的表中。 这意味着一个表中的信息可以轻松地与另一表中的信息组合在一起，以处理和提供有关该数据的报告。

关系数据库管理系统（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或其他，可以更新数据库中的数据并使用它为用户动态构建网页。

由于关系数据库通常与系统管理员管理的应用程序集成在一起，因此即使您不是数据库管理员，也有必要了解这些服务及其数据的安装和管理知识。

部署 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）的标准运行机制。

两个进程的核心作用

1. mysqld_safe 并不是数据库的核心服务进程，而是一个监控和守护程序，主要负责：

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

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

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

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

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

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

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

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

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

进程关系与验证方式

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

# 查看进程的父子关系（以 CentOS/RHEL 为例）
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）。

特殊场景：仅看到 mysqld 进程

如果你的 MariaDB 启动后只有 mysqld 进程，没有 mysqld_safe，通常是因为：

1. 使用了 systemd 管理（现代 Linux 系统）：systemctl start mariadb 会直接启动 mysqld，由 systemd 替代 mysqld_safe 做守护；
2. 手动以后台方式启动 mysqld：比如 mysqld --daemonize，此时没有守护进程，崩溃后不会自动重启。

加固 MariaDB

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

[root@server ~]# mysql_secure_installation

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

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

连接 MariaDB

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

• 如果客户端与服务器在同一台计算机上运行，则它可以使用特殊的套接字文件(socket)与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 bao@'%' 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 bao -p123 -h server
  

配置 MariaDB

默认情况下，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 等），简化客户端连接操作。

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

  常见配置项：

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

  示例：

  [mysql]
  user=bao
  password=123
  host=server
  port=3306
  # database=test
  

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

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

MariaDB 中 SQL

SQL 介绍

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软件包提供命令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 bao;
Query OK, 1 row affected (0.010 sec)

MariaDB [mysql]> USE bao;
Database changed

删除数据库

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

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

表操作

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

环境准备

# 导入备份
[root@server ~]# mysql -uroot -p123 -e 'create database inventory;'
[root@server ~]# mysql -uroot -p123 inventory < inventory.dump

查询表

查询表列表

# 查询表列表
MariaDB [(none)]> USE inventory;
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)

MariaDB [inventory]> 

输出显示，表格中有六列（属性）：

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

查询表中数据

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

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)

条件操作数

# 条件操作符包括：=、<>、>、<、>=、<=
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,'bao1',28,10);
MariaDB [inventory]> INSERT INTO staff (id,name,age) VALUES (2,'bao2',20);
MariaDB [inventory]> INSERT INTO staff (id,name) VALUES (3,'bao3');

更新记录

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 用户

创建用户账户

默认情况下，MariaDB有自己的用户和密码，与本地系统的用户和密码分开。这意味着MariaDB数据库用户与服务器的Linux用户不同， 即使用户帐户具有相同的名称。

为了控制用户对数据库服务器的访问级别，必须在MariaDB中设置数据库用户并授予他们在服务器及其数据上执行操作的权限。

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

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

• MariaDB的 root 用户。
• 被授予全局 CREATE USER 特权的用户。
• 被授予对mysql数据库的INSERT特权的用户。

用户帐户定义示例

我们可以使用CREATE USER语句，在mysql数据库的user表中创建一条新记。

用户名格式：user_name@host_name。 这样就可以根据主机源，创建具有相同名称但具有不同特权的多个用户帐户。

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

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

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

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

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

授予用户权限

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

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

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

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

1. 全局范围

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

常用权限

示例

-- 示例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; 生效。

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

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

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

示例

-- 示例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; 使权限立即生效。

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

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

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

示例

-- 示例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 类）

示例

注意：列级授权需显式指定列名，且仅支持 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 [(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 'bao'@'%';
   -- 查看当前登录用户的权限
   SHOW GRANTS;
   

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

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

常见场景权限参考

回收用户权限

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

MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT
    -> ON inventory.category FROM @localhost;
Query OK, 0 rows affected (0.011 sec)

删除用户

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

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

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

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

更改用户密码

# root用户修改普通用户账户密码
MariaDB [(none)]> USE mysql;
MariaDB [(mysql)]> UPDATE user SET password=PASSWORD('mypass') WHERE user='bao' and host=’localhost’;

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

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

排故数据库访问

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

思考

1. 忘记 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
   

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）。
• 在服务器联机时执行。
• 备份不包括日志或配置文件。

物理备份

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

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

执行备份

执行逻辑备份

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.

示例：

[root@server ~]# mysqldump -u root -p inventory > /backup/inventory.dump
[root@server ~]# mysqldump -u root -p --all-databases > /backup/mariadb.dump

导出的文本文件部分内容示例：

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 ~]# systemctl stop mariadb

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

执行恢复

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

恢复逻辑备份

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

恢复物理备份

mariabackup工具使用以下选项之一，以从物理备份执行还原：

• –copy-back，保留原始备份文件。
• –move-back，将备份文件移动到数据目录，然后删除原始备份文件。

操作过程如下：

# 停止mariadb服务
[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