MariaDB 实战指南

bash sudo apt update sudo apt install mariadb-server -y

**CentOS/RHEL/Fedora**
```bash
sudo dnf install mariadb-server -y     # RHEL 8+ / Fedora
sudo systemctl start mariadb
sudo systemctl enable mariadb

安装完成后，建议立即运行安全配置脚本：

sudo mysql_secure_installation

根据提示设置 root 密码、删除匿名用户、禁止远程 root 登录、删除测试数据库并重新加载权限表。

2.2 在 Windows 上安装

前往 mariadb.org/download 下载 MSI 安装包，按照向导安装，设置 root 密码并选择 UTF‑8 作为默认字符集。

2.3 基本管理命令

# 启动/停止/重启服务
sudo systemctl start|stop|restart mariadb

# 查看状态
sudo systemctl status mariadb

# 连接数据库
mysql -u root -p

3. 核心配置文件

MariaDB 的主配置文件通常位于 /etc/mysql/mariadb.conf.d/50-server.cnf（Ubuntu）或 /etc/my.cnf。重要参数举例：

[mysqld]
bind-address = 0.0.0.0          # 允许远程连接（生产环境需配合防火墙）
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 150
query_cache_size = 0            # MariaDB 10.2+ 默认禁用，推荐使用 ProxySQL 等外部缓存
innodb_buffer_pool_size = 1G    # 设置为物理内存的 50%～70%

修改配置后需重启服务：sudo systemctl restart mariadb

4. 用户与权限管理

创建与管理用户是安全运维的基础。

4.1 创建用户

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';  -- 允许特定网段

4.2 授予权限

-- 授予对 app_db 的所有权限
GRANT ALL PRIVILEGES ON app_db.* TO 'appuser'@'localhost';

-- 只授权读权限
GRANT SELECT ON app_db.* TO 'readonly'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

4.3 查看与回收

SHOW GRANTS FOR 'appuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'appuser'@'localhost';
DROP USER 'appuser'@'localhost';

5. 实战：创建数据库和表

5.1 数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;

-- 选择数据库
USE shop;

5.2 常用数据类型

5.3 创建表的例子

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5.4 插入、查询、更新与删除

-- 插入
INSERT INTO products (name, price, description)
VALUES ('Wireless Mouse', 25.99, 'Ergonomic wireless mouse');

-- 查询
SELECT * FROM products WHERE price > 20.00;

-- 更新
UPDATE products SET price = 22.90 WHERE id = 1;

-- 删除
DELETE FROM products WHERE id = 1;

6. 高级查询技巧

6.1 连接（JOIN）

假设有 orders 表与 products 表关联：

SELECT o.id AS order_id, p.name, o.quantity
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';

6.2 聚合与分组

SELECT p.name, SUM(o.quantity) AS total_sold
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id
HAVING total_sold > 10
ORDER BY total_sold DESC;

6.3 子查询

SELECT name, price
FROM products
WHERE id IN (
    SELECT product_id FROM orders WHERE quantity > 5
);

6.4 窗口函数（MariaDB 10.2+）

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

7. 索引与性能优化

7.1 创建索引

-- 单列索引
CREATE INDEX idx_price ON products(price);

-- 多列索引
CREATE INDEX idx_name_price ON products(name, price);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_name ON products(name);

7.2 查看执行计划

使用 EXPLAIN 分析查询能否有效利用索引：

EXPLAIN SELECT * FROM products WHERE name = 'Wireless Mouse';

关注 type 列是否为 ref 或 eq_ref，避免全表扫描（ALL）。

7.3 慢查询日志

在配置文件中开启慢查询日志：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2

重启后，用 mysqldumpslow 工具分析日志。

7.4 表优化建议

• 使用 OPTIMIZE TABLE table_name; 整理碎片。
• 定期 ANALYZE TABLE table_name; 更新统计信息。

8. 备份与恢复

8.1 逻辑备份：mysqldump

# 备份单个数据库
mysqldump -u root -p shop > shop_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

恢复：

mysql -u root -p shop < shop_backup.sql

8.2 物理备份：Mariabackup（推荐用于大数据库）

安装：

sudo apt install mariadb-backup   # Ubuntu
sudo dnf install mariadb-backup   # RHEL

完整备份：

sudo mariabackup --backup --target-dir=/backup/mariadb/full --user=root --password=YourPassword

准备恢复（在目标服务器）：

sudo mariabackup --prepare --target-dir=/backup/mariadb/full
sudo systemctl stop mariadb
sudo mariabackup --copy-back --target-dir=/backup/mariadb/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

9. 日志与监控

9.1 错误日志

默认位置：/var/log/mysql/error.log。排查启动失败、表损坏等问题。

9.2 通用查询日志（调试用，生产慎用）

general_log = 1
general_log_file = /var/log/mysql/mariadb-query.log

9.3 性能监控工具

• SHOW STATUS：查看连接数、流量等。
• information_schema：查询表大小、索引使用等。
• 使用 Percona Monitoring and Management (PMM) 或 MariaDB MaxScale 实现可视化监控。

10. 实战案例：简易博客数据库设计

设计一个支持用户、文章、评论的博客系统。

CREATE DATABASE blog;
USE blog;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    published_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id),
    INDEX idx_published (published_at)
);

-- 评论表
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

常用查询示例：

-- 最近发布的文章及作者
SELECT p.title, u.username, p.published_at
FROM posts p
JOIN users u ON p.author_id = u.id
ORDER BY p.published_at DESC LIMIT 10;

-- 某篇文章的评论数
SELECT p.title, COUNT(c.id) AS comment_count
FROM posts p LEFT JOIN comments c ON p.id = c.post_id
WHERE p.id = 1;