openSUSE数据库部署实战指南 从零开始掌握MySQL与PostgreSQL安装配置及性能优化技巧

引言:为什么选择openSUSE部署数据库
openSUSE作为一款稳定、安全的Linux发行版,凭借其强大的包管理系统YaST和Zypper,以及优秀的系统稳定性,成为部署数据库服务器的理想选择。无论是企业级应用还是个人开发项目,openSUSE都能提供可靠的运行环境。本文将详细介绍如何在openSUSE上部署、配置和优化MySQL与PostgreSQL两种主流数据库系统,帮助您从零开始掌握数据库管理的核心技能。
第一部分:环境准备与系统优化
1.1 系统更新与基础配置
在开始安装数据库之前,确保系统处于最新状态至关重要。首先更新系统软件包:
# 更新软件包索引
sudo zypper refresh

# 升级所有已安装的软件包
sudo zypper update

# 安装必要的系统工具
sudo zypper install -t pattern devel_basis
sudo zypper install wget curl vim htop iotop
1.2 配置防火墙
数据库服务通常需要开放特定端口,配置防火墙确保安全访问:
# 启用firewalld并设置开机自启
sudo systemctl enable firewalld
sudo systemctl start firewalld

# 开放MySQL默认端口3306
sudo firewall-cmd –permanent –add-port=3306/tcp

# 开放PostgreSQL默认端口5432
sudo firewall-cmd –permanent –add-port=5432/tcp

# 重新加载防火墙规则
sudo firewall-cmd –reload

# 查看已开放端口
sudo firewall-cmd –list-ports
1.3 磁盘I/O优化建议
数据库性能很大程度上依赖于磁盘I/O。建议将数据库数据目录放在独立的磁盘分区或SSD上:
# 查看磁盘信息
lsblk

# 创建独立的数据目录(假设使用独立磁盘/dev/sdb)
sudo mkdir /mnt/dbdata
sudo mkfs.ext4 /dev/sdb
sudo mount /dev/sdb /mnt/dbdata

# 永久挂载配置
echo “/dev/sdb /mnt/dbdata ext4 defaults 0 2” | sudo tee -a /etc/fstab

# 创建数据库专用目录
sudo mkdir -p /mnt/dbdata/mysql
sudo mkdir -p /mnt/dbdata/postgresql
sudo chown -R mysql:mysql /mnt/dbdata/mysql
sudo chown -R postgres:postgres /mnt/dbdata/postgresql
第二部分:MySQL部署与配置
2.1 安装MySQL服务器
openSUSE提供了MySQL和MariaDB两种选择,这里我们安装官方的MySQL:
# 添加MySQL官方仓库
sudo zypper addrepo https://repo.mysql.com/mysql80-community-release-openSUSE-15.3.noarch.rpm

# 安装MySQL服务器
sudo zypper install mysql-community-server

# 启动MySQL服务并设置开机自启
sudo systemctl enable mysqld
sudo systemctl start mysqld

# 查看MySQL服务状态
sudo systemctl status mysqld
2.2 初始安全配置
MySQL安装完成后,需要进行初始安全配置:
# 查看临时root密码
sudo grep ‘temporary password’ /var/log/mysqld.log

# 运行安全脚本
sudo mysql_secure_installation

# 按照提示操作:
# 1. 输入临时密码
# 2. 设置新的root密码(要求包含大小写字母、数字和特殊字符)
# 3. 是否移除匿名用户?(Y)
# 4. 是否禁止root远程登录?(N,根据需求选择)
# 5. 是否删除测试数据库?(Y)
# 6. 是否重新加载权限表?(Y)
2.3 基本配置与用户管理
创建专用数据库用户并配置权限:
— 登录MySQL
mysql -u root -p

— 创建新数据库
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

— 创建专用用户
CREATE USER ‘myapp_user’@’localhost’ IDENTIFIED BY ‘StrongPassword123!’;

— 授予用户权限
GRANT ALL PRIVILEGES ON myapp_db.* TO ‘myapp_user’@’localhost’;

— 允许远程访问(根据安全策略选择)
CREATE USER ‘myapp_user’@’%’ IDENTIFIED BY ‘StrongPassword123!’;
GRANT ALL PRIVILEGES ON myapp_db.* TO ‘myapp_user’@’%’;

— 刷新权限
FLUSH PRIVILEGES;

— 查看用户权限
SHOW GRANTS FOR ‘myapp_user’@’localhost’;
2.4 MySQL配置文件优化
MySQL的主配置文件位于
/etc/my.cnf
或
/etc/mysql/my.cnf
。根据服务器硬件配置进行优化:
# /etc/my.cnf 配置示例
[mysqld]
# 基础配置
datadir=/mnt/dbdata/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 连接配置
max_connections=200
max_connect_errors=100
connect_timeout=10

# 缓冲区配置(根据服务器内存调整)
innodb_buffer_pool_size=2G # 通常设置为可用内存的50-70%
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=2 # 平衡性能与数据安全
innodb_flush_method=O_DIRECT

# 查询缓存(MySQL 8.0已移除查询缓存,此处为旧版本参考)
# query_cache_type=1
# query_cache_size=64M

# 日志配置
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2

# 字符集配置
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4
2.5 性能优化技巧
2.5.1 使用Percona Toolkit进行监控
# 安装Percona Toolkit
sudo zypper install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 检查MySQL配置
pt-mysql-summary –user=root –password
2.5.2 索引优化实战
— 查看表索引
SHOW INDEX FROM myapp_db.users;

— 分析查询性能
EXPLAIN SELECT * FROM myapp_db.users WHERE email = ‘user@example.com’;

— 创建复合索引
CREATE INDEX idx_email_status ON myapp_db.users(email, status);

— 查看未使用的索引(需要开启per_schema)
SELECT * FROM sys.schema_unused_indexes;

— 使用覆盖索引优化查询
CREATE INDEX idx_cover ON myapp_db.users(email, name, created_at);
— 查询只需要扫描索引,无需回表
SELECT email, name FROM myapp_db.users WHERE email LIKE ‘user%’;
2.5.3 查询优化示例
— 优化前:全表扫描
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

— 优化后:使用索引范围扫描
SELECT * FROM orders WHERE created_at >= ‘2023-01-01’ AND created_at < '2024-01-01'; -- 避免SELECT * SELECT id, name, email FROM users WHERE id = 123; -- 使用JOIN替代子查询(优化前) SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

— 优化后:使用JOIN
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;
2.6 备份与恢复策略
2.6.1 物理备份(使用Percona XtraBackup)
# 安装Percona XtraBackup
sudo zypper install percona-xtrabackup-80

# 创建完整备份
sudo xtrabackup –backup –target-dir=/backup/mysql/full –user=root –password

# 准备备份
sudo xtrabackup –prepare –target-dir=/backup/mysql/full

# 还原备份
sudo systemctl stop mysqld
sudo xtrabackup –copy-back –target-dir=/backup/mysql/full –datadir=/mnt/dbdata/mysql
sudo chown -R mysql:mysql /mnt/dbdata/mysql
sudo systemctl start mysqld
2.6.2 逻辑备份与恢复
# 全量备份
mysqldump -u root -p –all-databases > /backup/mysql/all_databases_$(date +%Y%m%d).sql

# 单个数据库备份
mysqldump -u root -p myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d).sql

# 压缩备份
mysqldump -u root -p myapp_db | gzip > /backup/mysql/myapp_db_$(date +%Y%m%d).sql.gz

# 恢复数据库
mysql -u root -p myapp_db < /backup/mysql/myapp_db_20231001.sql # 增量备份(需要开启二进制日志) # 在my.cnf中添加 # log_bin=/mnt/dbdata/mysql/binlog # binlog_expire_logs_seconds=604800 # 7天 # 查看二进制日志 mysql -u root -p -e "SHOW BINARY LOGS;" # 使用时间点恢复 mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 12:00:00" /mnt/dbdata/mysql/binlog.000001 | mysql -u root -p 2.7 主从复制配置 2.7.1 主库配置 # /etc/my.cnf 主库配置 [mysqld] server-id=1 log_bin=/mnt/dbdata/mysql/binlog binlog_format=ROW binlog_expire_logs_seconds=604800 2.7.2 创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; -- 锁表并获取二进制日志位置 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; -- 记录File和Position值 2.7.3 从库配置 # /etc/my.cnf 从库配置 [mysqld] server-id=2 relay_log=/mnt/dbdata/mysql/relaylog read_only=1 2.7.4 启动复制 -- 在从库上执行 CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='binlog.000001', -- 从主库获取的File值 MASTER_LOG_POS=1234; -- 从主库获取的Position值 START SLAVE; SHOW SLAVE STATUS\G -- 确认Slave_IO_Running和Slave_SQL_Running都是Yes 第三部分:PostgreSQL部署与配置 3.1 安装PostgreSQL openSUSE提供了多个PostgreSQL版本,这里安装最新的稳定版: # 添加PostgreSQL官方仓库 sudo zypper addrepo https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm # 安装PostgreSQL 15 sudo zypper install postgresql15-server postgresql15-contrib # 初始化数据库 sudo /usr/pgsql-15/bin/postgresql-15-setup initdb # 启动服务并设置开机自启 sudo systemctl enable postgresql-15 sudo systemctl start postgresql-15 # 查看服务状态 sudo systemctl status postgresql-15 3.2 基本配置与用户管理 3.2.1 修改默认配置 PostgreSQL的主配置文件位于 /var/lib/pgsql/15/data/postgresql.conf : # /var/lib/pgsql/15/data/postgresql.conf 关键配置 listen_addresses = '*' # 允许远程连接 port = 5432 max_connections = 200 # 内存配置(根据服务器内存调整) shared_buffers = 512MB # 通常为内存的25% effective_cache_size = 1.5GB # 通常为内存的50-75% work_mem = 16MB # 每个查询的内存 maintenance_work_mem = 128MB # 日志配置 log_destination = 'stderr' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_min_duration_statement = 2000 # 记录慢查询(毫秒) # 检查点配置 checkpoint_completion_target = 0.9 wal_buffers = 16MB # 写入优化 default_statistics_target = 100 random_page_cost = 1.1 # SSD配置 effective_io_concurrency = 200 # SSD配置 3.2.2 配置访问控制 编辑 /var/lib/pgsql/15/data/pg_hba.conf : # TYPE DATABASE USER ADDRESS METHOD # 本地连接 local all all peer # IPv4本地连接 host all all 127.0.0.1/32 scram-sha-256 # 允许特定网段访问 host all all 192.168.1.0/24 scram-sha-256 # 拒绝其他所有连接 host all all 0.0.0.0/0 reject 3.2.3 创建数据库和用户 # 切换到postgres用户 sudo -i -u postgres # 创建数据库 createdb myapp_db # 创建用户 createuser --interactive --pwprompt # 输入用户名:myapp_user # 输入密码:StrongPassword123! # 是否超级用户?n # 是否创建数据库?n # 是否允许创建其他用户?n # 或者使用SQL命令 psql CREATE USER myapp_user WITH PASSWORD 'StrongPassword123!'; CREATE DATABASE myapp_db OWNER myapp_user; GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user; \q 3.3 性能优化技巧 3.3.1 使用pg_stat_statements监控查询 # 在postgresql.conf中添加 shared_preload_libraries = 'pg_stat_statements' # 重启PostgreSQL sudo systemctl restart postgresql-15 # 连接数据库并创建扩展 psql -d myapp_db CREATE EXTENSION pg_stat_statements; -- 查看最耗时的查询 SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 查看最频繁的查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; 3.3.2 索引优化 -- 查看表的索引 SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'; -- 分析查询计划 EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com'; -- 创建B-tree索引 CREATE INDEX idx_users_email ON users(email); -- 创建复合索引 CREATE INDEX idx_users_email_status ON users(email, status); -- 创建部分索引(只对活跃用户) CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- 创建表达式索引 CREATE INDEX idx_lower_email ON users(LOWER(email)); -- 查看未使用的索引 SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; 3.3.3 VACUUM和ANALYZE优化 # 配置自动清理参数 # postgresql.conf autovacuum = on autovacuum_vacuum_scale_factor = 0.2 # 当20%行被更新/删除时触发 autovacuum_analyze_scale_factor = 0.1 # 当10%行被更新/删除时触发 autovacuum_vacuum_cost_limit = 200 autovacuum_vacuum_cost_delay = 20ms # 手动执行VACUUM和ANALYZE psql -d myapp_db -c "VACUUM ANALYZE;" # 针对特定表 psql -d myapp_db -c "VACUUM ANALYZE users;" # 重度清理(回收空间,但会锁表) psql -d myapp_db -c "VACUUM FULL;" 3.3.4 连接池配置(使用pgbouncer) # 安装pgbouncer sudo zypper install pgbouncer # 配置pgbouncer sudo tee /etc/pgbouncer/pgbouncer.ini < /backup/postgresql/all_$(date +%Y%m%d).sql

# 单个数据库备份
sudo -u postgres pg_dump myapp_db > /backup/postgresql/myapp_db_$(date +%Y%m%d).sql

# 自定义格式备份(支持并行恢复)
sudo -u postgres pg_dump -Fc myapp_db > /backup/postgresql/myapp_db_$(date +%Y%m%d).dump

# 压缩备份
sudo -u postgres pg_dump myapp_db | gzip > /backup/postgresql/myapp_db_$(date +%Y%m%d).sql.gz

# 恢复数据库
sudo -u postgres psql -d myapp_db -f /backup/postgresql/myapp_db_20231001.sql

# 恢复自定义格式
sudo -u postgres pg_restore -d myapp_db /backup/postgresql/myapp_db_20231001.dump
3.4.2 物理备份(pg_basebackup)
# 创建基础备份(用于流复制)
sudo -u postgres pg_basebackup -D /backup/postgresql/basebackup -Ft -z -P -X stream

# 参数说明:
# -D: 备份目录
# -Ft: tar格式
# -z: 压缩
# -P: 显示进度
# -X stream: 在备份过程中流式传输WAL

# 还原物理备份
sudo systemctl stop postgresql-15
sudo rm -rf /var/lib/pgsql/15/data/*
sudo tar -xzf /backup/postgresql/basebackup/base.tar.gz -C /var/lib/pgsql/15/data/
sudo tar -xzf /backup/postgresql/basebackup/pg_wal.tar.gz -C /var/lib/pgsql/15/data/pg_wal/
sudo chown -R postgres:postgres /var/lib/pgsql/15/data/
sudo systemctl start postgresql-15
3.5 流复制配置
3.5.1 主库配置
# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
listen_addresses = ‘*’
3.5.2 创建复制用户
psql -c “CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘ReplPassword123!’;”
3.5.3 配置pg_hba.conf
# 允许复制连接
host replication replicator 192.168.1.0/24 scram-sha-256
3.5.4 从库配置
# 停止从库服务
sudo systemctl stop postgresql-15

# 清空数据目录
sudo rm -rf /var/lib/pgsql/15/data/*

# 从主库拉取基础备份
sudo -u postgres pg_basebackup -h 主库IP -D /var/lib/pgsql/15/data -U replicator -Fp -Xs -P -R

# 启动从库
sudo systemctl start postgresql-15
第四部分:高级性能优化技巧
4.1 系统级优化
4.1.1 内核参数调整
# 编辑/etc/sysctl.conf
sudo tee -a /etc/sysctl.conf < 25;

— 详细分析(实际执行)
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

— 包含缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 25;

— 输出为JSON格式
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE age > 25;
4.4 硬件资源配置建议
4.4.1 内存配置公式
# MySQL内存配置参考
innodb_buffer_pool_size = 总内存 * 0.7
max_connections = 200 # 根据实际并发调整
thread_cache_size = 16
table_open_cache = 2000

# PostgreSQL内存配置参考
shared_buffers = 总内存 * 0.25
effective_cache_size = 总内存 * 0.75
work_mem = (总内存 * 0.25) / max_connections
maintenance_work_mem = 总内存 * 0.05
4.4.2 存储配置建议
# RAID配置建议
# RAID 10: 最佳性能与冗余
# RAID 5/6: 适合读多写少场景
# RAID 0: 仅用于测试环境

# 文件系统选择
# XFS: 适合大文件和高并发
# ext4: 通用选择,稳定可靠

# 挂载选项优化
# noatime: 不更新访问时间
# nodiratime: 不更新目录访问时间
# data=writeback: ext4写入模式(性能更高,但风险略增)
mount -o noatime,nodiratime /dev/sdb1 /mnt/dbdata
第五部分:故障排查与维护
5.1 MySQL常见问题
5.1.1 连接问题
# 检查MySQL服务状态
sudo systemctl status mysqld

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

# 检查错误日志
sudo tail -f /var/log/mysqld.log

# 检查用户权限
mysql -u root -p -e “SELECT user, host FROM mysql.user;”
mysql -u root -p -e “SHOW GRANTS FOR ‘myapp_user’@’localhost’;”
5.1.2 性能问题诊断
— 查看当前连接
SHOW PROCESSLIST;

— 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

— 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_locks;

— 查看表状态
SHOW TABLE STATUS LIKE ‘users’\G
5.2 PostgreSQL常见问题
5.2.1 连接问题
# 检查PostgreSQL服务状态
sudo systemctl status postgresql-15

# �2. 检查端口监听
sudo netstat -tlnp | grep 5432

# 检查日志
sudo tail -f /var/log/postgresql/postgresql-*.log

# 检查pg_hba.conf配置
sudo cat /var/lib/pgsql/15/data/pg_hba.conf

# 测试本地连接
sudo -u postgres psql -c “SELECT version();”
5.2.2 性能问题诊断
— 查看当前连接
SELECT * FROM pg_stat_activity;

— 查看锁等待
SELECT * FROM pg_locks WHERE NOT granted;

— 查看数据库大小
SELECT pg_size_pretty(pg_database_size(‘myapp_db’));

— 查看表大小
SELECT pg_size_pretty(pg_total_relation_size(‘users’));

— 查看缓存命中率
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
5.3 自动化维护脚本
5.3.1 MySQL自动化备份脚本
#!/bin/bash
# /usr/local/bin/mysql_backup.sh

BACKUP_DIR=”/backup/mysql/daily”
MYSQL_USER=”root”
MYSQL_PASSWORD=”your_password”
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD –all-databases –single-transaction –quick > $BACKUP_DIR/all_databases_$DATE.sql

# 压缩
gzip $BACKUP_DIR/all_databases_$DATE.sql

# 删除7天前的备份
find $BACKUP_DIR -name “*.sql.gz” -mtime +7 -delete

# 记录日志
echo “$(date): MySQL backup completed” >> /var/log/mysql_backup.log
5.3.2 PostgreSQL自动化维护脚本
#!/bin/bash
# /usr/local/bin/pg_maintenance.sh

DB_NAME=”myapp_db”
DB_USER=”postgres”
LOG_FILE=”/var/log/pg_maintenance.log”

# 执行VACUUM ANALYZE
psql -U $DB_USER -d $DB_NAME -c “VACUUM ANALYZE;” >> $LOG_FILE 2>&1

# 检查数据库健康
psql -U $DB_USER -d $DB_NAME -c “SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;” >> $LOG_FILE 2>&1

echo “$(date): PostgreSQL maintenance completed” >> $LOG_FILE
第六部分:安全最佳实践
6.1 MySQL安全配置
# 1. 强密码策略
sudo mysql -u root -p -e “SET GLOBAL validate_password.policy=’MEDIUM’;”

# 2. 禁用危险函数
# 在my.cnf中添加
# secure_file_priv=/var/lib/mysql-files
# local_infile=0

# 3. 审计日志(MySQL Enterprise或MariaDB)
# 安装审计插件
sudo mysql -u root -p -e “INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;”

# 4. 加密通信
# 生成SSL证书
sudo mysql_ssl_rsa_setup –datadir=/var/lib/mysql

# 在my.cnf中启用
# require_secure_transport=ON
# ssl-ca=/var/lib/mysql/ca.pem
# ssl-cert=/var/lib/mysql/server-cert.pem
# ssl-key=/var/lib/mysql/server-key.pem
6.2 PostgreSQL安全配置
# 1. 强密码策略
# 在postgresql.conf中
# password_encryption = scram-sha-256

# 2. 审计日志
# 在postgresql.conf中
# log_statement = ‘all’ # 记录所有语句(性能影响大)
# log_connections = on
# log_disconnections = on

# 3. 行级安全(RLS)
psql -d myapp_db -c “ALTER TABLE users ENABLE ROW LEVEL SECURITY;”

# 4. 数据加密
# 使用pgcrypto扩展
psql -d myapp_db -c “CREATE EXTENSION pgcrypto;”

# 加密示例
psql -d myapp_db -c “INSERT INTO sensitive_data (data) VALUES (pgp_sym_encrypt(‘secret’, ‘my_key’));”
6.3 系统安全加固
# 1. 禁用root SSH登录
sudo sed -i ‘s/#PermitRootLogin yes/PermitRootLogin no/’ /etc/ssh/sshd_config

# 2. 使用密钥认证
ssh-keygen -t rsa -b 4096
ssh-copy-id user@server

# 3. 配置fail2ban防止暴力破解
sudo zypper install fail2ban
sudo tee /etc/fail2ban/jail.local < slow_report.txt

# 检查MySQL配置
pt-mysql-summary –user=root –password
2.5.2 索引优化实战
— 查看表索引
SHOW INDEX FROM myapp_db.users;

— 分析查询性能
EXPLAIN SELECT * FROM myapp_db.users WHERE email = ‘user@example.com’;

— 创建复合索引
CREATE INDEX idx_email_status ON myapp_db.users(email, status);

— 查看未使用的索引(需要开启per_schema)
SELECT * FROM sys.schema_unused_indexes;

— 使用覆盖索引优化查询
CREATE INDEX idx_cover ON myapp_db.users(email, name, created_at);
— 查询只需要扫描索引,无需回表
SELECT email, name FROM myapp_db.users WHERE email LIKE ‘user%’;
2.5.3 查询优化示例
— 优化前:全表扫描
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

— 优化后:使用索引范围扫描
SELECT * FROM orders WHERE created_at >= ‘2023-01-01’ AND created_at < '2024-01-01'; -- 避免SELECT * SELECT id, name, email FROM users WHERE id = 123; -- 使用JOIN替代子查询(优化前) SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

— 优化后:使用JOIN
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;
2.6 备份与恢复策略
2.6.1 物理备份(使用Percona XtraBackup)
# 安装Percona XtraBackup
sudo zypper install percona-xtrabackup-80

# 创建完整备份
sudo xtrabackup –backup –target-dir=/backup/mysql/full –user=root –password

# 准备备份
sudo xtrabackup –prepare –target-dir=/backup/mysql/full

# 还原备份
sudo systemctl stop mysqld
sudo xtrabackup –copy-back –target-dir=/backup/mysql/full –datadir=/mnt/dbdata/mysql
sudo chown -R mysql:mysql /mnt/dbdata/mysql
sudo systemctl start mysqld
2.6.2 逻辑备份与恢复
# 全量备份
mysqldump -u root -p –all-databases > /backup/mysql/all_databases_$(date +%Y%m%d).sql

# 单个数据库备份
mysqldump -u root -p myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d).sql

# 压缩备份
mysqldump -u root -p myapp_db | gzip > /backup/mysql/myapp_db_$(date +%Y%m%d).sql.gz

# 恢复数据库
mysql -u root -p myapp_db < /backup/mysql/myapp_db_20231001.sql # 增量备份(需要开启二进制日志) # 在my.cnf中添加 # log_bin=/mnt/dbdata/mysql/binlog # binlog_expire_logs_seconds=604800 # 7天 # 查看二进制日志 mysql -u root -p -e "SHOW BINARY LOGS;" # 使用时间点恢复 mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 12:00:00" /mnt/dbdata/mysql/binlog.000001 | mysql -u root -p 2.7 主从复制配置 2.7.1 主库配置 # /etc/my.cnf 主库配置 [mysqld] server-id=1 log_bin=/mnt/dbdata/mysql/binlog binlog_format=ROW binlog_expire_logs_seconds=604800 2.7.2 创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; -- 锁表并获取二进制日志位置 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; -- 记录File和Position值 2.7.3 从库配置 # /etc/my.cnf 从库配置 [mysqld] server-id=2 relay_log=/mnt/dbdata/mysql/relaylog read_only=1 2.7.4 启动复制 -- 在从库上执行 CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='binlog.000001', -- 从主库获取的File值 MASTER_LOG_POS=1234; -- 从主库获取的Position值 START SLAVE; SHOW SLAVE STATUS\G -- 确认Slave_IO_Running和Slave_SQL_Running都是Yes 第三部分:PostgreSQL部署与配置 3.1 安装PostgreSQL openSUSE提供了多个PostgreSQL版本,这里安装最新的稳定版: # 添加PostgreSQL官方仓库 sudo zypper addrepo https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm # 安装PostgreSQL 15 sudo zypper install postgresql15-server postgresql15-contrib # 初始化数据库 sudo /usr/pgsql-15/bin/postgresql-15-setup initdb # 启动服务并设置开机自启 sudo systemctl enable postgresql-15 sudo systemctl start postgresql-15 # 查看服务状态 sudo systemctl status postgresql-15 3.2 基本配置与用户管理 3.2.1 修改默认配置 PostgreSQL的主配置文件位于 /var/lib/pgsql/15/data/postgresql.conf : # /var/lib/pgsql/15/data/postgresql.conf 关键配置 listen_addresses = '*' # 允许远程连接 port = 5432 max_connections = 200 # 内存配置(根据服务器内存调整) shared_buffers = 512MB # 通常为内存的25% effective_cache_size = 1.5GB # 通常为内存的50-75% work_mem = 16MB # 每个查询的内存 maintenance_work_mem = 128MB # 日志配置 log_destination = 'stderr' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_min_duration_statement = 2000 # 记录慢查询(毫秒) # 检查点配置 checkpoint_completion_target = 0.9 wal_buffers = 16MB # 写入优化 default_statistics_target = 100 random_page_cost = 1.1 # SSD配置 effective_io_concurrency = 200 # SSD配置 3.2.2 配置访问控制 编辑 /var/lib/pgsql/15/data/pg_hba.conf : # TYPE DATABASE USER ADDRESS METHOD # 本地连接 local all all peer # IPv4本地连接 host all all 127.0.0.1/32 scram-sha-256 # 允许特定网段访问 host all all 192.168.1.0/24 scram-sha-256 # 拒绝其他所有连接 host all all 0.0.0.0/0 reject 3.2.3 创建数据库和用户 # 切换到postgres用户 sudo -i -u postgres # 创建数据库 createdb myapp_db # 创建用户 createuser --interactive --pwprompt # 输入用户名:myapp_user # 输入密码:StrongPassword123! # 是否超级用户?n # 是否创建数据库?n # 是否允许创建其他用户?n # 或者使用SQL命令 psql CREATE USER myapp_user WITH PASSWORD 'StrongPassword123!'; CREATE DATABASE myapp_db OWNER myapp_user; GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user; \q 3.3 性能优化技巧 3.3.1 使用pg_stat_statements监控查询 # 在postgresql.conf中添加 shared_preload_libraries = 'pg_stat_statements' # 重启PostgreSQL sudo systemctl restart postgresql-15 # 连接数据库并创建扩展 psql -d myapp_db CREATE EXTENSION pg_stat_statements; -- 查看最耗时的查询 SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 查看最频繁的查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; 3.3.2 索引优化 -- 查看表的索引 SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'; -- 分析查询计划 EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com'; -- 创建B-tree索引 CREATE INDEX idx_users_email ON users(email); -- 创建复合索引 CREATE INDEX idx_users_email_status ON users(email, status); -- 创建部分索引(只对活跃用户) CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- 创建表达式索引 CREATE INDEX idx_lower_email ON users(LOWER(email)); -- 查看未使用的索引 SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; 3.3.3 VACUUM和ANALYZE优化 # 配置自动清理参数 # postgresql.conf autovacuum = on autovacuum_vacuum_scale_factor = 0.2 # 当20%行被更新/删除时触发 autovacuum_analyze_scale_factor = 0.1 # 当10%行被更新/删除时触发 autovacuum_vacuum_cost_limit = 200 autovacuum_vacuum_cost_delay = 20ms # 手动执行VACUUM和ANALYZE psql -d myapp_db -c "VACUUM ANALYZE;" # 针对特定表 psql -d myapp_db -c "VACUUM ANALYZE users;" # 重度清理(回收空间,但会锁表) psql -d myapp_db -c "VACUUM FULL;" 3.3.4 连接池配置(使用pgbouncer) # 安装pgbouncer sudo zypper install pgbouncer # 配置pgbouncer sudo tee /etc/pgbouncer/pgbouncer.ini < /backup/postgresql/all_$(date +%Y%m%d).sql

# 单个数据库备份
sudo -u postgres pg_dump myapp_db > /backup/postgresql/myapp_db_$(date +%Y%m%d).sql

# 自定义格式备份(支持并行恢复)
sudo -u postgres pg_dump -Fc myapp_db > /backup/postgresql/myapp_db_$(date +%Y%m%d).dump

# 压缩备份
sudo -u postgres pg_dump myapp_db | gzip > /backup/postgresql/myapp_db_$(date +%Y%m%d).sql.gz

# 恢复数据库
sudo -u postgres psql -d myapp_db -f /backup/postgresql/myapp_db_20231001.sql

# 恢复自定义格式
sudo -u postgres pg_restore -d myapp_db /backup/postgresql/myapp_db_20231001.dump
3.4.2 物理备份(pg_basebackup)
# 创建基础备份(用于流复制)
sudo -u postgres pg_basebackup -D /backup/postgresql/basebackup -Ft -z -P -X stream

# 参数说明:
# -D: 备份目录
# -Ft: tar格式
# -z: 压缩
# -P: 显示进度
# -X stream: 在备份过程中流式传输WAL

# 还原物理备份
sudo systemctl stop postgresql-15
sudo rm -rf /var/lib/pgsql/15/data/*
sudo tar -xzf /backup/postgresql/basebackup/base.tar.gz -C /var/lib/pgsql/15/data/
sudo tar -xzf /backup/postgresql/basebackup/pg_wal.tar.gz -C /var/lib/pgsql/15/data/pg_wal/
sudo chown -R postgres:postgres /var/lib/pgsql/15/data/
sudo systemctl start postgresql-15
3.5 流复制配置
3.5.1 主库配置
# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
listen_addresses = ‘*’
3.5.2 创建复制用户
psql -c “CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘ReplPassword123!’;”
3.5.3 配置pg_hba.conf
# 允许复制连接
host replication replicator 192.168.1.0/24 scram-sha-256
3.5.4 从库配置
# 停止从库服务
sudo systemctl stop postgresql-15

# 清空数据目录
sudo rm -rf /var/lib/pgsql/15/data/*

# 从主库拉取基础备份
sudo -u postgres pg_basebackup -h 主库IP -D /var/lib/pgsql/15/data -U replicator -Fp -Xs -P -R

# 启动从库
sudo systemctl start postgresql-15
第四部分:高级性能优化技巧
4.1 系统级优化
4.1.1 内核参数调整
# 编辑/etc/sysctl.conf
sudo tee -a /etc/sysctl.conf < 25;

— 详细分析(实际执行)
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

— 包含缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 25;

— 输出为JSON格式
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE age > 25;
4.4 硬件资源配置建议
4.4.1 内存配置公式
# MySQL内存配置参考
innodb_buffer_pool_size = 总内存 * 0.7
max_connections = 200 # 根据实际并发调整
thread_cache_size = 16
table_open_cache = 2000

# PostgreSQL内存配置参考
shared_buffers = 总内存 * 0.25
effective_cache_size = 总内存 * 0.75
work_mem = (总内存 * 0.25) / max_connections
maintenance_work_mem = 总内存 * 0.05
4.4.2 存储配置建议
# RAID配置建议
# RAID 10: 最佳性能与冗余
# RAID 5/6: 适合读多写少场景
# RAID 0: 仅用于测试环境

# 文件系统选择
# XFS: 适合大文件和高并发
# ext4: 通用选择,稳定可靠

# 挂载选项优化
# noatime: 不更新访问时间
# nodiratime: 不更新目录访问时间
# data=writeback: ext4写入模式(性能更高,但风险略增)
mount -o noatime,nodiratime /dev/sdb1 /mnt/dbdata
第五部分:故障排查与维护
5.1 MySQL常见问题
5.1.1 连接问题
# 检查MySQL服务状态
sudo systemctl status mysqld

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

# 检查错误日志
sudo tail -f /var/log/mysqld.log

# 检查用户权限
mysql -u root -p -e “SELECT user, host FROM mysql.user;”
mysql -u root -p -e “SHOW GRANTS FOR ‘myapp_user’@’localhost’;”
5.1.2 性能问题诊断
— 查看当前连接
SHOW PROCESSLIST;

— 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

— 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_locks;

— 查看表状态
SHOW TABLE STATUS LIKE ‘users’\G
5.2 PostgreSQL常见问题
5.2.1 连接问题
# 检查PostgreSQL服务状态
sudo systemctl status postgresql-15

# 检查端口监听
sudo netstat -tlnp | grep 5432

# 检查日志
sudo tail -f /var/log/postgresql/postgresql-*.log

# 检查pg_hba.conf配置
sudo cat /var/lib/pgsql/15/data/pg_hba.conf

# 测试本地连接
sudo -u postgres psql -c “SELECT version();”
5.2.2 性能问题诊断
— 查看当前连接
SELECT * FROM pg_stat_activity;

— 查看锁等待
SELECT * FROM pg_locks WHERE NOT granted;

— 查看数据库大小
SELECT pg_size_pretty(pg_database_size(‘myapp_db’));

— 查看表大小
SELECT pg_size_pretty(pg_total_relation_size(‘users’));

— 查看缓存命中率
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
5.3 自动化维护脚本
5.3.1 MySQL自动化备份脚本
#!/bin/bash
# /usr/local/bin/mysql_backup.sh

BACKUP_DIR=”/backup/mysql/daily”
MYSQL_USER=”root”
MYSQL_PASSWORD=”your_password”
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD –all-databases –single-transaction –quick > $BACKUP_DIR/all_databases_$DATE.sql

# 压缩
gzip $BACKUP_DIR/all_databases_$DATE.sql

# 删除7天前的备份
find $BACKUP_DIR -name “*.sql.gz” -mtime +7 -delete

# 记录日志
echo “$(date): MySQL backup completed” >> /var/log/mysql_backup.log
5.3.2 PostgreSQL自动化维护脚本
#!/bin/bash
# /usr/local/bin/pg_maintenance.sh

DB_NAME=”myapp_db”
DB_USER=”postgres”
LOG_FILE=”/var/log/pg_maintenance.log”

# 执行VACUUM ANALYZE
psql -U $DB_USER -d $DB_NAME -c “VACUUM ANALYZE;” >> $LOG_FILE 2>&1

# 检查数据库健康
psql -U $DB_USER -d $DB_NAME -c “SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;” >> $LOG_FILE 2>&1

echo “$(date): PostgreSQL maintenance completed” >> $LOG_FILE
第六部分:安全最佳实践
6.1 MySQL安全配置
# 1. 强密码策略
sudo mysql -u root -p -e “SET GLOBAL validate_password.policy=’MEDIUM’;”

# 2. 禁用危险函数
# 在my.cnf中添加
# secure_file_priv=/var/lib/mysql-files
# local_infile=0

# 3. 审计日志(MySQL Enterprise或MariaDB)
# 安装审计插件
sudo mysql -u root -p -e “INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;”

# 4. 加密通信
# 生成SSL证书
sudo mysql_ssl_rsa_setup –datadir=/var/lib/mysql

# 在my.cnf中启用
# require_secure_transport=ON
# ssl-ca=/var/lib/mysql/ca.pem
# ssl-cert=/var/lib/mysql/server-cert.pem
# ssl-key=/var/lib/mysql/server-key.pem
6.2 PostgreSQL安全配置
# 1. 强密码策略
# 在postgresql.conf中
# password_encryption = scram-sha-256

# 2. 审计日志
# 在postgresql.conf中
# log_statement = ‘all’ # 记录所有语句(性能影响大)
# log_connections = on
# log_disconnections = on

# 3. 行级安全(RLS)
psql -d myapp_db -c “ALTER TABLE users ENABLE ROW LEVEL SECURITY;”

# 4. 数据加密
# 使用pgcrypto扩展
psql -d myapp_db -c “CREATE EXTENSION pgcrypto;”

— 加密示例
psql -d myapp_db -c “INSERT INTO sensitive_data (data) VALUES (pgp_sym_encrypt(‘secret’, ‘my_key’));”
6.3 系统安全加固
# 1. 禁用root SSH登录
sudo sed -i ‘s/#PermitRootLogin yes/PermitRootLogin no/’ /etc/ssh/sshd_config

# 2. 使用密钥认证
ssh-keygen -t rsa -b 4096
ssh-copy-id user@server

# 3. 配置fail2ban防止暴力破解
sudo zypper install fail2ban
sudo tee /etc/fail2ban/jail.local <