MySQL数据库管理实战指南：权限控制、日志分析与备份恢复

MySQL通过完善的权限系统来精细控制不同用户对数据库资源的访问，这是保障数据安全的第一道防线。

用户管理

通过CREATE USER、DROP USER等语句可以管理数据库用户。创建用户时需要指定用户名、允许连接的主机以及密码，遵循最小权限原则创建专用账户是安全最佳实践。

-- 创建用户
-- username: 用户名
-- host: 允许连接的主机
-- password: 用户密码
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 删除用户
DROP USER 'username'@'host';

-- 修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 刷新权限，使权限变更生效
FLUSH PRIVILEGES;

实际应用示例：

-- 创建只能从本地连接的开发者用户
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password';
-- 创建可以从任何主机连接的管理员用户（生产环境慎用‘%’）
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin_password';

权限分配

使用GRANT和REVOKE语句可以为用户授予或撤销特定的数据库操作权限，这是构建安全访问策略的核心。一个健壮的数据库/中间件权限体系对于任何系统都至关重要。

-- 授予权限
-- privileges: 权限类型
-- database.table: 数据库和表
-- username@host: 用户和主机
GRANT privileges ON database.table TO 'username'@'host';

-- 撤销权限
REVOKE privileges ON database.table FROM 'username'@'host';

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';

常用的权限类型包括：

• ALL PRIVILEGES: 所有权限
• SELECT: 查询数据
• INSERT: 插入数据
• UPDATE: 更新数据
• DELETE: 删除数据
• CREATE: 创建数据库/表
• DROP: 删除数据库/表
• ALTER: 修改表结构
• 其他如INDEX（索引）、CREATE VIEW（创建视图）、TRIGGER（触发器）等。

权限分配示例：

-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON company_db.* TO 'developer'@'localhost';
-- 授予用户对特定表的查询和插入权限
GRANT SELECT, INSERT ON company_db.employees TO 'developer'@'localhost';
-- 授予用户对所有数据库的只读查询权限
GRANT SELECT ON *.* TO 'readonly'@'localhost';

日志管理

MySQL提供了多种日志，用于监控数据库运行状态、排查问题和实现数据复制，是运维人员的重要工具。

错误日志(Error Log)

记录MySQL服务启动、运行和停止过程中产生的错误、警告和提示信息，是故障排查的首要查看点。

-- 查看错误日志相关配置
SHOW VARIABLES LIKE 'log_error';

慢查询日志(Slow Query Log)

记录执行时间超过long_query_time阈值（默认10秒）的SQL语句，用于分析和优化数据库性能。

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询时间阈值为2秒
SET GLOBAL long_query_time = 2;

二进制日志(Binary Log)

以二进制格式记录所有对数据进行更改的语句（DDL和DML），是实现主从复制和数据恢复的基石。

-- 检查二进制日志是否启用
SHOW VARIABLES LIKE 'log_bin';
-- 查看当前所有的二进制日志文件
SHOW MASTER LOGS;

查询日志(General Query Log)

记录所有连接到MySQL服务器的客户端执行的语句，在审计或深度调试时启用，由于会产生大量日志，一般不建议在生产环境长期开启。

SET GLOBAL general_log = 'ON';

数据备份与恢复

定期备份是防止数据丢失、应对灾难情况的最后保障，任何严谨的运维/DevOps流程都必须包含备份策略。

逻辑备份

使用mysqldump工具将数据库结构及数据导出为SQL语句文件。这种方式灵活、可读性强，适合数据量不大或需要跨版本迁移的场景。

# 备份整个数据库
mysqldump -u username -p database_name > backup.sql
# 备份所有数据库
mysqldump -u username -p --all-databases > all_backup.sql
# 备份并压缩
mysqldump -u username -p database_name | gzip > backup.sql.gz

物理备份

直接复制MySQL的数据目录文件（如/var/lib/mysql）。这种方式速度快，但需要停止服务或使用特殊工具（如Percona XtraBackup）来保证备份时数据的一致性。

# 停止服务后复制（确保一致性）
sudo systemctl stop mysql
cp -r /var/lib/mysql /backup/mysql_backup
sudo systemctl start mysql

数据恢复

根据备份类型选择相应的恢复方法。

# 恢复逻辑备份
mysql -u username -p database_name < backup.sql
# 恢复压缩的逻辑备份
gunzip < backup.sql.gz | mysql -u username -p database_name

备份策略

1. 完整备份：定期（如每周）进行全量备份。
2. 增量备份：备份自上次备份（无论是全量还是增量）以来发生变化的数据。
3. 差异备份：备份自上次完整备份以来发生变化的数据。

一个结合了压缩和定期清理的自动化备份脚本示例如下：

#!/bin/bash
# MySQL备份脚本
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="company_db"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR
# 执行备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# 压缩
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql
# 清理7天前的旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

主从复制

MySQL主从复制通过将主库（Master）的数据变更同步到一个或多个从库（Slave），来实现数据冗余、读写分离和负载均衡，是构建高可用与高性能云原生/IaaS架构的常见手段。

主服务器(Master)配置 (my.cnf):

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=company_db  # 可选项，指定同步的数据库

从服务器(Slave)配置 (my.cnf):

[mysqld]
server-id=2  # 必须唯一

配置与启动复制:

1. 在主库创建用于复制的账户并授权。
2. 记录主库当前的二进制日志状态。
3. 在从库配置主库信息并启动复制线程。

-- 在主服务器执行
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SHOW MASTER STATUS; -- 记录 File 和 Position

-- 在从服务器执行
CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001', -- 替换为SHOW MASTER STATUS的结果
MASTER_LOG_POS=107; -- 替换为SHOW MASTER STATUS的结果

START SLAVE;
-- 检查复制状态，确保 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes
SHOW SLAVE STATUS\G