MySQL/MariaDB数据库常用操作指南

适用环境：Debian/Ubuntu + MariaDB 10.x（个人博客最常见组合），MySQL 8.x 大同小异；博客程序以 Typecho 1.x 为例。文中 SQL 在 MariaDB 10.4+ 与 MySQL 8.0+ 下均适用，个别差异已单独标注。

本文定位：个人小流量的博客站点场景，给自己看的数据库运维学习笔记，覆盖日常 90% 的操作场景——登录、用户管理、建库删库、最小权限授权、备份与恢复、安全加固、日常维护与 Typecho 迁移。

1. 登录与基础操作

# 进入数据库（推荐直接 sudo，避免权限问题）
sudo mysql

# 普通账号登录
mysql -u 用户名 -p

Debian 提示：Debian/Ubuntu 的 MariaDB root 默认使用 unix_socket 认证，直接 mysql -u root -p 会报 Access denied，用 sudo mysql 即可免密进入。

-- 查看版本
SELECT VERSION();

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE typecho;

-- 查看当前登录用户
SELECT CURRENT_USER();

-- 退出
EXIT;  -- 或 \q

2. 用户管理

2.1 创建用户

MariaDB 10.4+ / MySQL 8.0 起，创建用户与授权是两条独立命令（旧版 GRANT 隐式建用户的写法已废弃）：

CREATE USER 'blog'@'localhost' IDENTIFIED BY '这里写强密码';

• 'localhost'：仅本机可连，博客与数据库同机部署时用这个最安全；
• 'blog'@'%'：允许任意主机连接（远程管理才用）；
• 'blog'@'192.168.1.%'：仅允许指定网段连接。

2.2 查看已有用户

SELECT User, Host, plugin FROM mysql.user;

2.3 修改密码

-- MariaDB 与 MySQL 8.0 通用写法
ALTER USER 'blog'@'localhost' IDENTIFIED BY '新密码';

-- MariaDB 也兼容旧写法
SET PASSWORD FOR 'blog'@'localhost' = PASSWORD('新密码');

2.4 删除用户

DROP USER 'blog'@'localhost';

2.5 忘记 root 密码时的紧急重置

sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables --skip-networking &
sudo mysql

-- 进入后执行
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

# 重启服务
sudo systemctl restart mariadb

3. 数据库管理

3.1 创建数据库（博客专用）

CREATE DATABASE typecho DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

重要：个人博客一律用 utf8mb4，否则 emoji、生僻字会变乱码。utf8 已过时，不要再用。

3.2 查看 / 删除数据库

SHOW DATABASES;

-- ⚠️ 删除不可恢复，删前必须备份
DROP DATABASE typecho;

3.3 修改数据库字符集（迁移/乱码时常用）

ALTER DATABASE typecho CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 表级转换
ALTER TABLE typecho_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 权限管理：博客站点最小必要权限

4.1 Typecho 需要的权限

个人博客只需「单库、非管理、读写改表」权限，不要给 FILE、PROCESS、SUPER、RELOAD、GRANT OPTION 等管理权限——博客程序一旦被入侵，越权权限会直接导致拖库：

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
ON typecho.* TO 'blog'@'localhost';

-- 个别插件/备份脚本需要临时表与锁表时，再追加：
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON typecho.* TO 'blog'@'localhost';

-- 使权限立即生效
FLUSH PRIVILEGES;

4.2 查看与回收权限

-- 查看某用户权限
SHOW GRANTS FOR 'blog'@'localhost';

-- 回收权限
REVOKE DROP ON typecho.* FROM 'blog'@'localhost';

4.3 建库建号一条龙（新站初始化模板）

CREATE DATABASE typecho DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'blog'@'localhost' IDENTIFIED BY '强密码';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
ON typecho.* TO 'blog'@'localhost';
FLUSH PRIVILEGES;

5. 备份与恢复（最重要的一节）

5.1 全量备份（mysqldump）

# 单库备份
mysqldump -u root -p --single-transaction --default-character-set=utf8mb4 typecho > typecho_$(date +%F).sql

# 压缩备份（推荐，2C2G 小机器更省空间）
mysqldump -u root -p --single-transaction --default-character-set=utf8mb4 typecho | gzip > typecho_$(date +%F).sql.gz

# 只备份单张表（如只导文章表）
mysqldump -u root -p typecho typecho_posts > posts.sql

参数说明：

• --single-transaction：InnoDB 表在线备份不加锁，不中断博客访问；
• --default-character-set=utf8mb4：防止备份文件里中文变乱码；
• --no-data：只要表结构不要数据（配合迁移建表用）。

5.2 定时自动备份（crontab）

crontab -e

# 每天凌晨 3:30 备份，保留最近 30 天
30 3 * * * mysqldump -u root -p'密码' --single-transaction typecho | gzip > /backup/typecho_$(date +\%F).sql.gz && find /backup -name '*.sql.gz' -mtime +30 -delete

命令行明文写密码有安全隐患，推荐把账号密码放进 ~/.my.cnf（并 chmod 600 ~/.my.cnf）：

[mysqldump]
user=root
password=你的密码

5.3 恢复数据库

# 方式一：命令行直接导入（目标库需已存在）
mysql -u root -p typecho < typecho_2026-09-29.sql

# 方式二：进入数据库后用 source
mysql -u root -p

USE typecho;
SOURCE /backup/typecho_2026-09-29.sql;

# 恢复压缩包
gunzip < typecho_2026-09-29.sql.gz | mysql -u root -p typecho

5.4 恢复后的检查清单

SHOW TABLES;                          -- 表是否齐全
SELECT COUNT(*) FROM typecho_posts;   -- 文章数是否对上
SELECT COUNT(*) FROM typecho_comments;-- 评论数

6. 安全配置

6.1 一键安全初始化

sudo mysql_secure_installation

交互式完成：设置/验证 root 密码、删除匿名用户、禁止 root 远程登录、删除 test 测试库。

6.2 只允许本机连接（推荐）

编辑 /etc/mysql/mariadb.conf.d/50-server.cnf（Debian/Ubuntu）或 /etc/my.cnf：

[mysqld]
bind-address = 127.0.0.1

sudo systemctl restart mariadb

博客与数据库同机部署时，数据库完全没必要对公网开放；公网 3306 暴露是数据库被爆破拖库的第一大原因。

6.3 服务端默认字符集

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

6.4 慢查询与错误日志

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

# 查看错误日志
sudo tail -f /var/log/mysql/error.log

7. 日常维护常用命令

7.1 查看表、表结构与索引

SHOW TABLES;
DESC typecho_posts;
SHOW CREATE TABLE typecho_posts;
SHOW INDEX FROM typecho_posts;

7.2 表维护（优化/检查/修复）

OPTIMIZE TABLE typecho_posts;   -- 整理碎片，文章多、删改频繁时定期执行
ANALYZE TABLE typecho_posts;    -- 更新统计信息，优化查询计划
CHECK TABLE typecho_posts;      -- 检查表完整性
REPAIR TABLE typecho_posts;     -- 修复损坏表

7.3 空间、进程与连接数

-- 各数据库占用空间
SELECT table_schema AS '数据库', ROUND(SUM(data_length+index_length)/1024/1024, 2) AS '大小(MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- 查看当前正在执行的进程
SHOW PROCESSLIST;

-- 最大连接数与当前连接数
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

7.4 服务状态检查

sudo systemctl status mariadb
mysqladmin -u root ping
mysqladmin -u root status

8. Typecho 数据库迁移实战

以「换服务器 / 换数据库」为例，五步搞定：

# ① 旧机备份
mysqldump -u root -p --single-transaction --default-character-set=utf8mb4 typecho | gzip > typecho.sql.gz
scp typecho.sql.gz 新服务器:/backup/

-- ② 新机建库建用户
CREATE DATABASE typecho DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'blog'@'localhost' IDENTIFIED BY '新密码';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON typecho.* TO 'blog'@'localhost';
FLUSH PRIVILEGES;

# ③ 导入
gunzip < /backup/typecho.sql.gz | mysql -u root -p typecho

// ④ 修改 Typecho 配置文件 usr/config.inc.php（注意是 __TYPECHO_DB_* 常量）
define('__TYPECHO_DB_HOST__', 'localhost');
define('__TYPECHO_DB_PORT__', '3306');
define('__TYPECHO_DB_NAME__', 'typecho');
define('__TYPECHO_DB_USER__', 'blog');
define('__TYPECHO_DB_PASSWORD__', '新密码');
define('__TYPECHO_DB_CHARSET__', 'utf8mb4');
define('__TYPECHO_DB_PREFIX__', 'typecho_');

-- ⑤ 核对表前缀
SHOW TABLES;

表前缀与 config 不一致时，Typecho 会报 "Table 'typecho.typecho_options' doesn't exist" 类错误，对照 SHOW TABLES 结果改前缀即可。

9. 常见问题排查

10. 命令速查表（背不下就查这张）

结语：三条保命原则

1. 字符集一律 utf8mb4，从建库那一刻就定死；
2. 博客账号只给单库最小权限，数据库永不直接暴露公网 3306；
3. 备份是底线：定时全量 + 定期手动恢复演练，没验证过能恢复的备份等于没有备份。