[mysqld]
# 存储引擎配置
default_storage_engine=InnoDB
innodb_file_per_table=1

# 系统表空间配置
innodb_data_file_path=ibdata1:1G:autoextend

# 临时表空间配置
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:50G

# 重做日志配置
innodb_log_files_in_group=2
innodb_log_file_size=1G
innodb_log_group_home_dir=/mysql/logs

# 保存并退出后重启MySQL
systemctl restart mysqld

# 验证配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_file_per_table’; SHOW VARIABLES LIKE
‘innodb_data_file_path’; SHOW VARIABLES LIKE ‘innodb_log_file_size’;”
Enter password: Fgedu123!
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| innodb_file_per_table | ON |
+———————–+——-+
+————————+——————-+
| Variable_name | Value |
+————————+——————-+
| innodb_data_file_path | ibdata1:1G:autoextend |
+————————+——————-+
+—————————+————+
| Variable_name | Value |
+—————————+————+
| innodb_log_file_size | 1073741824 |
+—————————+————+

# 查看独立表空间文件
ls -l /mysql/data/fgedudb/
-rw-r—–. 1 mysql mysql 8632 Apr 2 10:30 fgedu_test.frm
-rw-r—–. 1 mysql mysql 98304 Apr 2 10:30 fgedu_test.ibd

# 配置表空间自动扩展
mysql -u root -p -e “ALTER TABLE fgedudb.fgedu_test AUTOEXTEND_SIZE = 64M;”
Enter password: Fgedu123!

# I/O线程配置
innodb_read_io_threads=16
innodb_write_io_threads=16

# I/O容量配置（根据存储设备性能调整）
innodb_io_capacity=4000
innodb_io_capacity_max=8000

# 异步I/O配置
innodb_use_native_aio=1

# 刷新策略配置
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2

# 保存配置并重启MySQL
systemctl restart mysqld

# 验证缓冲池配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’; SHOW VARIABLES LIKE
‘innodb_buffer_pool_instances’;”
Enter password: Fgedu123!
+————————-+—————-+——+——–+—————————-+—————+
| Variable_name | Value | Type | Access | Context | Comment |
+————————-+—————-+——+——–+—————————-+—————+
| innodb_buffer_pool_size | 17179869184 | bigint | SYS_VAR | GLOBAL | Buffer pool size in bytes. |
+————————-+—————-+——+——–+—————————-+—————+
+——————————-+——-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+——————————-+——-+——+——–+———————-+——————————+
| innodb_buffer_pool_instances | 8 | int | SYS_VAR | GLOBAL | Number of buffer pool instances. |
+——————————-+——-+——+——–+———————-+——————————+

风哥提示：缓冲池大小应根据服务器内存和其他应用程序的内存需求进行合理分配，避免内存不足导致系统性能下降。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 SSD存储配置案例

# 配置NVMe SSD用于MySQL数据存储
mkfs.xfs -f -m reflink=1 /dev/nvme0n1p1
mkdir -p /mysql/data
mount -t xfs -o noatime,nodiratime,discard /dev/nvme0n1p1 /mysql/data

# 设置权限
chown -R mysql:mysql /mysql/data

# 更新/etc/fstab
UUID=$(blkid -s UUID -o value /dev/nvme0n1p1)
echo “UUID=$UUID /mysql/data xfs defaults,noatime,nodiratime,discard 0 2” >> /etc/fstab

# 优化表空间
mysql -u root -p -e “USE fgedudb; OPTIMIZE TABLE fgedu_test;”
Enter password: Fgedu123!
+—————–+————+———-+——————————————————————-+
| Table | Op | Msg_type | Msg_text |
+—————–+————+———-+——————————————————————-+
| fgedudb.fgedu_test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| fgedudb.fgedu_test | optimize | status | OK |
+—————–+————+———-+——————————————————————-+

# 调整表结构优化存储
mysql -u root -p -e “USE fgedudb; ALTER TABLE fgedu_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;”
Enter password: Fgedu123!

# 测试随机读性能
sysbench –test=fileio –file-total-size=10G –file-test-mode=rndrd –time=30 –max-requests=0 run

# 测试随机写性能
sysbench –test=fileio –file-total-size=10G –file-test-mode=rndwr –time=30 –max-requests=0 run

# 清理测试文件
sysbench –test=fileio –file-total-size=10G cleanup

# 问题2：存储性能下降
# 解决方案：
1. 检查存储设备健康状态：smartctl -a /dev/sdb
2. 检查I/O负载：iostat -x 1
3. 优化SQL查询减少I/O操作
4. 调整缓冲池大小和I/O参数

# 问题3：临时表空间过大
# 解决方案：
1. 限制临时表空间大小：innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:50G
2. 优化使用临时表的查询
3. 定期重启MySQL释放临时表空间

# 监控磁盘空间
df -h /mysql

# 监控MySQL I/O状态
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_data%’; SHOW GLOBAL STATUS LIKE ‘Innodb_log%’;”
Enter password: Fgedu123!

# 创建存储监控脚本
cat > /mysql/scripts/storage_monitor.sh << 'EOF' #!/bin/bash # storage_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn DATE=$(date +"%Y-%m-%d %H:%M:%S") DISK_USAGE=$(df -h /mysql | grep /mysql | awk '{print $5}' | sed 's/%//' ) IO_STATS=$(iostat -x -d /dev/sdb 1 1 | grep sdb | awk '{print $6, $7, $14, $15}' ) # 记录监控数据 echo "$DATE, Disk Usage: ${DISK_USAGE}%, IO Stats: $IO_STATS">> /mysql/logs/storage_monitor.log

# 如果磁盘使用率超过80%，发送告警
if [ $DISK_USAGE -gt 80 ]; then
echo “MySQL存储使用率超过80%: ${DISK_USAGE}%” | mail -s “MySQL存储告警” admin@fgedu.net.cn
fi
EOF

# 赋予执行权限并添加到定时任务
chmod +x /mysql/scripts/storage_monitor.sh
crontab -l > /tmp/crontab.tmp
echo “*/5 * * * * /mysql/scripts/storage_monitor.sh” >> /tmp/crontab.tmp
crontab /tmp/crontab.tmp
rm /tmp/crontab.tmp

from MySQL:www.itpux.com