深入解析：MariaDB 数据库管理

文章目录

MariaDB 数据库管理

介绍 MariaDB 数据库

数据库介绍

数据库是存放计算机数据的仓库，按照特定数据结构组织和存储数据，可通过多种方法管理数据。数据结构指数据的组织形式或数据间的联系。

数据库种类

  1. 早期数据库模型
    • 层次式数据库：以层次模型建立，如文件系统。
    • 网状数据库：以网状模型建立，如网络通信。
    • 关系型数据库：以二维表模型建立，如图书馆管理系统。
  2. 当今互联网企业常用数据库模式
    • 关系型数据库：将复杂数据结构归结为简单二维表形式，对数据的操作多基于关联表格的分类、合并、连接或选取等运算，如 Oracle、MySQL 和 PostgreSQL 等。
    • 非关系型数据库（NoSQL 数据库）：本意是 “Not Only SQL”，作为传统数据库的有效补充，典型产品有 Redis（持久化缓存）、Mongodb、Memcached（纯内存）等。

关系数据库

关系数据库将结构化数据存储在相互关联的表中，可轻松组合不同表的信息处理和提供数据报告。关系数据库管理系统（RDBMS）是管理关系数据库的软件，多数支持用结构化查询语言（SQL）查找和管理数据。

许多组织用关系数据库存储业务信息（如库存、销售和财务），在诸多应用中起关键作用。例如，支持动态生成内容的 Web 应用程序常围绕 LAMP（LNMP）解决方案构建：

  • Linux 操作系统：提供基本环境。
  • Apache HTTPS Server 或 Nginx：提供 Web 服务器。
  • MariaDB、MySQL 或其他关系数据库（如 PostgreSQL）：存储站点数据。
  • 编程语言（如 PHP、Python 等）：由 Web 服务器运行，可更新数据库数据并动态构建网页。

即使非数据库管理员，了解这些服务及数据的安装和管理知识也很有必要。

MariaDB 介绍

  1. MariaDB 数据库管理系统是 MySQL 数据库的分支，由开源社区维护，采用 GPL 授权许可。因甲骨文公司收购 MySQL 后存在闭源潜在风险，MySQL 开源社区通过分支方式避开该风险。
  2. 完全兼容 MySQL 数据库，包括 API 和命令行，可轻松替代 MySQL。
  3. 可包含多个 database，每个 database 包含多张表，关系数据库的表采用二维表格存储数据，类似 Excel 工作表：
    • 一行：一个元组，即一条记录。
    • 一列：一个字段（属性），表由字段定义，每个字段描述数据意义、分配数据类型并定义长度等属性。
    • 行和列交叉位置：表示某个属性值。

部署 MariaDB 数据库

# 安装服务端
[root@server ~ 15:10:38]# yum install -y mariadb-server
# 安装客户端
[root@client ~ 15:11:39]# yum install -y mariadb-server
# 启用并启动服务
[root@server ~ 15:11:12]# systemctl enable mariadb --now
# 查看并关闭防火墙
[root@server ~ 15:12:14]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)

加固 MariaDB

MariaDB 默认有 test 数据库和不安全配置，运行 mysql_secure_installation 修改，交互式提示更改内容包括：

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

连接 MariaDB

  1. 套接字文件连接：客户端与服务器在同一台计算机时，可通过特殊套接字文件通信，更安全，但需使用数据库的服务在 MariaDB 服务器上运行，可能影响性能。
  2. TCP/IP 网络连接：远程服务器与 MariaDB 服务器在不同主机，服务器需配置侦听 3306/TCP 端口的连接。

配置 MariaDB

  1. 默认配置：默认情况下，MariaDB 侦听系统所有网络地址上的 3306/TCP 端口。

  2. 配置文件

    • 主配置文件：/etc/my.cnf

    • 辅助配置文件：/etc/my.conf.d/*

      ，其中 /etc/my.cnf.d/mariadb-server.cnf

      是数据库服务主要配置，在块中可定义以下参数：

      • bind-address：指定 MariaDB 侦听客户端连接的网络地址，仅能输入一个选项，如单个 IPv4/IPv6 地址、::（连接所有可用地址）、空白或 0.0.0.0（所有 IPv4 地址）；若仅允许本地客户端访问，可用127.0.0.1或::1。
      • skip-networking：在 [mysqld] 部分设置 skip-networking 或 skip-networking=1，禁用网络连接，客户端需用套接字文件通信；默认 skip-networking=0，MariaDB 侦听网络连接。
      • port：可指定除 3306/TCP 外的网络端口。
客户端配置
# 配置文件内容
[root@client ~ 15:11:52]#  vim /etc/my.cnf.d/client.cnf 
[client]
user=wan
password=123
host=server
port=3306

实践 MariaDB 中 SQL

描述 SQL

SQL（Structured Query Language，结构化查询语言），1986 年 10 月由美国国家标准局颁布，国际标准化组织（ISO）也颁布了 SQL 国际标准。它是关系数据库最重要的操作语言，影响超出数据库领域，在人工智能数据检索、第四代软件开发工具等领域也有应用。

SQL 语句分类：

  1. 数据查询语言（DQL）：又称 “数据检索语句”，用于从表中获取数据，保留字有 SELECT、WHERE、ORDER BY、GROUP BY 和 HAVING。
  2. 数据操作语言（DML）：语句包括 INSERT、UPDATE 和 DELETE。
  3. 数据定义语言（DDL）：语句包括 CREATE 和 DROP，如创建新表（CREATE TABLE）、删除表（DROP TABLE）、为表添加索引等。
  4. 事务处理语言（TPL）：确保 DML 语句影响的表的所有行得以更新，语句有 BEGIN TRANSACTION、COMMIT 和 ROLLBACK。
  5. 数据控制语言（DCL）：通过 GRANT 或 REVOKE 获取许可，确定用户和用户组对数据库对象的访问权限。

连接数据库

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

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

示例：[root@server~]#mysql -uroot -h localhost -p

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

[root@server ~ 15:17:55]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
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)]>

数据库操作

数据库说明：

  • mysql 数据库：系统数据库，保存数据库用户及其访问权限等信息。
  • INFORMATION_SCHEMA 数据库：保存数据库或数据表的元数据信息。
  • PERFORMANCE_SCHEMA 数据库：保存数据库服务器性能信息。
# 查询数据库列表
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbname             |
| inventory          |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

使用数据库：用 USE 语句选择数据库，如 USE mysql;，后续默认操作的表属于该数据库。

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

# 使用数据库
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]>
# 创建数据库
MariaDB [mysql]> create database wan;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> use wan;
Database changed
MariaDB [wan]>

删除数据库：DROP DATABASE 语句删除数据库中的所有表并删除数据库，仅对该数据库有 DROP 特权的用户可运行，不更改用户特权，若重新创建同名数据库，旧数据库的用户权限仍有效。

# 删除数据库
MariaDB [wan]> drop database wan;
Query OK, 0 rows affected (0.00 sec)

表操作

# 提前导入数据库
MariaDB [(none)]> use inventory;
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 [inventory]> show tables;
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
+---------------------+
3 rows in set (0.00 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.00 sec)

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

  • Field 列：属性名称。
  • Type 列：属性数据格式，如 stock 属性为最多 11 位数字的整数。
  • Null 列：指示属性是否可为 null。
  • Default 列：指示未指定属性时是否有默认值。
  • Key 列：显示属性是否为主键（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 |
|  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)
# 查询所有记录特定字段
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 |
| 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 子句及条件操作符

OperatorDescription
=Equal
!=Not equal. Note: In some versions of SQL, this operator may be written as <>
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
BETWEENMatches values within a range (inclusive)
LIKESearch for a pattern
INMatches any value in a list
# 条件操作符包括：=、!=、>、<、>=、<=
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 |
|  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)
# BETWEEN，匹配2个数字之间（包括数字本身）的记录。
MariaDB [inventory]> select * from product where id between 1 and 3;
+----+-------------------+---------+-------+-------------+-----------------+
| 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.00 sec)
# IN，匹配列表中记录。
MariaDB [inventory]> select * from product where id in (1,3);
+----+-------------------+--------+-------+-------------+-----------------+
| id | name              | price  | stock | id_category | id_manufacturer |
+----+-------------------+--------+-------+-------------+-----------------+
|  1 | ThinkServer TS140 | 539.88 |    20 |           2 |               4 |
|  3 | RT-AC68U          | 219.99 |    10 |           1 |               3 |
+----+-------------------+--------+-------+-------------+-----------------+
2 rows in set (0.00 sec)
# LIKE，用于匹配字符串。%表示一个或多个字符，_表示一个字符，[charlist]表示字符列中的任何单一字符，[!charlist]表示不在字符列中的任何单一字符。
MariaDB [inventory]> select * from product where name like '%server%';
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
|  1 | ThinkServer TS140 |  539.88 |    20 |           2 |               4 |
|  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 |
+----+-------------------+---------+-------+-------------+-----------------+
2 rows in set (0.00 sec)
# 逻辑与AND
MariaDB [inventory]> select * from product where name like '%rd%' and price > 1000;
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
|  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 |
+----+-------------------+---------+-------+-------------+-----------------+
1 row in set (0.00 sec)
# 逻辑或OR
MariaDB [inventory]> select * from product where name like '%rd%' or price > 500;
+----+-------------------------+---------+-------+-------------+-----------------+
| id | name                    | price   | stock | id_category | id_manufacturer |
+----+-------------------------+---------+-------+-------------+-----------------+
|  1 | ThinkServer TS140       |  539.88 |    20 |           2 |               4 |
|  2 | ThinkServer RD630       | 2379.14 |    20 |           2 |               4 |
|  5 | Dell XPS 15             | 1299.99 |    15 |           4 |               5 |
|  6 | HP Pavilion 14          |  799.99 |    25 |           4 |               6 |
|  9 | Acer Swift 3            |  699.99 |    18 |           4 |               9 |
| 10 | Microsoft Surface Pro 9 | 1199.99 |    12 |           4 |              10 |
| 13 | Dell OptiPlex 7010      |  899.99 |    16 |           2 |               5 |
| 14 | HP Z2 Mini Workstation  | 1499.99 |     8 |           2 |               6 |
| 20 | Acer Nitro AN515-57     |  999.99 |    14 |           4 |               9 |
+----+-------------------------+---------+-------+-------------+-----------------+
9 rows in set (0.00 sec)
# ORDER BY关键字用于对结果集进行排序。
MariaDB [inventory]> select * from product order by price;
+----+-------------------------------+---------+-------+-------------+-----------------+
| id | name                          | price   | stock | id_category | id_manufacturer |
+----+-------------------------------+---------+-------+-------------+-----------------+
| 18 | Kingston Fury Beast 16GB DDR4 |   44.99 |    60 |          10 |               2 |
| 11 | Kingston A400 240GB           |   49.99 |    80 |           3 |               2 |
|  4 | X110 64GB                     |   73.84 |   100 |           3 |               1 |
| 15 | Logitech G502 Hero            |   79.99 |    40 |           7 |               7 |
|  8 | Logitech Mechanical K845      |   89.99 |    50 |           6 |               7 |
| 17 | Samsung T7 Shield 2TB         |  199.99 |    35 |           9 |               8 |
|  3 | RT-AC68U                      |  219.99 |    10 |           1 |               3 |
| 19 | Dell Ultrasharp U2419H        |  299.99 |    20 |           5 |               5 |
|  7 | Samsung 27-inch 4K            |  349.99 |    30 |           5 |               8 |
| 12 | Asus TUF Gaming VG27AQ        |  399.99 |    22 |           5 |               3 |
| 16 | HP LaserJet Pro M428fdw       |  499.99 |    12 |           8 |               6 |
|  1 | ThinkServer TS140             |  539.88 |    20 |           2 |               4 |
|  9 | Acer Swift 3                  |  699.99 |    18 |           4 |               9 |
|  6 | HP Pavilion 14                |  799.99 |    25 |           4 |               6 |
| 13 | Dell OptiPlex 7010            |  899.99 |    16 |           2 |               5 |
| 20 | Acer Nitro AN515-57           |  999.99 |    14 |           4 |               9 |
| 10 | Microsoft Surface Pro 9       | 1199.99 |    12 |           4 |              10 |
|  5 | Dell XPS 15                   | 1299.99 |    15 |           4 |               5 |
| 14 | HP Z2 Mini Workstation        | 1499.99 |     8 |           2 |               6 |
|  2 | ThinkServer RD630             | 2379.14 |    20 |           2 |               4 |
+----+-------------------------------+---------+-------+-------------+-----------------+
20 rows in set (0.01 sec)
# 多表查询
# 示例1：产品类型是Servers的产品名称和价格
MariaDB [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.00 sec)
# 示例2：查询厂商是Lenovo的产品名称和价格
MariaDB [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：查询产品价格平均值
MariaDB [inventory]> select avg(price) from product;
+-------------------+
| avg(price)        |
+-------------------+
| 631.3844999999999 |
+-------------------+
1 row in set (0.00 sec)
# 示例2：查询产品价格最大值
MariaDB [inventory]> select max(price) from product;
+------------+
| max(price) |
+------------+
|    2379.14 |
+------------+
1 row in set (0.00 sec)
# 示例3：查询产品价格最小值
MariaDB [inventory]> select min(price) from product;
+------------+
| min(price) |
+------------+
|      44.99 |
+------------+
1 row in set (0.00 sec)
# 示例4：查询产品存量总和
MariaDB [inventory]> select sum(stock) from product;
+------------+
| sum(stock) |
+------------+
|        607 |
+------------+
1 row in set (0.00 sec)
# 示例5：查询产品价格最小值的那个产品信息
MariaDB [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厂商提供了几种产品
MariaDB [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语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
MariaDB [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)
# 创建表
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.00 sec)
MariaDB [inventory]> show tables;
+---------------------+
| Tables_in_inventory |
+---------------------+
| category            |
| manufacturer        |
| product             |
| staff               |
+---------------------+
4 rows in set (0.00 sec)
# 插入记录
MariaDB [inventory]> insert into staff (id,name,age,id_department) values (1,'wan',22,10);
Query OK, 1 row affected (0.00 sec)
# 更新记录
MariaDB [inventory]> update staff set age = 30 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

# 删除记录
MariaDB [inventory]> delete from staff where id = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [inventory]> delete from staff;
Query OK, 0 rows affected (0.00 sec)

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

# 删除表
MariaDB [inventory]> drop table staff;
Query OK, 0 rows affected (0.00 sec)

管理 MariaDB 用户

创建用户账户

  1. 默认情况下，MariaDB 的用户和密码与本地系统的用户和密码分开，即 MariaDB 数据库用户与服务器的 Linux 用户不同，即使名称相同。
  2. 为控制用户对数据库服务器的访问级别，需在 MariaDB 中设置数据库用户并授予操作权限。可配置 MariaDB 使用 pam 身份验证插件集成系统用户账户和密码，但本课程不介绍，通常建议分开管理数据库和系统用户账户。
  3. 创建新用户所需权限级别：
    • MariaDB 的 root 用户。
    • 被授予全局 CREATE USER 特权的用户。
    • 被授予对 mysql 数据库的 INSERT 特权的用户。
  4. 创建用户使用 CREATE USER 语句，在 mysql 数据库的 user 表中创建新记录，用户名格式为 user_name@host_name，可根据主机源创建同名但不同特权的多个用户账户。
# 因为没有wan用户所以不显示内容
MariaDB [mysql]> select host,user,password from user where user = 'wan';
Empty set (0.00 sec)

控制用户权限

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

# 创建用户wan
MariaDB [(none)]> create user wan@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
# 以wan用户登录MySQL
[root@server ~ 17:25:30]# mysql -u wan -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
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)]> select user();
+---------------+
| user()        |
+---------------+
| wan@localhost |
+---------------+
1 row in set (0.00 sec)
# 查看数据库列表
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
# 使用mysql数据库，权限不够无法执行
MariaDB [(none)]> use mysql
ERROR 1044 (42000): Access denied for user 'wan'@'%' to database 'mysql'

特权按范围组织：

  • 全局特权：用于管理 MariaDB 数据库服务器本身，如 CREATE USER。
  • 数据库特权：用于在 MariaDB 服务器上创建数据库和使用数据库，如 CREATE DATABASE。
  • 表特权：用于在特定数据库中创建表和处理数据，如 CRUD 命令（增加 Create、查询 Retrieve、更新 Update、删除 Delete）。
  • 列特权：用于授予类似表的命令用法，但针对特定列（通常很少见）。

查询用户权限

# 切换到root用户进行查看
MMariaDB [(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                                                                           |
+----------------------------------------------------------------------------------------------------

授予用户权限

GRANT 语句可向帐户授予特权，连接的用户需具有 GRANT OPTION 且拥有要授予的特定特权。例如，wan 用户需先有 SELECT 特权和 GRANT OPTION 表特权，才能授予数据库表 SELECT 特权。

# 给wan用户授予inventory.category表的增删改权限
MariaDB [(none)]> grant select,update,delete,insert
-> on inventory.category
-> to wan@localhost;
Query OK, 0 rows affected (0.00 sec)

回收用户权限

MariaDB [(none)]> revoke select,update,delete,insert
-> on inventory.category from wan@localhost;
Query OK, 0 rows affected (0.00 sec)

删除用户

MariaDB [(none)]> drop user wan@localhost;
Query OK, 0 rows affected (0.00 sec)

排故数据库访问

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

备份和恢复 MariaDB

备份方式

逻辑备份

  • 特点：将数据库导出为包含重新创建数据库所需 SQL 命令的文本文件。
    • 通过查询数据库，检索数据库结构。
    • 备份速度较慢，因服务器需访问数据库信息并转换为逻辑格式。
    • 可移植性高，在某些情况下可还原到其他数据库提供程序（如 PostgreSQL）。
    • 服务器联机时执行。
    • 备份不包括日志或配置文件。

物理备份

  • 特点：复制原始数据库目录和包含数据库内容的文件。
    • 备份可包括日志和配置文件。
    • 仅可移植到硬件和软件类似的其他计算机。
    • 比逻辑备份更快。
    • 服务器脱机或数据库中所有表锁定时执行，防止备份期间数据更改。

物理备份

# 停止mariadb服务 
[root@server ~ 17:40:00]# systemctl stop mariadb
# tar命令打包 /var/lib/mysql 目录为mysql.tar.gz，保留原先权限 -p选项
[root@server ~ 17:40:19]# tar -czf mysql.tar.gz /var/lib/mysql/ -p
tar: 从成员名中删除开头的“/”
[root@server ~ 17:42:17]# ll
总用量 600
-rw-------. 1 root root   1447 9月   2 10:21 anaconda-ks.cfg
-r--------  1 root root   5221 9月  28 11:35 inventory.sql
-rw-r--r--  1 root root 599789 9月  28 17:42 mysql.tar.gz
# 启动mariadb服务，删除inventory库中product表
[root@server ~ 17:42:33]# systemctl start mariadb
[root@server ~ 17:42:55]# mysql
MariaDB [(none)]> use inventory
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 [inventory]> drop table product;
Query OK, 0 rows affected (0.00 sec)
# 停止mariadb 服务，mv /var/lib/mysql /var/lib/mysql.old
[root@server ~ 17:44:43]# systemctl stop mariadb
[root@server ~ 17:45:25]# mv /var/lib/mysql/ /var/lib/mysql.old
# 提权mysql.tar.gz内容，恢复到/var/lib/mysql
[root@server ~ 17:44:02]# tar -xf mysql.tar.gz 
[root@server ~ 17:44:30]# ll
总用量 600
-rw-------. 1 root root   1447 9月   2 10:21 anaconda-ks.cfg
-r--------  1 root root   5221 9月  28 11:35 inventory.sql
-rw-r--r--  1 root root 599789 9月  28 17:42 mysql.tar.gz
drwxr-xr-x  3 root root     17 9月  28 17:44 var
[root@server ~ 17:45:45]# mv var /var/lib/mysql
# 启动mariadb服务验证
[root@server ~ 17:52:21]# systemctl start mariadb

逻辑备份

# 备份特定表
[root@server ~ 17:55:36]# mysqldump inventory product
-- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: inventory
-- ------------------------------------------------------
-- Server version       5.5.68-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
...
# 特定库（库中所有表），不包括创建数据库命令
[root@server ~ 17:55:52]# mysqldump inventory
-- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: inventory
-- ------------------------------------------------------
-- Server version       5.5.68-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
...
# 特定库（库中所有表），包括创建数据库命令
[root@server ~ 17:56:36]# mysqldump --database inventory
[root@server ~ 17:56:36]# mysqldump --database inventory
-- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: inventory
-- ------------------------------------------------------
-- Server version       5.5.68-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
...
# 恢复
posted @ 2025-09-30 16:49  wzzkaifa  阅读(23)  评论(0)    收藏  举报