MySQL 数据库运维手册

目录

  1. MySQL 安装与配置
  2. 用户与权限管理
  3. 数据库操作
  4. 备份与恢复
  5. 性能优化
  6. 主从复制
  7. 监控与诊断
  8. 安全加固
  9. 日常维护

1. MySQL 安装与配置

1.1 CentOS/RHEL 安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 下载 MySQL YUM 仓库
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum localinstall -y mysql80-community-release-el7-3.noarch.rpm

# 安装 MySQL
yum install -y mysql-community-server

# 启动 MySQL
systemctl start mysqld
systemctl enable mysqld

# 查看临时密码
grep 'temporary password' /var/log/mysqld.log

# 安全初始化
mysql_secure_installation

1.2 Ubuntu/Debian 安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 下载 MySQL APT 仓库
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
dpkg -i mysql-apt-config_0.8.22-1_all.deb
apt-get update

# 安装 MySQL
apt-get install -y mysql-server

# 启动 MySQL
systemctl start mysql
systemctl enable mysql

# 安全初始化
mysql_secure_installation

1.3 配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

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

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

# 连接配置
max_connections = 500
max_connect_errors = 10000
wait_timeout = 28800
interactive_timeout = 28800

# InnoDB 配置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 日志配置
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 二进制日志
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

[client]
default-character-set = utf8mb4

2. 用户与权限管理

2.1 用户管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password123';

-- 修改密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('newpassword');

-- 删除用户
DROP USER 'app_user'@'localhost';

-- 查看用户
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'app_user'@'localhost';

2.2 权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 授予权限
GRANT ALL PRIVILEGES ON database.* TO 'app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON database.table TO 'app_user'@'localhost';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';

-- 撤销权限
REVOKE DELETE ON database.* FROM 'app_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';

2.3 角色管理（MySQL 8.0+）

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建角色
CREATE ROLE 'read_only', 'read_write';

-- 授予角色权限
GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'read_write';

-- 授予用户角色
GRANT 'read_write' TO 'app_user'@'localhost';

-- 设置默认角色
SET DEFAULT ROLE 'read_write' TO 'app_user'@'localhost';

3. 数据库操作

3.1 数据库管理

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;
SHOW CREATE DATABASE mydb;

-- 选择数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

3.2 表管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(200);
ALTER TABLE users ADD INDEX idx_phone (phone);

-- 删除表
DROP TABLE users;
TRUNCATE TABLE users;

3.3 数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 插入数据
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com'), ('bob', 'bob@example.com');

-- 查询数据
SELECT * FROM users;
SELECT id, username FROM users WHERE id > 10;
SELECT COUNT(*) FROM users;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

4. 备份与恢复

4.1 mysqldump 备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

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

# 备份特定表
mysqldump -u root -p mydb users orders > tables_backup.sql

# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 带时间戳备份
mysqldump -u root -p mydb > mydb_$(date +%Y%m%d_%H%M%S).sql

# 仅备份结构
mysqldump -u root -p --no-data mydb > mydb_structure.sql

# 仅备份数据
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

4.2 恢复备份

1
2
3
4
5
6
7
8
# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复所有数据库
mysql -u root -p < all_databases.sql

4.3 物理备份

1
2
3
4
5
6
7
8
# 停止 MySQL
systemctl stop mysqld

# 复制数据目录
cp -r /var/lib/mysql /backup/mysql_backup_$(date +%Y%m%d)

# 启动 MySQL
systemctl start mysqld

4.4 增量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
# 启用二进制日志
# 在 my.cnf 中配置
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW

# 刷新二进制日志
mysql -u root -p -e "FLUSH LOGS;"

# 查看二进制日志
SHOW BINARY LOGS;

# 使用二进制日志恢复
mysqlbinlog mysql-bin.000001 | mysql -u root -p

5. 性能优化

5.1 索引优化

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age);

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_email ON users;

5.2 查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 避免 SELECT *
SELECT id, username, email FROM users;

-- 使用 LIMIT
SELECT * FROM users LIMIT 100;

-- 避免在索引列上使用函数
-- 不推荐
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
-- 推荐
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 使用 JOIN 代替子查询
SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id;

5.3 配置优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 关键性能参数

# 缓冲池（建议设置为物理内存的 50-70%）
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8

# 日志配置
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M

# 连接配置
max_connections = 1000
thread_cache_size = 50

# 表缓存
table_open_cache = 4000
table_definition_cache = 2000

# 排序和临时表
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 256M
max_heap_table_size = 256M

5.4 慢查询分析

1
2
3
4
5
6
7
8
9
-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询统计
SELECT * FROM mysql.slow_log;

-- 使用 pt-query-digest 分析
pt-query-digest /var/log/mysql/slow.log > slow_query_analysis.txt

6. 主从复制

6.1 配置主服务器

1
2
3
4
5
6
# my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_do_db = mydb
1
2
3
4
5
6
7
-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看主服务器状态
SHOW MASTER STATUS;
-- 记录 File 和 Position

6.2 配置从服务器

1
2
3
4
5
# my.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
read_only = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 配置主从关系
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G
-- 检查 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes

6.3 主从切换

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 停止主服务器写入
FLUSH TABLES WITH READ LOCK;

-- 等待从服务器同步
SHOW SLAVE STATUS\G
-- 检查 Seconds_Behind_Master 为 0

-- 提升从服务器为主
STOP SLAVE;
RESET SLAVE ALL;

-- 在新主服务器上创建复制用户
-- 配置其他从服务器指向新主

7. 监控与诊断

7.1 状态监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看服务器状态
SHOW STATUS;
SHOW GLOBAL STATUS;

-- 查看变量
SHOW VARIABLES;
SHOW GLOBAL VARIABLES;

-- 查看进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看引擎状态
SHOW ENGINE INNODB STATUS\G

7.2 性能监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看缓存命中率
SHOW STATUS LIKE 'Qcache_hits';
SHOW STATUS LIKE 'Qcache_inserts';

-- 查看表锁
SHOW STATUS LIKE 'Table_locks_waited';
SHOW STATUS LIKE 'Table_locks_immediate';

-- 查看 InnoDB 状态
SHOW STATUS LIKE 'Innodb_row_lock%';

7.3 监控工具

1
2
3
4
5
6
7
8
9
# MySQL Enterprise Monitor
# Percona Monitoring and Management (PMM)
pmmd-server start

# Prometheus + mysqld_exporter
mysqld_exporter --config.my-cnf=/etc/mysql/debian.cnf

# Grafana 仪表盘
# 导入 MySQL 官方仪表盘 ID: 7362

8. 安全加固

8.1 基础安全

1
2
3
4
5
6
7
8
9
10
11
12
-- 删除匿名用户
DELETE FROM mysql.user WHERE User='';

-- 删除测试数据库
DROP DATABASE IF EXISTS test;

-- 修改 root 密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- 限制 root 远程登录
UPDATE mysql.user SET Host='localhost' WHERE User='root';
FLUSH PRIVILEGES;

8.2 网络安全

1
2
3
4
5
6
7
8
9
10
# my.cnf
[mysqld]
# 绑定到特定 IP
bind-address = 127.0.0.1

# 禁用本地文件导入
local_infile = 0

# 跳过符号链接
symbolic-links = 0
1
2
3
4
5
6
# 防火墙配置
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

# 或使用 iptables
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT

8.3 审计日志

1
2
3
4
5
# 启用审计插件（MySQL Enterprise）
[mysqld]
plugin_load_add=audit_log
audit_log_policy=ALL
audit_log_format=JSON

9. 日常维护

9.1 定期任务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/bash
# daily_maintenance.sh

# 备份数据库
mysqldump -u root -p'password' --all-databases | gzip > /backup/all_$(date +%Y%m%d).sql.gz

# 清理旧备份
find /backup -name "*.sql.gz" -mtime +7 -delete

# 优化表
mysql -u root -p'password' -e "OPTIMIZE TABLE mydb.users; OPTIMIZE TABLE mydb.orders;"

# 清理二进制日志
mysql -u root -p'password' -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

# 分析表
mysqlcheck -u root -p'password' --analyze --all-databases

9.2 表维护

1
2
3
4
5
6
7
8
9
10
11
-- 优化表
OPTIMIZE TABLE users;

-- 分析表
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

9.3 日志轮转

1
2
3
4
5
6
7
8
9
10
11
12
13
# /etc/logrotate.d/mysql
/var/log/mysql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        mysqladmin flush-logs
    endscript
}

文档版本: 1.0
最后更新: 2026-02-27
适用版本: MySQL 8.0+