MariaDB数据库运维的笔试题

一、基础与架构

1.MariaDB与MySQL的核心

存储引擎：MariaDB默认使用Aria引擎，MySQL默认使用InnoDB。Aria通过事务日志实现崩溃安全，但实际不支持完整的ACID事务（如无法回滚未提交的事务）InnoDB支持完整ACID事务：原生支持原子性、一致性、隔离性、持久性，提供事务提交、回滚及多版本并发控制。

复制机制：MariaDB支持Galera Cluster多主同步复制（事务需在多数节点（如半数以上）提交成功后才返回客户端，确保全局数据一致性），MySQL默认异步复制（主库提交事务后立即返回，不等待从库确认。主从不一致风险高）

2.MariaDB主从复制的原理

包管理：MariaDB使用apt/yum install mariadb-server，MySQL使用apt/yum install mysql-server

描述binlog传输流程（二进制日志同步机制）：主库记录所有数据变更到binlog，从库通过IO线程读取主库binlog并应用到本地的Relay Log，SQL线程执行Relay Log中的事件

如何解决复制延迟：调整slave_parallel_workers参数启用并行复制，优化主库SQL执行效率

– 主库宕机后如何快速恢复：基于GTID或binlog回放恢复数据

二、运维实践

1.备份与恢复策略

逻辑备份与物理备份的优缺点对比：

逻辑备份（mysqldump）：可读性强，跨平台兼容，但备份时间长、恢复慢

物理备份（xtrabackup）：备份速度快，恢复效率高，但依赖存储引擎特性

设计一个包含全量+增量备份的自动化脚本（需包含时间戳命名规则）：

!/bin/bash

DATE=$(date +%F_%T)
mysqldump -u root -p –all-databases > full_$DATE.sql
mysqlbinlog –start-datetime=”$DATE” mysql-bin.000001 > inc_$DATE.sql
脚本逐行解释

#!/bin/bash 声明这是一个 Bash Shell 脚本。
DATE=$(date +%F_%T) 存入变量 DATE获取当前时间并格式化为年-月-日_时:分:秒
作用：（如 2024-05-20_14:30:00），。
mysqldump -u root -p –all-databases > full_$DATE.sql
作用：执行全量备份。
细节：
-u root：使用 MySQL 的 root 用户（实际应避免，改用专用备份用户）。
-p：提示输入密码（但脚本中未提供密码，需手动输入，无法自动化）。
–all-databases：备份所有数据库。
输出保存为 full_2024-05-20_14:30:00.sql。
mysqlbinlog –start-datetime=”$DATE” mysql-bin.000001 > inc_$DATE.sql
作用：从指定时间点开始解析二进制日志，生成增量备份。

单表误删后如何通过二进制日志恢复：基于 MySQL 二进制日志（binlog）进行时间点恢复
mysqlbinlog –start-datetime=”2025-03-17 10:00:00″ –stop-datetime=”2025-03-17 10:05:00″ mysql-bin.000001 | mysql -u root -p
mysql-bin.000001 的二进制日志文件，提取 2025-03-17 10:00:00 到 10:05:00 之间的所有数据库变更事件（如增删改操作
将解析出的 SQL 语句通过管道传递给 MySQL 客户端，以 root 用户身份执行这些操作，实现数据恢复

三、故障排查与高可用

1.常见报错处理

连接超时（Error 2003）：检查防火墙规则与bind-address配置，确保3306端口开放
复制中断（Error 1236）：通过SHOW SLAVE STATUS\G查看Last_IO_Error，检查网络连通性或主库binlog位置
表空间不足（Error 1114）：扩容磁盘或清理冗余数据，调整innodb_file_per_table=ON单表存储
2.高可用方案设计
Galera Cluster与MySQL Group Replication的对比：
Galera Cluster：多主同步复制，自动冲突解决，但仅支持InnoDB/XtraDB引擎
Group Replication：基于Paxos协议，支持跨机房部署，需至少3个节点
设计一个基于Keepalived+ProxySQL的负载均衡方案（拓扑图略）：
Keepalived实现VIP漂移，ProxySQL监控后端MariaDB节点，自动切换故障实例
脑裂场景下的数据恢复流程：
SET GLOBAL wsrep_provider_options=”pc.bootstrap=true”;

四、安全与权限

1.权限管理最佳实践

最小权限原则示例：
CREATE USER ‘backupuser’@’192.168.1.%’ IDENTIFIED BY ‘password’;
创建一个用户 backupuser，允许该用户从 192.168.1.0 到 192.168.1.255 的 IP 地址连接设置登录密码为 password
GRANT SELECT, LOCK TABLES ON . TO ‘backupuser’@’192.168.1.%’;
授权用户读取数据库中的数据，允许用户锁定表，权限作用于所有数据库和表，将权限赋予backupuser用户
密码策略配置：启用
validate_password插件，设置validate_password_length=8和validate_password_policy=MEDIUM
防御SQL注入的MariaDB配置：
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES
作用：对事务型存储引擎（如 InnoDB）启用严格模式。
细节：
插入或更新数据时，若数据不符合表定义（如字段长度超限、类型不匹配、非法日期等），直接报错并拒绝操作。
非事务型表（如 MyISAM）在遇到非法数据时，若无法修正则会报错，否则可能截断数据并发出警告（仅当第一个非法记录出现时）
NO_ZERO_IN_DATE
作用：禁止日期或时间中的月份或日为零（如 ‘2024-00-00’）。
细节：
若启用此模式，’2024-00-00′ 会被视为非法日期，直接报错。
若未启用，允许插入但会发出警告；若同时启用 STRICT_TRANS_TABLES，则直接拒绝写入
NO_ZERO_DATE
作用：禁止插入零日期 ‘0000-00-00’。
细节：
若启用，尝试插入 ‘0000-00-00’ 会报错。
若未启用，允许插入但发出警告；若同时启用 STRICT_TRANS_TABLES，直接拒绝写入
ERROR_FOR_DIVISION_BY_ZERO
作用：在除零运算时抛出错误（如 SELECT 1/0）。
细节：
若启用，除零操作会直接报错，而非返回 NULL。
若未启用，返回 NULL 并发出警告；若同时启用 STRICT_TRANS_TABLES，直接拒绝写入
NO_AUTO_CREATE_USER
作用：禁止通过 GRANT 语句自动创建无密码用户。
细节：
若未启用，执行 GRANT ALL ON db.* TO ‘user’@’host’; 会自动创建用户 user（密码为空）。
启用后，必须显式使用 CREATE USER 创建用户，增强安全性
NO_ENGINE_SUBSTITUTION
作用：当指定存储引擎不可用时直接报错，而非替换为默认引擎。
细节：
若未启用，创建表时若指定引擎不可用（如未编译或禁用），MySQL 会自动替换为默认引擎（如 InnoDB）并发出警告。

启用后，直接报错，避免因引擎替换导致表结构与预期不符