MySQL教程FG200-MySQL资源限制

Part01-基础概念与理论知识

MySQL资源限制是一种重要的数据库管理和安全机制，用于控制数据库服务器的资源使用，防止单个用户或会话占用过多资源，从而保障系统的稳定性和可用性。通过合理配置资源限制，可以有效防止资源耗尽、拒绝服务攻击和性能问题。更多学习教程www.fgedu.net.cn

1.1 资源限制的作用

保障系统稳定性：防止单个用户或查询占用过多资源，导致系统崩溃
提高资源利用率：合理分配资源，确保所有用户都能获得公平的资源份额
防止滥用：防止恶意用户通过消耗大量资源进行拒绝服务攻击
优化性能：通过限制长时间运行的查询，提高系统整体性能
便于管理：可以根据用户的重要性和需求分配不同的资源配额

1.2 资源限制的实现方式

MySQL通过以下方式实现资源限制：

全局参数配置：通过修改MySQL配置文件中的全局参数来限制系统级资源
用户级限制：为每个用户设置资源限制，如最大连接数、查询次数等
会话级限制：为每个会话设置资源限制，如查询超时时间、临时表大小等
插件扩展：通过安装插件来实现更高级的资源限制功能

Part02-生产环境规划与建议

MySQL支持多种类型的资源限制，包括连接限制、查询限制、内存限制等。学习交流加群风哥微信: itpux-com

2.1 连接相关限制

最大连接数：限制MySQL服务器允许的最大并发连接数
用户最大连接数：限制单个用户允许的最大并发连接数
连接超时：限制连接空闲时间

2.2 查询相关限制

查询超时：限制查询的最大执行时间
每小时查询次数：限制用户每小时可以执行的查询次数
每小时更新次数：限制用户每小时可以执行的更新操作次数

2.3 内存相关限制

全局内存限制：限制MySQL服务器使用的总内存量
会话内存限制：限制单个会话可以使用的内存量
临时表内存限制：限制临时表可以使用的内存量

2.4 其他资源限制

表锁定限制：限制用户可以锁定的表数量
打开文件限制：限制MySQL服务器可以打开的文件数量
线程数限制：限制MySQL服务器可以创建的线程数量

Part03-生产环境项目实施方案

全局资源限制是应用于整个MySQL服务器的资源限制，可以通过修改MySQL配置文件或使用SET GLOBAL语句来配置。

3.1 最大连接数限制

限制MySQL服务器允许的最大并发连接数。

# 修改最大连接数配置
mysql> SET GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

# 永久保存配置
# 在my.cnf文件中添加
[mysqld]
max_connections = 1000

3.2 连接超时配置

限制连接的空闲时间，超过该时间后自动关闭连接。

# 查看当前连接超时配置
mysql> SHOW VARIABLES LIKE ‘%timeout%’;
+—————————–+———-+
| Variable_name | Value |
+—————————–+———-+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+—————————–+———-+
13 rows in set (0.01 sec)

# 修改连接超时配置
mysql> SET GLOBAL interactive_timeout = 1800;
mysql> SET GLOBAL wait_timeout = 1800;
Query OK, 0 rows affected (0.00 sec)

# 永久保存配置
# 在my.cnf文件中添加
[mysqld]
interactive_timeout = 1800
wait_timeout = 1800

3.3 全局内存限制

配置MySQL服务器使用的全局内存限制。

# 查看当前内存配置
mysql> SHOW VARIABLES LIKE ‘%buffer%’ OR variable_name LIKE ‘%cache%’;
+————————————-+—————-+
| Variable_name | Value |
+————————————-+—————-+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | NO |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_doublewrite_buffer_size | 2097152 |
| innodb_log_buffer_size | 16777216 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+————————————-+—————-+
18 rows in set (0.01 sec)

# 修改InnoDB缓冲池大小
mysql> SET GLOBAL innodb_buffer_pool_size = 1073741824; — 1GB
Query OK, 0 rows affected (0.01 sec)

# 永久保存配置
# 在my.cnf文件中添加
[mysqld]
innodb_buffer_pool_size = 1G

3.4 查询超时配置

设置全局查询超时时间，限制查询的最大执行时间。

# 设置全局查询超时时间为60秒
mysql> SET GLOBAL max_execution_time = 60000;
Query OK, 0 rows affected (0.00 sec)

# 永久保存配置
# 在my.cnf文件中添加
[mysqld]
max_execution_time = 60000

Part04-生产案例与实战讲解

用户级资源限制是应用于特定用户的资源限制，可以在创建用户或修改用户时配置。

4.1 创建用户时设置资源限制

在创建用户时，可以同时设置资源限制。

# 创建带资源限制的用户
mysql> CREATE USER ‘limited_user’@’%’ IDENTIFIED WITH caching_sha2_password BY ‘Limit@2026’
WITH MAX_QUERIES_PER_HOUR 100
MAX_UPDATES_PER_HOUR 50
MAX_CONNECTIONS_PER_HOUR 20
MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.01 sec)

# 查看用户资源限制
mysql> SHOW CREATE USER ‘limited_user’@’%’;
+——————————————————————————————————————————————————————–+
| CREATE USER for limited_user@% |
+——————————————————————————————————————————————————————–+
| CREATE USER `limited_user`@`%` IDENTIFIED BY PASSWORD ‘*A1B2C3D4E5F6G7H8I9J0’ WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 50 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTIONS 5 |
+——————————————————————————————————————————————————————–+
1 row in set (0.00 sec)

4.2 修改用户资源限制

使用ALTER USER语句修改用户的资源限制。

# 修改用户资源限制
mysql> ALTER USER ‘limited_user’@’%’ WITH
MAX_QUERIES_PER_HOUR 200
MAX_UPDATES_PER_HOUR 100
MAX_CONNECTIONS_PER_HOUR 50
MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.01 sec)

# 验证修改后的资源限制
mysql> SHOW CREATE USER ‘limited_user’@’%’;
+——————————————————————————————————————————————————————–+
| CREATE USER for limited_user@% |
+——————————————————————————————————————————————————————–+
| CREATE USER `limited_user`@`%` IDENTIFIED BY PASSWORD ‘*A1B2C3D4E5F6G7H8I9J0’ WITH MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 50 MAX_USER_CONNECTIONS 10 |
+——————————————————————————————————————————————————————–+
1 row in set (0.00 sec)

4.3 测试用户资源限制

测试用户资源限制是否生效。

# 以limited_user用户登录
$ mysql -ulimited_user -p’Limit@2026′ -h192.168.1.10

# 执行多次查询，超过MAX_QUERIES_PER_HOUR限制
mysql> SELECT 1;
+—+
| 1 |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

… 执行多次查询后 …

mysql> SELECT 1;
ERROR 1226 (42000): User ‘limited_user’ has exceeded the ‘max_queries_per_hour’ resource (current value: 200)

4.4 重置用户资源计数器

使用FLUSH USER_RESOURCES语句重置用户的资源计数器。

# 重置所有用户的资源计数器
mysql> FLUSH USER_RESOURCES;
Query OK, 0 rows affected (0.00 sec)

# 测试用户现在可以再次执行查询
$ mysql -ulimited_user -p’Limit@2026′ -h192.168.1.10

mysql> SELECT 1;
+—+
| 1 |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

Part05-风哥经验总结与分享

会话级资源限制是应用于特定会话的资源限制，可以在会话级别设置或通过全局参数设置默认值。

5.1 会话内存限制

设置会话级别的内存限制。

# 查看当前会话内存配置
mysql> SHOW SESSION VARIABLES LIKE ‘%buffer%’ OR variable_name LIKE ‘%cache%’;
+————————————-+—————-+
| Variable_name | Value |
+————————————-+—————-+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+————————————-+—————-+
11 rows in set (0.01 sec)

# 修改会话级别的排序缓冲大小
mysql> SET SESSION sort_buffer_size = 524288; — 512KB
Query OK, 0 rows affected (0.00 sec)

5.2 会话查询超时

设置会话级别的查询超时时间。

# 设置会话查询超时时间为30秒
mysql> SET SESSION max_execution_time = 30000;
Query OK, 0 rows affected (0.00 sec)

# 测试长时间运行的查询
mysql> SELECT SLEEP(40);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

5.3 临时表大小限制

设置会话级别的临时表大小限制。

# 修改临时表大小限制
mysql> SET SESSION tmp_table_size = 33554432; — 32MB
mysql> SET SESSION max_heap_table_size = 33554432; — 32MB
Query OK, 0 rows affected (0.00 sec)

6. 生产环境案例分析

本部分通过实际案例展示资源限制在生产环境中的应用。

6.1 案例1：防止查询风暴

配置资源限制，防止单个用户发起过多查询导致系统负载过高。

# 1. 为普通用户设置资源限制
mysql> ALTER USER ‘app_user’@’%’ WITH
MAX_QUERIES_PER_HOUR 10000
MAX_UPDATES_PER_HOUR 5000
MAX_CONNECTIONS_PER_HOUR 100
MAX_USER_CONNECTIONS 20;

# 2. 为报表用户设置更严格的限制
mysql> ALTER USER ‘report_user’@’%’ WITH
MAX_QUERIES_PER_HOUR 2000
MAX_UPDATES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 50
MAX_USER_CONNECTIONS 5;

# 3. 设置全局查询超时
mysql> SET GLOBAL max_execution_time = 60000;

# 4. 监控资源使用情况
mysql> SHOW GLOBAL STATUS LIKE ‘Threads_connected’;
mysql> SHOW GLOBAL STATUS LIKE ‘Questions’;
mysql> SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

6.2 案例2：限制内存使用

配置内存限制，防止单个会话占用过多内存导致系统OOM。

# 1. 配置全局内存限制
[mysqld]
# InnoDB缓冲池大小（物理内存的50%-70%）
innodb_buffer_pool_size = 8G

# 连接缓冲
key_buffer_size = 256M

# 限制单个会话的内存使用
max_heap_table_size = 64M
tmp_table_size = 64M
join_buffer_size = 1M
sort_buffer_size = 1M
read_buffer_size = 256K
read_rnd_buffer_size = 512K

# 2. 重启MySQL服务使配置生效
$ systemctl restart mysqld

# 3. 验证配置
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
mysql> SHOW VARIABLES LIKE ‘max_heap_table_size’;
mysql> SHOW VARIABLES LIKE ‘tmp_table_size’;

6.3 案例3：保护数据库免受拒绝服务攻击

配置资源限制，防止恶意用户发起拒绝服务攻击。

# 1. 限制最大连接数
[mysqld]
max_connections = 500

# 2. 为所有用户设置连接限制
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘App@2026’ WITH MAX_USER_CONNECTIONS 10;
mysql> CREATE USER ‘web_user’@’%’ IDENTIFIED BY ‘Web@2026’ WITH MAX_USER_CONNECTIONS 50;

# 3. 设置连接超时
[mysqld]
interactive_timeout = 1800
wait_timeout = 1800

# 4. 启用连接控制插件防止暴力破解
[mysqld]
plugin-load-add = connection_control.so
connection-control = FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts = FORCE_PLUS_PERMANENT
connection-control-failed-connections-threshold = 3
connection-control-min-connection-delay = 5000

7. 最佳实践与经验总结

作为资深DBA，风哥在MySQL资源限制方面积累了丰富的经验。

风哥提示：资源限制是一把双刃剑，合理配置可以提高系统稳定性和安全性，配置不当可能影响正常业务。建议根据实际业务需求和系统资源情况，制定合适的资源限制策略。

7.1 资源限制最佳实践

根据实际需求配置：根据业务特点和系统资源情况设置合理的限制值
遵循最小特权原则：只为用户分配必要的资源配额
区别对待不同用户：为不同类型的用户设置不同的资源限制
监控资源使用情况：定期监控系统资源使用情况，及时调整限制值
设置合理的缓冲区大小：根据系统内存情况设置合适的缓冲区大小
测试配置效果：在测试环境中验证资源限制配置的效果

7.2 常见问题与解决方案

问题1：资源限制导致正常业务受影响
解决方案：调整限制值，增加必要的资源配额；为重要用户设置更高的限制值。
问题2：资源限制不生效
解决方案：检查配置是否正确；确认是否需要重启服务；检查用户权限。
问题3：系统资源仍然耗尽
解决方案：分析资源消耗情况；优化查询和应用程序；考虑增加系统资源。
问题4：用户频繁达到资源限制
解决方案：分析用户行为；优化用户的查询和操作；考虑增加资源配额。

7.3 生产环境资源限制建议

生产环境建议：

最大连接数：根据系统内存和CPU情况设置，一般为500-2000
连接超时：一般设置为30分钟（1800秒）
查询超时：根据业务需求设置，一般为30-60秒
InnoDB缓冲池：设置为物理内存的50%-70%
临时表大小：根据业务需求设置，一般为64-256MB
用户连接数：根据用户类型设置，普通用户10-50，报表用户5-10

7.4 风哥的资源限制建议

风哥提示：资源限制是MySQL性能优化和安全防护的重要手段，但不是万能的。建议结合其他措施，如查询优化、索引优化、监控告警等，全面提升系统的性能和安全性。同时，要定期审查资源限制配置，根据业务变化和系统资源情况及时调整，以确保资源限制既能保护系统安全，又不会影响正常业务。更多学习教程公众号风哥教程itpux_com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html