...
從[mysqld]以下加入:
程式碼區塊 |
---|
bind-address=0.0.0.0 lower_case_table_names = 1 collation-server = utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 skip-name-resolve max_connections = 32768 max_connect_errors=1000 max_allowed_packet=4096M connect_timeout = 15 |
存檔、離開
啟動並且確認 Mariadb 服務運行狀態
...
設置 MariaDB root 密碼
先登入 Mariadb 確認狀況,以下是假設 root 尚未設置密碼的情況下,如果已經設置過密碼,則跳過本章節步驟
mysql -u root SHOW GRANTS; 以下是 root 還沒有設置密碼的情況,畫面要看到: +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.000 sec)
修改 MariaDB 密碼為 1qaz2wsx 步驟如下: mysql -u root 登入 ALTER USER 'root'@'localhost' IDENTIFIED BY '1qaz2wsx'; FLUSH PRIVILEGES; SHOW GRANTS; exit systemctl restart mariadb systemctl status mariadb
驗證: mysql -u root 畫面要看到: Access denied for user 'root'@'localhost' (using password: NO)
|
設置 MariaDB 開放 root 連線,假設密碼為 1qaz2ws 如果環境不開放 root 連線,以下請自行將 root 更換掉
mysql -u root GRANT SELECT ON *.* TO 'root'@'%' IDENTIFIED BY '1qaz2wsx' WITH GRANT OPTION; GRANT SELECT ON *.* TO 'root'@'你的 vm 主機 IP' IDENTIFIED BY '1qaz2wsx' WITH GRANT OPTION; GRANT SELECT ON *.* TO 'root'@'你的筆電或桌機 IP' IDENTIFIED BY '1qaz2wsx' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1qaz2wsx' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'你的主機 IP' IDENTIFIED BY '1qaz2wsx' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'你的筆電或桌機 IP' IDENTIFIED BY '1qaz2wsx'; FLUSH PRIVILEGES; SELECT User, Host FROM mysql.user WHERE Host <> 'localhost'; exit
|
備份議題:
設置定期備份 MariaDB 資料
假設要將資料庫備份檔要存放至/dbbak 的目錄下,參考以下備份腳本範例,檔名或路徑請自行斟酌更改。建議將腳本檔置放於 /etc/cron.daily 目錄下,並加上執行的權限
#!/bin/bash d=`date +%Y-%m-%d` final="omnistore_"$d"_db.dump" target="/dbbak/" echo `date +%Y-%m_%d-%H:%M:%S`" Daily backup database to "$target$final >> /dbbak/daily.log mysqldump -u root -p1qaz2wsx --single-transaction --routines --triggers omnistore > $target$final
|
定期清整(housekeeping)議題:
如果需要定期清整可以參考加入以下範例,清理存放超過 60 天以上的檔案
以下是 CROND JOB 腳本範例,檔名或路徑請自行斟酌更改。 #Clean more than 60 days dump file find /dbbak/ -type f -name "*.dump" -mtime +60 -delete
|