全面解析Fedora系统数据库配置流程与管理最佳实践

威震华夏关云长

1. Fedora系统与数据库概述

Fedora是一个强大的Linux发行版，以其前沿技术和稳定性著称。作为Red Hat Enterprise Linux(RHEL)的社区版，Fedora为数据库服务器提供了优秀的运行环境。在Fedora系统上配置和管理数据库需要系统性的知识和实践经验。

1.1 Fedora系统特点

Fedora系统具有以下特点，使其成为数据库服务器的理想选择：

• 先进的技术栈：Fedora总是采用最新的Linux内核和软件包
• 强大的包管理系统：DNF包管理器提供了高效的软件安装和更新机制
• SELinux支持：提供了强大的安全框架，保护数据库服务器免受未授权访问
• 活跃的社区支持：拥有庞大的开发者社区，提供及时的技术支持和更新

1.2 常见数据库类型

在Fedora系统上，可以部署多种类型的数据库系统：

• 关系型数据库：MySQL/MariaDB、PostgreSQL、SQLite
• NoSQL数据库：MongoDB、Redis、Cassandra
• 时序数据库：InfluxDB、TimescaleDB
• 图数据库：Neo4j

本文将重点介绍在Fedora系统上配置和管理最常用的关系型数据库：MySQL/MariaDB和PostgreSQL。

2. 数据库安装流程

2.1 系统准备

在安装数据库之前，需要确保Fedora系统已更新并配置了基本的安全设置：

2. # 更新系统
3. sudo dnf update -y
5. # 安装必要的工具
6. sudo dnf install -y wget vim curl
8. # 配置防火墙
9. sudo firewall-cmd --permanent --add-service=ssh
10. sudo firewall-cmd --reload

复制代码

2.2 安装MySQL/MariaDB

MariaDB是MySQL的一个分支，完全兼容MySQL，并且在Fedora中是默认的MySQL替代品。

2. # 安装MariaDB服务器
3. sudo dnf install -y mariadb-server
5. # 启动MariaDB服务
6. sudo systemctl start mariadb
8. # 设置开机自启
9. sudo systemctl enable mariadb
11. # 查看服务状态
12. sudo systemctl status mariadb

复制代码

2.3 安装PostgreSQL

PostgreSQL是一个功能强大的开源对象关系型数据库系统。

2. # 安装PostgreSQL服务器
3. sudo dnf install -y postgresql-server postgresql-contrib
5. # 初始化数据库
6. sudo postgresql-setup --initdb
8. # 启动PostgreSQL服务
9. sudo systemctl start postgresql
11. # 设置开机自启
12. sudo systemctl enable postgresql
14. # 查看服务状态
15. sudo systemctl status postgresql

复制代码

3. 数据库基本配置

3.1 MySQL/MariaDB基本配置

安装完成后，运行安全初始化脚本：

2. sudo mysql_secure_installation

复制代码

此脚本将引导您完成以下步骤：

• 设置root密码
• 移除匿名用户
• 禁止root远程登录
• 移除测试数据库
• 重新加载权限表

MariaDB的主配置文件位于/etc/my.cnf或/etc/my.cnf.d/目录下。创建自定义配置文件：

2. sudo vim /etc/my.cnf.d/custom.cnf

复制代码

添加以下基本配置：

2. [mysqld]
3. # 设置默认字符集
4. character-set-server = utf8mb4
5. collation-server = utf8mb4_unicode_ci
7. # 设置默认存储引擎
8. default-storage-engine = InnoDB
10. # 设置最大连接数
11. max_connections = 200
13. # 设置InnoDB缓冲池大小（通常为系统内存的50-70%）
14. innodb_buffer_pool_size = 2G
16. # 设置日志文件大小
17. innodb_log_file_size = 256M
19. # 设置查询缓存
20. query_cache_type = 1
21. query_cache_size = 128M
23. # 设置超时时间
24. wait_timeout = 300
25. interactive_timeout = 300

复制代码

重启MariaDB服务以应用配置：

2. sudo systemctl restart mariadb

复制代码

3.2 PostgreSQL基本配置

PostgreSQL安装后，会自动创建一个名为postgres的系统用户和数据库超级用户。首先，设置postgres用户的密码：

2. sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_password';"

复制代码

PostgreSQL的主要配置文件位于/var/lib/pgsql/data/postgresql.conf和/var/lib/pgsql/data/pg_hba.conf。

编辑postgresql.conf文件：

2. sudo vim /var/lib/pgsql/data/postgresql.conf

复制代码

修改以下关键参数：

2. # 连接设置
3. listen_addresses = '*'          # 监听所有IP地址
4. port = 5432                     # 默认端口
5. max_connections = 100           # 最大连接数
7. # 内存设置
8. shared_buffers = 256MB          # 共享缓冲区，通常为系统内存的25%
9. effective_cache_size = 1GB      # 有效缓存大小，通常为系统内存的50%
10. work_mem = 4MB                  # 排序操作使用的内存
11. maintenance_work_mem = 64MB     # 维护操作使用的内存
13. # 日志设置
14. logging_collector = on          # 启用日志收集
15. log_directory = 'log'           # 日志目录
16. log_filename = 'postgresql-%a.log'  # 日志文件名
17. log_statement = 'mod'           # 记录修改数据的语句
18. log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '  # 日志行前缀
20. # 检查点设置
21. checkpoint_completion_target = 0.9  # 检查点完成目标
22. wal_buffers = 16MB               # WAL缓冲区

复制代码

编辑pg_hba.conf文件以配置客户端认证：

2. sudo vim /var/lib/pgsql/data/pg_hba.conf

复制代码

添加或修改以下行以允许密码认证：

2. # TYPE  DATABASE        USER            ADDRESS                 METHOD
3. host    all             all             0.0.0.0/0               md5
4. local   all             all                                     md5

复制代码

重启PostgreSQL服务以应用配置：

2. sudo systemctl restart postgresql

复制代码

4. 数据库安全配置

4.1 MySQL/MariaDB安全配置

创建具有特定权限的数据库用户：

2. -- 登录到MariaDB
3. mysql -u root -p
5. -- 创建新用户
6. CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'secure_password';
8. -- 授予用户特定数据库的所有权限
9. GRANT ALL PRIVILEGES ON database_name.* TO 'db_user'@'localhost';
11. -- 授予用户远程访问权限
12. CREATE USER 'db_user'@'%' IDENTIFIED BY 'secure_password';
13. GRANT ALL PRIVILEGES ON database_name.* TO 'db_user'@'%';
15. -- 刷新权限
16. FLUSH PRIVILEGES;
18. -- 查看用户权限
19. SHOW GRANTS FOR 'db_user'@'localhost';

复制代码

为MariaDB配置SSL/TLS加密连接：

2. # 创建SSL证书目录
3. sudo mkdir /etc/mysql/ssl
4. cd /etc/mysql/ssl
6. # 创建CA证书
7. sudo openssl genrsa 2048 > ca-key.pem
8. sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
10. # 创建服务器证书
11. sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
12. sudo openssl rsa -in server-key.pem -out server-key.pem
13. sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
15. # 创建客户端证书
16. sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
17. sudo openssl rsa -in client-key.pem -out client-key.pem
18. sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
20. # 设置权限
21. sudo chmod 600 *.pem
22. sudo chown mysql:mysql *.pem

复制代码

编辑MariaDB配置文件以启用SSL：

2. sudo vim /etc/my.cnf.d/custom.cnf

复制代码

添加以下配置：

2. [mysqld]
3. # SSL配置
4. ssl-ca = /etc/mysql/ssl/ca-cert.pem
5. ssl-cert = /etc/mysql/ssl/server-cert.pem
6. ssl-key = /etc/mysql/ssl/server-key.pem
8. # 要求所有连接使用SSL
9. require_secure_transport = ON

复制代码

重启MariaDB服务：

2. sudo systemctl restart mariadb

复制代码

验证SSL配置：

2. SHOW VARIABLES LIKE '%ssl%';

复制代码

4.2 PostgreSQL安全配置

创建具有特定权限的数据库用户：

2. # 切换到postgres用户
3. sudo -u postgres psql
5. -- 创建新用户
6. CREATE USER db_user WITH PASSWORD 'secure_password';
8. -- 创建数据库并设置所有者
9. CREATE DATABASE database_name OWNER db_user;
11. -- 授予用户数据库连接权限
12. GRANT CONNECT ON DATABASE database_name TO db_user;
14. -- 授予用户模式使用权限
15. GRANT USAGE ON SCHEMA public TO db_user;
17. -- 授予用户表的所有权限
18. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_user;
20. -- 授予用户序列的所有权限
21. GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_user;
23. -- 设置默认权限
24. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO db_user;
25. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO db_user;
27. -- 查看用户权限
28. \du db_user

复制代码

为PostgreSQL配置SSL/TLS加密连接：

2. # 创建SSL证书目录
3. sudo mkdir /var/lib/pgsql/data/ssl
4. cd /var/lib/pgsql/data/ssl
6. # 创建服务器证书
7. sudo openssl req -new -x509 -days 3650 -nodes -text -out server.crt -keyout server.key -subj "/CN=db.example.com"
8. sudo chmod og-rwx server.key
10. # 创建根证书
11. sudo openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.example.com"
12. sudo openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
14. # 设置权限
15. sudo chown postgres:postgres /var/lib/pgsql/data/ssl/*
16. sudo chmod 600 /var/lib/pgsql/data/ssl/server.key

复制代码

编辑PostgreSQL配置文件以启用SSL：

2. sudo vim /var/lib/pgsql/data/postgresql.conf

复制代码

修改以下参数：

2. # SSL配置
3. ssl = on
4. ssl_cert_file = 'ssl/server.crt'
5. ssl_key_file = 'ssl/server.key'
6. ssl_ca_file = 'ssl/root.crt'

复制代码

编辑pg_hba.conf文件以要求SSL连接：

2. sudo vim /var/lib/pgsql/data/pg_hba.conf

复制代码

修改认证方法：

2. # TYPE  DATABASE        USER            ADDRESS                 METHOD
3. hostssl all             all             0.0.0.0/0               md5
4. host    all             all             127.0.0.1/32            md5

复制代码

重启PostgreSQL服务：

2. sudo systemctl restart postgresql

复制代码

验证SSL配置：

2. sudo -u postgres psql -c "SHOW ssl;"

复制代码

5. 数据库性能优化

5.1 MySQL/MariaDB性能优化

索引是提高数据库查询性能的关键。以下是一些索引优化的最佳实践：

2. -- 创建索引
3. CREATE INDEX idx_user_email ON users(email);
5. -- 创建复合索引
6. CREATE INDEX idx_user_name_email ON users(last_name, first_name);
8. -- 创建全文索引（适用于文本搜索）
9. CREATE FULLTEXT INDEX idx_article_content ON articles(content);
11. -- 分析索引使用情况
12. SHOW INDEX FROM users;
14. -- 查看查询执行计划
15. EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
17. -- 识别未使用的索引
18. SELECT * FROM sys.schema_unused_indexes;
20. -- 识别冗余索引
21. SELECT * FROM sys.schema_redundant_indexes;

复制代码

优化SQL查询可以显著提高数据库性能：

2. -- 使用LIMIT限制结果集大小
3. SELECT * FROM large_table LIMIT 1000;
5. -- 避免使用SELECT *，只选择需要的列
6. SELECT id, name, email FROM users;
8. -- 使用JOIN替代子查询
9. SELECT u.id, u.name, o.order_date
10. FROM users u
11. JOIN orders o ON u.id = o.user_id
12. WHERE u.status = 'active';
14. -- 使用EXISTS替代IN
15. SELECT u.id, u.name
16. FROM users u
17. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
19. -- 使用批量插入替代单条插入
20. INSERT INTO users (name, email) VALUES
21. ('User1', 'user1@example.com'),
22. ('User2', 'user2@example.com'),
23. ('User3', 'user3@example.com');
25. -- 使用事务处理批量操作
26. START TRANSACTION;
27. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
28. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
29. COMMIT;

复制代码

根据服务器硬件和工作负载调整MariaDB配置：

2. sudo vim /etc/my.cnf.d/custom.cnf

复制代码

添加以下性能优化配置：

2. [mysqld]
3. # InnoDB优化
4. innodb_buffer_pool_size = 4G        # 根据系统内存调整，通常为50-70%
5. innodb_buffer_pool_instances = 4    # 缓冲池实例数，通常为CPU核心数
6. innodb_log_file_size = 512M         # 日志文件大小
7. innodb_log_buffer_size = 64M         # 日志缓冲区大小
8. innodb_flush_log_at_trx_commit = 2   # 平衡性能和数据安全
9. innodb_flush_method = O_DIRECT       # 直接I/O，避免双缓冲
10. innodb_file_per_table = ON           # 每表一个文件空间
11. innodb_read_io_threads = 8          # 读I/O线程数
12. innodb_write_io_threads = 8         # 写I/O线程数
13. innodb_thread_concurrency = 0       # 并发线程数，0表示不限制
15. # MyISAM优化（如果使用MyISAM表）
16. key_buffer_size = 256M              # 键缓冲区大小
17. myisam_sort_buffer_size = 64M       # 排序缓冲区大小
18. myisam_max_sort_file_size = 10G     # 最大排序文件大小
19. myisam_repair_threads = 1           # 修复线程数
21. # 其他优化
22. tmp_table_size = 256M               # 临时表大小
23. max_heap_table_size = 256M          # 内存表大小
24. query_cache_size = 128M             # 查询缓存大小
25. query_cache_type = 1                # 启用查询缓存
26. table_open_cache = 2000             # 表缓存数
27. thread_cache_size = 10              # 线程缓存数
28. thread_stack = 256K                 # 线程堆栈大小
29. max_connect_errors = 100000         # 最大连接错误数

复制代码

重启MariaDB服务以应用配置：

2. sudo systemctl restart mariadb

复制代码

5.2 PostgreSQL性能优化

PostgreSQL提供了多种索引类型，根据数据类型和查询模式选择合适的索引：

2. -- 创建B-tree索引（默认索引类型）
3. CREATE INDEX idx_user_email ON users(email);
5. -- 创建复合索引
6. CREATE INDEX idx_user_name_email ON users(last_name, first_name);
8. -- 创建部分索引
9. CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';
11. -- 创建唯一索引
12. CREATE UNIQUE INDEX idx_user_email ON users(email);
14. -- 创建表达式索引
15. CREATE INDEX idx_user_lower_email ON users(LOWER(email));
17. -- 创建GIN索引（适用于全文搜索）
18. CREATE INDEX idx_article_content_gin ON articles USING gin(to_tsvector('english', content));
20. -- 创建GiST索引（适用于地理数据）
21. CREATE INDEX idx_location_gist ON locations USING gist(geom);
23. -- 分析索引使用情况
24. EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
26. -- 查看索引统计信息
27. SELECT * FROM pg_stat_user_indexes;
29. -- 识别未使用的索引
30. SELECT s.schemaname, s.tablename, s.indexname, s.idx_scan
31. FROM pg_stat_user_indexes s
32. JOIN pg_index i ON s.indexrelid = i.indexrelid
33. WHERE s.idx_scan = 0 AND i.indisunique = 'f';

复制代码

优化PostgreSQL查询的技巧：

2. -- 使用LIMIT限制结果集大小
3. SELECT * FROM large_table LIMIT 1000;
5. -- 避免使用SELECT *，只选择需要的列
6. SELECT id, name, email FROM users;
8. -- 使用JOIN替代子查询
9. SELECT u.id, u.name, o.order_date
10. FROM users u
11. JOIN orders o ON u.id = o.user_id
12. WHERE u.status = 'active';
14. -- 使用EXISTS替代IN
15. SELECT u.id, u.name
16. FROM users u
17. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
19. -- 使用CTE (Common Table Expressions)提高复杂查询的可读性
20. WITH active_users AS (
21.     SELECT id, name FROM users WHERE status = 'active'
22. )
23. SELECT u.id, u.name, COUNT(o.id) as order_count
24. FROM active_users u
25. LEFT JOIN orders o ON u.id = o.user_id
26. GROUP BY u.id, u.name;
28. -- 使用窗口函数
29. SELECT
30.     id,
31.     name,
32.     salary,
33.     RANK() OVER (ORDER BY salary DESC) as salary_rank
34. FROM employees;
36. -- 使用批量插入
37. INSERT INTO users (name, email) VALUES
38. ('User1', 'user1@example.com'),
39. ('User2', 'user2@example.com'),
40. ('User3', 'user3@example.com');
42. -- 使用COPY命令进行大批量数据导入
43. COPY users(name, email) FROM '/path/to/users.csv' DELIMITER ',' CSV HEADER;

复制代码

根据服务器硬件和工作负载调整PostgreSQL配置：

2. sudo vim /var/lib/pgsql/data/postgresql.conf

复制代码

修改以下性能优化参数：

2. # 内存设置
3. shared_buffers = 1GB                # 共享缓冲区，通常为系统内存的25%
4. effective_cache_size = 3GB          # 有效缓存大小，通常为系统内存的50%
5. work_mem = 16MB                     # 排序操作使用的内存
6. maintenance_work_mem = 256MB        # 维护操作使用的内存
7. max_stack_depth = 2MB               # 最大堆栈深度
9. # WAL设置
10. wal_buffers = 16MB                  # WAL缓冲区
11. wal_level = replica                 # WAL级别
12. synchronous_commit = on             # 同步提交设置
13. full_page_writes = on               # 完整页写入
14. wal_compression = on                # WAL压缩
16. # 检查点设置
17. checkpoint_completion_target = 0.9  # 检查点完成目标
18. checkpoint_timeout = 15min          # 检查点超时
19. max_wal_size = 1GB                  # 最大WAL大小
20. min_wal_size = 80MB                 # 最小WAL大小
22. # 查询优化
23. random_page_cost = 1.1              # 随机页成本
24. effective_io_concurrency = 200      # 有效I/O并发
25. parallel_tuple_cost = 0.1           # 并行元组成本
26. parallel_setup_cost = 1000.0        # 并行设置成本
27. min_parallel_table_scan_size = 8MB  # 最小并行表扫描大小
28. min_parallel_index_scan_size = 512kB # 最小并行索引扫描大小
29. max_parallel_workers_per_gather = 2 # 每个Gather节点的最大并行工作进程数
31. # 连接设置
32. max_connections = 100               # 最大连接数
33. superuser_reserved_connections = 3  # 超级用户保留连接数
35. # 日志设置
36. log_statement = 'mod'               # 记录修改数据的语句
37. log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '  # 日志行前缀
38. log_min_duration_statement = 1000   # 记录执行时间超过此值的语句

复制代码

重启PostgreSQL服务以应用配置：

2. sudo systemctl restart postgresql

复制代码

6. 数据库备份与恢复

6.1 MySQL/MariaDB备份与恢复

使用mysqldump工具进行逻辑备份：

2. # 备份所有数据库
3. mysqldump -u root -p --all-databases > /backup/all_databases.sql
5. # 备份特定数据库
6. mysqldump -u root -p database_name > /backup/database_name.sql
8. # 备份特定表
9. mysqldump -u root -p database_name table1 table2 > /backup/tables.sql
11. # 备份数据库结构（不包含数据）
12. mysqldump -u root -p --no-data database_name > /backup/database_structure.sql
14. # 备份数据库数据（不包含结构）
15. mysqldump -u root -p --no-create-info database_name > /backup/database_data.sql
17. # 压缩备份
18. mysqldump -u root -p database_name | gzip > /backup/database_name.sql.gz
20. # 备份并添加时间戳
21. mysqldump -u root -p database_name > /backup/database_name_$(date +%Y%m%d_%H%M%S).sql

复制代码

使用mariabackup工具进行物理备份（热备份）：

2. # 安装mariabackup
3. sudo dnf install -y mariadb-backup
5. # 创建完整备份
6. mariabackup --backup --target-dir=/backup/full_backup --user=root --password=your_password
8. # 准备备份
9. mariabackup --prepare --target-dir=/backup/full_backup
11. # 创建增量备份（基于完整备份）
12. mariabackup --backup --target-dir=/backup/inc_backup1 --incremental-basedir=/backup/full_backup --user=root --password=your_password
14. # 准备增量备份
15. mariabackup --prepare --target-dir=/backup/full_backup --incremental-dir=/backup/inc_backup1

复制代码

从逻辑备份恢复：

2. # 恢复整个数据库系统
3. mysql -u root -p < /backup/all_databases.sql
5. # 恢复特定数据库
6. mysql -u root -p database_name < /backup/database_name.sql
8. # 从压缩备份恢复
9. gunzip < /backup/database_name.sql.gz | mysql -u root -p database_name

复制代码

从物理备份恢复：

2. # 停止MariaDB服务
3. sudo systemctl stop mariadb
5. # 备份原始数据目录
6. sudo mv /var/lib/mysql /var/lib/mysql.bak
8. # 恢复数据
9. sudo mariabackup --copy-back --target-dir=/backup/full_backup
11. # 修复权限
12. sudo chown -R mysql:mysql /var/lib/mysql
14. # 启动MariaDB服务
15. sudo systemctl start mariadb

复制代码

创建自动化备份脚本：

2. #!/bin/bash
4. # 设置变量
5. BACKUP_DIR="/backup/mysql"
6. DATE=$(date +%Y%m%d_%H%M%S)
7. MYSQL_USER="root"
8. MYSQL_PASSWORD="your_password"
9. RETENTION_DAYS=30
11. # 创建备份目录
12. mkdir -p $BACKUP_DIR
14. # 备份所有数据库
15. mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases | gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz
17. # 备份重要数据库
18. for DB in database1 database2 database3; do
19.     mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DB | gzip > $BACKUP_DIR/${DB}_$DATE.sql.gz
20. done
22. # 删除旧备份
23. find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
25. # 记录备份状态
26. echo "Backup completed at $(date)" >> $BACKUP_DIR/backup_log.txt

复制代码

设置脚本可执行并添加到cron：

2. chmod +x /usr/local/bin/mysql_backup.sh
3. crontab -e

复制代码

添加以下行以每天凌晨2点执行备份：

2. 0 2 * * * /usr/local/bin/mysql_backup.sh

复制代码

6.2 PostgreSQL备份与恢复

使用pg_dump和pg_dumpall工具进行逻辑备份：

2. # 备份所有数据库
3. pg_dumpall -U postgres -f /backup/all_databases.sql
5. # 备份特定数据库
6. pg_dump -U postgres database_name > /backup/database_name.sql
8. # 备份特定数据库并压缩
9. pg_dump -U postgres database_name | gzip > /backup/database_name.sql.gz
11. # 备份数据库结构（不包含数据）
12. pg_dump -U postgres -s database_name > /backup/database_structure.sql
14. # 备份数据库数据（不包含结构）
15. pg_dump -U postgres -a database_name > /backup/database_data.sql
17. # 备份特定表
18. pg_dump -U postgres -t table1 -t table2 database_name > /backup/tables.sql
20. # 创建自定义格式的备份（允许选择性恢复）
21. pg_dump -U postgres -Fc database_name > /backup/database_name.dump
23. # 备份并添加时间戳
24. pg_dump -U postgres database_name > /backup/database_name_$(date +%Y%m%d_%H%M%S).sql

复制代码

使用pg_basebackup工具进行物理备份（热备份）：

2. # 创建完整备份
3. pg_basebackup -U postgres -D /backup/postgresql_backup -Ft -z -P -X stream
5. # 创建增量备份需要使用WAL归档
6. # 首先配置postgresql.conf
7. wal_level = replica
8. archive_mode = on
9. archive_command = 'cp %p /backup/wal_archive/%f'
11. # 重启PostgreSQL服务
12. sudo systemctl restart postgresql
14. # 然后可以使用pg_basebackup创建基础备份，WAL文件将自动归档

复制代码

从逻辑备份恢复：

2. # 恢复所有数据库
3. psql -U postgres -f /backup/all_databases.sql
5. # 恢复特定数据库
6. psql -U postgres -d database_name -f /backup/database_name.sql
8. # 从压缩备份恢复
9. gunzip -c /backup/database_name.sql.gz | psql -U postgres -d database_name
11. # 从自定义格式备份恢复
12. pg_restore -U postgres -d database_name /backup/database_name.dump

复制代码

从物理备份恢复：

2. # 停止PostgreSQL服务
3. sudo systemctl stop postgresql
5. # 备份原始数据目录
6. sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.bak
8. # 恢复数据
9. sudo tar -xzf /backup/postgresql_backup/base.tar.gz -C /var/lib/pgsql
11. # 如果有WAL归档，创建recovery.conf文件
12. sudo vim /var/lib/pgsql/data/recovery.conf

复制代码

添加以下内容：

2. restore_command = 'cp /backup/wal_archive/%f %p'
3. recovery_target_time = '2023-01-01 12:00:00'
4. recovery_target_inclusive = true

复制代码

修复权限并启动PostgreSQL服务：

2. sudo chown -R postgres:postgres /var/lib/pgsql/data
3. sudo systemctl start postgresql

复制代码

创建自动化备份脚本：

2. #!/bin/bash
4. # 设置变量
5. BACKUP_DIR="/backup/postgresql"
6. DATE=$(date +%Y%m%d_%H%M%S)
7. POSTGRES_USER="postgres"
8. RETENTION_DAYS=30
10. # 创建备份目录
11. mkdir -p $BACKUP_DIR
13. # 备份所有数据库（全局对象）
14. pg_dumpall -U $POSTGRES_USER -g > $BACKUP_DIR/globals_$DATE.sql
16. # 备份重要数据库
17. for DB in database1 database2 database3; do
18.     pg_dump -U $POSTGRES_USER -Fc $DB > $BACKUP_DIR/${DB}_$DATE.dump
19. done
21. # 创建基础备份
22. pg_basebackup -U $POSTGRES_USER -D $BACKUP_DIR/base_backup_$DATE -Ft -z -P -X stream
24. # 删除旧备份
25. find $BACKUP_DIR -type f -name "*.sql" -mtime +$RETENTION_DAYS -delete
26. find $BACKUP_DIR -type f -name "*.dump" -mtime +$RETENTION_DAYS -delete
27. find $BACKUP_DIR -type d -name "base_backup_*" -mtime +$RETENTION_DAYS -exec rm -rf {} \;
29. # 记录备份状态
30. echo "Backup completed at $(date)" >> $BACKUP_DIR/backup_log.txt

复制代码

设置脚本可执行并添加到cron：

2. chmod +x /usr/local/bin/postgresql_backup.sh
3. crontab -e

复制代码

添加以下行以每天凌晨2点执行备份：

2. 0 2 * * * /usr/local/bin/postgresql_backup.sh

复制代码

7. 数据库日常管理最佳实践

7.1 监控与维护

使用内置工具和命令监控数据库状态：

2. -- 查看服务器状态
3. SHOW STATUS;
4. SHOW VARIABLES;
6. -- 查看进程列表
7. SHOW PROCESSLIST;
8. SHOW FULL PROCESSLIST;
10. -- 查看运行中的查询
11. SELECT id, user, host, db, command, time, state, info
12. FROM information_schema.processlist
13. WHERE command != 'Sleep';
15. -- 查看服务器负载
16. SHOW GLOBAL STATUS LIKE 'Threads_connected';
17. SHOW GLOBAL STATUS LIKE 'Threads_running';
18. SHOW GLOBAL STATUS LIKE 'Questions';
19. SHOW GLOBAL STATUS LIKE 'Slow_queries';
21. -- 查看InnoDB状态
22. SHOW ENGINE INNODB STATUS;
24. -- 查看锁信息
25. SHOW OPEN TABLES WHERE In_use > 0;
26. SELECT * FROM information_schema.innodb_locks;
27. SELECT * FROM information_schema.innodb_lock_waits;
29. -- 查看复制状态（如果配置了复制）
30. SHOW SLAVE STATUS\G
31. SHOW MASTER STATUS\G

复制代码

使用mysqladmin工具进行监控：

2. # 查看服务器状态
3. mysqladmin -u root -p status
4. mysqladmin -u root -p extended-status
6. # 查看进程列表
7. mysqladmin -u root -p processlist
9. # 查看服务器变量
10. mysqladmin -u root -p variables
12. # 查看服务器是否存活
13. mysqladmin -u root -p ping

复制代码

使用内置工具和命令监控数据库状态：

2. -- 查看服务器状态
3. SELECT * FROM pg_stat_activity;
4. SELECT * FROM pg_stat_database;
6. -- 查看运行中的查询
7. SELECT pid, usename, application_name, client_addr, state, query, query_start
8. FROM pg_stat_activity
9. WHERE state != 'idle';
11. -- 查看表统计信息
12. SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
13. FROM pg_stat_user_tables;
15. -- 查看索引统计信息
16. SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
17. FROM pg_stat_user_indexes;
19. -- 查看锁信息
20. SELECT locktype, relation::regclass, mode, pid
21. FROM pg_locks
22. WHERE NOT granted;
24. -- 查看复制状态（如果配置了复制）
25. SELECT * FROM pg_stat_replication;

复制代码

使用pg_stat_statements扩展监控查询性能：

2. -- 启用pg_stat_statements扩展
3. CREATE EXTENSION pg_stat_statements;
5. -- 查看查询统计信息
6. SELECT query, calls, total_time, mean_time, rows
7. FROM pg_stat_statements
8. ORDER BY total_time DESC
9. LIMIT 10;

复制代码

创建定期维护脚本：

2. #!/bin/bash
4. # MySQL/MariaDB维护脚本
6. # 设置变量
7. MYSQL_USER="root"
8. MYSQL_PASSWORD="your_password"
9. LOG_FILE="/var/log/mysql_maintenance.log"
11. # 记录开始时间
12. echo "Maintenance started at $(date)" >> $LOG_FILE
14. # 检查并优化表
15. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT CONCAT('CHECK TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')" | grep -v "CONCAT" | mysql -u $MYSQL_USER -p$MYSQL_PASSWORD
17. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')" | grep -v "CONCAT" | mysql -u $MYSQL_USER -p$MYSQL_PASSWORD
19. # 删除二进制日志（保留最近7天）
20. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"
22. # 刷新慢查询日志
23. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL slow_query_log = 'ON';"
25. # 记录结束时间
26. echo "Maintenance completed at $(date)" >> $LOG_FILE

复制代码

PostgreSQL维护脚本：

2. #!/bin/bash
4. # PostgreSQL维护脚本
6. # 设置变量
7. POSTGRES_USER="postgres"
8. LOG_FILE="/var/log/postgresql_maintenance.log"
10. # 记录开始时间
11. echo "Maintenance started at $(date)" >> $LOG_FILE
13. # 更新统计信息
14. sudo -u $POSTGRES_USER psql -c "ANALYZE;"
16. # 清理过期事务ID
17. sudo -u $POSTGRES_USER vacuumdb -a -z
19. # 重建索引
20. sudo -u $POSTGRES_USER reindexdb -a
22. # 清理WAL文件（如果使用归档）
23. # 这里需要根据实际归档策略进行调整
25. # 记录结束时间
26. echo "Maintenance completed at $(date)" >> $LOG_FILE

复制代码

7.2 安全最佳实践

实施严格的访问控制策略：

2. -- MySQL/MariaDB访问控制
4. -- 创建具有最小必要权限的用户
5. CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
6. GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_user'@'localhost';
8. -- 限制root用户访问
9. DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
10. FLUSH PRIVILEGES;
12. -- 定期审查用户权限
13. SELECT user, host, authentication_string FROM mysql.user;
14. SELECT * FROM information_schema.user_privileges;

复制代码

2. -- PostgreSQL访问控制
4. -- 创建具有最小必要权限的角色
5. CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
6. GRANT CONNECT ON DATABASE database_name TO app_user;
7. GRANT USAGE ON SCHEMA public TO app_user;
8. GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
9. GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
11. -- 限制postgres用户远程访问
12. ALTER ROLE postgres WITH NOLOGIN;
14. -- 定期审查角色权限
15. SELECT rolname, rolcreaterole, rolcreatedb, rolsuper FROM pg_roles;
16. SELECT grantee, privilege_type FROM information_schema.role_table_grants;

复制代码

配置防火墙和SSL以保护数据库服务器：

2. # 配置防火墙规则
3. sudo firewall-cmd --permanent --add-service=mysql
4. sudo firewall-cmd --permanent --add-service=postgresql
5. sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" service name="mysql" accept'
6. sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" service name="postgresql" accept'
7. sudo firewall-cmd --reload
9. # 配置SELinux（如果启用）
10. sudo setsebool -P mysql_connect_any=1
11. sudo setsebool -P postgresql_connect_any=1

复制代码

实施数据加密保护敏感信息：

2. -- MySQL/MariaDB数据加密
4. -- 启用数据静态加密（需要企业版或特定版本）
5. -- 在配置文件中添加
6. # plugin-load-add=file_key_management.so
7. # file_key_management_filename = /etc/mysql/encryption.key
8. # innodb_encrypt_tables = ON
9. # innodb_encrypt_log = ON
10. # innodb_encryption_threads = 4
12. -- 使用加密函数
13. SELECT AES_ENCRYPT('sensitive_data', 'encryption_key');
14. SELECT AES_DECRYPT(encrypted_column, 'encryption_key') FROM table_name;

复制代码

2. -- PostgreSQL数据加密
4. -- 使用pgcrypto扩展
5. CREATE EXTENSION pgcrypto;
7. -- 使用加密函数
8. SELECT encrypt('sensitive_data', 'encryption_key', 'aes');
9. SELECT decrypt(encrypted_column, 'encryption_key', 'aes') FROM table_name;
11. -- 使用加密哈希
12. SELECT crypt('password', gen_salt('bf'));
13. SELECT (password_hash = crypt('input_password', password_hash)) AS password_match FROM users;

复制代码

7.3 高可用性与灾难恢复

配置主从复制实现高可用性：

2. # 主服务器配置
3. sudo vim /etc/my.cnf.d/custom.cnf

复制代码

添加以下配置：

2. [mysqld]
3. # 服务器ID
4. server-id = 1
6. # 启用二进制日志
7. log-bin = mysql-bin
8. binlog_format = ROW
10. # 需要复制的数据库
11. binlog-do-db = database_name
13. # 忽略复制的数据库
14. binlog-ignore-db = mysql
15. binlog-ignore-db = information_schema
16. binlog-ignore-db = performance_schema

复制代码

重启MariaDB服务：

2. sudo systemctl restart mariadb

复制代码

创建复制用户：

2. CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
3. GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
4. FLUSH PRIVILEGES;

复制代码

获取主服务器二进制日志位置：

2. SHOW MASTER STATUS;

复制代码

从服务器配置：

2. sudo vim /etc/my.cnf.d/custom.cnf

复制代码

添加以下配置：

2. [mysqld]
3. # 服务器ID（必须与主服务器不同）
4. server-id = 2
6. # 中继日志配置
7. relay-log = mysql-relay-bin
8. relay-log-index = mysql-relay-bin.index
10. # 只读模式（可选）
11. read_only = ON

复制代码

重启MariaDB服务：

2. sudo systemctl restart mariadb

复制代码

配置从服务器连接到主服务器：

2. CHANGE MASTER TO
3.     MASTER_HOST = 'master_ip',
4.     MASTER_USER = 'repl_user',
5.     MASTER_PASSWORD = 'repl_password',
6.     MASTER_LOG_FILE = 'mysql-bin.000001',
7.     MASTER_LOG_POS = 154;
9. -- 启动复制
10. START SLAVE;
12. -- 检查复制状态
13. SHOW SLAVE STATUS\G

复制代码

配置PostgreSQL流复制实现高可用性：

2. # 主服务器配置
3. sudo vim /var/lib/pgsql/data/postgresql.conf

复制代码

修改以下参数：

2. # 服务器ID
3. wal_level = replica
4. max_wal_senders = 3
5. max_replication_slots = 3
6. synchronous_commit = on
7. synchronous_standby_names = 'standby1'

复制代码

编辑pg_hba.conf文件：

2. sudo vim /var/lib/pgsql/data/pg_hba.conf

复制代码

添加以下行：

2. # 允许复制连接
3. host replication replicator 192.168.1.0/24 md5

复制代码

创建复制用户：

2. CREATE USER replicator REPLICATION LOGIN PASSWORD 'repl_password';

复制代码

重启PostgreSQL服务：

2. sudo systemctl restart postgresql

复制代码

从服务器配置：

2. # 停止PostgreSQL服务
3. sudo systemctl stop postgresql
5. # 备份数据目录
6. sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.bak
8. # 使用pg_basebackup从主服务器获取基础备份
9. sudo -u postgres pg_basebackup -h master_ip -U replicator -D /var/lib/pgsql/data -Fp -Xs -P -R
11. # 编辑standby.signal文件
12. sudo vim /var/lib/pgsql/data/standby.signal

复制代码

添加以下内容：

2. standby_mode = 'on'

复制代码

编辑postgresql.conf文件：

2. sudo vim /var/lib/pgsql/data/postgresql.conf

复制代码

添加以下配置：

2. # 服务器ID（必须与主服务器不同）
3. hot_standby = on
5. # 主服务器连接信息
6. primary_conninfo = 'host=master_ip port=5432 user=replicator password=repl_password'
8. # 应用延迟（可选）
9. recovery_min_apply_delay = 0

复制代码

启动PostgreSQL服务：

2. sudo systemctl start postgresql

复制代码

验证复制状态：

2. -- 在主服务器上执行
3. SELECT * FROM pg_stat_replication;
5. -- 在从服务器上执行
6. SELECT * FROM pg_stat_wal_receiver;

复制代码

制定全面的灾难恢复计划：

1. 风险评估：识别潜在风险（硬件故障、自然灾害、人为错误等）评估每种风险的可能性和影响确定恢复时间目标(RTO)和恢复点目标(RPO)
2. 识别潜在风险（硬件故障、自然灾害、人为错误等）
3. 评估每种风险的可能性和影响
4. 确定恢复时间目标(RTO)和恢复点目标(RPO)
5. 备份策略：定期全量备份（每日或每周）增量备份（每小时或每日）异地备份存储备份验证和测试
6. 定期全量备份（每日或每周）
7. 增量备份（每小时或每日）
8. 异地备份存储
9. 备份验证和测试
10. 恢复流程：详细记录恢复步骤指定恢复负责人和联系方式定期进行恢复演练
11. 详细记录恢复步骤
12. 指定恢复负责人和联系方式
13. 定期进行恢复演练
14. 故障转移计划：自动故障转移机制手动故障转移流程故障恢复后的数据同步
15. 自动故障转移机制
16. 手动故障转移流程
17. 故障恢复后的数据同步
18. 文档和沟通：维护详细的系统文档建立紧急沟通渠道定期更新和审查灾难恢复计划
19. 维护详细的系统文档
20. 建立紧急沟通渠道
21. 定期更新和审查灾难恢复计划

风险评估：

• 识别潜在风险（硬件故障、自然灾害、人为错误等）
• 评估每种风险的可能性和影响
• 确定恢复时间目标(RTO)和恢复点目标(RPO)

备份策略：

• 定期全量备份（每日或每周）
• 增量备份（每小时或每日）
• 异地备份存储
• 备份验证和测试

恢复流程：

• 详细记录恢复步骤
• 指定恢复负责人和联系方式
• 定期进行恢复演练

故障转移计划：

• 自动故障转移机制
• 手动故障转移流程
• 故障恢复后的数据同步

文档和沟通：

• 维护详细的系统文档
• 建立紧急沟通渠道
• 定期更新和审查灾难恢复计划

8. 监控与故障排除

8.1 监控工具

使用系统监控工具监控数据库服务器：

2. # 安装监控工具
3. sudo dnf install -y htop iotop sysstat nmon
5. # 使用htop监控进程和资源使用
6. htop
8. # 使用iotop监控I/O使用
9. sudo iotop
11. # 使用vmstat监控内存和CPU
12. vmstat 1 10
14. # 使用iostat监控磁盘I/O
15. iostat -xz 1 10
17. # 使用sar收集系统活动数据
18. sar -u 1 10    # CPU使用率
19. sar -r 1 10    # 内存使用率
20. sar -b 1 10    # I/O传输率
21. sar -n DEV 1 10 # 网络使用率

复制代码

安装和使用数据库专用监控工具：

2. # 安装Percona Monitoring and Management (PMM) for MySQL/MariaDB
3. # 首先安装Docker
4. sudo dnf install -y docker
5. sudo systemctl start docker
6. sudo systemctl enable docker
8. # 运行PMM服务器
9. docker run -d -p 80:80 -p 443:443 --name pmm-server \
10.   -v /opt/prometheus/data:/opt/prometheus/data \
11.   -v /opt/grafana/data:/opt/grafana/data \
12.   -v /var/lib/mysql:/var/lib/mysql \
13.   -v /var/lib/grafana:/var/lib/grafana \
14.   --restart always \
15.   percona/pmm-server:latest
17. # 在数据库服务器上安装PMM客户端
18. sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
19. sudo dnf install -y pmm2-client
21. # 注册客户端到PMM服务器
22. pmm-admin config --server-insecure-tls --server-url=https://pmm-server-ip --force
23. pmm-admin add mysql --username=root --password=your_password

复制代码

对于PostgreSQL，可以使用pgAdmin和pgBadger：

2. # 安装pgAdmin
3. sudo dnf install -y pgadmin4
5. # 安装pgBadger
6. sudo dnf install -y pgbadger
8. # 使用pgBadger分析PostgreSQL日志
9. pgbadger /var/lib/pgsql/data/log/postgresql-*.log -o /var/www/html/pgbadger/index.html

复制代码

创建自定义监控脚本：

2. #!/bin/bash
4. # MySQL/MariaDB监控脚本
6. # 设置变量
7. MYSQL_USER="monitor_user"
8. MYSQL_PASSWORD="monitor_password"
9. ALERT_EMAIL="admin@example.com"
10. ALERT_SUBJECT="MySQL Alert"
11. LOG_FILE="/var/log/mysql_monitor.log"
12. TEMP_FILE="/tmp/mysql_status.txt"
14. # 获取MySQL状态
15. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL STATUS;" > $TEMP_FILE
16. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL VARIABLES;" >> $TEMP_FILE
17. mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW PROCESSLIST;" >> $TEMP_FILE
19. # 检查关键指标
20. THREADS_CONNECTED=$(grep "Threads_connected" $TEMP_FILE | awk '{print $2}')
21. THREADS_RUNNING=$(grep "Threads_running" $TEMP_FILE | awk '{print $2}')
22. SLOW_QUERIES=$(grep "Slow_queries" $TEMP_FILE | awk '{print $2}')
23. MAX_CONNECTIONS=$(grep "max_connections" $TEMP_FILE | awk '{print $2}')
24. CONNECTION_USAGE=$(echo "scale=2; $THREADS_CONNECTED * 100 / $MAX_CONNECTIONS" | bc)
26. # 检查阈值并发出警报
27. if [ $(echo "$CONNECTION_USAGE > 80" | bc) -eq 1 ]; then
28.     echo "High connection usage: $CONNECTION_USAGE%" | mailx -s "$ALERT_SUBJECT" $ALERT_EMAIL
29.     echo "$(date): High connection usage: $CONNECTION_USAGE%" >> $LOG_FILE
30. fi
32. if [ $THREADS_RUNNING -gt 10 ]; then
33.     echo "High running threads: $THREADS_RUNNING" | mailx -s "$ALERT_SUBJECT" $ALERT_EMAIL
34.     echo "$(date): High running threads: $THREADS_RUNNING" >> $LOG_FILE
35. fi
37. # 清理临时文件
38. rm -f $TEMP_FILE

复制代码

8.2 常见问题及解决方案

问题：数据库响应缓慢

解决方案：

2. -- MySQL/MariaDB
4. -- 识别慢查询
5. SELECT * FROM mysql.slow_log WHERE start_time > NOW() - INTERVAL 1 DAY;
7. -- 启用慢查询日志
8. SET GLOBAL slow_query_log = 'ON';
9. SET GLOBAL long_query_time = 2;
10. SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
12. -- 分析查询执行计划
13. EXPLAIN SELECT * FROM large_table WHERE column = 'value';
15. -- 检查索引使用情况
16. SELECT * FROM sys.schema_unused_indexes;
17. SELECT * FROM sys.schema_redundant_indexes;
19. -- 检查表碎片化
20. SELECT table_schema, table_name, data_free / 1024 / 1024 AS fragmentation_mb
21. FROM information_schema.tables
22. WHERE data_free > 0;
24. -- 优化表
25. OPTIMIZE TABLE table_name;

复制代码

2. -- PostgreSQL
4. -- 识别慢查询
5. SELECT query, calls, total_time, mean_time, rows
6. FROM pg_stat_statements
7. ORDER BY total_time DESC
8. LIMIT 10;
10. -- 启用查询日志
11. ALTER SYSTEM SET log_min_duration_statement = '2000';
12. SELECT pg_reload_conf();
14. -- 分析查询执行计划
15. EXPLAIN ANALYZE SELECT * FROM large_table WHERE column = 'value';
17. -- 检查索引使用情况
18. SELECT schemaname, tablename, indexname, idx_scan
19. FROM pg_stat_user_indexes
20. WHERE idx_scan = 0;
22. -- 检查表膨胀
23. SELECT
24.   schemaname, tablename,
25.   pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
26.   pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as external_size,
27.   pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size
28. FROM pg_tables
29. WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
30. ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
32. -- 清理表膨胀
33. VACUUM (FULL, ANALYZE) table_name;

复制代码

问题：无法连接到数据库

解决方案：

2. # MySQL/MariaDB连接问题排查
4. # 检查服务状态
5. sudo systemctl status mariadb
7. # 检查端口监听
8. sudo netstat -tulnp | grep mysql
9. sudo ss -tulnp | grep mysql
11. # 检查防火墙设置
12. sudo firewall-cmd --list-all
14. # 检查SELinux设置
15. sudo getsebool -a | grep mysql
16. sudo grep mysql /var/log/audit/audit.log | audit2allow -M mymysql
17. sudo semodule -i mymysql.pp
19. # 检查用户权限
20. mysql -u root -p -e "SELECT user, host FROM mysql.user;"
22. # 检查错误日志
23. sudo tail -f /var/log/mariadb/mariadb.log

复制代码

2. # PostgreSQL连接问题排查
4. # 检查服务状态
5. sudo systemctl status postgresql
7. # 检查端口监听
8. sudo netstat -tulnp | grep postgres
9. sudo ss -tulnp | grep postgres
11. # 检查防火墙设置
12. sudo firewall-cmd --list-all
14. # 检查SELinux设置
15. sudo getsebool -a | grep postgres
16. sudo grep postgres /var/log/audit/audit.log | audit2allow -mypostgres
17. sudo semodule -i mypostgres.pp
19. # 检查用户权限
20. sudo -u postgres psql -c "SELECT usename, usecreatedb, usesuper, userepl FROM pg_user;"
22. # 检查客户端认证配置
23. sudo cat /var/lib/pgsql/data/pg_hba.conf
25. # 检查错误日志
26. sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log

复制代码

问题：主从复制中断

解决方案：

2. -- MySQL/MariaDB复制问题排查
4. -- 在从服务器上检查复制状态
5. SHOW SLAVE STATUS\G
7. -- 检查错误信息
8. SHOW SLAVE STATUS\G
9. -- 查看Last_Error和Last_SQL_Error字段
11. -- 常见错误解决方法
13. -- 1. 主键冲突
14. STOP SLAVE;
15. SET GLOBAL sql_slave_skip_counter = 1;
16. START SLAVE;
18. -- 2. 网络问题
19. -- 检查主从服务器之间的网络连接
20. ping master_ip
21. telnet master_ip 3306
23. -- 3. 中继日志损坏
24. STOP SLAVE;
25. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
26. START SLAVE;
28. -- 4. 重新同步数据
29. -- 使用mysqldump或mariabackup重新同步数据

复制代码

2. -- PostgreSQL复制问题排查
4. -- 在主服务器上检查复制状态
5. SELECT * FROM pg_stat_replication;
7. -- 在从服务器上检查接收状态
8. SELECT * FROM pg_stat_wal_receiver;
10. -- 检查从服务器日志
11. sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log
13. -- 常见错误解决方法
15. -- 1. WAL文件缺失
16. -- 检查归档设置
17. SHOW archive_command;
19. -- 2. 网络问题
20. -- 检查主从服务器之间的网络连接
21. ping master_ip
22. telnet master_ip 5432
24. -- 3. 重新同步数据
25. -- 在从服务器上停止服务
26. sudo systemctl stop postgresql
28. -- 重新备份主服务器数据
29. sudo -u postgres rm -rf /var/lib/pgsql/data/*
30. sudo -u postgres pg_basebackup -h master_ip -U replicator -D /var/lib/pgsql/data -Fp -Xs -P -R
32. -- 启动从服务器
33. sudo systemctl start postgresql

复制代码

9. 高级配置技巧

9.1 MySQL/MariaDB高级配置

虽然MySQL 8.0移除了查询缓存功能，但在MariaDB中仍然可以使用：

2. [mysqld]
3. # 查询缓存配置
4. query_cache_type = 1
5. query_cache_size = 128M
6. query_cache_limit = 2M
7. query_cache_min_res_unit = 4K

复制代码

监控查询缓存使用情况：

2. SHOW STATUS LIKE 'Qcache%';
3. SHOW STATUS LIKE 'Com_select';

复制代码

对于高并发环境，配置线程池可以提高性能：

2. [mysqld]
3. # 线程池配置
4. thread_pool_size = 16           # 通常为CPU核心数
5. thread_pool_oversubscribe = 10  # 每个线程组的额外线程数
6. thread_pool_stall_limit = 100   # 线程停滞时间阈值（毫秒）

复制代码

针对InnoDB存储引擎的高级优化：

2. [mysqld]
3. # InnoDB I/O优化
4. innodb_io_capacity = 2000           # SSD使用2000，HDD使用200
5. innodb_io_capacity_max = 4000       # SSD使用4000，HDD使用400
6. innodb_flush_neighbors = 0          # SSD使用0，HDD使用1
8. # InnoDB并发控制
9. innodb_thread_concurrency = 0       # 0表示不限制
10. innodb_read_io_threads = 8         # 读I/O线程数
11. innodb_write_io_threads = 8        # 写I/O线程数
13. # InnoDB内存优化
14. innodb_buffer_pool_instances = 4    # 缓冲池实例数，通常为CPU核心数
15. innodb_page_cleaners = 4            # 页清理线程数，通常为缓冲池实例数
16. innodb_purge_threads = 4            # 清除线程数，通常为CPU核心数
18. # InnoDB日志优化
19. innodb_log_write_ahead_size = 8192  # 日志写入前大小，通常与文件系统块大小匹配
20. innodb_log_buffer_size = 64M        # 日志缓冲区大小
21. innodb_log_file_size = 512M         # 日志文件大小

复制代码

9.2 PostgreSQL高级配置

PostgreSQL支持并行查询，可以显著提高大型查询的性能：

2. # 并行查询配置
3. max_parallel_workers_per_gather = 2    # 每个Gather节点的最大并行工作进程数
4. max_parallel_workers = 8                 # 最大并行工作进程数
5. max_parallel_maintenance_workers = 2     # 维护操作的最大并行工作进程数
6. parallel_tuple_cost = 0.1               # 并行元组成本
7. parallel_setup_cost = 1000.0            # 并行设置成本
8. min_parallel_table_scan_size = 8MB      # 最小并行表扫描大小
9. min_parallel_index_scan_size = 512kB    # 最小并行索引扫描大小

复制代码

强制使用并行查询：

2. -- 设置表并行扫描的工作进程数
3. ALTER TABLE large_table SET (parallel_workers = 4);
5. -- 强制使用并行查询
6. SET max_parallel_workers_per_gather = 4;
7. EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;

复制代码

PostgreSQL支持表空间，可以将数据库对象存储在不同的位置：

2. -- 创建表空间
3. CREATE TABLESPACE fast_space LOCATION '/mnt/fast_disk/postgresql';
4. CREATE TABLESPACE archive_space LOCATION '/mnt/archive_disk/postgresql';
6. -- 在表空间中创建表
7. CREATE TABLE large_table (
8.     id SERIAL PRIMARY KEY,
9.     data TEXT
10. ) TABLESPACE fast_space;
12. -- 将现有表移动到表空间
13. ALTER TABLE existing_table SET TABLESPACE fast_space;
15. -- 为特定用户设置默认表空间
16. ALTER ROLE user_name SET default_tablespace = fast_space;

复制代码

对于大型表，使用分区可以提高查询性能和维护效率：

2. -- 创建分区表
3. CREATE TABLE measurement (
4.     id SERIAL,
5.     logdate DATE NOT NULL,
6.     peaktemp INT,
7.     unitsales INT
8. ) PARTITION BY RANGE (logdate);
10. -- 创建分区
11. CREATE TABLE measurement_y2023m01 PARTITION OF measurement
12.     FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
14. CREATE TABLE measurement_y2023m02 PARTITION OF measurement
15.     FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
17. -- 创建索引
18. CREATE INDEX ON measurement (logdate);
20. -- 查询分区表
21. EXPLAIN SELECT * FROM measurement WHERE logdate = '2023-01-15';
23. -- 添加新分区
24. CREATE TABLE measurement_y2023m03 PARTITION OF measurement
25.     FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
27. -- 删除旧分区
28. DROP TABLE measurement_y2022m12;

复制代码

9.3 自动化运维

创建数据库自动化部署脚本：

2. #!/bin/bash
4. # 数据库自动化部署脚本
6. # 设置变量
7. DB_TYPE="mariadb"  # 或 "postgresql"
8. DB_NAME="app_db"
9. DB_USER="app_user"
10. DB_PASSWORD="secure_password"
11. ROOT_PASSWORD="root_password"
13. # 安装数据库
14. if [ "$DB_TYPE" == "mariadb" ]; then
15.     # 安装MariaDB
16.     sudo dnf install -y mariadb-server
17.     sudo systemctl start mariadb
18.     sudo systemctl enable mariadb
19.    
20.     # 安全初始化
21.     sudo mysql_secure_installation <<EOF
23. y
24. $ROOT_PASSWORD
25. $ROOT_PASSWORD
26. y
27. y
28. y
29. y
30. EOF
31.    
32.     # 创建数据库和用户
33.     mysql -u root -p$ROOT_PASSWORD -e "CREATE DATABASE $DB_NAME;"
34.     mysql -u root -p$ROOT_PASSWORD -e "CREATE USER '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASSWORD';"
35.     mysql -u root -p$ROOT_PASSWORD -e "GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost';"
36.     mysql -u root -p$ROOT_PASSWORD -e "FLUSH PRIVILEGES;"
37.    
38.     # 配置数据库
39.     sudo tee /etc/my.cnf.d/custom.cnf > /dev/null <<EOF
40. [mysqld]
41. character-set-server = utf8mb4
42. collation-server = utf8mb4_unicode_ci
43. default-storage-engine = InnoDB
44. max_connections = 200
45. innodb_buffer_pool_size = 1G
46. EOF
47.    
48.     sudo systemctl restart mariadb
49.    
50. elif [ "$DB_TYPE" == "postgresql" ]; then
51.     # 安装PostgreSQL
52.     sudo dnf install -y postgresql-server postgresql-contrib
53.     sudo postgresql-setup --initdb
54.     sudo systemctl start postgresql
55.     sudo systemctl enable postgresql
56.    
57.     # 设置postgres用户密码
58.     sudo -u postgres psql -c "ALTER USER postgres PASSWORD '$ROOT_PASSWORD';"
59.    
60.     # 创建数据库和用户
61.     sudo -u postgres createdb $DB_NAME
62.     sudo -u postgres psql -c "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';"
63.     sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;"
64.    
65.     # 配置数据库
66.     sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /var/lib/pgsql/data/postgresql.conf
67.     sudo sed -i "s/#port = 5432/port = 5432/" /var/lib/pgsql/data/postgresql.conf
68.     sudo sed -i "s/#max_connections = 100/max_connections = 200/" /var/lib/pgsql/data/postgresql.conf
69.     sudo sed -i "s/#shared_buffers = 128MB/shared_buffers = 256MB/" /var/lib/pgsql/data/postgresql.conf
70.    
71.     sudo tee -a /var/lib/pgsql/data/pg_hba.conf > /dev/null <<EOF
72. host    $DB_NAME    $DB_USER    0.0.0.0/0    md5
73. EOF
74.    
75.     sudo systemctl restart postgresql
76. fi
78. # 配置防火墙
79. sudo firewall-cmd --permanent --add-service=${DB_TYPE}
80. sudo firewall-cmd --reload
82. echo "Database deployment completed successfully!"

复制代码

使用Prometheus和Grafana实现自动化监控与报警：

2. # 安装Prometheus
3. sudo dnf install -y prometheus
4. sudo systemctl start prometheus
5. sudo systemctl enable prometheus
7. # 安装Grafana
8. sudo dnf install -y grafana
9. sudo systemctl start grafana
10. sudo systemctl enable grafana
12. # 安装MySQL/MariaDB Exporter
13. sudo dnf install -y mariadb-exporter
14. sudo systemctl start mariadb-exporter
15. sudo systemctl enable mariadb-exporter
17. # 安装PostgreSQL Exporter
18. sudo dnf install -y postgresql-exporter
19. sudo systemctl start postgresql-exporter
20. sudo systemctl enable postgresql-exporter
22. # 配置Prometheus采集数据
23. sudo tee /etc/prometheus/prometheus.yml > /dev/null <<EOF
24. global:
25.   scrape_interval: 15s
27. scrape_configs:
28.   - job_name: 'prometheus'
29.     static_configs:
30.       - targets: ['localhost:9090']
32.   - job_name: 'mariadb'
33.     static_configs:
34.       - targets: ['localhost:9104']
36.   - job_name: 'postgresql'
37.     static_configs:
38.       - targets: ['localhost:9187']
39. EOF
41. sudo systemctl restart prometheus

复制代码

访问Grafana Web界面（http://localhost:3000），添加Prometheus数据源，并导入数据库监控仪表板。

创建自动化扩展与收缩脚本：

2. #!/bin/bash
4. # 数据库自动化扩展与收缩脚本
6. # 设置变量
7. DB_TYPE="mariadb"  # 或 "postgresql"
8. CPU_THRESHOLD=80
9. MEMORY_THRESHOLD=80
10. DISK_THRESHOLD=80
11. CONNECTION_THRESHOLD=80
12. SCALE_UP_THRESHOLD=3
13. SCALE_DOWN_THRESHOLD=1
14. CHECK_INTERVAL=60
16. # 获取系统指标
17. get_system_metrics() {
18.     CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}')
19.     MEMORY_USAGE=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
20.     DISK_USAGE=$(df -h / | awk 'NR==2{print $5}' | sed 's/%//')
21.    
22.     if [ "$DB_TYPE" == "mariadb" ]; then
23.         MAX_CONNECTIONS=$(mysql -u root -p$ROOT_PASSWORD -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2{print $2}')
24.         THREADS_CONNECTED=$(mysql -u root -p$ROOT_PASSWORD -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2{print $2}')
25.         CONNECTION_USAGE=$(echo "scale=2; $THREADS_CONNECTED * 100 / $MAX_CONNECTIONS" | bc)
26.     elif [ "$DB_TYPE" == "postgresql" ]; then
27.         MAX_CONNECTIONS=$(sudo -u postgres psql -t -c "SHOW max_connections;")
28.         THREADS_CONNECTED=$(sudo -u postgres psql -t -c "SELECT count(*) FROM pg_stat_activity;")
29.         CONNECTION_USAGE=$(echo "scale=2; $THREADS_CONNECTED * 100 / $MAX_CONNECTIONS" | bc)
30.     fi
31.    
32.     echo "CPU: $CPU_USAGE%, Memory: $MEMORY_USAGE%, Disk: $DISK_USAGE%, Connections: $CONNECTION_USAGE%"
33. }
35. # 扩展数据库服务器
36. scale_up() {
37.     echo "Scaling up database server..."
38.    
39.     # 这里可以添加自动扩展的逻辑，例如：
40.     # 1. 增加服务器资源（CPU、内存）
41.     # 2. 启动只读副本
42.     # 3. 调整数据库配置参数
43.    
44.     if [ "$DB_TYPE" == "mariadb" ]; then
45.         # 增加最大连接数
46.         NEW_MAX_CONNECTIONS=$(echo "$MAX_CONNECTIONS * 1.5 / 1" | bc)
47.         mysql -u root -p$ROOT_PASSWORD -e "SET GLOBAL max_connections = $NEW_MAX_CONNECTIONS;"
48.         echo "Increased max_connections to $NEW_MAX_CONNECTIONS"
49.     elif [ "$DB_TYPE" == "postgresql" ]; then
50.         # 增加最大连接数
51.         NEW_MAX_CONNECTIONS=$(echo "$MAX_CONNECTIONS * 1.5 / 1" | bc)
52.         sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = $NEW_MAX_CONNECTIONS;"
53.         sudo systemctl restart postgresql
54.         echo "Increased max_connections to $NEW_MAX_CONNECTIONS"
55.     fi
56.    
57.     # 发送通知
58.     echo "Database server scaled up at $(date)" | mailx -s "Database Scale Up Alert" admin@example.com
59. }
61. # 收缩数据库服务器
62. scale_down() {
63.     echo "Scaling down database server..."
64.    
65.     # 这里可以添加自动收缩的逻辑，例如：
66.     # 1. 减少服务器资源
67.     # 2. 关闭只读副本
68.     # 3. 调整数据库配置参数
69.    
70.     if [ "$DB_TYPE" == "mariadb" ]; then
71.         # 减少最大连接数
72.         NEW_MAX_CONNECTIONS=$(echo "$MAX_CONNECTIONS * 0.7 / 1" | bc)
73.         mysql -u root -p$ROOT_PASSWORD -e "SET GLOBAL max_connections = $NEW_MAX_CONNECTIONS;"
74.         echo "Decreased max_connections to $NEW_MAX_CONNECTIONS"
75.     elif [ "$DB_TYPE" == "postgresql" ]; then
76.         # 减少最大连接数
77.         NEW_MAX_CONNECTIONS=$(echo "$MAX_CONNECTIONS * 0.7 / 1" | bc)
78.         sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = $NEW_MAX_CONNECTIONS;"
79.         sudo systemctl restart postgresql
80.         echo "Decreased max_connections to $NEW_MAX_CONNECTIONS"
81.     fi
82.    
83.     # 发送通知
84.     echo "Database server scaled down at $(date)" | mailx -s "Database Scale Down Alert" admin@example.com
85. }
87. # 主循环
88. while true; do
89.     get_system_metrics
90.    
91.     # 检查是否需要扩展
92.     if (( $(echo "$CPU_USAGE > $CPU_THRESHOLD" | bc -l) )) || \
93.        (( $(echo "$MEMORY_USAGE > $MEMORY_THRESHOLD" | bc -l) )) || \
94.        [ "$DISK_USAGE" -gt "$DISK_THRESHOLD" ] || \
95.        (( $(echo "$CONNECTION_USAGE > $CONNECTION_THRESHOLD" | bc -l) )); then
96.         SCALE_UP_COUNTER=$((SCALE_UP_COUNTER + 1))
97.         SCALE_DOWN_COUNTER=0
98.         
99.         if [ "$SCALE_UP_COUNTER" -ge "$SCALE_UP_THRESHOLD" ]; then
100.             scale_up
101.             SCALE_UP_COUNTER=0
102.         fi
103.     else
104.         SCALE_UP_COUNTER=0
105.         SCALE_DOWN_COUNTER=$((SCALE_DOWN_COUNTER + 1))
106.         
107.         if [ "$SCALE_DOWN_COUNTER" -ge "$SCALE_DOWN_THRESHOLD" ]; then
108.             scale_down
109.             SCALE_DOWN_COUNTER=0
110.         fi
111.     fi
112.    
113.     sleep $CHECK_INTERVAL
114. done

复制代码

10. 总结

本文全面解析了Fedora系统数据库配置流程与管理最佳实践，涵盖了MySQL/MariaDB和PostgreSQL两大主流关系型数据库的安装、配置、安全、性能优化、备份恢复、日常管理、监控与故障排除以及高级配置技巧等方面。

通过遵循本文提供的最佳实践，您可以：

1. 在Fedora系统上高效安装和配置数据库服务器
2. 实施严格的安全措施保护数据库
3. 优化数据库性能以满足高并发需求
4. 建立可靠的备份与恢复机制
5. 实现有效的监控与故障排除
6. 采用高级配置技巧进一步提升数据库性能和可靠性

数据库管理是一个持续优化的过程，需要根据实际工作负载和业务需求不断调整配置和策略。希望本文提供的知识和实践能够帮助您在Fedora系统上构建稳定、高效、安全的数据库环境。