系统运维人员MySQL数据库完全掌握指南：从基础指令到生产实战#

1
-- 查看当前隔离级别
2
SELECT @@transaction_isolation;
3
SELECT @@global.transaction_isolation;
4

5
-- 设置会话级别隔离级别
6
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
7

8
-- 设置全局隔离级别
9
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1
-- 查看当前持有的锁
2
SELECT * FROM performance_schema.data_locks\G
3

4
-- 查看锁等待情况
5
SELECT * FROM performance_schema.data_lock_waits\G
6

7
-- 查看InnoDB锁状态（老版本）
8
SHOW ENGINE INNODB STATUS\G

1
[client]
2
port = 3306
3
socket = /tmp/mysql.sock
4

5
[mysqld]
6
# ============ 基础配置 ============
7
user = mysql
8
port = 3306
9
basedir = /usr/local/mysql
10
datadir = /data/mysql/data
11
socket = /tmp/mysql.sock
12
pid-file = /data/mysql/mysql.pid
13
tmpdir = /data/mysql/tmp
14

15
# ============ 字符集配置 ============
16
character-set-server = utf8mb4
17
collation-server = utf8mb4_unicode_ci
18
init_connect = 'SET NAMES utf8mb4'
19

20
# ============ 连接配置 ============
21
max_connections = 500              # 最大连接数
22
max_connect_errors = 100           # 最大错误连接数
23
max_allowed_packet = 64M          # 最大数据包大小
24
wait_timeout = 300                # 连接空闲超时时间（秒）
25
interactive_timeout = 300         # 交互式连接超时时间（秒）
26
connect_timeout = 10              # 连接超时时间（秒）
27

28
# ============ InnoDB核心配置 ============
29
innodb_buffer_pool_size = 4G      # 缓冲池大小，设置为物理内存的50%-70%
30
innodb_buffer_pool_instances = 4  # 缓冲池实例数，建议1G配置1个实例
31
innodb_log_file_size = 512M       # 重做日志大小
32
innodb_log_files_in_group = 2     # 重做日志文件数量
33
innodb_log_buffer_size = 16M      # 日志缓冲区大小
34
innodb_flush_log_at_trx_commit = 2  # 日志刷盘策略（0最快但不安全，1最安全但最慢，2折中）
35
innodb_flush_method = O_DIRECT    # 文件刷盘方法
36
innodb_file_per_table = ON        # 每个表使用独立表空间
37
innodb_data_file_path = ibdata1:100M:autoextend  # 系统表空间配置
38
innodb_autoextend_increment = 64  # 自动扩展增量（MB）
39
innodb_open_files = 1000          # InnoDB最大打开文件数
40
innodb_io_capacity = 2000         # 磁盘IO能力
41
innodb_io_capacity_max = 4000     # 磁盘IO能力上限
42
innodb_read_io_threads = 4        # 读IO线程数
43
innodb_write_io_threads = 4       # 写IO线程数
44

45
# ============ 日志配置 ============
46
log_error = /data/mysql/logs/error.log
47
log_error_verbosity = 2           # 错误日志详细程度（1-3）
48

49
# 慢查询日志
50
slow_query_log = ON
51
slow_query_log_file = /data/mysql/logs/slow.log
52
long_query_time = 2               # 慢查询阈值（秒）
53
log_queries_not_using_indexes = ON  # 记录未使用索引的查询
54
log_throttle_queries_not_using_indexes = 10  # 限制未使用索引日志的记录频率
55

56
# 二进制日志（用于主从复制和时间点恢复）
57
log_bin = /data/mysql/logs/mysql-bin
58
binlog_format = ROW               # 二进制日志格式（ROW/STATEMENT/MIXED）
59
binlog_expire_logs_seconds = 604800  # binlog保留时间（秒，7天）
60
max_binlog_size = 1G             # 单个binlog文件最大大小
61
sync_binlog = 1                  # binlog同步频率（0最快，1最安全）
62

63
# ============ 复制配置 ============
64
server-id = 1                     # 服务器ID，主从复制环境中必须唯一
65
gtid_mode = ON                    # 启用GTID
66
enforce_gtid_consistency = ON     # 强制GTID一致性
67
log_slave_updates = ON            # 从库是否记录复制的binlog
68

69
# ============ 性能优化配置 ============
70
skip_name_resolve = ON            # 跳过DNS解析，提高连接速度
71
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  # SQL模式
72
transaction_isolation = REPEATABLE-READ  # 事务隔离级别
73
query_cache_type = 0              # MySQL 8.0已移除Query Cache
74
table_open_cache = 2000           # 表缓存数量
75
table_open_cache_instances = 16   # 表缓存实例数
76
open_files_limit = 10000          # 最大打开文件数
77
thread_cache_size = 64            # 线程缓存大小
78
tmp_table_size = 64M             # 内存临时表大小
79
max_heap_table_size = 64M        # 内存表最大大小
80

81
# ============ 安全配置 ============
82
# local_infile = OFF              # 禁用LOAD DATA LOCAL INFILE
83
# secure_file_priv = /data/mysql/secure  # 限制导入导出文件路径
84

85
[mysql]
86
default-character-set = utf8mb4
87

88
[mysqldump]
89
max_allowed_packet = 64M
90
default-character-set = utf8mb4

1
-- 1. 创建应用专用用户（限制IP段访问）
2
CREATE USER 'appuser'@'192.168.1.%'
3
IDENTIFIED WITH mysql_native_password BY 'AppUser@2026!Strong';
4

5
-- 2. 创建只读用户（用于报表查询）
6
CREATE USER 'readonly'@'10.0.%.%'
7
IDENTIFIED BY 'ReadOnly@2026!';
8

9
-- 3. 创建备份专用用户
10
CREATE USER 'backup'@'localhost'
11
IDENTIFIED BY 'Backup@2026!';
12

13
-- 4. 授予应用用户基本权限（遵循最小权限原则）
14
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'192.168.1.%';
15

16
-- 5. 授予只读用户查询权限
17
GRANT SELECT ON myapp.* TO 'readonly'@'10.0.%.%';
18

19
-- 6. 授予备份用户必要权限
20
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD
21
ON *.* TO 'backup'@'localhost';
22

23
-- 7. 刷新权限表
24
FLUSH PRIVILEGES;
25

26
-- 8. 查看用户权限
27
SHOW GRANTS FOR 'appuser'@'192.168.1.%';
28
SHOW GRANTS FOR 'readonly'@'10.0.%.%';
29

30
-- 9. 撤销权限示例
31
REVOKE DELETE ON myapp.* FROM 'appuser'@'192.168.1.%';
32

33
-- 10. 修改用户密码
34
ALTER USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'NewPassword@2026!';
35

36
-- 11. 设置密码过期时间（90天）
37
ALTER USER 'appuser'@'192.168.1.%' PASSWORD EXPIRE INTERVAL 90 DAY;
38

39
-- 12. 锁定用户
40
ALTER USER 'suspicious_user'@'%' ACCOUNT LOCK;
41

42
-- 13. 解锁用户
43
ALTER USER 'suspicious_user'@'%' ACCOUNT UNLOCK;
44

45
-- 14. 删除用户
46
DROP USER 'olduser'@'%';

1
-- 查看密码验证组件
2
SHOW VARIABLES LIKE 'validate_password%';
3

4
-- 设置密码策略
5
-- LOW: 只检查密码长度
6
-- MEDIUM: 检查长度、数字、大小写、特殊字符
7
-- STRONG: 检查长度、数字、大小写、特殊字符、字典文件
8
SET GLOBAL validate_password.policy = 'MEDIUM';
9

10
-- 设置密码最小长度
11
SET GLOBAL validate_password.length = 12;
12

13
-- 设置密码必须包含的数字个数
14
SET GLOBAL validate_password.number_count = 2;
15

16
-- 设置密码必须包含的小写字母个数
17
SET GLOBAL validate_password.mixed_case_count = 1;
18

19
-- 设置密码必须包含的特殊字符个数
20
SET GLOBAL validate_password.special_char_count = 1;

1
-- 创建要求SSL连接的用户
2
CREATE USER 'secureuser'@'%'
3
IDENTIFIED BY 'SecurePass@2026!'
4
REQUIRE SSL;
5

6
-- 修改现有用户要求SSL
7
ALTER USER 'appuser'@'192.168.1.%' REQUIRE SSL;
8

9
-- 验证SSL连接状态
10
SHOW STATUS LIKE 'Ssl_cipher';
11
\s

1
-- 1. 查看连接数统计
2
SHOW STATUS LIKE 'Threads_connected';      -- 当前连接数
3
SHOW STATUS LIKE 'Threads_running';        -- 正在运行的线程数
4
SHOW STATUS LIKE 'Max_used_connections';   -- 最大连接数峰值
5
SHOW VARIABLES LIKE 'max_connections';     -- 最大连接数配置
6

7
-- 2. 查看QPS和TPS
8
SHOW GLOBAL STATUS LIKE 'Questions';       -- 总查询数
9
SHOW GLOBAL STATUS LIKE 'Com_select';      -- SELECT查询数
10
SHOW GLOBAL STATUS LIKE 'Com_insert';      -- INSERT查询数
11
SHOW GLOBAL STATUS LIKE 'Com_update';      -- UPDATE查询数
12
SHOW GLOBAL STATUS LIKE 'Com_delete';      -- DELETE查询数
13
SHOW GLOBAL STATUS LIKE 'Com_commit';      -- 提交事务数
14
SHOW GLOBAL STATUS LIKE 'Com_rollback';    -- 回滚事务数
15

16
-- 3. 查看缓冲池使用情况
17
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';
18
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
19
SELECT
20
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
21
    AS buffer_pool_hit_rate
22
FROM (
23
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
24
    FROM performance_schema.global_status
25
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
26
) AS t1,
27
(
28
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
29
    FROM performance_schema.global_status
30
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
31
) AS t2;
32

33
-- 4. 查看锁等待情况
34
SELECT * FROM performance_schema.data_locks;
35
SELECT * FROM performance_schema.data_lock_waits;
36
SHOW ENGINE INNODB STATUS\G
37

38
-- 5. 查看表锁情况
39
SHOW OPEN TABLES WHERE In_use > 0;
40

41
-- 6. 查看死锁信息
42
SHOW ENGINE INNODB STATUS\G  -- 查看LATEST DETECTED DEADLOCK部分

1
-- MySQL 8.0 性能监控视图
2
SELECT
3
    -- 连接信息
4
    (SELECT COUNT(*) FROM performance_schema.threads WHERE TYPE='FOREGROUND') AS current_connections,
5
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Max_used_connections') AS max_used_connections,
6
    (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') AS max_connections,
7

8
    -- QPS统计
9
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Questions') AS total_questions,
10
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Com_select') AS total_selects,
11
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Com_insert') AS total_inserts,
12
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Com_update') AS total_updates,
13
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Com_delete') AS total_deletes,
14

15
    -- 缓冲池统计
16
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') AS buffer_pool_read_requests,
17
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') AS buffer_pool_reads,
18
    ROUND((1 - (
19
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
20
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
21
    )) * 100, 2) AS buffer_pool_hit_rate_pct\G

1
-- 1. 修改主库配置文件 /etc/my.cnf
2
-- [mysqld]
3
-- server-id = 1
4
-- log_bin = /data/mysql/logs/mysql-bin
5
-- binlog_format = ROW
6
-- gtid_mode = ON
7
-- enforce_gtid_consistency = ON
8
-- log_slave_updates = ON
9

10
-- 2. 创建复制用户
11
CREATE USER 'repl'@'192.168.1.%'
12
IDENTIFIED WITH mysql_native_password BY 'Repl@2026!Strong';
13

14
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
15
FLUSH PRIVILEGES;
16

17
-- 3. 查看主库状态
18
SHOW MASTER STATUS\G

1
-- 1. 修改从库配置文件 /etc/my.cnf
2
-- [mysqld]
3
-- server-id = 2
4
-- relay_log = /data/mysql/logs/mysql-relay-bin
5
-- read_only = ON
6
-- super_read_only = ON
7
-- gtid_mode = ON
8
-- enforce_gtid_consistency = ON
9
-- log_slave_updates = ON
10

11
-- 2. 配置主从复制（使用GTID）
12
STOP SLAVE;
13

14
CHANGE MASTER TO
15
    MASTER_HOST='192.168.1.100',
16
    MASTER_PORT=3306,
17
    MASTER_USER='repl',
18
    MASTER_PASSWORD='Repl@2026!Strong',
19
    MASTER_AUTO_POSITION=1;  -- 使用GTID自动定位
20

21
START SLAVE;
22

23
-- 3. 查看从库状态
24
SHOW SLAVE STATUS\G
25

26
-- 重点检查以下字段：
27
-- Slave_IO_Running: Yes
28
-- Slave_SQL_Running: Yes
29
-- Seconds_Behind_Master: 0或很小的值
30
-- Last_IO_Error: 空
31
-- Last_SQL_Error: 空

1
-- 1. 主从延迟过大
2
SHOW SLAVE STATUS\G
3

4
-- 查看延迟原因
5
-- 可能原因：
6
-- a) 从库配置低于主库
7
-- b) 从库有大量查询占用资源
8
-- c) 网络延迟
9
-- d) 主库写入压力过大
10

11
-- 解决方案：
12
-- 升级从库硬件、优化查询、启用并行复制
13

14
-- 2. 主从复制中断
15
SHOW SLAVE STATUS\G
16
-- 查看Last_IO_Error和Last_SQL_Error
17

18
-- 如果是SQL线程错误，可以跳过错误事务（慎用）
19
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
20
START SLAVE;
21

22
-- 或者使用GTID跳过错误事务
23
STOP SLAVE;
24
SET GTID_NEXT='错误事务的GTID';
25
BEGIN;
26
COMMIT;
27
SET GTID_NEXT='AUTOMATIC';
28
START SLAVE;
29

30
-- 3. 主从数据不一致
31
-- 使用pt-table-checksum检查数据一致性
32
-- pt-table-checksum --host=主库IP --replicate=test.checksums
33

34
-- 使用pt-table-sync修复数据不一致
35
-- pt-table-sync --execute --sync-to-master 从库IP

1
-- 查看当前连接数
2
SHOW STATUS LIKE 'Threads_connected';
3
SHOW VARIABLES LIKE 'max_connections';
4

5
-- 查看所有连接详情
6
SHOW FULL PROCESSLIST;
7

8
-- 找出长时间Sleep的连接
9
SELECT
10
    id, user, host, db, command, time, state, info
11
FROM information_schema.processlist
12
WHERE command = 'Sleep' AND time > 300
13
ORDER BY time DESC;
14

15
-- 杀掉空闲连接（批量kill）
16
SELECT
17
    CONCAT('KILL ', id, ';') AS kill_cmd
18
FROM information_schema.processlist
19
WHERE command = 'Sleep' AND time > 300;
20

21
-- 临时增加最大连接数（重启后失效）
22
SET GLOBAL max_connections = 1000;
23

24
-- 优化wait_timeout参数（自动断开空闲连接）
25
SET GLOBAL wait_timeout = 300;
26
SET GLOBAL interactive_timeout = 300;

1
-- 查看正在执行的查询
2
SHOW FULL PROCESSLIST;
3

4
-- 查找耗时最长的查询
5
SELECT
6
    id, user, host, db, command, time, state,
7
    LEFT(info, 100) AS query_preview
8
FROM information_schema.processlist
9
WHERE command != 'Sleep'
10
ORDER BY time DESC
11
LIMIT 10;
12

13
-- 杀掉慢查询
14
KILL QUERY 进程ID;  -- 只杀查询，不断开连接
15
KILL 进程ID;        -- 杀进程并断开连接
16

17
-- 分析慢查询日志
18
-- pt-query-digest /var/log/mysql/slow.log
19

20
-- 使用EXPLAIN分析SQL
21
EXPLAIN SELECT ...;

1
-- 查看从库状态
2
SHOW SLAVE STATUS\G
3

4
-- 场景1：主库binlog被清理导致同步中断
5
-- 需要重新搭建主从复制
6
-- 1) 在主库做全量备份
7
-- 2) 恢复到从库
8
-- 3) 重新配置主从复制
9

10
-- 场景2：SQL线程错误（数据冲突）
11
-- 方法1：跳过错误（谨慎使用）
12
STOP SLAVE;
13
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
14
START SLAVE;
15

16
-- 方法2：手动修复数据后重启复制
17
STOP SLAVE;
18
-- 手动修复数据...
19
START SLAVE;
20

21
-- 场景3：IO线程错误（网络问题）
22
STOP SLAVE IO_THREAD;
23
START SLAVE IO_THREAD;
24

25
-- 场景4：主从数据不一致
26
-- 使用pt-table-checksum + pt-table-sync工具修复