1.配置文件参数

1.1 基础配置

[mysqld]
# 基础设置
datadir=/var/lib/mysql           # 数据文件存储路径
socket=/var/lib/mysql/mysql.sock  # Unix套接字路径
pid-file=/var/run/mysqld/mysqld.pid  # 进程ID文件
port=3306                        # 服务端口
user=mysql                       # 运行用户

# 字符集设置（重要！避免乱码）
character-set-server=utf8mb4     # 服务器默认字符集
collation-server=utf8mb4_unicode_ci  # 默认排序规则
init_connect='SET NAMES utf8mb4'  # 客户端连接时自动设置

# 网络与安全
bind-address=0.0.0.0             # 监听所有IP（生产环境建议指定具体IP）
skip-name-resolve                # 禁用DNS解析，提高性能

1.2 内存和性能优化

# 内存分配（根据服务器总内存调整）
innodb_buffer_pool_size=1G       # InnoDB缓存池大小（通常占总内存50-75%）
key_buffer_size=128M             # MyISAM索引缓存
query_cache_size=64M             # 查询缓存大小（5.7后逐渐弃用，8.0移除）

# 日志配置
log-bin=mysql-bin                # 启用二进制日志（主从复制必须）
expire-logs-days=7               # 二进制日志保留天数
slow-query-log=1                 # 开启慢查询日志
slow-query-log-file=/var/log/mysql/slow.log  # 慢查询日志路径
long_query_time=2                # 超过2秒的查询记录到慢日志

1.3 连接与线程

max_connections=200              # 最大连接数
back_log=100                     # 连接队列长度
wait_timeout=28800               # 空闲连接超时时间（秒）

2. 日常管理命令

2.1 服务控制

# 启动/停止/重启服务（systemd方式）
systemctl start mysql
systemctl stop mysql
systemctl restart mysql

# 查看服务状态
systemctl status mysql

# 查看端口监听
netstat -tlnp | grep 3306

2.2 客户端连接

# 连接本地MySQL
mysql -u root -p

# 连接远程MySQL
mysql -h 192.168.1.100 -P 3306 -u user -p

# 执行SQL脚本
mysql -u root -p < script.sql

2.3 数据库操作

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE mydb;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

2.4 表操作

-- 创建表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE
);

-- 查看表结构
DESCRIBE users;

-- 删除表
DROP TABLE users;

-- 查看所有表
SHOW TABLES;

2.5 用户与权限管理

-- 创建用户并授权（允许从任意主机连接）
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'%';
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'user'@'%';

-- 撤销权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'user'@'%';

-- 删除用户
DROP USER 'user'@'%';

3. 性能优化命令

3.1 查看状态与变量

-- 查看服务器状态
SHOW STATUS;

-- 查看配置变量
SHOW VARIABLES;

-- 查看InnoDB缓冲池状态
SHOW ENGINE INNODB STATUS;

-- 查看当前连接
SHOW PROCESSLIST;

3.2 优化查询

-- 分析查询性能
EXPLAIN SELECT * FROM users WHERE name = 'test';

-- 检查表碎片并优化
ANALYZE TABLE users;
OPTIMIZE TABLE users;

4. 备份和恢复

4.1 mysqldump 逻辑备份

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

4.2 物理备份（xtrabackup）

# 全量备份
xtrabackup --backup --user=root --password=password --target-dir=/backup/full/

# 恢复备份
xtrabackup --prepare --target-dir=/backup/full/
xtrabackup --copy-back --target-dir=/backup/full/

5. 安全配置

5.1 修改 root 密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.2 禁用远程 root 登录

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

5.3 仅允许特定IP访问3306端口（CentOS）

firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  port protocol="tcp" port="3306" accept
firewall-cmd --reload

6. 常用运维脚本

6.1 查看 MySQL 占用内存

ps -eo pid,user,%mem,args | grep mysqld | grep -v grep

6.2 监控慢查询日志

pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt

6.3 定期备份脚本

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
USER="root"
PASSWORD="password"

mkdir -p $BACKUP_DIR/$DATE
mysqldump -u$USER -p$PASSWORD --all-databases > $BACKUP_DIR/$DATE/all_databases.sql

# 删除7天前的备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;