MySQL安装与配置

第2章：MySQL安装与配置（续）

2.4 MySQL基本配置

MySQL的配置对于数据库的性能、安全性和可靠性至关重要。本节将具体介绍MySQL的配置文件、常用配置参数、字符集设置以及安全配置等内容。
2.4.1 配置文件概述

MySQL使用配置文件来存储服务器和客户端的设置。相识配置文件的位置和结构是管理MySQL的基础。
配置文件位置

MySQL会按照特定的次序查找并加载配置文件。配置文件的位置取决于操作体系：
Windows体系：

• C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  
• C:\Program Files\MySQL\MySQL Server 8.0\my.ini
  
• C:\my.ini
  
• C:\Windows\my.ini
  

Linux/Unix体系：

• /etc/my.cnf
  
• /etc/mysql/my.cnf
  
• /usr/local/mysql/etc/my.cnf
  
• ~/.my.cnf（用户特定配置）
  

可以使用以下命令检察MySQL当前使用的配置文件：

1. mysql --help | grep "Default options" -A 1

复制代码

配置文件结构

MySQL配置文件使用INI格式，由多个部分构成，每个部分以方括号中的标签开始。常见的配置部分包罗：

• [mysqld]：MySQL服务器配置
  
• [mysql]：MySQL命令行客户端配置
  
• [client]：全部MySQL客户端步伐的通用配置
  
• [mysqldump]：mysqldump工具的配置
  
• [mysqladmin]：mysqladmin工具的配置
  

示例配置文件结构：

1. [mysqld]
2. # 服务器配置
3. port=3306
4. datadir=/var/lib/mysql
5. socket=/var/lib/mysql/mysql.sock
7. [mysql]
8. # 命令行客户端配置
9. prompt=\u@\h [\d]>\_
11. [client]
12. # 所有客户端的通用配置
13. port=3306
14. socket=/var/lib/mysql/mysql.sock

复制代码

配置优先级

MySQL按照以下优先级加载配置（从低到高）：

• 全局配置文件（如/etc/my.cnf）
  
• MySQL安装目次中的配置文件
  
• 用户特定的配置文件（~/.my.cnf）
  
• 命令行参数
  

这意味着命令行参数会覆盖配置文件中的设置，而用户特定的配置会覆盖全局配置。
2.4.2 常用配置参数

MySQL有数百个配置参数，但只有少数参数必要常常调解。以下是一些最常用的配置参数及其说明：
基本设置

1. [mysqld]
2. # 服务器标识，在复制环境中必须唯一
3. server-id=1
5. # 监听地址和端口
6. bind-address=0.0.0.0  # 监听所有网络接口
7. port=3306
9. # 数据目录
10. datadir=/var/lib/mysql
12. # 套接字文件位置
13. socket=/var/run/mysqld/mysqld.sock
15. # 临时目录
16. tmpdir=/tmp
18. # 最大连接数
19. max_connections=200
21. # 连接超时（秒）
22. wait_timeout=28800
23. interactive_timeout=28800
25. # 最大允许的数据包大小
26. max_allowed_packet=64M

复制代码

内存相关设置

1. [mysqld]
2. # InnoDB缓冲池大小（通常设置为系统内存的50-80%）
3. innodb_buffer_pool_size=1G
5. # InnoDB缓冲池实例数（通常设置为缓冲池大小/1GB）
6. innodb_buffer_pool_instances=1
8. # 查询缓存大小（MySQL 8.0已移除查询缓存）
9. # query_cache_size=32M
10. # query_cache_type=1
12. # 排序缓冲区大小
13. sort_buffer_size=2M
15. # 连接缓冲区大小
16. join_buffer_size=2M
18. # 表定义缓存
19. table_definition_cache=1400
21. # 表打开缓存
22. table_open_cache=2000

复制代码

日志设置

1. [mysqld]
2. # 错误日志
3. log_error=/var/log/mysql/error.log
5. # 一般查询日志（通常在生产环境中禁用）
6. general_log=0
7. general_log_file=/var/log/mysql/mysql.log
9. # 慢查询日志
10. slow_query_log=1
11. slow_query_log_file=/var/log/mysql/mysql-slow.log
12. long_query_time=2  # 记录执行时间超过2秒的查询
14. # 二进制日志（用于复制和时间点恢复）
15. log_bin=/var/log/mysql/mysql-bin.log
16. expire_logs_days=14  # 自动删除14天前的日志
17. max_binlog_size=100M

复制代码

InnoDB存储引擎设置

1. [mysqld]
2. # 默认存储引擎
3. default_storage_engine=InnoDB
5. # InnoDB日志文件大小
6. innodb_log_file_size=256M
8. # InnoDB日志缓冲区大小
9. innodb_log_buffer_size=16M
11. # InnoDB刷新方法
12. innodb_flush_method=O_DIRECT
14. # InnoDB刷新日志的频率
15. # 1=最安全但最慢，0=最快但最不安全，2=折中
16. innodb_flush_log_at_trx_commit=1
18. # InnoDB文件每表一个（MySQL 5.6+）
19. innodb_file_per_table=1
21. # InnoDB缓冲池转储和加载
22. innodb_buffer_pool_dump_at_shutdown=1
23. innodb_buffer_pool_load_at_startup=1

复制代码

复制设置

1. [mysqld]
2. # 启用二进制日志（用于复制）
3. log_bin=mysql-bin
5. # 服务器ID（在复制环境中必须唯一）
6. server_id=1
8. # 二进制日志格式（ROW, STATEMENT, MIXED）
9. binlog_format=ROW
11. # 从服务器设置
12. # read_only=1  # 使从服务器只读
13. # relay_log=relay-bin
14. # relay_log_index=relay-bin.index

复制代码

2.4.3 字符集和排序规则设置

字符集和排序规则对于准确存储和比较多语言数据至关重要。MySQL 8.0默认使用utf8mb4字符集和utf8mb4_0900_ai_ci排序规则，这是一个很好的选择，由于它支持全部Unicode字符（包罗心情符号）。
设置全局字符集和排序规则

在配置文件中设置全局字符集和排序规则：

1. [mysqld]
2. # 服务器字符集和排序规则
3. character-set-server=utf8mb4
4. collation-server=utf8mb4_unicode_ci
6. [mysql]
7. # 客户端字符集
8. default-character-set=utf8mb4
10. [client]
11. # 连接字符集
12. default-character-set=utf8mb4

复制代码

检察当前字符集设置

连接到MySQL后，可以使用以下命令检察当前的字符集和排序规则设置：

1. -- 查看服务器字符集变量
2. SHOW VARIABLES LIKE 'character_set%';
4. -- 查看排序规则变量
5. SHOW VARIABLES LIKE 'collation%';

复制代码

修改现有数据库和表的字符集

对于已经创建的数据库和表，可以使用以下命令修改其字符集和排序规则：

1. -- 修改数据库字符集
2. ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. -- 修改表字符集
5. ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
7. -- 修改列字符集
8. ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

复制代码

字符集最佳实践

• 使用utf8mb4：始终使用utf8mb4而不是utf8，由于utf8mb4支持全部Unicode字符，包罗心情符号和一些稀有的中笔墨符。
  
• 保持一致性：在服务器、数据库、表和连接级别使用雷同的字符集，以避免转换问题。
  
• 选择适当的排序规则：
  
  
  
  • utf8mb4_unicode_ci：正确但较慢
    
  • utf8mb4_general_ci：速度较快但不太正确
    
  • utf8mb4_0900_ai_ci：MySQL 8.0的默认值，结合了正确性和性能
    
  
  
• 注意连接设置：确保应用步伐连接字符串中指定了准确的字符集。
  

2.4.4 安全配置

MySQL的安全配置对于保护数据库和数据至关重要。以下是一些基本的安全配置建议：
网络安全

1. [mysqld]
2. # 限制MySQL只监听特定IP地址（如本地连接）
3. bind-address=127.0.0.1
5. # 禁用远程root登录
6. # 注意：这需要在MySQL中使用SQL命令设置
8. # 使用安全连接
9. require_secure_transport=ON  # 要求使用SSL/TLS连接（MySQL 5.7+）

复制代码

认证和访问控制

1. [mysqld]
2. # 使用强密码加密（MySQL 8.0默认）
3. default_authentication_plugin=caching_sha2_password
5. # 启用密码验证组件（MySQL 8.0+）
6. plugin-load-add=validate_password.so
7. validate_password.policy=MEDIUM
8. validate_password.length=8

复制代码

文件体系安全

1. [mysqld]
2. # 限制文件系统访问
3. secure-file-priv=/var/lib/mysql-files
5. # 设置适当的文件权限（这是系统级设置，不在配置文件中）
6. # sudo chown -R mysql:mysql /var/lib/mysql
7. # sudo chmod 750 /var/lib/mysql

复制代码

用户账户安全

MySQL 8.0引入了角色，这使得权限管理更加机动和安全。以下是一些用户账户安全的最佳实践（通过SQL命令实现）：

1. -- 创建具有最小权限的用户
2. CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
3. GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'localhost';
5. -- 创建角色（MySQL 8.0+）
6. CREATE ROLE 'app_read_role', 'app_write_role';
7. GRANT SELECT ON app_database.* TO 'app_read_role';
8. GRANT INSERT, UPDATE, DELETE ON app_database.* TO 'app_write_role';
10. -- 将角色分配给用户
11. GRANT 'app_read_role' TO 'read_user'@'localhost';
12. GRANT 'app_write_role' TO 'write_user'@'localhost';
14. -- 激活角色
15. SET DEFAULT ROLE ALL TO 'read_user'@'localhost', 'write_user'@'localhost';

复制代码

审计和日志

1. [mysqld]
2. # 启用审计日志（企业版功能，社区版可以使用MariaDB审计插件）
3. # audit_log=FORCE_PLUS_PERMANENT
4. # audit_log_file=/var/log/mysql/audit.log
6. # 启用一般查询日志（仅用于调试，生产环境通常禁用）
7. general_log=0
8. general_log_file=/var/log/mysql/mysql.log
10. # 记录所有失败的登录尝试
11. log_error_verbosity=3

复制代码

2.4.5 性能优化配置

MySQL的性能很大程度上取决于其配置。以下是一些基本的性能优化配置：
缓冲池和内存设置

1. [mysqld]
2. # InnoDB缓冲池大小（根据可用内存调整）
3. # 对于专用服务器，可以设置为系统内存的50-80%
4. innodb_buffer_pool_size=4G
6. # InnoDB缓冲池实例数（每GB内存一个实例）
7. innodb_buffer_pool_instances=4
9. # InnoDB读写I/O线程数
10. innodb_read_io_threads=4
11. innodb_write_io_threads=4
13. # 表定义缓存
14. table_definition_cache=2000
16. # 表打开缓存
17. table_open_cache=4000
18. table_open_cache_instances=16

复制代码

并发设置

1. [mysqld]
2. # 最大连接数
3. max_connections=500
5. # 线程缓存大小
6. thread_cache_size=32
8. # 临时表大小
9. tmp_table_size=64M
10. max_heap_table_size=64M
12. # 后台线程并发
13. innodb_parallel_read_threads=4  # MySQL 8.0.14+

复制代码

日志和事件设置

1. [mysqld]
2. # InnoDB日志文件大小（通常设置为缓冲池大小的25%）
3. innodb_log_file_size=1G
5. # InnoDB日志缓冲区大小
6. innodb_log_buffer_size=32M
8. # InnoDB刷新设置
9. # 1=最安全但最慢，0=最快但最不安全，2=折中
10. innodb_flush_log_at_trx_commit=1
12. # 二进制日志同步
13. sync_binlog=1  # 最安全设置，每次事务都同步

复制代码

查询优化

1. [mysqld]
2. # 排序缓冲区大小
3. sort_buffer_size=4M
5. # 连接缓冲区大小
6. join_buffer_size=4M
8. # 读取缓冲区大小
9. read_buffer_size=2M
10. read_rnd_buffer_size=2M
12. # 优化器设置
13. optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

复制代码

2.4.6 监控和调优配置

为了有用地监控和调优MySQL，可以启用一些特定的配置选项：
性能模式（Performance Schema）

Performance Schema是MySQL的一个功能，用于监控MySQL服务器的执行情况：

1. [mysqld]
2. # 启用性能模式
3. performance_schema=ON
5. # 设置性能模式内存限制（根据可用内存调整）
6. performance_schema_max_table_instances=500
7. performance_schema_max_table_handles=500
8. performance_schema_max_mutex_instances=5000
9. performance_schema_max_rwlock_instances=2000

复制代码

信息模式（Information Schema）

Information Schema提供了对数据库元数据的访问：

1. [mysqld]
2. # 启用额外的统计信息
3. innodb_stats_on_metadata=ON

复制代码

慢查询日志

慢查询日志对于识别性能问题非常有用：

1. [mysqld]
2. # 启用慢查询日志
3. slow_query_log=1
4. slow_query_log_file=/var/log/mysql/mysql-slow.log
6. # 记录执行时间超过1秒的查询
7. long_query_time=1
9. # 记录未使用索引的查询
10. log_queries_not_using_indexes=1
12. # 限制每分钟记录的未使用索引的查询数
13. log_throttle_queries_not_using_indexes=10
15. # 记录管理语句（如ALTER TABLE）
16. log_slow_admin_statements=1

复制代码

2.4.7 配置文件示例

以下是一个综合的MySQL配置文件示例，适用于中等规模的应用：

1. [mysqld]
2. # 基本设置
3. user=mysql
4. pid-file=/var/run/mysqld/mysqld.pid
5. socket=/var/run/mysqld/mysqld.sock
6. port=3306
7. basedir=/usr
8. datadir=/var/lib/mysql
9. tmpdir=/tmp
10. bind-address=0.0.0.0
12. # 字符集设置
13. character-set-server=utf8mb4
14. collation-server=utf8mb4_unicode_ci
16. # InnoDB设置
17. default_storage_engine=InnoDB
18. innodb_buffer_pool_size=2G
19. innodb_buffer_pool_instances=2
20. innodb_log_file_size=512M
21. innodb_log_buffer_size=16M
22. innodb_flush_log_at_trx_commit=1
23. innodb_file_per_table=1
24. innodb_flush_method=O_DIRECT
25. innodb_read_io_threads=4
26. innodb_write_io_threads=4
28. # 连接设置
29. max_connections=300
30. thread_cache_size=32
31. wait_timeout=3600
32. interactive_timeout=3600
33. max_allowed_packet=64M
35. # 表缓存设置
36. table_open_cache=2000
37. table_definition_cache=1400
38. table_open_cache_instances=16
40. # 临时表设置
41. tmp_table_size=32M
42. max_heap_table_size=32M
44. # 查询缓冲设置
45. sort_buffer_size=2M
46. join_buffer_size=2M
47. read_buffer_size=1M
48. read_rnd_buffer_size=1M
50. # 日志设置
51. log_error=/var/log/mysql/error.log
52. slow_query_log=1
53. slow_query_log_file=/var/log/mysql/mysql-slow.log
54. long_query_time=2
55. log_queries_not_using_indexes=0
56. log_slow_admin_statements=1
58. # 二进制日志设置
59. log_bin=/var/log/mysql/mysql-bin.log
60. expire_logs_days=14
61. max_binlog_size=100M
62. binlog_format=ROW
63. sync_binlog=1
65. # 复制设置
66. server_id=1
67. # 如果是从服务器，取消下面的注释
68. # read_only=1
69. # relay_log=relay-bin
70. # relay_log_index=relay-bin.index
72. # 安全设置
73. secure-file-priv=/var/lib/mysql-files
74. default_authentication_plugin=caching_sha2_password
76. # 性能模式
77. performance_schema=ON
79. [mysql]
80. default-character-set=utf8mb4
82. [client]
83. default-character-set=utf8mb4

复制代码

2.4.8 动态配置参数

MySQL的很多配置参数可以在运行时动态修改，无需重启服务器。这对于调试和优化非常有用。
检察可动态修改的参数

1. -- 查看所有系统变量
2. SHOW VARIABLES;
4. -- 查看特定变量
5. SHOW VARIABLES LIKE 'max_connections';
7. -- 查看变量是否可动态修改
8. SELECT @@max_connections;

复制代码

动态修改参数

1. -- 修改全局变量（影响新连接）
2. SET GLOBAL max_connections = 500;
4. -- 修改会话变量（仅影响当前连接）
5. SET SESSION sort_buffer_size = 4*1024*1024;

复制代码

长期化动态参数（MySQL 8.0+）

MySQL 8.0引入了SET PERSIST命令，可以动态修改参数并将其保存到配置文件中，使其在重启后仍旧有用：

1. -- 修改参数并持久化
2. SET PERSIST max_connections = 500;
4. -- 仅持久化参数，不立即修改
5. SET PERSIST_ONLY max_connections = 500;
7. -- 查看持久化的配置
8. SELECT * FROM performance_schema.persisted_variables;
10. -- 删除持久化的配置
11. RESET PERSIST max_connections;
12. RESET PERSIST IF EXISTS max_connections;
13. RESET PERSIST; -- 删除所有持久化配置

复制代码

2.4.9 配置最佳实践

以下是一些MySQL配置的最佳实践：

• 基于实际需求配置：根据应用步伐的实际需求和服务器的硬件资源调解配置。
  
• 监控和调解：定期监控MySQL的性能，并根据监控结果调解配置。
  
• 增量调解：一次只调解一个或少数几个参数，然后观察效果，避免大规模更改导致问题难以诊断。
  
• 记录更改：记录全部配置更改及其效果，以便将来参考。
  
• 使用默认值作为出发点：MySQL的默认配置在大多数情况下是合理的，可以作为调解的出发点。
  
• 考虑工作负载类型：不同类型的工作负载（如OLTP、OLAP）必要不同的配置优化。
  
• 定期审查安全设置：定期检查和更新安全配置，确保数据库的安全性。
  
• 备份配置文件：在修改配置文件之前，始终创建备份，以便在出现问题时可以规复。
  
• 测试环境验证：在生产环境应用配置更改之前，先在测试环境中验证。
  
• 使用配置管理工具：对于多服务器环境，考虑使用配置管理工具（如Ansible、Puppet或Chef）来管理MySQL配置。
  

通过合理配置MySQL，可以显著进步数据库的性能、安全性和可靠性，为应用步伐提供稳固高效的数据存储和检索服务。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告