Ubuntu MariaDB權限設置與優化指南

一、Ubuntu MariaDB權限設置

1. 登錄MariaDB

使用root用戶或具有sudo權限的用戶登錄MariaDB，命令如下：
sudo mysql -u root -p
輸入密碼后進入MariaDB命令行界面。

2. 創建數據庫用戶

通過CREATE USER語句創建用戶，指定用戶名、主機（localhost表示本地連接，%表示允許遠程連接）和密碼：
CREATE USER 'username'@'localhost' IDENTIFIED BY 'StrongPassword123';
示例：創建本地用戶db_user，密碼為SecurePass@2025：
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'SecurePass@2025';

3. 分配最小必要權限

遵循最小權限原則，僅授予用戶完成任務所需的權限，避免使用GRANT ALL PRIVILEGES（除非必要）。常見權限類型：

• 完全權限（僅用于管理員）：GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
• 讀寫權限：GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';
• 只讀權限：GRANT SELECT ON database_name.* TO 'username'@'localhost';
• 特定表權限：GRANT SELECT, UPDATE ON database_name.table_name TO 'username'@'localhost';
  示例：授予db_user對mydb數據庫的讀寫權限：
  GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'db_user'@'localhost';

4. 刷新權限使更改生效

執行FLUSH PRIVILEGES;命令，使權限修改立即生效（MariaDB 10.4+版本會自動刷新，但顯式執行更安全）。

5. 查看與管理用戶權限

• 查看用戶及主機：SELECT User, Host FROM mysql.user;
• 查看用戶權限：SHOW GRANTS FOR 'username'@'localhost';
• 撤銷權限：REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';
• 刪除用戶：DROP USER 'username'@'localhost';

6. 遠程訪問配置（可選）

若需允許遠程連接，需修改MariaDB配置文件并調整權限：

• 編輯配置文件：sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf，找到bind-address并將其修改為服務器IP地址或0.0.0.0（允許所有IP連接）；
• 重啟MariaDB服務：sudo systemctl restart mariadb；
• 授權遠程用戶：GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'remote_ip' IDENTIFIED BY 'StrongPassword123';（remote_ip為遠程客戶端IP）。

二、Ubuntu MariaDB權限優化

1. 遵循最小權限原則

定期審計用戶權限，刪除不必要的權限或用戶。例如，若用戶僅需讀取數據，應將權限從ALL PRIVILEGES調整為SELECT；禁止為開發人員分配生產數據庫的DROP或ALTER權限。

2. 定期審計權限

使用SELECT User, Host, Select_priv, Insert_priv, Update_priv FROM mysql.db;命令查看用戶權限詳情，識別并移除未使用的權限或用戶。建議每月執行一次權限審計。

3. 限制遠程訪問

• 僅允許可信IP地址連接MariaDB，通過GRANT語句指定username@specific_ip；
• 修改bind-address限制監聽地址（如bind-address = 192.168.1.100）；
• 配置防火墻（如ufw）允許僅必要的端口（默認3306）：sudo ufw allow from trusted_ip to any port 3306。

4. 強化密碼策略

• 創建用戶時使用強密碼（包含大小寫字母、數字、特殊字符，長度≥12位）；
• 定期更換密碼（如每90天）；
• 啟用密碼過期策略（通過ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPassword' PASSWORD EXPIRE INTERVAL 90 DAY;設置）。

5. 移除匿名用戶與測試數據庫

運行mysql_secure_installation腳本，自動移除匿名用戶、禁止root遠程登錄、刪除測試數據庫（test），提升數據庫安全性。

三、Ubuntu MariaDB性能優化

1. 硬件優化

• 增加內存：MariaDB依賴內存緩存數據，建議內存容量為數據量的1.5-2倍；
• 使用SSD：SSD的隨機讀寫性能遠優于HDD，能顯著提升數據庫響應速度；
• 多核CPU：MariaDB支持多線程處理，更多CPU核心可提高并發處理能力。

2. 配置優化

編輯/etc/mysql/mariadb.conf.d/50-server.cnf文件，調整關鍵參數：

• innodb_buffer_pool_size：設置為總內存的70%左右（如16GB內存設置為11GB），用于緩存InnoDB表數據和索引；
• innodb_log_file_size：增大日志文件大?。ㄈ?56MB-1GB），減少日志刷盤頻率，提升寫入性能；
• innodb_flush_log_at_trx_commit：設置為2（平衡性能與安全性，犧牲部分數據安全性換取更高性能，適用于非金融場景）；
• max_connections：根據應用需求設置（如100-500），避免過多連接導致資源耗盡；
• query_cache_size：啟用查詢緩存（如64MB），緩存重復查詢結果（注意：MariaDB 10.4+版本已移除查詢緩存，需使用其他緩存機制）。

3. 索引優化

• 創建合適索引：為WHERE、JOIN、ORDER BY子句中頻繁使用的列創建索引（如CREATE INDEX idx_column ON table_name(column_name);）；
• 避免過度索引：每個索引會增加寫入和更新的開銷，建議單表索引數量不超過5個；
• 使用組合索引：對于多個列聯合查詢，創建組合索引（如CREATE INDEX idx_name_age ON users(last_name, first_name);）；
• 定期重建索引：使用OPTIMIZE TABLE命令整理表碎片，保持索引效率（如OPTIMIZE TABLE large_table;）。

4. 查詢優化

• 使用EXPLAIN分析查詢：通過EXPLAIN SELECT * FROM table_name WHERE condition;查看查詢計劃，識別全表掃描、索引未使用等問題；
• 避免SELECT *：僅選擇需要的列（如SELECT id, name FROM users;），減少數據傳輸量和內存占用；
• 優化查詢語句：簡化復雜子查詢，使用JOIN替代子查詢（如SELECT a.name FROM table_a a JOIN table_b b ON a.id = b.a_id WHERE b.status = 1;）；
• 使用LIMIT限制結果集：對于大數據量分頁查詢，使用LIMIT 10 OFFSET 20（獲取第3頁，每頁10條）。

5. 定期維護

• 優化表：定期執行OPTIMIZE TABLE命令，整理表碎片（適用于頻繁更新的表）；
• 清理日志：定期清理二進制日志（mysqlbinlog）和慢查詢日志（slow_query_log），釋放磁盤空間；
• 備份數據：使用mysqldump或mariabackup工具定期備份數據（如每日增量備份、每周全量備份）。