MySQL 管理指南 - 服务器管理、用户权限与配置

MySQL数据库管理是确保数据库系统稳定运行的关键任务。本章将详细介绍MySQL服务器的启动与关闭、用户权限管理、配置文件设置以及常用管理命令。

启动及关闭 MySQL 服务器

检查MySQL服务器状态

首先，我们需要通过以下命令来检查MySQL服务器是否启动：

ps -ef | grep mysqld

如果MySQL已经启动，以上命令将输出mysql进程列表。

启动MySQL服务器

如果MySQL未启动，你可以使用以下命令来启动mysql服务器：

cd /usr/bin
./safe_mysqld &

使用systemctl管理MySQL服务（现代Linux系统）

# 启动MySQL服务
sudo systemctl start mysqld

# 停止MySQL服务
sudo systemctl stop mysqld

# 重启MySQL服务
sudo systemctl restart mysqld

# 查看MySQL服务状态
sudo systemctl status mysqld

# 设置MySQL开机自启
sudo systemctl enable mysqld

关闭MySQL服务器

如果你想关闭目前运行的MySQL服务器，可以执行以下命令：

cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

注意：在生产环境中，建议使用mysqladmin shutdown命令来安全关闭MySQL服务器，这样可以确保所有数据都正确写入磁盘。

MySQL 用户与权限管理

直接操作user表添加用户（不推荐）

早期版本的MySQL可以通过直接操作mysql数据库中的user表来添加用户：

-- 登录MySQL
mysql -u root -p
Enter password:*******

-- 切换到mysql数据库
USE mysql;

-- 插入新用户记录
INSERT INTO user
    (host, user, password, select_priv, insert_priv, update_priv)
    VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证用户创建
SELECT host, user, password FROM user WHERE user = 'guest';

注意：直接操作user表的方式在现代MySQL版本中已不推荐使用，因为这种方式容易出错且不安全。建议使用CREATE USER和GRANT语句。

使用CREATE USER和GRANT语句（推荐）

-- 创建新用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 授予权限
GRANT privilege_type ON database_name.table_name TO 'username'@'host';

-- 示例：创建用户zara并授予对TUTORIALS数据库的所有权限
CREATE USER 'zara'@'localhost' IDENTIFIED BY 'zara123';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
    ON TUTORIALS.*
    TO 'zara'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

MySQL用户权限列表

MySQL提供了多种权限来控制用户对数据库的访问：

SELECT_priv - 查询数据
INSERT_priv - 插入数据
UPDATE_priv - 更新数据
DELETE_priv - 删除数据
CREATE_priv - 创建数据库/表
DROP_priv - 删除数据库/表
RELOAD_priv - 重新加载权限

SHUTDOWN_priv - 关闭服务器
PROCESS_priv - 查看进程
FILE_priv - 文件操作
GRANT_priv - 授权权限
REFERENCES_priv - 外键权限
INDEX_priv - 创建/删除索引
ALTER_priv - 修改表结构

查看和撤销权限

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

-- 撤销用户权限
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

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

MySQL 配置文件详解

主要配置文件

MySQL的主要配置文件通常位于：

Linux: /etc/my.cnf 或 /etc/mysql/my.cnf
Windows: C:\Program Files\MySQL\MySQL Server X.X\my.ini

配置文件示例

[mysqld]
# 基础设置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# 网络设置
port=3306
bind-address=127.0.0.1

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

# 日志设置
log-error=/var/log/mysqld.log
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow.log
long_query_time=2

# 缓冲池设置（根据内存调整）
innodb_buffer_pool_size=1G

# 连接设置
max_connections=151
max_connect_errors=100000

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8mb4

重要配置参数说明

innodb_buffer_pool_size: InnoDB缓冲池大小，建议设置为系统内存的50-70%
max_connections: 最大连接数，根据应用需求调整
character-set-server: 服务器默认字符集，推荐使用utf8mb4
slow_query_log: 慢查询日志，用于性能优化

常用 MySQL 管理命令

数据库操作命令

-- 选择数据库
USE database_name;

-- 显示所有数据库
SHOW DATABASES;

-- 显示当前数据库的所有表
SHOW TABLES;

-- 显示表结构
SHOW COLUMNS FROM table_name;
DESCRIBE table_name;

-- 显示表索引信息
SHOW INDEX FROM table_name;

-- 显示表状态信息
SHOW TABLE STATUS LIKE 'table_name'\G

系统状态和性能监控

-- 显示服务器状态
SHOW STATUS;

-- 显示系统变量
SHOW VARIABLES;

-- 显示当前进程
SHOW PROCESSLIST;

-- 显示引擎状态
SHOW ENGINE INNODB STATUS;

-- 显示主从复制状态
SHOW SLAVE STATUS\G

备份与恢复

# 备份数据库
mysqldump -u username -p database_name > backup_file.sql

# 恢复数据库
mysql -u username -p database_name < backup_file.sql

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

MySQL 安全管理最佳实践

账户安全

为root账户设置强密码并定期更换
避免在应用代码中硬编码数据库密码
为每个应用创建独立的数据库用户
遵循最小权限原则，只授予必要的权限

网络安全

将MySQL绑定到内网IP，避免公网暴露
使用防火墙限制访问来源IP
考虑使用SSL加密连接

定期维护

定期备份重要数据
监控数据库性能和资源使用情况
定期更新MySQL到安全版本
清理不再使用的用户和权限

提示： 使用mysql_secure_installation命令可以帮助你提高新安装MySQL服务器的安全性。

MYSQL教程

MySQL 管理与维护