Chapter9 - 配置MARIADB数据库

安装 MariaDB

关系数据库

关系数据库是一种机制，能够能有组织的方式持久存储数据。数据储存数据项时将其组织为一组表，每个表代表一个实体。在给定的表中，每个行对应一个记录，而每个列对应该记录的某个属性。

1553144061191662.png

1553144075641176.png

1553144088581571.png

先前的表表明：

• product 表有四条记录。每条记录有六个属性：(id,name,price,stock,id_category,id_manufacturer)
• X110 64GB 表示由SanDis制造的固态硬盘
• 负责ThinkS TS140 产品的seller 是 Allen Scott 。

RHEL 7 随附了两个关系数据库软件包：

• PostgreSQL – 由PostgreSQL ,全球开发团队开发的开源数据库，该团队由 Postgre 用户（个人和公司）以及其他公司和志愿者组成，并且由红帽和 EnterpriseDB 等公司监管。
• MariaDB – 由社区开发的一个MySQL 分支，由MySQL 的部分原作者构建。它提供了一系列丰富的增强功能，包括备用存储引擎、服务器优化和补丁。MariaDB基金会本着免费和开源软件和精神，与大型的用户和开发者社区紧密协作 。

MariaDB安装

完整的MariaDB数据库安装要求安装mariadb 和 mariadb-client 软件组

以下软件包将与mariadb 组一起安装：

• mariadb-server – MariaDB 服务器和相关文件（必需软件包）
• mariadb-beach – MariaDB 基准测试脚本和数据（可选软件包）
• mariadb-test -与 MariaDB 一起分发的测试套件（可选软件包）

以下软件包将与 mariadb-client 组一起安装：

• mariadb – 由社区开发的MySQL分支（必需软件包）
• MySQL-python – Python 的MariaDB接口（默认软件包）
• mysql-connector-odbc – MariaDB 的 ODBC 驱动程序（默认软件包）
• libdbi-dbd-mysql – 适用于libdbi 的 MariaDB插件（可选软件包）
• mysql-connector-java – 适用于MariaDB的本机Java驱动程序（可选软件包）
• perl-DBD-MySQL – 适用于Perl 的 MariaDB接口（可选软件包）

/etc/my.cnf 文件包含MariaDB 的默认配置，如数据配置目录、套接字绑定以及日志和错误文件位置。

注意

可以不必向 /etc/my.cnf 文件中添加新配置，而是将一个新创建的名为 *.cnf 的文件添加到用于保存 MariaDB配置的 /etc/my.cnf.d/ 目录

MariaDB 安装演示

1、在serverX 上使用yum 命令安装 MariaDB。

[root@serverX]# yum groupinstall mariadb mariadb-client -y

2、在serverX 上使用systemctl 命令启动MariaDB 服务

[root@serverX]# systemctl start mariadb

注意

MariaDB的默认日志文件为 /var/log/mariadb/mariadb.log .在对MariaDB进行故障排除时，应首先查看该文件。

3、在serverX 上，使MariaDB 服务在系统启动时启动。

[root@serverX]# systemctl enable mariadb

4、在serverX 上验证该服务的状态

[root@serverX]# systemctl status mariadb

如果数据库启动，status 选项将报告一些属性：

• Loaded – 显示该服务是否已加载和启用
• Active – 显示该服务是否激活
• Main PID – 显示该服务的主进程 ID 。
• CGroup – 显示属于该服务的所有进程

注意

如果数据库停止，则 status 选项报告最后已知的 PID ，并且报告服务处于非活动状态。

提高MariaDB安装安全性

MariaDB 提供了一个程序以提高基线安装状态中的安全性。不带任何参数运行 mysql_secure_installation：

[root@serverX]# mysql_secure_installation

该程序通过以下方法提高 MariaDB安全性：

• 设置 root 账户的密码
• 删除可以从本地主机外部访问的root 账户
• 删除anonymous-user账户
• 删除test 数据库。

脚本为完全交互式，过程中的每一步都将提示用户。

MariaDB 和联网

可以将 MariaDB配置为远程访问，或仅限于本地连接。

1553144805984108.png

1553144822839696.png

在第一个场景中，只能从本地访问数据库。安全性极大提高，因为只能从相同服务器上的应用程序来访问记录。缺点是，服务器与其他服务共享相同的资源 ，这可能会影响数据库服务器的性能。

在第二个场景中，可以远程访问数据库。在此情况下，安全性降低，因为另一个端口在服务器上打开，这可以导致攻击。另一方面，由于不必共享资源，因此服务器性能会提高。

当远程访问 MariaDB 时，默认情况下，服务器侦听 3306上所有可用接口的 TCP/IP 连接。

注意

尽管MariaDB默认情况下侦听所有接口，但是默认情况下，任何用户都没有远程访问权限。

配置MariaDB联网

/etc/my.cnf 文件的[mysqld] 节下包含MariaDB网络配置指令。

bind-address

服务器将根据此指令进行侦听，只能输入一个值。可能的值包括：

• 主机名
• ipv4地址
• ipv6地址
• 将此值设置为 :: 可连接到所有可用地址（ipv4 和ipv6），或者留为空白（或设置为 0.0.0.0)以表示所有IPv4 地址。

重要

/etc/my.cnf 只能有一个 bind-address 条目。在具有多个地址的系统上，可以选择单个地址或全部地址。但不能进行这两者之间的选择。

skip-networking

如果设置为 1 ,服务器将仅侦听本地客户端。将通过套接字与服务器进行所有交互，默认情况下，套接字位置 /var/lig/mysql/mysql.sock。可以使用 /etc/my.cnf 中的 socket 值来更改此位置。

重要：**

请注意，如果以这种关闭联网，这还将禁用经由本地主机的连接。MySQL客户端仍可能通过套接字文件自动进行本地连接。

port

侦听 TCP/IP 连接的端口。

重要

对于远程访问，需要修改防火墙。幸运的是，这是一项已知的服务，因此只需要通过以下方式来添加：

[root@serverX]# firewall-cmd --permanent --add-service=mysql
[root@serverX]# firewall-cmd --reload

密码重置方法：

[root@server0 ~]# systemctl stop mariadb
[root@server0 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[root@server0 ~]# update user set password=password('redhat') whrer user='root';
[root@server0 ~]# flush privileges;

使用MariaDB 数据库

创建数据库

安装mariadb-client 组便会提供一个名为mysql 的程序 ，通过该程序，可以联系到本地或远程MariaDB 数据库服务器

1553158472321784.png

要连接到MariaDB数据库服务器的客户端

1. 用于指定此连接用户名的选项。
2. 此连接的用户名
3. 用于指定此连接的主机名的选项。如果未指定，则默认值为 localhost
4. 此连接的主机名
5. 用于提示密码的选项。

MariaDB中的数据库是作为目录实现的。默认安装具有四个数据库，请运行以下命令：

1553158632650339.png

重要

唯一可以擦除的默认数据库是 test

注意

MariaDB与大部分关系数据库系统一样，简单命令不区分大小写，这一点不同于 shell 。 show databases; 和 SHOW DATABASES; 的效果等同于 ShOw DATAbase ； 但是，表和数据库名区分大小写。很多数据库在设置时使用全小写作为数据库名称，因此常见做法是对命令使用大写，以区分命令自身与命令的目标（后面有更多示例）。这些命令以及在提示符中输入的大部分命令的重要部分将以；终止命令。

要创建新数据库，请运行以下命令

MariaDB [(none)]> CREATE DATABASE inventory;

在创建新数据库后，下一步是连接到此数据库，以便于使用表和数据来填充此数据库：

MariaDB [(none)]> USE inventory;

注意

使用此命令，可以随时在数据库之间切换。

MariaDB与所有关系数据库系统一样，每个数据库可以具有多个表。使用 SHOW TABLES;命令列出表：

1553159179622829.png

要列出某个表中的属性（或列名称），请使用：

1553159306182051.png

此输出完全描述了 mysql 数据库中servers 表中的数据。Port 属性存储为 interger ，使用最多 4 位数，且默认为0

对于其中大部分属性，Key 值为空。仅Server_name 具有一个值：PRI 。这将此属性设置为表的主键。主键是指表中数据的唯一标识符。任何两个条目都不能具有相同的主键，并且只能将一个属性设置为主键。主键通常用于将表链接到一起，在设计复杂数据库时，主键是一个重要概念。还有一些次键和组合键（其中多个属性共同构成唯一键）。

Extra 值用于显示属性的任何其他特性。此值可能很复杂，但是常见的是 auto_increment ，其声明，对于表中添加的每个新条目，该列的值将按 1 递增。这是主键具有的一个常见值。

使用 SQL ：结构化查询语言

结构化查询语言（SQL）是特殊编程语言，旨在管理关系数据库中保存的数据。一些常见的 SQL 命令包括 ：insert 、update 、delete 和 select 。

注意

这四个基本命令通常是指一般术语“CRUD操作”。CRUD 表示创建（insert) 、读取（select) 、更新（update）和删除（delete）.

要在表中 insert 数据，第一步是指出表的属性。

1553160155832581.png

在此示例中，所有属性都是必需的。要插入新产品，命令将相当长并且复杂：

1553160224515621.png

1. 表名称
2. 将插入的属性（列）
3. 将按照属性定义的相同顺序使用正确数据类型来插入的值。

注意

请注意，未指定属性 ID ，即使这是必需的。插入新记录时，MariaDB将自动为该列分配一个连续值。这是因为该列标记为 auto_increment.

使用 delete 语句删除记录：

1553222305282661.png

1. 表名称
2. 用于对命令执行施加条件的子句
3. 记录被删除的条件；经常使用主键值对。

警告

如果未指定 where 子句，则将擦除表中的所有记录。这是等同于运行 rm -rf / 的数据库

要更新记录，请使用 update 语句：

1553222690431839.png

1. 表名称
2. 定义指定属性中的新值
3. 用于对命令执行施加条件的子句
4. 记录得到更新的条件。

警告

如果未指定 where 子句，则将更新所有记录。

要从数据库中读取数据记录，请使用 select 语句：

1553223477301625.png

1. 将要选择的属性
2. 表名称

要选择所有属性，请使用通配符 * ;

1553223546724003.png

使用 where 子句过滤结果：

1553223559268223.png

where 子句的常用运算符：

运算符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	介于某个闭区间
LIKE	搜索某种模式
IN	指定某一列的多个可能值

管理数据库用户和访问权限

使用MariaDB 创建用户账户

默认情况下，MariaDB通过 mysql 数据库中的user 表来处理验证和授权。这意味着数据库的root 用户密码是保留在user 表中，而不是操作系统中。

注意

最新版本的 MariaDB 可以使用 PAM 在 Linux 上进行验证。

CREATE USER 语句创建新账户。该语句将在 mysql.user 表中创建一个没有特权的新行。

注意

information_schema 和 test 数据库为所有用户赋予部分特权。这就是为何 test 数据库常常被删除的主要原因。

重要

要创建新用户，连接的用户必须具有 mysql 数据库的全局 CREATE USER 特权或者 INSERT 特权。

账户名称指定为 'user_name'@'host_name'.这样便可以根据源主机（即，用户从该主机进行连接）来创建多个名称相同但特权不同的用户账户。

1553224588957594.png

该账户的用户名/主机名

该账户的密码

该用户只能从localhost 使用密码redhat 进行连接，并且没有任何特权。密码在 user 表中加密：

1553224668781852.png

使用该账户时，在授予任何特权前，将拒绝几乎任何操作的访问：

1553224720535285.png

注意

如果未提供主机名，则假定主机名为 '%' 。 这意味着该用户可以从任何源主机进行访问。

账户	描述
mobius@'localhost'	用户 mobius 只能从本地主机进行连接
mobius@'192.168.1.5'	用户 mobius 可以从主机 192.168.1.5进行连接
mobius@'192.168.1.%'	用户 mobius 可以从任何属于网络 192.168.1.0 的主机进行连接
mobius@'%'	用户 mobius 可以从任何主机进行连接
mobius@'2000:472:18:b51:c32:a21'	用户 mobius 可以从主机 2000:472:18:b51:c32:a21 进行连接

为用户账户授权和撤销特权

特权是指用户在 MariaDB中可以具有的权限。特权分为：

• 全局特权，如 CREATE USER 和 SHOW DATABASES, 用于管理数据库服务器自身。
• 数据库特权，如 CRUD 命令，用于创建表和操作数据库中的数据。
• 列特权，用于授权与表类似的命令用法，但是针对特定列（一般很少）
• 其他更细致的特权，在 MariaDB 文档中有详细讨论。

GRANT 语句可用于为账户授权特权。连接的用户必须具有 GRANT OPTION 特权（在多个级别存在的一种特殊特权）才能授予特定。用户只能向其他用户授予自己已经获得的特权（例如，除非mobius 用户已具有 SELECT 特权，和 GRANT OPTION 表特权，否则 mobius 无法对数据库表授予该特权）

1553239821231349.png

1. 定义要授予的特权（在此情况下，将授予完整的 CRUD 功能）
2. 定义将针对哪些表授予特权。
3. 要授予特权的用户

授权示例：

授权	描述
GRANT SELECT ON database.table TO username@hostname	向特定用户授予对特定数据库特定表的选择
GRANT SELECT ON database.* TO username@hostname	向特定用户授予特定数据库中所有表的选择特权
GRANT SELECT ON*.* TO username@hostname	向特定用户授予对所有数据库中所有表的选择特权
GRANT CREATE,ALTER,DROP ON database.* TO username@hostname	向特定用户授予在特定数据库中创建、更改和丢弃（删除）表的特权
GRANT ALL PRIVILEGES ON*.* TO username@hostname	向特定用户授予对所有数据库的所有可用特权，事实上是创建一个超级用户（类似于 root 用户）。

ROVOKE 语句可撤销的特权。连接的用户必须具有 GRANT OPTION 特权并具有要撤销的特权，才能撤销特权。

1553240602945918.png

1. 定义要撤销的特权
2. 定义要针对哪些撤销特权。
3. 从该用户撤销特权

重要

在授予或撤销特权后，将从 mysql 数据库的特权表中重新加载所有特权。

MariaDB [(none)]> FLUSH PRIVILEGES;

要撤销特权，将需要已对用户授予的特权列表。简单命令 SHOW GRANT FOR username ;将能提供该用户的特权列表：

1553240945244097.png

当不再需要某个用户时，可以使用 DROP USER username; 将其从数据库中删除。username 应使用用于 CREATE USER 的相同格式，即 'user'@'host'

重要

如果对当前连接的账户发出了 DROP 命令，那么在连接关闭之前不会删除该账户。连接将不会自动关闭。

数据库访问权限故障排除

一些常见的数据库访问权限问题

问题	解决方案
用户已被授予了从任何主机进行连接的访问权限，但是只能使用mysql 命令在本地主机上进行连接（用户使用的应用程序无法进行连接，即使是在本地主机上也如此）	从 my.cnf 中删除 skip-networking 指令，然后重新启动服务
用户在本地主机上可以使用任何应用程序进行连接，但是无法远程进行连接。	检查 my.cnf 中的 bind-address 配置以确保数据库可以访问。确保 user 表包含用户尝试从中进行连接的源主机中用户的条目。
用户可以连接，但是看不到 information_schema 和 test 以外的任何其他数据库。	这是在刚刚创建用户时的常见问题，因为这些用户在默认情况下没有特权，尽管他们可以进行连接并使用这些默认数据库。针对用户需要的数据库添加授权。
用户可以连接，但是不能创建任何数据库	为用户授予全局 CREATE 特权（这还将授予 DROP 特权）
用户可以连接，但是不能读写任何数据	仅针对用户将要使用的数据库，为用户授予 CRUD 特权。

创建和恢复 MariaDB 备份

创建备份

备份 MariaDB 数据库和一般数据库非常重要。数据库通常包含公司的大部分任何关键数据（销售、客户等等）。执行备份使系统管理员能够在发生下列几种类型的事件之后恢复数据。

• 操作系统崩溃
• 电源故障
• 文件系统崩溃
• 硬件问题
• 安全漏洞
• 数据库损坏
• 数据中毒

有两种备份MariaDB的方法：

• 逻辑
• 物理（原始）

逻辑备份在纯文本文件中导出信息和记录，而物理备份则包含存储内容的文件和目录的副本。

逻辑备份具有以下特征：

• 数据库结构是通过查询数据库来检索的
• 逻辑备份的可移植性很高，在某些情况下可以恢复到另一个数据库提供程序（如Postgres)
• 备份过程很慢，因为服务器必须访问数据库信息并将其转换为逻辑模式。
• 在服务器联机时执行。
• 备份不包含日志和配置文件。

物理备份具有以下特征：

包含数据库目录和文件夹的原始副本。

• 输出更精简。
• 备份可以包含日志和配置文件。
• 只能移植到具有类似硬件和软件的其他计算机。
• 比逻辑备份快。
• 应在服务器脱机或者数据库中所有表均锁定时执行，防止在备份期间发生更改。

执行逻辑备份

可以通过 mysqldump 命令来完成逻辑备份：

1553243759558374.png

1. 用户连接要备份的MariaDB的用户名
2. 对此用户的密码提示
3. 要进行备份的选项数据库
4. 备份文件

注意

要以逻辑方式备份所有数据库，请使用选项 –all-databases;

[root@serverX ]# mysqldump -uroot -p --all-databases > /backup/mariadb.dump

这种类型的转储将包括 mysql 数据库，其中包含所有的user 信息。

逻辑备份的输出将显示为一系列的SQL 语句。例如，以下是 mysql 数据库的转储中的一个片段：

1553244185411535.png

请注意，可轻松查看 mobius 的加密密码，因此请小心存储这种类型的备份。此外，在逻辑备份期间读取个别表时，默认将这些表锁定和解锁。

重要

mysqldump 要求至少对转储的表具有 select 权限，对转储的的视图具有 SHOW VIEW 权限，对转储的触发器具有 TRIGGER 权限 。

有用的选项

选项	描述
–add-drop-tables	告知 MariaDB 在每个 CREATE TABLE 语句前面添加一个 DROP TABLE 语句。
–no-data	仅转储数据库结构，不转储内容
–lock-all-tables	在复制完成后，无法在数据库中的任何位置插入新记录。此选项对于确保备份完整性非常重要。
–add-drop-databases	告知 MariaDB在每个 CREATE DATABASE 语句前面添加一个 DROP DATABASE 语句

执行物理备份

有多种工具可用于执行物理备份，如 ibbackup、cp、mysqlhotcopy 和 lvm 。

MariaDB 物理备份任务可以利用LVM 快照的已知优势。以下过程将使用 LVM 来备份MariaDB。

重要

这种方法的关键优势是，速度非常快，并且可使数据库的停机时间缩短。这是一个很有用的参数，可将数据库文件放在专用LVM 分区上。

验证MariaDB文件的存储位置

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysqladmin variables | grep datadir
| datadir                                           | /var/lib/mysql/

验证托管该位置的逻辑卷

[root@localhost ~]# df /var/lib/mysql
Filesystem              1K-blocks    Used Available Use% Mounted on
/dev/mapper/centos-root   8374272 1827152   6547120  22% /

这表明卷组为 centos 并且逻辑卷名称为 root 。

验证可供快照使用的空间量：

[root@localhost ~]# vgdisplay centos | grep Free
  Free  PE / Size       15321 / 61.29 GB

这表明有 61.29 GB 空间可供快照使用。

连接到MariaDB 将表刷新到磁盘，并且将其锁定（或者，关闭 mariadb 服务）：

警告

这一步非常重要，可使用新记录插入到数据库中，同时创建快照。

[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

警告

请勿关闭此会话，只要客户端断开连接便会释放此锁定。在 LVM 快照创建之前，数据库必须保存锁定状态。

在另一个终端会话中，创建LVM 快照：

[root@localhost ~]# lvcreate -L20G -s -n mariadb-backup /dev/centos/root

重要

该快照的大小需要足以保存快照

在原始的MariaDB会话中，解锁表（或者，启动 mariadb服务）

MariaDB [(none)]> UNLOCK TABLES;

现在可以在任意位置挂载快照：

[root@localhost ~]# mkdir /mnt/snapshot
[root@localhost ~]# mount /dev/centos/mariadb-backup /mnt/snapshot

此后，任何标准文件系统备份均可以用于存储挂载在 /mnt/snapshot 下面的 var/lib/mysql 的副本。

重要

在备份了快照之后 ，切记删除快照

[root@localhost ~]# umount /mnt/snapshot
[root@localhost ~]# lvremove /dev/centos/mariadb-bakcup

恢复备份

逻辑恢复

可以使用命令 mysql 来完成逻辑恢复：

1553321594358720.png

1. 恢复MariaDB 备份时需要连接到用户（通常是 root 用户或某些其他超级用户）
2. 该用户的密码
3. 要进行恢复备份的选定数据库
4. 备份文件

物理恢复

要进行物理恢复，必须停止 MariaDB 服务：

[root@localhost ~]# mysqladmin variables | grep datadir
| datadir                                           | /var/lib/mysql/               |
[root@localhost ~]# systemctl stop mariadb             
[root@localhost ~]# rm -rf /var/lib/mysql/*

此后 ，任何标准文件系统恢复都可以用于将副本从备份恢复到 /var/lib/mysql