# 查看MySQL配置文件位置
# mysqld –help –verbose | grep -A 1 “Default options”
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

# 查看配置文件内容
# cat /etc/my.cnf
[mysqld]
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 编辑MySQL配置文件
# vi /etc/my.cnf
[mysqld]
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

# 检查MySQL服务状态
# systemctl status mysqld
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2026-04-01 10:00:00 CST; 1h ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1234 ExecStart=/usr/sbin/mysqld –daemonize –pid-file=/var/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
Main PID: 1235 (mysqld)
Status: “Server is operational”
CGroup: /system.slice/mysqld.service
└─1235 /usr/sbin/mysqld –daemonize –pid-file=/var/run/mysqld/mysqld.pid

# 优化MySQL配置
# vi /etc/my.cnf
[mysqld]
# 基本配置
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 内存配置
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
key_buffer_size = 256M

# IO配置
innodb_log_file_size = 2G
innodb_log_buffer_size = 16M
innodb_flush_method = O_DIRECT

# 连接配置
max_connections = 2000
max_connect_errors = 10000
wait_timeout = 300

# 查询配置
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M

# 安全配置
skip-networking = 0
bind-address = 0.0.0.0

# 重启MySQL服务
# systemctl restart mysqld

# 验证配置是否生效
# mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
Enter password:
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 8589934592 |
+————————-+————+

# 创建MySQL配置管理脚本
# vi mysql_config.sh
#!/bin/bash
# mysql_config.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

CONFIG_FILE=”/etc/my.cnf”
BACKUP_DIR=”/backup/config”
DATE=$(date +%Y%m%d)

mkdir -p $BACKUP_DIR

# 备份当前配置
cp $CONFIG_FILE $BACKUP_DIR/my.cnf_$DATE

echo “Current MySQL configuration backup created: $BACKUP_DIR/my.cnf_$DATE”

# 检查配置文件
if [ -f $CONFIG_FILE ]; then
echo “MySQL configuration file exists: $CONFIG_FILE”
else
echo “MySQL configuration file not found: $CONFIG_FILE”
exit 1
fi

# 显示当前配置
echo “\nCurrent MySQL configuration:”
grep -v “^#” $CONFIG_FILE | grep -v “^$”

# 提示用户输入新配置
echo “\nEnter new MySQL configuration parameters (press Ctrl+D when done):”
cat > $CONFIG_FILE.new

# 验证新配置
echo “\nNew MySQL configuration:”
grep -v “^#” $CONFIG_FILE.new | grep -v “^$”

# 应用新配置
read -p “Apply new configuration? (y/n): ” APPLY
if [ “$APPLY” = “y” ]; then
cp $CONFIG_FILE.new $CONFIG_FILE
systemctl restart mysqld
echo “\nMySQL configuration applied and service restarted.”
else
echo “\nMySQL configuration not applied.”
fi

# 执行脚本
# chmod +x mysql_config.sh
# ./mysql_config.sh