MySQL - 管理

现代 MySQL 管理基础

MySQL 管理涉及管理数据库服务器的健康状况、安全性和性能。这已从手动服务器管理演变为使用现代命令行工具、容器化和云服务。本教程涵盖了现代 MySQL 设置的基本管理任务。

服务器生命周期管理

您管理 MySQL 服务器进程的方式取决于您的操作系统和环境。

Linux（使用 systemd）

在大多数现代 Linux 发行版上，您将使用 systemctl 管理 MySQL 服务（服务名称可能是 mysql 或 mysqld）。

# 启动 MySQL 服务器
sudo systemctl start mysqld

# 停止 MySQL 服务器
sudo systemctl stop mysqld

# 重启服务器
sudo systemctl restart mysqld

# 检查服务器状态（对调试非常有用）
sudo systemctl status mysqld

macOS（使用 Homebrew）

如果您通过 Homebrew 安装了 MySQL，可以使用 brew services。

# 启动 MySQL 服务器并在登录时启动
brew services start mysql

# 停止 MySQL 服务器
brew services stop mysql

# 重启服务器
brew services restart mysql

Docker（现代、便携的方法）

使用 Docker 为开发和部署提供了一致、隔离的环境。您通过 Docker 容器生命周期管理服务器。

# 在后台运行一个新的 MySQL 容器
docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306 mysql:latest

# 停止容器
docker stop my-mysql

# 再次启动容器
docker start my-mysql

# 查看日志以进行调试
docker logs my-mysql

安全的用户和访问控制管理

适当的用户管理对安全性至关重要。指导原则是最小权限原则：只授予用户他们绝对需要的权限。

创建用户和授予权限

始终为您的应用程序创建专用用户，而不是使用 root 用户。该过程分两步：创建用户，然后授予权限。

-- 步骤 1：创建一个新用户。'app_user' 只能从 localhost 连接。
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-very-strong-password';

-- 步骤 2：在特定数据库上授予特定权限。
-- 该用户只能对 'my_app_db' 数据库中的表执行 CRUD 操作。
GRANT SELECT, INSERT, UPDATE, DELETE ON my_app_db.* TO 'app_user'@'localhost';

-- 重新加载权限是个好习惯，尽管
-- GRANT/CREATE USER 语句通常会自动处理。
FLUSH PRIVILEGES;

更新密码

更改用户密码的现代方法是使用 ALTER USER。

-- 以具有足够权限的用户身份连接（例如 root）
-- 然后执行此命令更改 root 用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'a-new-secure-password';

使用角色（MySQL 8.0+）

角色简化了权限管理。您可以将权限分组到一个角色中，然后将该角色分配给多个用户。

-- 创建只读和读写访问的角色
CREATE ROLE 'app_readonly', 'app_readwrite';

-- 授予角色权限
GRANT SELECT ON my_app_db.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON my_app_db.* TO 'app_readwrite';

-- 创建用户并授予只读角色
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'secure_password';
GRANT 'app_readonly' TO 'analyst'@'localhost';

-- 为用户的会话激活角色
SET DEFAULT ROLE 'app_readonly' TO 'analyst'@'localhost';

基本管理命令

这些命令是您日常检查数据库状态的工具。

• USE database_name; — 选择要使用的数据库。
• SHOW DATABASES; — 列出您有权查看的所有数据库。
• SHOW TABLES; — 列出当前选中数据库中的所有表。
• DESCRIBE table_name; — 显示表的结构（列、类型等）。
• SHOW PROCESSLIST; — 显示所有当前正在运行的查询和连接。对于诊断性能问题至关重要。
• SHOW VARIABLES LIKE 'variable_name%'; — 检查服务器配置变量的值。
• SHOW ENGINE INNODB STATUS; — 提供有关 InnoDB 存储引擎的详细信息，包括死锁和事务历史记录。

理解配置文件

MySQL 的行为由配置文件控制，通常命名为 my.cnf。其位置因操作系统而异。您可以通过运行 mysql --help | grep 'Default options' 来查找其位置。

一个最小化的现代配置文件可能如下所示：

[mysqld]
# 常规设置
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 对安全性和一致性很重要
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# InnoDB 性能调优
innodb_buffer_pool_size = 1G # 根据服务器的 RAM 进行调整（例如，可用 RAM 的 70-80%）
innodb_log_file_size = 256M

# 日志记录
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

在进行更改之前，请务必备份您的 my.cnf 文件，并且一次只更改一个设置以观察其效果。更改生效需要重启 MySQL 服务器。