Orange的运维学习日记–MariaDB详解与服务部署
文章目录
介绍 MariaDB
数据库概念
数据库是存放和管理计算机数据的仓库采用一定的数据结构来组织和存储数据通过数据库提供的接口进行数据的增删改查
数据结构是指数据的组织形式及数据之间的逻辑联系在数据库中常见的数据结构包括表格、索引、视图等
数据库模型
按照传统模型数据库可分为层次式、网状和关系型
- 层次式数据库采用树状结构来组织数据例如文件系统
- 网状数据库采用网状结构支持多对多关系例如早期的网络通信系统
- 关系型数据库采用二维表模型将数据抽象为行和列的表格通过表与表之间的关系来管理数据
现代互联网应用中常用的数据库模型主要有关系型数据库和非关系型数据库
关系型数据库将复杂的数据结构映射为表格通过 SQL 语言进行灵活的查询和操作典型产品包括 Oracle、MySQL、PostgreSQL 等
非关系型数据库也称为 NoSQL 数据存储支持灵活的数据模型典型产品包括 Redis、MongoDB 等
关系型数据库与 LAMP 架构
关系型数据库将结构化数据存储在一组相互关联的表中不同表间可通过外键建立关系支持复杂查询和事务处理
RDBMS 是用于管理关系数据库的软件系统大多数 RDBMS 支持 SQL 结构化查询语言用于定义、操作和控制数据库
LAMP 架构通常由 Linux、Apache、MariaDB/MySQL、PHP 等组件组成其中 MariaDB 负责存储和管理动态生成的 Web 应用数据
MariaDB 概述
MariaDB 是 MySQL 的开源分支由社区主导开发采用 GPL 许可协议在沿用 MySQL 特性的同时引入多项性能和安全增强
MariaDB 与 MySQL 在 API 和命令行接口上完全兼容可在不修改应用代码前提下无缝替换 MySQL
MariaDB 支持多种存储引擎如 InnoDB、MyISAM、Aria 等并提供热备份、加密、压缩等高级功能
部署 MariaDB
安装 MariaDB
在 Rocky 8 系统中 MariaDB 由 AppStream 仓库提供可以通过 yum 命令完成安装
yum install -y mariadb-server mariadb
安装完成后启用并启动数据库服务
systemctl enable --now mariadb
为允许远程访问需要在防火墙中开放 3306 端口
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload
安全加固
MariaDB 默认包含无密码 root 账户、测试数据库和匿名用户需运行 mysql_secure_installation 脚本进行安全配置
mysql_secure_installation
在交互式提示中依次完成以下操作
- 为 root 账户设置强密码
- 禁止远程 root 登录
- 删除匿名用户
- 删除测试数据库及访问权限
连接方式
MariaDB 客户端可通过本地套接字或 TCP 网络与服务器通信
本地套接字连接性能更佳无需网络监听
mysql -urootTCP 网络连接适用于远程访问需在服务器上授权并监听 3306 端口
grant all privileges on *.* to 'user'@'%' identified by 'password' mysql -h server -u user -p
配置优化
MariaDB 主配置文件位于 /etc/my.cnf，主要配置可在 [mysqld] 节中调整
- bind-address 指定侦听地址可设置为特定 IP、0.0.0.0 或 :: 监听所有地址
- port 指定侦听端口默认 3306 可根据需要修改
- skip-networking 设置为 1 可禁用网络监听仅允许套接字连接
客户端配置文件位于 /etc/my.cnf.d/mysql-clients.cnf 可预置连接参数
[mysql]
user=laoma
password=redhat
host=server
port=3306
在 MariaDB 中使用 SQL
SQL 语言分类
SQL 是关系数据库的标准操作语言国际标准化组织 ISO 颁布了 SQL 标准主要包含以下子语言
- 数据查询语言 DQL 用于检索数据关键字包括 SELECT、WHERE、ORDER BY、GROUP BY、HAVING
- 数据操作语言 DML 用于修改数据关键字包括 INSERT、UPDATE、DELETE
- 数据定义语言 DDL 用于定义数据结构关键字包括 CREATE、DROP、ALTER
- 事务控制语言 TCL 用于管理事务关键字包括 BEGIN、COMMIT、ROLLBACK
- 数据控制语言 DCL 用于权限管理关键字包括 GRANT、REVOKE
交互式与非交互式使用
mysql 客户端支持交互式和非交互式两种模式
交互式模式下结果以 ASCII 表格格式显示
mysql -u root -p
非交互式模式下结果以制表符分隔的格式输出适合脚本处理
mysql -u root -p -e "SELECT VERSION()"
基本操作示例
查询示例
SELECT id, name, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
插入示例
INSERT INTO users(name, email, status)
VALUES('zhangsan', 'zhangsan@example.com', 'active')
更新示例
UPDATE users
SET status = 'inactive'
WHERE last_login < '2023-01-01'
删除示例
DELETE FROM users
WHERE status = 'inactive'
AND created_at < '2022-01-01'
管理 MariaDB 用户
创建与授权
使用 CREATE USER 和 GRANT 语句管理用户及权限
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass123'
GRANT SELECT, INSERT, UPDATE, DELETE
ON mydb.*
TO 'appuser'@'%'
FLUSH PRIVILEGES
查看与修改权限
查看权限
SHOW GRANTS FOR 'appuser'@'%'
修改密码
ALTER USER 'appuser'@'%' IDENTIFIED BY 'NewPass456'
删除用户
DROP USER 'appuser'@'%'
备份与恢复 MariaDB
逻辑备份
使用 mysqldump 工具导出数据库为 SQL 文本文件
mysqldump -u root -p mydb > mydb_backup.sql
恢复逻辑备份
mysql -u root -p mydb < mydb_backup.sql
物理备份
MariaDB 官方提供 mariabackup 工具进行热备份支持 InnoDB 和 XtraDB 引擎
mariabackup --backup --target-dir=/data/backup/$(date +%F)
mariabackup --prepare --target-dir=/data/backup/$(date +%F)
mariabackup --copy-back --target-dir=/data/backup/$(date +%F)
二进制日志恢复
通过二进制日志可以在备份后应用增量变更实现点-in-time 恢复
在配置文件中启用二进制日志
[mysqld]
log-bin = mysql-bin
server-id = 1
使用 mysqlbinlog 回放日志
mysqlbinlog \
--start-datetime="2023-08-01 00:00:00" \
--stop-datetime="2023-08-01 12:00:00" \
/var/lib/mysql/mysql-bin.000001 \
| mysql -u root -p mydb
备份和恢复
备份方式
逻辑备份
逻辑备份将数据库导出为包含重新创建数据库所需 SQL 命令的文本文件
- 通过查询数据库检索数据库结构
- 备份速度较慢需要将数据转换为逻辑格式
- 具有高度可移植性可还原到其他数据库提供程序
- 在服务器联机时执行无需停机
- 不包含日志或配置文件
物理备份特征
物理备份复制原始数据库目录和文件以保留数据页和日志
- 可包括日志和配置文件
- 仅可移植到硬件和软件环境相似的系统
- 速度快无需解析数据结构
- 通常在服务器脱机或全表锁定时执行以保证一致性
执行备份
执行逻辑备份
执行逻辑备份的用户至少需要以下权限
- 对待导出表具有 SELECT 权限
- 对视图具有 SHOW VIEW 权限
- 对触发器具有 TRIGGER 权限
常用命令和选项
# 基本用法
mysqldump [OPTIONS] database [tables]
# 导出多个数据库
mysqldump [OPTIONS] --databases DB1 [DB2 ...]
# 导出全部数据库
mysqldump [OPTIONS] --all-databases
# 常用选项示例
-A, --all-databases 导出所有数据库
-B, --databases 导出指定数据库并包含 USE 声明
--ignore-database=name 排除指定数据库
--ignore-table=db.table 排除指定表
--add-drop-database 每个库前添加 DROP DATABASE
--add-drop-table 每个表前添加 DROP TABLE
-x, --lock-all-tables 全局读锁模式导出
示例
mysqldump -u root -p inventory > /backup/inventory.dump
mysqldump -u root -p --all-databases > /backup/all_databases.sql
导出文件片段
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `category` WRITE;
INSERT INTO `category` VALUES (1,'Networking'),(2,'Servers'),(3,'Ssd');
UNLOCK TABLES;
执行物理备份
物理备份工具 mariabackup 由 mariadb-backup 包提供
# 安装 mariabackup
yum install -y mariadb-backup
# 准备备份目录
mkdir -p /var/mariadb/backup/
# 执行物理备份
mariabackup --backup --target-dir /var/mariadb/backup/ \
--user root --password redhat
备份完成后目录示例
ls /var/mariadb/backup/
aria_log.00000001 ib_buffer_pool mysql
aria_log_control ibdata1 performance_schema
backup-my.cnf ib_logfile0 xtrabackup_checkpoints
xtrabackup_info
可将密码预置于配置文件
[xtrabackup]
user=root
password=redhat
推荐创建具有 RELOAD、LOCK TABLES 和 REPLICATION CLIENT 权限的专用备份用户以替换 root
执行恢复
恢复操作会用备份内容覆盖现有数据请谨慎执行
恢复逻辑备份
mysql -u root -p inventory < /backup/all_databases.sql
恢复物理备份
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
datadir=$(grep '^datadir' /etc/my.cnf.d/mariadb-server.cnf | cut -d= -f2)
rm -rf ${datadir}/*
# 恢复备份数据
mariabackup --copy-back --target-dir /var/mariadb/backup/
# 修正文件权限
chown -R mysql:mysql ${datadir}
# 重启数据库服务
systemctl start mariadb
## 恢复逻辑备份
```bash
mysql -u root -p inventory < /backup/all_databases.sql
恢复物理备份
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
datadir=$(grep '^datadir' /etc/my.cnf.d/mariadb-server.cnf | cut -d= -f2)
rm -rf ${datadir}/*
# 恢复备份数据
mariabackup --copy-back --target-dir /var/mariadb/backup/
# 修正文件权限
chown -R mysql:mysql ${datadir}
# 重启数据库服务
systemctl start mariadb