MariaDB 数据库从入门到实战完全指南

2401_83463699

501人浏览 · 2026-06-08 21:17:42

2401_83463699 · 2026-06-08 21:17:42 发布

一、数据库介绍

数据库是存放计算机数据的仓库，它按照一定的数据结构对数据进行组织和存储，并提供了多种方法来管理数据。

数据结构：数据的组织形式或数据之间的联系。

1.1 数据库种类

根据早期数据库理论，主要有三种模型：

层次式数据库（如文件系统、DNS）
网状数据库（如网络通信）
关系型数据库（如图书馆管理系统）

当今互联网企业常用的数据库模式：

类型	说明	典型产品
关系型数据库	将复杂结构归结为二维表形式，通过表间运算实现管理	Oracle, MySQL, PostgreSQL, MariaDB
非关系型数据库	NoSQL（Not Only SQL），作为传统数据库的有效补充，不否定关系数据库	Redis（持久化缓存）, MongoDB, Memcached（纯内存）

1.2 数据库排名

参考 DB-Engines 排名（2026-01）

在这里插入图片描述

1.3 关系数据库

关系数据库将结构化数据存储在相互关联的表中，通过 SQL 进行管理和查询。

**关系数据库管理系统（RDBMS）**是管理关系数据库的软件，在许多业务场景中起关键作用，例如 LAMP/LNMP 架构：

Linux：操作系统基础环境
Apache / Nginx：Web 服务器
MariaDB/MySQL：存储站点数据
编程语言（PHP, Python, Java, JS 等）：动态处理数据并生成网页

即使不是专职 DBA，了解关系数据库的安装、管理知识也是必要的。

二、MariaDB 介绍

2.1 概述

MariaDB 是 MySQL 的一个分支，由开源社区维护，采用 GPL 授权。由于甲骨文收购 MySQL 后存在闭源风险，社区创建了 MariaDB 分支。

MariaDB 完全兼容 MySQL（API 和命令行），可轻松作为 MySQL 的替代品。

2.2 存储结构

MariaDB 可包含多个 database，每个数据库包含多张 table。

在这里插入图片描述

关系数据库的表采用二维表格存储数据，类似于 Excel 工作表：

元组/记录：表中的一行
字段/属性：表中的一列，每个字段有数据类型、长度等定义
属性值：行与列的交叉位置

在这里插入图片描述

三、MariaDB 部署

3.1 安装数据库

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

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

# 启用并启动服务
[root@server ~ 09:51:31]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

3.2 数据库进程

MariaDB 启动后会出现 mysqld_safe 和 mysqld 两个进程，这是标准运行机制。

核心进程

进程	作用
`mysqld`	核心进程：处理客户端连接、SQL 执行、内存/磁盘数据管理、锁机制、事务、复制等
`mysqld_safe`	监控守护进程：安全启动 `mysqld`，崩溃自动重启，统一日志输出，参数兼容转换

进程关系

mysqld_safe 是父进程，mysqld 是其启动的子进程。验证命令：

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

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

输出示例：

[root@server ~ 12:23:01]# ps -ef | grep -E 'mysqld_safe|mysqld'
mysql      1643      1  0 09:51 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql      1808   1643  0 09:51 ?        00:00:12 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql ...

mysqld_safe 由 root 运行（需要高权限做环境准备）
mysqld 由 mysql 普通用户运行（安全原则）

特殊场景

如果只有 mysqld 没有 mysqld_safe，通常是因为：

使用 systemd 管理（现代 Linux）：systemd 替代了守护功能
手动后台启动 mysqld（如 mysqld --daemonize），崩溃不会自动重启

3.3 加固数据库

运行 mysql_secure_installation 进行安全配置：

设置 root 密码
禁止 root 远程访问
删除匿名用户
删除 test 数据库

[root@server ~ 09:52:01]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
...

3.4 配置数据库

MariaDB 默认监听所有网络地址的 3306/TCP 端口。配置文件采用分层结构：

主配置文件

/etc/my.cnf 是全局主配置入口，可通过 !includedir /etc/my.cnf.d/ 加载细分配置文件。

细分配置文件

文件	用途
`/etc/my.cnf.d/server.cnf`	服务器端参数（`[mysqld]` 段落），如端口、监听地址、数据目录、缓存大小、存储引擎等
`/etc/my.cnf.d/client.cnf`	客户端工具通用参数（`[client]` 段落），如默认用户名、密码、主机、端口、字符集
`/etc/my.cnf.d/mysql-clients.cnf`	与 `client.cnf` 类似，更偏向 `mysql` 命令行客户端专属优化

例如禁止网络连接（只允许本地 socket 通信）：

[mysqld]
skip-networking = 1

3.5 连接数据库

本地连接（socket）

[root@server ~ 10:06:37]# mysql -uroot -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
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)]> GRANT ALL PRIVILEGES ON *.* TO 'liu'@'%' IDENTIFIED BY '123';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

客户端配置 /etc/my.cnf.d/client.cnf：

# 客户端测试
[root@client ~ 10:28:46]# vim /etc/hosts
[root@client ~ 10:30:55]# egrep -v '^#|^S' /etc/my.cnf.d/client.cnf 
[client]
user=liu
password=123
host=server
port=3306
database=mysql

然后直接执行 mysql 即可登录。

[root@client ~ 10:31:22]# mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
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 [mysql]> exit
Bye

非交互方式连接

使用 -e 选项执行单条 SQL：

[root@client ~ 12:28:16]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

配置登录提示符

在 [client-mariadb] 段落中添加 prompt 配置：

[root@client ~ 12:32:07]# vim /etc/my.cnf.d/client.cnf
[client-mariadb]
prompt="\\u@\\h [\\d]> "

登录效果：

[root@client ~ 12:32:58]# mysql -u liu -p -h 10.1.8.10 mysql
liu@10.1.8.10 [mysql]>

四、结构化查询语言（SQL）

4.1 SQL 概述

SQL（Structured Query Language）于 1986 年由美国国家标准局颁布，是关系数据库最重要的操作语言。

SQL 语句分类：

类别	说明	关键字
DQL（数据查询语言）	从表中获取数据	`SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`
DML（数据操作语言）	增删改数据	`INSERT`, `UPDATE`, `DELETE`
DDL（数据定义语言）	定义或删除数据库对象	`CREATE`, `DROP`, `ALTER`
TPL（事务处理语言）	确保 DML 语句的原子性	`BEGIN`, `COMMIT`, `ROLLBACK`
DCL（数据控制语言）	权限授予与回收	`GRANT`, `REVOKE`

ACID 特性（事务正确执行的四个要素）：

原子性：事务中的所有操作要么全部成功，要么全部失败。

一致性：事务执行前后数据库保持一致性状态。

隔离性：并发事务之间互不干扰。

持久性：事务一旦提交，更改永久生效（即使系统故障）。

4.2 数据库操作

查询数据库列表

liu@server [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| mysql              |
| performance_schema |
+--------------------+

系统数据库说明：

mysql：保存用户、权限等系统信息
information_schema：保存元数据信息
performance_schema：保存性能监控信息

使用数据库

liu@server [(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
liu@server [mysql]>

SQL 语句不区分大小写，但数据库名称区分大小写，通常 SQL 使用大写，库名使用小写。

创建数据库

liu@server [mysql]> CREATE DATABASE webapp;
Query OK, 1 row affected (0.00 sec)

liu@server [mysql]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| webapp             |
+--------------------+

删除数据库

liu@server [webapp]> DROP DATABASE webapp;
Query OK, 0 rows affected (0.01 sec)

删除数据库不会自动删除该数据库的用户权限，若重新创建同名数据库，原用户权限依然有效。

4.3 表操作

环境准备

# 导入备份
[root@client ~ 11:10:41]# mysql -uliu -p123 -h server inventory < inventory.middle.sql

查询表

查询表列表

liu@server [inventory]> SHOW TABLES;
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
+---------------------+
3 rows in set (0.00 sec)

查询表结构

liu@server [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.00 sec)

字段含义：

Field：属性名称
Type：数据类型及长度
Null：是否允许为空
Key：PRI 表示主键，唯一标识一行
Default：默认值
Extra：额外信息，如 auto_increment（自增）

查询表中数据

查询所有记录所有字段：

liu@server [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 |
|  5 | Dell XPS 15                   | 1299.99 |    15 |           4 |               5 |
|  6 | HP Pavilion 14                |  799.99 |    25 |           4 |               6 |
|  7 | Samsung 27-inch 4K            |  349.99 |    30 |           5 |               8 |
|  8 | Logitech Mechanical K845      |   89.99 |    50 |           6 |               7 |
|  9 | Acer Swift 3                  |  699.99 |    18 |           4 |               9 |
| 10 | Microsoft Surface Pro 9       | 1199.99 |    12 |           4 |              10 |
| 11 | Kingston A400 240GB           |   49.99 |    80 |           3 |               2 |
| 12 | Asus TUF Gaming VG27AQ        |  399.99 |    22 |           5 |               3 |
| 13 | Dell OptiPlex 7010            |  899.99 |    16 |           2 |               5 |
| 14 | HP Z2 Mini Workstation        | 1499.99 |     8 |           2 |               6 |
| 15 | Logitech G502 Hero            |   79.99 |    40 |           7 |               7 |
| 16 | HP LaserJet Pro M428fdw       |  499.99 |    12 |           8 |               6 |
| 17 | Samsung T7 Shield 2TB         |  199.99 |    35 |           9 |               8 |
| 18 | Kingston Fury Beast 16GB DDR4 |   44.99 |    60 |          10 |               2 |
| 19 | Dell Ultrasharp U2419H        |  299.99 |    20 |           5 |               5 |
| 20 | Acer Nitro AN515-57           |  999.99 |    14 |           4 |               9 |
+----+-------------------------------+---------+-------+-------------+-----------------+
20 rows in set (0.00 sec)

liu@server [inventory]> SELECT * FROM category;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | Networking    |
|  2 | Servers       |
|  3 | Ssd           |
|  4 | Laptops       |
|  5 | Monitors      |
|  6 | Keyboards     |
|  7 | Mice          |
|  8 | Printers      |
|  9 | External HDDs |
| 10 | RAM Modules   |
+----+---------------+
10 rows in set (0.00 sec)

liu@server [inventory]> SELECT * FROM category;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | Networking    |
|  2 | Servers       |
|  3 | Ssd           |
|  4 | Laptops       |
|  5 | Monitors      |
|  6 | Keyboards     |
|  7 | Mice          |
|  8 | Printers      |
|  9 | External HDDs |
| 10 | RAM Modules   |
+----+---------------+
10 rows in set (0.00 sec)

查询指定字段：

liu@server [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 |
| Dell XPS 15                   | 1299.99 |    15 |
| HP Pavilion 14                |  799.99 |    25 |
| Samsung 27-inch 4K            |  349.99 |    30 |
| Logitech Mechanical K845      |   89.99 |    50 |
| Acer Swift 3                  |  699.99 |    18 |
| Microsoft Surface Pro 9       | 1199.99 |    12 |
| Kingston A400 240GB           |   49.99 |    80 |
| Asus TUF Gaming VG27AQ        |  399.99 |    22 |
| Dell OptiPlex 7010            |  899.99 |    16 |
| HP Z2 Mini Workstation        | 1499.99 |     8 |
| Logitech G502 Hero            |   79.99 |    40 |
| HP LaserJet Pro M428fdw       |  499.99 |    12 |
| Samsung T7 Shield 2TB         |  199.99 |    35 |
| Kingston Fury Beast 16GB DDR4 |   44.99 |    60 |
| Dell Ultrasharp U2419H        |  299.99 |    20 |
| Acer Nitro AN515-57           |  999.99 |    14 |
+-------------------------------+---------+-------+
20 rows in set (0.00 sec)

WHERE 子句

常用条件操作符：

操作符	描述
`=`	等于
`<>` / `!=`	不等于
`>`	大于
`<`	小于
`>=`	大于等于
`<=`	小于等于
`BETWEEN`	在某个范围（包含边界）
`LIKE`	模式匹配
`IN`	指定多个可能值

示例：

liu@server [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 |
|  5 | Dell XPS 15             | 1299.99 |    15 |           4 |               5 |
|  6 | HP Pavilion 14          |  799.99 |    25 |           4 |               6 |
|  7 | Samsung 27-inch 4K      |  349.99 |    30 |           5 |               8 |
|  9 | Acer Swift 3            |  699.99 |    18 |           4 |               9 |
| 10 | Microsoft Surface Pro 9 | 1199.99 |    12 |           4 |              10 |
| 12 | Asus TUF Gaming VG27AQ  |  399.99 |    22 |           5 |               3 |
| 13 | Dell OptiPlex 7010      |  899.99 |    16 |           2 |               5 |
| 14 | HP Z2 Mini Workstation  | 1499.99 |     8 |           2 |               6 |
| 16 | HP LaserJet Pro M428fdw |  499.99 |    12 |           8 |               6 |
| 17 | Samsung T7 Shield 2TB   |  199.99 |    35 |           9 |               8 |
| 19 | Dell Ultrasharp U2419H  |  299.99 |    20 |           5 |               5 |
| 20 | Acer Nitro AN515-57     |  999.99 |    14 |           4 |               9 |
+----+-------------------------+---------+-------+-------------+-----------------+
15 rows in set (0.00 sec)

liu@server [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 |
|  5 | Dell XPS 15             | 1299.99 |    15 |           4 |               5 |
|  6 | HP Pavilion 14          |  799.99 |    25 |           4 |               6 |
|  7 | Samsung 27-inch 4K      |  349.99 |    30 |           5 |               8 |
|  9 | Acer Swift 3            |  699.99 |    18 |           4 |               9 |
| 10 | Microsoft Surface Pro 9 | 1199.99 |    12 |           4 |              10 |
| 12 | Asus TUF Gaming VG27AQ  |  399.99 |    22 |           5 |               3 |
| 13 | Dell OptiPlex 7010      |  899.99 |    16 |           2 |               5 |
| 14 | HP Z2 Mini Workstation  | 1499.99 |     8 |           2 |               6 |
| 16 | HP LaserJet Pro M428fdw |  499.99 |    12 |           8 |               6 |
| 17 | Samsung T7 Shield 2TB   |  199.99 |    35 |           9 |               8 |
| 19 | Dell Ultrasharp U2419H  |  299.99 |    20 |           5 |               5 |
| 20 | Acer Nitro AN515-57     |  999.99 |    14 |           4 |               9 |
+----+-------------------------+---------+-------+-------------+-----------------+
15 rows in set (0.00 sec)

多表查询

# 示例1：产品类型是Servers的产品名称和价格
liu@server [inventory]> SELECT product.name,product.price
    -> FROM product,category
    -> WHERE product.id_category = category.id 
    -> AND category.name='Servers';
+------------------------+---------+
| name                   | price   |
+------------------------+---------+
| ThinkServer TS140      |  539.88 |
| ThinkServer RD630      | 2379.14 |
| Dell OptiPlex 7010     |  899.99 |
| HP Z2 Mini Workstation | 1499.99 |
+------------------------+---------+
4 rows in set (0.01 sec)

# 示例2：查询厂商是Lenovo的产品名称和价格
liu@server [inventory]> SELECT product.name,product.price
    -> FROM product,manufacturer 
    -> WHERE product.id_manufacturer = manufacturer.id
    -> AND manufacturer.name='Lenovo';
+-------------------+---------+
| name              | price   |
+-------------------+---------+
| ThinkServer TS140 |  539.88 |
| ThinkServer RD630 | 2379.14 |
+-------------------+---------+
2 rows in set (0.00 sec)

聚合函数与分组

# 示例1：查询产品价格平均值
liu@server [inventory]> SELECT avg(price) FROM product;
+-------------------+
| avg(price)        |
+-------------------+
| 631.3844999999999 |
+-------------------+
1 row in set (0.00 sec)

# 示例2：查询产品价格最大值
liu@server [inventory]> SELECT max(price) FROM product;
+------------+
| max(price) |
+------------+
|    2379.14 |
+------------+
1 row in set (0.01 sec)


# 示例3：查询产品价格最小值
liu@server [inventory]> SELECT min(price) FROM product;
+------------+
| min(price) |
+------------+
|      44.99 |
+------------+
1 row in set (0.00 sec)

# 示例4：查询产品存量
liu@server [inventory]> SELECT sum(stock) FROM product;
+------------+
| sum(stock) |
+------------+
|        607 |
+------------+
1 row in set (0.00 sec)

# 示例5：查询产品价格最小值的那个产品信息
liu@server [inventory]> SELECT min(price) FROM product;
+------------+
| min(price) |
+------------+
|      44.99 |
+------------+
1 row in set (0.00 sec)

liu@server [inventory]> SELECT * FROM product WHERE price=73.84;
+----+-----------+-------+-------+-------------+-----------------+
| id | name      | price | stock | id_category | id_manufacturer |
+----+-----------+-------+-------+-------------+-----------------+
|  4 | X110 64GB | 73.84 |   100 |           3 |               1 |
+----+-----------+-------+-------+-------------+-----------------+
1 row in set (0.00 sec)

或者
liu@server [inventory]> SELECT * FROM product WHERE price=(SELECT min(price) FROM product);
+----+-------------------------------+-------+-------+-------------+-----------------+
| id | name                          | price | stock | id_category | id_manufacturer |
+----+-------------------------------+-------+-------+-------------+-----------------+
| 18 | Kingston Fury Beast 16GB DDR4 | 44.99 |    60 |          10 |               2 |
+----+-------------------------------+-------+-------+-------------+-----------------+
1 row in set (0.00 sec)

# 示例6：查询Lenovo厂商提供了几种产品
liu@server [inventory]> SELECT count(product.name)
    -> FROM product,manufacturer 
    -> WHERE product.id_manufacturer = manufacturer.id
    -> AND manufacturer.name='Lenovo';
+---------------------+
| count(product.name) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)


# GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
liu@server [inventory]> SELECT id_category,sum(stock) FROM product GROUP BY id_category;
+-------------+------------+
| id_category | sum(stock) |
+-------------+------------+
|           1 |         10 |
|           2 |         64 |
|           3 |        180 |
|           4 |         84 |
|           5 |         72 |
|           6 |         50 |
|           7 |         40 |
|           8 |         12 |
|           9 |         35 |
|          10 |         60 |
+-------------+------------+
10 rows in set (0.00 sec)

创建表

liu@server [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.01 sec)

liu@server [inventory]> SHOW TABLES;
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
| staff               |
+---------------------+
4 rows in set (0.00 sec)

插入记录

-- 指定所有列
liu@server [inventory]> INSERT INTO staff (id,name,age,id_department)
    -> VALUES (1,'liu1',28,10);
Query OK, 1 row affected (0.00 sec)

-- 只指定部分列（未指定的列使用默认值或 NULL）
liu@server [inventory]> INSERT INTO staff (id,name,age) VALUES (2,'liu2',20);
Query OK, 1 row affected (0.00 sec)

liu@server [inventory]> INSERT INTO staff (id,name) VALUES (3,'liu3');
Query OK, 1 row affected (0.01 sec)

更新记录

-- 更新指定条件记录
liu@server [inventory]> UPDATE staff SET age=30 WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 不带 WHERE 将更新所有记录（谨慎使用）
liu@server [inventory]> UPDATE staff SET age=30 ;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 3  Changed: 2  Warnings: 0

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

删除记录

liu@server [inventory]> DELETE FROM staff WHERE id=3 ;
Query OK, 1 row affected (0.01 sec)

liu@server [inventory]> DELETE FROM staff ;
Query OK, 2 rows affected (0.01 sec)

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

删除表

liu@server [inventory]> DROP TABLE staff ;
Query OK, 0 rows affected (0.00 sec)

五、管理数据库用户

MariaDB 的用户与操作系统用户相互独立，即使同名也是不同的账户。要创建和管理用户，需具备以下权限之一：

MariaDB 的 root 用户
全局 CREATE USER 权限
对 mysql 数据库的 INSERT 权限

5.1 用户帐户定义格式

user_name@host_name，根据主机源可创建相同用户名不同权限的多个账户。

账户示例	说明
`liu` 或 `liu@'%'`	可从任何主机连接
`liu@'localhost'`	仅从本地主机连接
`liu@'192.168.1.5'`	仅从指定 IP 连接
`liu@'192.168.1.%'`	从 192.168.1.0/24 网段连接
`liu@'2001:db8:18:b51:c32:a21'`	从指定 IPv6 地址连接

创建用户

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

新帐户默认具有最小权限。密码加密存储在 mysql.user 表中：

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]> SELECT host,user,password FROM user WHERE user = 'liu';
+------+------+-------------------------------------------+
| host | user | password                                  |
+------+------+-------------------------------------------+
| %    | liu  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)

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

5.2 授予用户权限

GRANT 语句格式：

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

权限作用范围

作用范围	授权对象格式	适用场景
全局范围	`.`	管理员 / 超级用户
数据库范围	`数据库名.*`	业务库专属权限
表范围	`数据库名.表名`	单表专属权限
列范围	`数据库名.表名(列1,列2)`	敏感列管控（如手机号/密码）

1. 全局范围常用权限

权限	作用
`CREATE USER`	创建、删除、重命名用户，授权
`SUPER`	终止连接、修改全局变量、主从管理等
`PROCESS`	查看所有连接进程
`RELOAD`	执行 `FLUSH` 命令
`SHUTDOWN`	关闭服务
`FILE`	读写服务器文件
`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. 数据库范围

作用于 数据库名.*，仅对指定数据库生效。

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

权限名称	作用	授权示例
`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';`

常用数据操作权限：

GRANT SELECT, INSERT, UPDATE, 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';`

-- 示例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 权限。

注意：所有授权后需执行 FLUSH PRIVILEGES; 使权限生效。

5.3 查询用户权限

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

关键注意事项

最小权限原则：优先授予“刚好够用”的权限（如业务开发仅授予数据库级权限，而非全局），避免过度授权。
权限生效：所有授权后需执行 FLUSH PRIVILEGES;。
用户范围限制：生产环境避免 @'%'（任意主机），替换为具体 IP（如 @'192.168.1.100'）或网段（@'192.168.1.%'）。

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

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

权限回收：如需撤销权限，格式与授权对应（替换 GRANT 为 REVOKE）：
```
-- 撤销 liu 的全局创建用户权限
REVOKE CREATE USER ON *.* FROM 'liu'@'%';
```

常见场景权限参考

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

5.4 回收权限与删除用户

-- 回收权限
MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT
    -> ON inventory.category FROM liu@localhost;

-- 删除用户
MariaDB [(none)]> DROP USER liu@localhost;

如果删除了当前连接的用户，连接关闭后才真正删除。

5.5 更改用户密码

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

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

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

5.6 排故数据库访问

问题	解决方法
用户已授权从任何主机连接，但只能本地连接	检查 `/etc/my.cnf.d/server.cnf` 是否设置了 `skip-networking`，删除并重启服务
用户可本地连接但不能远程连接	确保 `bind-address` 配置正确，且用户表中包含对应主机的条目
用户连接后只能看到 `information_schema`	授予用户访问业务数据库的权限（新用户默认最小权限）
用户无法创建数据库	考虑授予全局 `CREATE` 权限
用户无法读写数据	授予对应数据库的 CRUD 权限

思考：忘记 root 密码

在 /etc/my.cnf.d/server.cnf 的 [mysqld] 下添加 skip-grant-tables=1
重启 mariadb 服务

无密码登录，修改密码：

[root@server ~ 14:21:55]# mysql -u root
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('新密码') where USER='root';
MariaDB [(none)]> exit

注释掉 skip-grant-tables，重启服务

思考：误回收 root 权限或误删除 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 服务

思考：无意删除了 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 服务（恢复正常）

六、备份和恢复

6.1 备份方式对比

特性	逻辑备份	物理备份
方式	导出为 SQL 文本文件	复制原始数据库目录和文件
速度	较慢（需转换格式）	较快（直接复制文件）
可移植性	高（可跨平台、甚至跨数据库）	差（需相同硬件/软件环境）
备份时服务状态	联机执行	建议脱机或锁表
包含内容	仅数据，不包括日志和配置文件	可包含日志和配置文件

6.2 物理备份与恢复

# 停止服务
[root@db1 ~ 17:14:19]# systemctl stop mariadb

# 执行备份
[root@db1 ~ 17:15:12]# cp -a /var/lib/mysql /backup/mysql

# 模拟删除原数据
[root@db1 ~ 17:16:11]# rm -fr /var/lib/mysql

# 恢复数据
[root@db1 ~ 17:16:27]# cp -a /backup/mysql /var/lib/mysql
[root@db1 ~ 17:16:48]# chown -R mysql:mysql /var/lib/mysql/

# 启动服务
# 确认目录中内容
[root@db1 ~ 17:16:58]# ls /var/lib/mysql
columns_priv.frm   help_topic.frm        slow_log.CSM
columns_priv.MYD   help_topic.MYD        slow_log.CSV
columns_priv.MYI   help_topic.MYI        slow_log.frm
...

# 启动服务
[root@db1 ~ 17:17:40]# systemctl start mariadb

6.3 逻辑备份与恢复

使用 mysqldump 命令，用户至少需要 SELECT（表）、SHOW VIEW（视图）、TRIGGER（触发器）权限。

常用选项

  -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@db1 ~ 17:20:00]# mysqldump -u root -p inventory > /backup/inventory.dump.sql-1

# 备份单个数据库（包含创建数据库语句）
[root@db1 ~ 17:21:55]# mysqldump -u root -p --databases inventory > /backup/inventory.dump.sql-2

# 备份所有数据库
[root@db1 ~ 17:22:39]# 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;

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

恢复示例

-- 方式1：如果备份文件包含 CREATE DATABASE，可直接 source
MariaDB [(none)]> source /backup/inventory.dump.sql

-- 方式2：如果备份文件不含 CREATE DATABASE，需先创建数据库
CREATE DATABASE inventory;
MariaDB [(none)]> use inventory;
MariaDB [(inventory)]> source /backup/inventory.dump.sql-1

七、MySQL 集群架构概述

7.1 常见集群架构类别

架构	特点	适用场景
普通主从（1主+N从）	主写从读，异步/半同步复制，手动切换	小企业、测试环境、非核心业务
主从 + Keepalived/VIP	自动漂移 VIP，简单高可用，但可能脑裂，不判断复制延迟	中小企业，低成本高可用
主从 + MHA	业界最成熟，自动检测故障、补齐数据、秒级切换，配置较复杂	中大规模企业核心库，电商/金融
主从 + Orchestrator	可视化、自动化集群管理，跨机房，故障自愈	中大型企业、云厂商、多实例集群
MySQL MGR（单主模式）	官方原生高可用，基于 Paxos 强一致，自动选主，无需第三方工具	新一代主流架构，金融/政企/云平台
主主架构（双主）	双写有冲突风险，企业通常只做一主一备不双写	几乎不用于核心业务

7.2 企业主流架构

MGR 单主模式：新系统首选（官方、强一致、不丢数据）
MHA：老系统、传统架构主流（最成熟、最稳定）
Orchestrator：大规模集群自动化管理
简单主从：小型业务

7.3 电商平台（如京东、拼多多）数据库架构

单机 MySQL 写入天花板约为 2000~8000 TPS，而电商大促可达几十万到上百万 TPS，因此不能靠单主 MySQL。

真实方案：

分库分表：将大库拆成 16~128 个独立 MySQL 集群，每组都是高可用架构
读写分离 + 缓存：Redis 集群扛 90% 读流量，MySQL 只做核心落盘
消息队列削峰
分布式数据库：如 TDSQL、PolarDB、OceanBase 等，支持无限水平扩展

结论：电商使用成百上千套主从/MGR 集群，每套负责一小块业务，分库分表 + 读写分离 + 缓存共同支撑海量流量。

八、MySQL 主从同步解决方案

8.1 主从同步架构

一主一从：小型业务
一主多从：读写分离，提升读性能
级联复制：主 → 从1 → 从2，减轻主库压力
双主复制：互为主从，用于高可用切换

8.2 主从同步流程

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

8.3 主从同步方式

方式	说明	优缺点
异步复制（默认）	主库写 binlog 后立即返回，不等待从库	性能最好，但主库宕机可能丢失未同步的数据
半同步复制	主库等待至少一个从库确认收到 binlog 后才返回	数据一致性提升，有轻微性能损耗，超时后降级为异步
全同步复制	主库等待所有从库执行完 binlog 后才返回	一致性最高，性能极差，MySQL 原生不支持，需 MGR

半同步复制配置示例

主库安装插件及配置：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

在 /etc/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

从库安装插件及配置：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

从库 my.cnf 中额外配置：

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

8.4 主从延迟与数据丢失

延迟原因及范围

场景	典型延迟	原因
小事务 + 同机房 + 高性能从库	0-100ms	网络/执行快
中等事务 + 跨机房	100ms-1s	网络延迟 + binlog 堆积
大批量写入 / 大事务	1s-数分钟	SQL 线程执行跟不上
从库性能瓶颈 / 慢查询	数分钟-小时	资源耗尽

监控延迟

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

-- 关键字段说明：
-- Seconds_Behind_Master：从库落后主库的秒数
-- Slave_IO_Running：I/O线程是否正常（负责拉取binlog）
-- Slave_SQL_Running：SQL线程是否正常（负责执行relay log）

优化手段

提升从库硬件配置（CPU/内存/SSD）
开启并行复制（slave_parallel_workers = 4）
拆分大事务
从库关闭不必要的慢查询、报表任务
跨地域使用级联复制

数据丢失防范

主库宕机可能导致未同步的 binlog 丢失。应对策略：

开启半同步复制
设置 sync_binlog=1（每次事务刷盘）
设置 innodb_flush_log_at_trx_commit=1

九、MariaDB 主从同步实践

9.1 实验环境

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

所有节点已安装 MariaDB 并完成安全初始化。

9.2 配置主库

编辑 /etc/my.cnf.d/master.cnf：

[root@db1 ~ 16:43:50]# 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 ~]# systemctl restart mariadb
# 预期输出：无报错即重启成功

9.3 配置从库

编辑 /etc/my.cnf.d/master.cnf：

[root@db2 ~ 16:42:55]#  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 ~]# systemctl restart mariadb
# 预期输出：无报错即重启成功

9.4 建立主从同步

主库操作

# 配置主数据库
[root@db1 ~ 16:44:20]# mysql -uroot -p123
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)]> grant replication slave, replication client on *.* to 'repl'@'10.1.8.12' identified by '123';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

ERROR: No query specified

从库操作

# 配置从数据库
[root@db2 ~ 16:47:27]# mysql -uroot -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
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)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> RESET SLAVE ALL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.1.8.11',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='123',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=493;
Query OK, 0 rows affected (0.01 sec)



# 启动同步，再次查看状态
# 确保 Slave_IO_Running 和 Slave_SQL_Running 状态为Y es。
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.8.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 493
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

验证同步

在主库写入数据：

# 主库写入数据
MariaDB [test]> create table linux(username varchar(15) not null,password varchar(15) not null);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into linux values ('liu1', 'Liu@123');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into linux values ('liu2', 'Liu@123');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into linux values ('liu3', 'Liu@123');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| liu1     | Liu@123  |
| liu2     | Liu@123  |
| liu3     | Liu@123  |
+----------+----------+
3 rows in set (0.00 sec)

在从库查询：

# 从库查询
MariaDB [(none)]> select * from test.linux;
+----------+----------+
| username | password |
+----------+----------+
| liu1     | Liu@123  |
| liu2     | Liu@123  |
| liu3     | Liu@123  |
+----------+----------+
3 rows in set (0.00 sec)

AtomGit开源社区

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

更多推荐

AI 代码审查与质量保障 — 主流 AI 应用方向深度调研

AtomGit开源社区

个人认为产品经理进步最快的方式：没有之一

AtomGit开源社区

环境搭建与 Claude Code 安装

本文详细记录了在WSL2环境下配置Ubuntu 26.04开发环境的完整流程，主要包括：1）系统初始化配置（更换清华源）；2）通过nvm安装Node.js 24.16.0 LTS；3）全局安装Claude Code v2.1.168并进行API配置；4）安装uv包管理器；5）建立项目目录结构。文中提供了关键工具的安装路径、版本信息和配置方法，特别针对国内网络环境采用了镜像源优化安装速度，适合作为A

AtomGit开源社区

所有评论(0)

查看更多评论

2401_83463699

@2401_83463699

已为社区贡献2条内容

MariaDB 数据库从入门到实战完全指南

2401_83463699

一、数据库介绍

1.1 数据库种类

1.2 数据库排名

1.3 关系数据库

二、MariaDB 介绍

2.1 概述

2.2 存储结构

三、MariaDB 部署

3.1 安装数据库

3.2 数据库进程

核心进程

进程关系

特殊场景

3.3 加固数据库

3.4 配置数据库

主配置文件

细分配置文件

3.5 连接数据库

本地连接（socket）

远程连接

非交互方式连接

配置登录提示符

四、结构化查询语言（SQL）

4.1 SQL 概述

4.2 数据库操作

查询数据库列表

使用数据库

创建数据库

删除数据库

4.3 表操作

环境准备

查询表

查询表列表

查询表结构

查询表中数据

WHERE 子句

多表查询

聚合函数与分组

创建表

插入记录

更新记录

删除记录

删除表

五、管理数据库用户

5.1 用户帐户定义格式

创建用户

5.2 授予用户权限

权限作用范围

1. 全局范围常用权限

2. 数据库范围

3. 表范围

4. 列范围

5.3 查询用户权限

关键注意事项

常见场景权限参考

5.4 回收权限与删除用户

5.5 更改用户密码

5.6 排故数据库访问

思考：忘记 root 密码

思考：误回收 root 权限或误删除 root 账户

思考： 无意删除了 root 账户

六、备份和恢复

6.1 备份方式对比

6.2 物理备份与恢复

6.3 逻辑备份与恢复

常用选项

备份示例

恢复示例

七、MySQL 集群架构概述

7.1 常见集群架构类别

7.2 企业主流架构

7.3 电商平台（如京东、拼多多）数据库架构

八、MySQL 主从同步解决方案

8.1 主从同步架构

8.2 主从同步流程

8.3 主从同步方式

半同步复制配置示例

8.4 主从延迟与数据丢失

思考：无意删除了 root 账户