Mysql
概念
- 单进程, 多线程
- 存储引擎可选
- 8.4 版本引入 LTS, 支持到29年4月30日
/usr/local/mysql/ # MySQL 程序目录（软链接）
/etc/mysql/my.cnf # 配置文件
/data/mysql/
├── data/ # 数据文件目录
├── logs/
│ ├── error.log # 错误日志
│ ├── slow.log # 慢查询日志
│ ├── mysql-bin.* # 二进制日志
│ └── relay-bin.* # 中继日志（从库）
├── tmp/ # 临时文件目录
└── run/
├── mysql.pid # PID 文件
└── mysql.sock # Unix Socket
二进制安装
1. 安装依赖
# RHEL/CentOS/Rocky
yum install -y libaio numactl-libs libncurses6 libtinfo perl
# Ubuntu/Debian
apt install -y libaio1 libnuma1 libncurses6 perl
# 验证是否有缺失的依赖库
# ldd /usr/local/mysql/bin/mysqld | grep "not found"
# ldconfig
2. 下载解压
cd /opt
# md5sum 命令查看校验
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
tar -xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz -C /usr/local
ln -s /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql
3. 创建用户和目录
# 创建 mysql 系统用户（无登录 shell）
groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin -M mysql
# 创建数据目录结构
mkdir -p /data/mysql/{data,logs,tmp,run}
mkdir -p /etc/mysql
4. 配置环境变量
cat >> /etc/profile.d/mysql.sh << 'EOF'
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
EOF
source /etc/profile.d/mysql.sh
5. 修改目录权限
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /usr/local/mysql
chmod 750 /data/mysql/data
6. 配置文件
/etc/mysql/my.cnf
[client]
port = 3306
socket = /data/mysql/run/mysql.sock
default-character-set = utf8mb4
[mysql]
prompt = "\\u@\\h:\\p [\\d]> "
no-auto-rehash
[mysqld]
########################################
# 基础设置
########################################
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/run/mysql.sock
pid-file = /data/mysql/run/mysql.pid
tmpdir = /data/mysql/tmp
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
########################################
# 连接与线程
########################################
max_connections = 1000
max_connect_errors = 10000
back_log = 1024
interactive_timeout = 3600
wait_timeout = 3600
thread_stack = 512K
thread_cache_size = 64
########################################
# InnoDB 引擎（核心）
########################################
default_storage_engine = InnoDB
innodb_buffer_pool_size = 8G # 推荐为内存的 50%~75%
innodb_buffer_pool_instances = 8 # buffer_pool_size / 1GB，最多 64
innodb_buffer_pool_chunk_size = 128M
innodb_redo_log_capacity = 3G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 # 1=最安全，0/2 性能更好但有风险
innodb_flush_method = O_DIRECT # 避免双重缓存（推荐 SSD）
innodb_io_capacity = 2000 # SSD 设 2000-4000，HDD 设 200
innodb_io_capacity_max = 4000
innodb_file_per_table = ON # 每表独立 ibd 文件
innodb_open_files = 4096
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_lock_wait_timeout = 10
innodb_deadlock_detect = ON
innodb_print_all_deadlocks = ON
# 双写缓冲（MySQL 8.0.20+ 移到独立文件）
innodb_doublewrite = ON
########################################
# Binary Log（主从复制 / 数据恢复）
########################################
log_bin = /data/mysql/logs/mysql-bin
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800 # 保留 7 天
max_binlog_size = 512M
sync_binlog = 1 # 与 flush_log_at_trx_commit=1 配合保证双一
log_replica_updates = ON # 级联复制时开启
binlog_cache_size = 4M
binlog_stmt_cache_size = 1M
########################################
# GTID（推荐开启，简化主从切换）
########################################
gtid_mode = ON
enforce_gtid_consistency = ON
########################################
# 慢查询日志
########################################
slow_query_log = ON
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 2 # 超过 2 秒记录
log_queries_not_using_indexes = ON
min_examined_row_limit = 100
########################################
# 错误日志
########################################
log_error = /data/mysql/logs/error.log
log_error_verbosity = 2
log_timestamps = SYSTEM
########################################
# 通用查询日志（默认关闭，调试时开启）
########################################
general_log = OFF
general_log_file = /data/mysql/logs/general.log
########################################
# 表缓存
########################################
table_open_cache = 4096
table_definition_cache = 2048
open_files_limit = 65536
########################################
# 排序与连接缓冲
########################################
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
########################################
# 安全设置
########################################
local_infile = OFF # 禁用 LOAD DATA LOCAL
skip_name_resolve = ON # 跳过 DNS 解析，提升连接速度
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
########################################
# 主从复制（从库配置）
########################################
# server_id = 2 # 集群内唯一，主库设 1
# read_only = ON # 从库只读
# super_read_only = ON # 超级权限也只读
# relay_log = /data/mysql/logs/relay-bin
# relay_log_recovery = ON
########################################
# 性能模式
########################################
performance_schema = ON
performance_schema_instrument = 'wait/%=ON'
########################################
# 其他
########################################
lower_case_table_names = 1 # 表名大小写不敏感（Linux 推荐设 1）
explicit_defaults_for_timestamp = ON
group_concat_max_len = 1048576
max_allowed_packet = 256M
net_buffer_length = 32K
bulk_insert_buffer_size = 64M
concurrent_insert = 2
7. 初始化数据库
/usr/local/mysql/bin/mysqld \
--defaults-file=/etc/mysql/my.cnf \
--initialize \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/data/mysql/data
# 查看初始临时密码
grep 'temporary password' /data/mysql/logs/error.log
8. systemd 服务
cat > /usr/lib/systemd/system/mysqld.service << 'EOF'
[Unit]
Description=MySQL 8.4 Database Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Service]
Type=notify
# 运行用户
User=mysql
Group=mysql
# 主进程
ExecStart=/usr/local/mysql/bin/mysqld \
--defaults-file=/etc/mysql/my.cnf \
--daemonize=OFF \
--pid-file=/data/mysql/run/mysql.pid
# 优雅重启：发送 SIGTERM，等待进程退出后再启动
ExecReload=/bin/kill -HUP $MAINPID
# 超时设置
TimeoutStartSec=300
TimeoutStopSec=300
# 异常重启策略
Restart=on-failure
RestartSec=5s
# 资源限制
LimitNOFILE=65536
LimitNPROC=65536
LimitMEMLOCK=infinity
LimitCORE=0
# 安全加固（可选）
PrivateTmp=true
ProtectSystem=full
NoNewPrivileges=true
[Install]
WantedBy=multi-user.target
EOF
9. 安全配置
# 使用临时密码登录
mysql -u root -p
# 修改 root 密码（替换 YourStrongPassword）
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YourStrongPassword@123';
# 创建管理员账号（不推荐直接使用 root 远程连接）
CREATE USER 'admin'@'%' IDENTIFIED WITH caching_sha2_password BY 'AdminPassword@456';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 删除匿名用户和测试库
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
FLUSH PRIVILEGES;
常用命令
# 连接数据库
mysql -u root -p -S /data/mysql/run/mysql.sock
# 备份（mysqldump）
mysqldump -u root -p --single-transaction --all-databases \
| gzip > /backup/all-databases-$(date +%F).sql.gz
# 检查运行状态
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_%';"
mysql -u root -p -e "SHOW PROCESSLIST;"
系统优化
内核参数 /etc/sysctl.conf
# 虚拟内存
vm.swappiness = 10
vm.dirty_ratio = 15
vm.dirty_background_ratio = 5
# 网络
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
# 文件描述符
fs.file-max = 6815744
fs.aio-max-nr = 1048576
命令 sysctl -p 使生效
pam 模块
cat >> /etc/security/limits.conf << 'EOF'
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
EOF