CentOS环境下的数据库管理技巧

CentOS环境下数据库管理技巧

一、基础安装与配置管理

1. 安装常见数据库

   • MySQL/MariaDB：使用sudo yum install mysql-server（或mariadb-server）安装，启动服务sudo systemctl start mysqld，并设置开机自启sudo systemctl enable mysqld。
   • PostgreSQL：通过sudo yum install postgresql-server postgresql-contrib安装，初始化数据库sudo postgresql-setup initdb，启动服务sudo systemctl start postgresql并启用开机自启。
   • 安装后需通过mysql_secure_installation（MySQL）或修改postgresql.conf（PostgreSQL）配置基础安全（如设置root密码、移除匿名用户）。

2. 配置文件优化

   • MySQL：主配置文件为/etc/my.cnf，关键参数包括innodb_buffer_pool_size（设置为物理内存的50%-80%，提升InnoDB性能）、max_connections（根据并发需求调整，避免过多连接导致内存耗尽）、innodb_log_file_size（增大日志文件减少切换频率，如设置为256M）。
   • PostgreSQL：主配置文件为/var/lib/pgsql/data/postgresql.conf，关键参数包括shared_buffers（设置为物理内存的25%-40%，用于缓存数据）、work_mem（排序和哈希操作的内存，如设置为4M）、maintenance_work_mem（维护操作内存，如设置为64M）。

二、用户与权限管理

• 创建用户与授权
  • MySQL：使用CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';创建用户，GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';授权，FLUSH PRIVILEGES;刷新权限。
  • PostgreSQL：使用createuser -U postgres -P -e username（交互式创建）或CREATE USER username WITH PASSWORD 'password';，GRANT ALL PRIVILEGES ON DATABASE database TO username;授权。
• 权限控制技巧：遵循最小权限原则，仅授予用户必要的权限（如SELECT、INSERT而非ALL PRIVILEGES）；定期使用REVOKE撤销闲置权限；避免使用root账户进行日常操作。

三、性能优化技巧

1. 硬件优化

   • 使用SSD替代HDD提升磁盘IO性能；增加内存（MySQL的innodb_buffer_pool_size依赖内存，PostgreSQL的shared_buffers同理）；使用高速网络（千兆/万兆网卡）减少网络延迟。

2. 配置参数优化

   • MySQL：调整innodb_buffer_pool_size（核心参数，建议设为物理内存的50%-80%）、query_cache_size（读多写少场景可启用，高并发写入建议关闭）、innodb_flush_log_at_trx_commit（设置为0或2提升性能，但会牺牲部分数据安全性）。
   • PostgreSQL：调整shared_buffers（缓存数据，建议25%-40%物理内存）、work_mem（排序/哈希操作内存，避免全表排序）、effective_cache_size（操作系统缓存，建议为物理内存的50%-75%）。

3. 查询与索引优化

   • 查询优化：使用EXPLAIN分析查询执行计划，避免全表扫描；减少SELECT *，只查询必要列；使用LIMIT限制返回结果数量；优化JOIN操作（避免笛卡尔积）。
   • 索引优化：为经常用于WHERE、JOIN、ORDER BY的列创建索引（如CREATE INDEX idx_name ON table(column)）；使用覆盖索引（索引包含查询所需的所有列）；避免在低基数列（如性别）上创建索引；定期使用ANALYZE TABLE更新索引统计信息。

4. 定期维护

   • 使用OPTIMIZE TABLE整理表碎片（针对MyISAM和InnoDB表）；使用ANALYZE TABLE更新表统计信息（帮助优化器选择更好的执行计划）；定期重建索引（如使用ALTER TABLE table_name DROP INDEX index_name, ADD INDEX index_name(column);）。

四、备份与恢复策略

1. MySQL备份与恢复

   • 逻辑备份：使用mysqldump导出结构和数据，如mysqldump -u root -p database > backup.sql（全库备份），mysqldump -u root -p --databases db1 db2 > backup.sql（多库备份）。
   • 物理备份：直接复制数据目录（如/var/lib/mysql），需停止MySQL服务（sudo systemctl stop mysqld），恢复时复制回原目录并启动服务。
   • 恢复方法：逻辑备份恢复使用mysql -u root -p database < backup.sql；物理备份恢复需将数据目录复制到原位置并设置正确权限。

2. PostgreSQL备份与恢复

   • 逻辑备份：使用pg_dump导出，如pg_dump -U postgres -d database -F c -b -v -f backup_file（自定义格式，支持压缩），pg_dump -U postgres -d database -F p -b -v -f backup_file（纯文本格式，可读性强）。
   • 物理备份：使用pg_basebackup复制整个数据目录，如pg_basebackup -U postgres -D /path/to/backup -F t -v -P（tar格式，包含WAL日志）。
   • 恢复方法：逻辑备份恢复使用pg_restore -U postgres -d database -v backup_file（自定义格式）；物理备份恢复需将备份目录复制到数据目录，修改postgresql.conf并启动服务。

3. 自动化备份

   • 使用cron定时任务执行备份脚本，如每天凌晨2点备份PostgreSQL数据库：

     # 编辑cron任务
     crontab -e
     # 添加以下内容（每天凌晨2点执行）
     0 2 * * * /path/to/backup_postgresql.sh
     

     备份脚本示例（PostgreSQL）：

     #!/bin/bash
     USER="postgres"
     DB_NAME="mydatabase"
     BACKUP_DIR="/path/to/backups"
     DATE=$(date +%Y%m%d%H%M%S)
     BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql"
     pg_dump -U $USER -d $DB_NAME -f $BACKUP_FILE
     echo "Backup of $DB_NAME completed on $DATE. Backup file is $BACKUP_FILE"
     

五、监控与维护

• 监控工具
  • MySQL：使用performance_schema（内置性能监控框架）、MySQLTuner（第三方工具，分析配置参数）、Percona Toolkit（高级监控与维护工具）。
  • PostgreSQL：使用pg_stat_statements（扩展，监控SQL语句性能）、pgAdmin（图形化管理工具，内置监控模块）、Barman（物理备份管理与监控）。
• 日志分析
  • MySQL：查看错误日志（/var/log/mysqld.log）和慢查询日志（需开启slow_query_log参数），分析慢查询并优化。
  • PostgreSQL：查看日志文件（/var/lib/pgsql/data/log），通过log_min_duration_statement参数记录慢查询（如设置为1000ms，记录执行时间超过1秒的查询）。