MySQL管理

MySQL管理是确保数据库系统稳定、安全、高效运行的核心工作，涵盖用户权限管理、数据备份与恢复、性能监控与优化、服务器配置等多个方面。有效的MySQL管理能够保障数据完整性，提高系统可用性，并应对业务增长带来的挑战。

一、用户与权限管理

1. 用户管理

（1）创建用户

-- 基本语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 示例：允许用户在本地登录
CREATE USER 'john'@'localhost' IDENTIFIED BY 'John@123456';

-- 示例：允许用户从任何主机登录（%表示所有主机）
CREATE USER 'mary'@'%' IDENTIFIED BY 'Mary@123456';

-- 示例：限制用户只能从特定IP登录
CREATE USER 'bob'@'192.168.1.100' IDENTIFIED BY 'Bob@123456';

-- MySQL 8.0+推荐使用 caching_sha2_password 认证插件
CREATE USER 'alice'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Alice@123456';

（2）修改用户

-- 修改用户名
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- 修改密码
ALTER USER 'john'@'localhost' IDENTIFIED BY 'NewPassword@123';

-- 锁定/解锁用户
ALTER USER 'john'@'localhost' ACCOUNT LOCK;
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;

（3）删除用户

DROP USER '用户名'@'主机名';

-- 示例
DROP USER 'john'@'localhost';
DROP USER 'mary'@'%';

（4）查看用户

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 查看用户详细信息
DESCRIBE mysql.user;

2. 权限管理

（1）权限类型

MySQL权限可分为以下几类：

① 数据库级别权限：

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX等

② 表级别权限：

针对特定表的SELECT, INSERT, UPDATE, DELETE等

③ 列级别权限：

针对表中特定列的权限（如只允许更新某个列）

④ 管理权限：

GRANT OPTION（授予权限）, SUPER（超级权限）, PROCESS（查看进程）等

（2）授予权限

-- 基本语法
GRANT 权限列表 ON 数据库.表 TO '用户名'@'主机名';

-- 示例1：授予对所有数据库所有表的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 示例2：授予对特定数据库的所有权限
GRANT ALL PRIVILEGES ON sales.* TO 'sales_user'@'%';

-- 示例3：授予对特定表的查询和插入权限
GRANT SELECT, INSERT ON sales.orders TO 'order_clerk'@'192.168.1.%';

-- 示例4：授予对特定表特定列的更新权限
GRANT UPDATE(price) ON products.items TO 'price_editor'@'localhost';

-- 示例5：授予创建视图的权限
GRANT CREATE VIEW ON sales.* TO 'report_user'@'%';

（3）回收权限

-- 基本语法
REVOKE 权限列表 ON 数据库.表 FROM '用户名'@'主机名';

-- 示例1：回收所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';

-- 示例2：回收特定权限
REVOKE DELETE ON sales.orders FROM 'order_clerk'@'192.168.1.%';

-- 回收权限后刷新
FLUSH PRIVILEGES;

（4）查看权限

-- 查看用户拥有的权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 示例
SHOW GRANTS FOR 'sales_user'@'%';
SHOW GRANTS FOR CURRENT_USER;

二、数据库备份与恢复

1. 备份类型

① 物理备份：直接复制数据库文件，如数据文件、日志文件等

② 逻辑备份：将数据导出为SQL语句或其他格式的文件

③ 全量备份：备份所有数据

④ 增量备份：仅备份上次备份后变化的数据

⑤ 差异备份：备份上次全量备份后变化的数据

2. 常用备份工具

（1）mysqldump（逻辑备份）

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

-- 备份特定数据库
mysqldump -u root -p sales > sales_backup.sql

-- 备份特定数据库的多个表
mysqldump -u root -p sales orders customers > sales_tables_backup.sql

-- 备份时包含存储过程、函数和事件
mysqldump -u root -p --routines --events sales > sales_full_backup.sql

-- 对InnoDB表进行一致性备份（不锁表）
mysqldump -u root -p --single-transaction sales > sales_consistent_backup.sql

-- 压缩备份
mysqldump -u root -p sales | gzip > sales_backup.sql.gz

（2）mysqlpump（MySQL 5.7+，mysqldump的改进版）

-- 并行备份多个数据库
mysqlpump -u root -p --databases sales inventory --parallel-schemas=2 > multi_db_backup.sql

-- 排除特定表
mysqlpump -u root -p sales --exclude-tables=sales.logs > sales_backup.sql

（3）物理备份工具

① Percona XtraBackup（开源，支持热备份）

-- 创建全量备份
xtrabackup --user=root --password=123456 --backup --target-dir=/backup/full

-- 准备备份（用于恢复）
xtrabackup --prepare --target-dir=/backup/full

② MySQL Enterprise Backup（商业工具，Oracle官方提供）

3. 数据恢复

（1）使用SQL备份文件恢复

-- 恢复整个数据库
mysql -u root -p sales < sales_backup.sql

-- 从压缩文件恢复
gunzip < sales_backup.sql.gz | mysql -u root -p sales

-- 恢复到新数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS sales_new;"
mysql -u root -p sales_new < sales_backup.sql

（2）使用物理备份恢复

-- 停止MySQL服务
systemctl stop mysql

-- 清空数据目录
rm -rf /var/lib/mysql/*

-- 恢复备份
xtrabackup --copy-back --target-dir=/backup/full

-- 调整文件权限
chown -R mysql:mysql /var/lib/mysql

-- 启动MySQL服务
systemctl start mysql

（3）时间点恢复（基于二进制日志）

-- 1. 查看二进制日志文件
SHOW BINARY LOGS;

-- 2. 导出特定时间段的二进制日志
mysqlbinlog --start-datetime="2023-10-01 08:00:00" --stop-datetime="2023-10-01 09:00:00" /var/log/mysql/binlog.000001 > recovery.sql

-- 3. 恢复到数据库
mysql -u root -p sales < recovery.sql

三、服务器配置与管理

1. 配置文件

MySQL的主要配置文件通常为my.cnf或my.ini，不同系统位置可能不同：

- Linux：/etc/my.cnf, /etc/mysql/my.cnf

- Windows：C:\ProgramData\MySQL\MySQL Server X.X\my.ini

- macOS：/usr/local/mysql/my.cnf 或 ~/.my.cnf

2. 核心配置参数

[mysqld]
# 基本设置
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
user = mysql

# 内存设置
max_connections = 1000
innodb_buffer_pool_size = 8G
key_buffer_size = 256M

# 日志设置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_bin = /var/log/mysql/binlog
expire_logs_days = 7

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 安全设置
skip_name_resolve = 1
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

3. 服务管理

-- 启动MySQL服务
systemctl start mysql  # systemd系统
service mysql start    # SysV系统

-- 停止MySQL服务
systemctl stop mysql
service mysql stop

-- 重启MySQL服务
systemctl restart mysql
service mysql restart

-- 查看服务状态
systemctl status mysql
service mysql status

-- 设置开机自启动
systemctl enable mysql
chkconfig mysql on     # SysV系统

4. 版本升级与迁移

① 升级注意事项：

- 先备份数据

- 查看官方升级指南，注意版本间兼容性

- 小版本升级（如5.7.20→5.7.30）通常直接替换二进制文件

- 大版本升级（如5.7→8.0）需运行mysql_upgrade

② 升级步骤示例：

-- 1. 备份数据
mysqldump --all-databases --routines --events > full_backup.sql

-- 2. 停止旧版本服务
systemctl stop mysql

-- 3. 安装新版本MySQL

-- 4. 运行升级检查
mysql_upgrade -u root -p

-- 5. 启动新版本服务
systemctl start mysql

四、性能监控与优化

1. 状态查看

-- 查看服务器状态变量
SHOW GLOBAL STATUS;

-- 查看特定状态变量
SHOW GLOBAL STATUS LIKE 'Threads%';  -- 线程相关
SHOW GLOBAL STATUS LIKE 'Connections';  -- 连接数
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';  -- InnoDB缓冲池

-- 查看系统变量
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 查看当前进程
SHOW PROCESSLIST;
-- 或查询information_schema
SELECT * FROM information_schema.processlist WHERE STATE != 'Sleep';

2. 慢查询日志

慢查询日志记录执行时间超过阈值的SQL语句，是性能优化的重要工具。

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 2;

-- 设置慢查询日志文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;

3. 常用监控工具

① mysqladmin：命令行管理工具

-- 查看服务器状态
mysqladmin -u root -p status

-- 查看变量和状态
mysqladmin -u root -p variables
mysqladmin -u root -p extended-status

-- 刷新日志、权限等
mysqladmin -u root -p flush-logs
mysqladmin -u root -p flush-privileges

② MySQL Workbench：官方GUI工具，提供性能监控仪表盘

③ 第三方工具：

- Percona Monitoring and Management (PMM)

- Navicat Monitor

- Zabbix + MySQL模板

4. 性能优化方向

① 硬件优化：

- 增加内存，扩大InnoDB缓冲池

- 使用SSD提升IO性能

- 多核CPU提高并发处理能力

② 配置优化：

- 调整连接数（max_connections）

- 优化缓存设置（query_cache_size已在8.0移除）

- 调整InnoDB相关参数

③ 数据库设计优化：

- 合理设计表结构，选择合适的数据类型

- 优化索引设计

- 分库分表处理大数据量

④ SQL语句优化：

- 优化慢查询

- 避免全表扫描

- 合理使用JOIN和子查询

五、安全管理

1. 基本安全措施

① 初始安全设置：

-- MySQL 5.7+提供安全脚本
mysql_secure_installation

-- 主要操作：
-- 1. 设置root密码
-- 2. 移除匿名用户
-- 3. 禁止root远程登录
-- 4. 移除test数据库

② 密码策略：

-- 设置密码复杂度
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 10;

-- 定期更换密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword@2023';

③ 限制网络访问：

-- 绑定特定IP
bind-address = 192.168.1.100  # 在my.cnf中设置

-- 使用防火墙限制3306端口访问
ufw allow from 192.168.1.0/24 to any port 3306  # UFW防火墙
firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept'  # firewalld

2. 数据加密

① 传输加密（SSL/TLS）：

-- 配置MySQL使用SSL
[mysqld]
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

-- 创建要求SSL连接的用户
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'SecurePass@123' REQUIRE SSL;

② 数据加密：

MySQL 8.0支持数据-at-rest加密，可加密表空间和 redo/undo 日志。

3. 审计与日志

① 启用审计日志（需安装审计插件）：

-- 加载审计插件
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 配置审计日志
[mysqld]
audit_log = ON
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON

② 定期检查日志：

- 错误日志：记录启动、运行和关闭过程中的错误

- 通用查询日志：记录所有SQL语句（通常不开启，影响性能）

- 二进制日志：记录数据修改，用于复制和恢复

六、高可用与扩展性

1. 主从复制

主从复制（Master-Slave Replication）实现数据从主库复制到从库，提供读写分离和数据备份。

① 主库配置：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/binlog
binlog_do_db = sales  # 只复制特定数据库
binlog_ignore_db = mysql  # 忽略复制的数据库

② 从库配置：

[mysqld]
server-id = 2
relay_log = /var/log/mysql/relaylog

③ 配置复制：

-- 在主库创建复制用户
CREATE USER 'repl_user'@'从库IP' IDENTIFIED BY 'ReplPass@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'从库IP';

-- 在从库配置主库信息
CHANGE MASTER TO
MASTER_HOST = '主库IP',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'ReplPass@123',
MASTER_LOG_FILE = 'binlog.000001',
MASTER_LOG_POS = 154;

-- 启动从库复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

2. 读写分离

通过中间件实现读写分离，写操作走主库，读操作走从库，提高系统吞吐量。

常用中间件：

- MySQL Router（官方）

- ProxySQL

- MyCat

3. 集群解决方案

① MySQL InnoDB Cluster（官方集群方案）：

由MySQL Server、MySQL Shell和MySQL Router组成，提供高可用和自动故障转移。

② Percona XtraDB Cluster：

基于Galera Cluster技术，实现多主架构，同步复制，无单点故障。

③ 其他方案：

- MariaDB Galera Cluster

- 基于MGR（MySQL Group Replication）的集群

七、MySQL管理最佳实践

1. 定期备份：制定合理的备份策略，包括全量备份和增量备份，并定期测试恢复流程

2. 最小权限原则：为用户分配最小必要权限，避免过度授权

3. 监控告警：建立完善的监控体系，及时发现和解决问题

4. 定期维护：包括索引优化、碎片整理、统计信息更新等

5. 版本管理：保持MySQL版本在稳定的小版本，及时修复安全漏洞

6. 文档化：记录数据库架构、配置变更、操作流程等重要信息

7. 灾难恢复计划：制定详细的灾难恢复流程，定期演练

8. 安全审计：定期检查用户权限、日志文件，确保系统安全

9. 容量规划：根据业务增长预测，提前规划存储和性能需求

10. 变更管理：数据库结构变更需经过测试和审批，避免直接在生产环境操作