greatsql教程FG004-GreatSQL配置文件与参数优化
内容简介
本教程详细介绍GreatSQL数据库的配置文件结构、核心参数配置以及性能优化参数设置。风哥教程参考GreatSQL官方文档配置指南，帮助读者掌握GreatSQL的配置方法和参数调优技巧。
配置文件和参数优化是GreatSQL数据库性能调优的重要环节，合理的配置可以显著提升数据库的性能和稳定性。本教程将从配置文件结构入手，逐步深入到核心参数的配置和优化。
目录大纲
Part01-基础概念与理论知识
1.1 配置文件概述
GreatSQL的配置文件是用来存储数据库配置参数的文件，通过修改配置文件可以调整数据库的行为和性能。配置文件的主要作用包括：
- 设置数据库的基本参数
- 调整内存使用
- 配置存储引擎
- 设置日志参数
- 配置安全参数
1.2 配置文件结构
GreatSQL的配置文件采用分段结构，主要包括以下段落：
- [mysqld]：MySQL服务器的配置
- [mysql]：MySQL客户端的配置
- [mysqld_safe]：mysqld_safe脚本的配置
- [client]：所有客户端的通用配置
1.3 参数分类与作用
GreatSQL的参数可以分为以下几类：
- 基础参数：如basedir、datadir、port等
- 内存参数：如innodb_buffer_pool_size、key_buffer_size等
- 存储参数：如innodb_file_per_table、innodb_log_file_size等
- 性能参数：如max_connections、query_cache_size等
- 安全参数：如skip-networking、bind-address等
Part02-生产环境规划与建议
2.1 配置文件位置
风哥提示：生产环境建议将配置文件放在标准位置，便于管理和维护。
GreatSQL配置文件的默认位置：
- Linux系统：/etc/my.cnf 或 /etc/mysql/my.cnf
- Windows系统：my.ini 文件
2.2 核心参数规划
核心参数规划建议：
- 基础配置：basedir、datadir、port、socket
- 字符集配置：character-set-server、collation-server
- 内存配置：innodb_buffer_pool_size、key_buffer_size
- 连接配置：max_connections、wait_timeout
- 日志配置：log-error、slow_query_log、general_log
2.3 性能参数建议
性能参数建议值：
| 参数 | 建议值 | 说明 |
|---|---|---|
| innodb_buffer_pool_size | 物理内存的50-70% | InnoDB缓冲池大小 |
| max_connections | 根据业务需求设置 | 最大连接数 |
| innodb_log_file_size | 256M-1G | InnoDB日志文件大小 |
| innodb_file_per_table | 1 | 每个表使用独立表空间 |
更多视频教程www.fgedu.net.cn
Part03-生产环境项目实施方案
3.1 配置文件模板
生产环境配置文件模板：
[mysqld]
# 基础配置
basedir = /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64
datadir = /greatsql/fgdata
port = 3306
socket = /tmp/mysql.sock
pid-file = /greatsql/fgdata/fgedu.net.cn.pid
# 基础配置
basedir = /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64
datadir = /greatsql/fgdata
port = 3306
socket = /tmp/mysql.sock
pid-file = /greatsql/fgdata/fgedu.net.cn.pid
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# 内存配置
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
key_buffer_size = 256M
# 连接配置
max_connections = 1000
max_connect_errors = 100000
wait_timeout = 3600
interactive_timeout = 3600
# 存储配置
innodb_file_per_table = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
# 性能配置
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 日志配置
log-error = /greatsql/logs/error.log
slow_query_log = 1
slow_query_log_file = /greatsql/logs/slow.log
long_query_time = 2
general_log = 0
general_log_file = /greatsql/logs/general.log
# 安全配置
skip-networking = 0
bind-address = 0.0.0.0
# 其他配置
server-id = 1
expire_logs_days = 7
3.2 参数调优策略
参数调优策略：
- 根据服务器硬件配置调整内存参数
- 根据业务特点调整连接参数
- 根据存储设备性能调整I/O参数
- 根据业务需求调整日志参数
- 定期监控参数效果，持续优化
3.3 配置文件管理
配置文件管理建议：
- 使用版本控制系统管理配置文件
- 定期备份配置文件
- 修改配置文件前先备份
- 记录配置变更历史
- 使用配置管理工具统一管理
Part04-生产案例与实战讲解
4.1 配置文件创建与修改
# 查看当前配置文件位置
mysql –help | grep my.cnf
mysql –help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
# 创建配置文件
cat > /etc/my.cnf << EOF [mysqld] basedir = /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64 datadir = /greatsql/fgdata port = 3306 socket = /tmp/mysql.sock pid-file = /greatsql/fgdata/fgedu.net.cn.pid character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci innodb_buffer_pool_size = 8G max_connections = 1000 log-error = /greatsql/logs/error.log slow_query_log = 1 slow_query_log_file = /greatsql/logs/slow.log long_query_time = 2 EOF
cat > /etc/my.cnf << EOF [mysqld] basedir = /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64 datadir = /greatsql/fgdata port = 3306 socket = /tmp/mysql.sock pid-file = /greatsql/fgdata/fgedu.net.cn.pid character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci innodb_buffer_pool_size = 8G max_connections = 1000 log-error = /greatsql/logs/error.log slow_query_log = 1 slow_query_log_file = /greatsql/logs/slow.log long_query_time = 2 EOF
学习交流加群风哥微信: itpux-com
4.2 核心参数配置
# 查看当前参数值
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 8589934592 |
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 8589934592 |
+————————-+————+
# 查看字符集配置
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘%character%’;”
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘%character%’;”
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /greatsql/app/…/charsets/ |
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /greatsql/app/…/charsets/ |
+————————–+—————————-+
# 查看连接配置
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘max_connections’;”
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘max_connections’;”
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 1000 |
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 1000 |
+—————–+——-+
4.3 性能参数调优
# 调整InnoDB缓冲池大小
mysql -u root -pFGedu123456! -e “SET GLOBAL innodb_buffer_pool_size = 8589934592;”
mysql -u root -pFGedu123456! -e “SET GLOBAL innodb_buffer_pool_size = 8589934592;”
Query OK, 0 rows affected (0.00 sec)
# 调整最大连接数
mysql -u root -pFGedu123456! -e “SET GLOBAL max_connections = 1500;”
mysql -u root -pFGedu123456! -e “SET GLOBAL max_connections = 1500;”
Query OK, 0 rows affected (0.00 sec)
# 开启慢查询日志
mysql -u root -pFGedu123456! -e “SET GLOBAL slow_query_log = 1;” mysql -u root -pFGedu123456! -e “SET GLOBAL long_query_time = 1;”
mysql -u root -pFGedu123456! -e “SET GLOBAL slow_query_log = 1;” mysql -u root -pFGedu123456! -e “SET GLOBAL long_query_time = 1;”
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
学习交流加群风哥QQ113257174
# 查看慢查询日志配置
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘slow_query%’;” mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘long_query_time’;”
mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘slow_query%’;” mysql -u root -pFGedu123456! -e “SHOW VARIABLES LIKE ‘long_query_time’;”
+———————+—————————+
| Variable_name | Value |
+———————+—————————+
| slow_query_log | ON |
| slow_query_log_file | /greatsql/logs/slow.log |
+———————+—————————+
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 1.000000 |
+—————–+———-+
| Variable_name | Value |
+———————+—————————+
| slow_query_log | ON |
| slow_query_log_file | /greatsql/logs/slow.log |
+———————+—————————+
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 1.000000 |
+—————–+———-+
# 重启GreatSQL服务使配置生效
systemctl restart greatsql
systemctl restart greatsql
# 检查服务状态
systemctl status greatsql
systemctl status greatsql
● greatsql.service – GreatSQL Server
Loaded: loaded (/etc/systemd/system/greatsql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-09 11:00:00 CST; 1min ago
Process: 12345 ExecStart=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld_safe –user=greatsql (code=exited, status=0/SUCCESS)
Main PID: 12456 (mysqld)
Tasks: 38 (limit: 16384)
Memory: 1.5G
CGroup: /system.slice/greatsql.service
├─12456 /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld –basedir=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64 –datadir=/greatsql/fgdata –plugin-dir=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/lib/plugin –user=greatsql –log-error=/greatsql/logs/error.log –pid-file=/greatsql/fgdata/fgedu.net.cn.pid –socket=/tmp/mysql.sock –port=3306
└─12457 /bin/sh /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld_safe –user=greatsql
Apr 09 11:00:00 fgedu.net.cn systemd[1]: Starting GreatSQL Server…
Apr 09 11:00:01 fgedu.net.cn systemd[1]: Started GreatSQL Server.
Loaded: loaded (/etc/systemd/system/greatsql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-09 11:00:00 CST; 1min ago
Process: 12345 ExecStart=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld_safe –user=greatsql (code=exited, status=0/SUCCESS)
Main PID: 12456 (mysqld)
Tasks: 38 (limit: 16384)
Memory: 1.5G
CGroup: /system.slice/greatsql.service
├─12456 /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld –basedir=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64 –datadir=/greatsql/fgdata –plugin-dir=/greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/lib/plugin –user=greatsql –log-error=/greatsql/logs/error.log –pid-file=/greatsql/fgdata/fgedu.net.cn.pid –socket=/tmp/mysql.sock –port=3306
└─12457 /bin/sh /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld_safe –user=greatsql
Apr 09 11:00:00 fgedu.net.cn systemd[1]: Starting GreatSQL Server…
Apr 09 11:00:01 fgedu.net.cn systemd[1]: Started GreatSQL Server.
Part05-风哥经验总结与分享
5.1 常见配置问题与解决方案
| 问题 | 解决方案 |
|---|---|
| 配置文件语法错误 | 检查配置文件语法，使用mysqld –validate-config验证 |
| 参数值设置不合理 | 根据服务器硬件和业务需求调整参数值 |
| 配置文件权限问题 | 确保配置文件权限正确，建议644权限 |
| 参数修改后不生效 | 检查参数是否需要重启服务，或使用SET GLOBAL命令 |
5.2 配置最佳实践
- 使用标准化的配置文件模板
- 根据服务器硬件配置调整内存参数
- 定期监控参数效果，持续优化
- 记录配置变更历史，便于回滚
- 使用配置管理工具统一管理多台服务器
更多学习教程公众号风哥教程itpux_com
5.3 配置文件备份与恢复
# 备份配置文件 cp /etc/my.cnf /etc/my.cnf.$(date +%Y%m%d)
# 查看备份文件 ls -l /etc/my.cnf*
-rw-r–r– 1 root root 1000 Apr 9 11:00 /etc/my.cnf
-rw-r–r– 1 root root 1000 Apr 9 10:00 /etc/my.cnf.20260409
-rw-r–r– 1 root root 1000 Apr 9 10:00 /etc/my.cnf.20260409
# 恢复配置文件 cp /etc/my.cnf.20260409 /etc/my.cnf
systemctl restart greatsql
systemctl restart greatsql
配置文件验证
# 验证配置文件 /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld –validate-config
2026-04-09T03:00:00.000000Z 0 [System] [MY-010116] [Server] /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld (mysqld 8.0.32-24) starting as process 12345
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010091] [Server] Can’t create test file /greatsql/fgdata/fgedu.net.cn.lower-test
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /greatsql/fgdata is case insensitive
2026-04-09T03:00:00.000000Z 0 [System] [MY-010116] [Server] /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld (mysqld 8.0.32-24) starting as process 12346
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010091] [Server] Can’t create test file /greatsql/fgdata/fgedu.net.cn.lower-test
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /greatsql/fgdata is case insensitive
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010091] [Server] Can’t create test file /greatsql/fgdata/fgedu.net.cn.lower-test
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /greatsql/fgdata is case insensitive
2026-04-09T03:00:00.000000Z 0 [System] [MY-010116] [Server] /greatsql/app/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64/bin/mysqld (mysqld 8.0.32-24) starting as process 12346
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010091] [Server] Can’t create test file /greatsql/fgdata/fgedu.net.cn.lower-test
2026-04-09T03:00:00.000000Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /greatsql/fgdata is case insensitive
from greatsql视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息