MySQL 安装配置
装 MySQL、跑安全初始化、建库建用户、开远程与调参。
Key Points装完先跑 mysql_secure_installation；建库用 utf8mb4，给应用单独建账号只授权需要的库。
安装
Bash
# Ubuntu / Debian
sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql
# RHEL / Rocky
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqldBash
mysql --version
sudo systemctl status mysql --no-pager安全初始化
Bash
sudo mysql_secure_installation
# 依次回答：设置 root 密码 / 删除匿名用户 / 禁止 root 远程 / 删除 test 库 / 重载权限建库与用户
SQL
CREATE DATABASE app
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'localhost';
FLUSH PRIVILEGES;
SHOW DATABASES;
SELECT user, host FROM mysql.user;Never grant the app
ALL PRIVILEGES ON *.*. A single SQL injection then dumps every database. Grant only the schemas and verbs it needs.允许远程连接
Bash
# /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = 0.0.0.0
sudo systemctl restart mysqlSQL
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';
FLUSH PRIVILEGES;'app'@'localhost' and 'app'@'10.0.0.%' are different accounts. When a remote connection fails, check mysql.user for a matching host row.
配置与调参
INI
[mysqld]
bind-address = 127.0.0.1
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
max_connections = 200
innodb_buffer_pool_size = 1G
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1| Parameter | Rule of thumb |
|---|---|
innodb_buffer_pool_size | 50-70% of RAM on a dedicated DB host |
max_connections | Sum of app pool sizes + headroom |
long_query_time | 1s is a reasonable start |
innodb_flush_log_at_trx_commit | 1 for durability, 2 for speed |
备份与恢复
Bash
mysqldump -u root -p --single-transaction --databases app > app-$(date +%F).sql
mysql -u root -p < app-2026-10-31.sqlA backup is not done until you have restored it once. Unverified backups usually fail exactly when you need them — permissions, charset or FK order.
排查
Bash
sudo journalctl -u mysql -n 50 --no-pager
mysql -u root -p -e 'SHOW PROCESSLIST;'
mysql -u root -p -e 'SHOW ENGINE INNODB STATUS\G' | head -40安装
Bash
# Ubuntu / Debian
sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql
# RHEL / Rocky
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqldBash
mysql --version
sudo systemctl status mysql --no-pager安全初始化
Bash
sudo mysql_secure_installation
# 依次回答：设置 root 密码 / 删除匿名用户 / 禁止 root 远程 / 删除 test 库 / 重载权限建库与用户
SQL
CREATE DATABASE app
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'localhost';
FLUSH PRIVILEGES;
SHOW DATABASES;
SELECT user, host FROM mysql.user;不要给应用
ALL PRIVILEGES ON *.*。一个 SQL 注入就能拖走整台实例的所有库，只授它真正需要的那几个库和那几种操作。允许远程连接
Bash
# /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = 0.0.0.0
sudo systemctl restart mysqlSQL
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';
FLUSH PRIVILEGES;'app'@'localhost' 和 'app'@'10.0.0.%' 是两个不同的账号。远程连不上时先看 mysql.user 里有没有对应 host 的记录。
配置与调参
INI
[mysqld]
bind-address = 127.0.0.1
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
max_connections = 200
innodb_buffer_pool_size = 1G
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1| 参数 | 经验值 |
|---|---|
innodb_buffer_pool_size | 专用库机给内存的 50-70% |
max_connections | 各应用连接池之和 + 余量 |
long_query_time | 1 秒起步 |
innodb_flush_log_at_trx_commit | 1 保安全，2 换性能 |
备份与恢复
Bash
mysqldump -u root -p --single-transaction --databases app > app-$(date +%F).sql
mysql -u root -p < app-2026-10-31.sql备份一定要实际恢复一次再算完成。没验证过的备份，在真出事的时候通常恢复不了——权限、字符集、外键顺序都可能出错。
排查
Bash
sudo journalctl -u mysql -n 50 --no-pager
mysql -u root -p -e 'SHOW PROCESSLIST;'
mysql -u root -p -e 'SHOW ENGINE INNODB STATUS\G' | head -40