MariaDB 單機版安裝步驟文件
執行安裝
MariaDB 10.3.x 環境安裝指令:
# yum -y install MariaDB-compat MariaDB-server MariaDB-client MariaDB-backup
MariaDB 10.4.x、10.5.x 環境安裝指令:
# yum -y install MariaDB-server MariaDB-client MariaDB-backup
【關鍵步驟】編輯 /etc/my.cnf.d/server.cnf
# vi /etc/my.cnf.d/server.cnf
從[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 服務運行狀態
systemctl enable mariadb systemctl restart mariadb systemctl status -l 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
|