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秒的查詢）。