---

在 CentOS 已使用 MariaDB 取代 MySQL 了
但基本上指令都是一樣的，所以之前寫的程式可以通用

安裝 MariaDB

$ sudo yum install mariadb-server

版本為 1:5.5

啟動並設為開機自動啟動
$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

執行 mysql 的安全性設定
$ sudo mysql_secure_installation

# 輸入root密碼，剛安裝完密碼是空的，直接按Enter即可
Enter current password for root (enter for none):
# 設定root密碼
Set root password? [Y/n] y
# 移除預設的匿名帳號
Remove anonymous users? [Y/n] y
# 禁止root帳號從遠端登入
Disallow root login remotely? [Y/n] y
# 移除測試用的 test 資料庫
Remove test database and access to it? [Y/n] y
# 立即重載讓設定生效
Reload privilege tables now? [Y/n] y


登入看看
$ mysql -u root -p

輸入 exit 離開
MariaDB [(none)]> exit


編輯設定檔

MarioDB 的設定檔在 /etc/my.cnf

在 /usr/share/mysql/
裡有針對不同記憶體大小的主機提供不同的設定檔

my-innodb-heavy-4G.cnf: 記憶體4G以上，資料表只用innodb，有複雜query的資料庫主機
my-huge.cnf:  適合 1GB - 2GB 記憶體且主要是給資料庫使用的主機
my-large.cnf: 適合 512MB 記憶體且主要是給資料庫使用的主機
my-medium.cnf:只有 32MB - 64MB 記憶體的主機使用，
              或者有 128MB RAM 但需要運行其他伺服器，例如網頁伺服器
my-small.cnf: 記憶體少於 64MB 時適用這個，資料庫會佔用較少資源


依主機大小選擇適合的設定檔，複製到 /etc/my.cnf
$ sudo mv /etc/my.cnf /etc/my.cnf.default
$ sudo mv /usr/share/mysql/my-huge.cnf /etc/my.cnf

編輯設定檔
$ sudo vim /etc/my.cnf

在 [mysqld] 的設定裡

# thread_concurrency改成CPU的核心數*2
thread_concurrency = 8

# 若網頁和資料庫在同一台，可關閉外部連結的功能比較安全
#skip-networking
skip-networking

# 沒有使用 MASTER 功能時可以關閉 binary log
# 以免產生一大堆占空間的 mysql-bin.000xxx 的檔案
#log-bin=mysql-bin

# 或是設定 binary log 超過幾天的自動移除
# expire-logs-days=7


# 記錄花費時間超過5秒的指令
slow_query_log
long_query_time = 5

# 設定啟動發生錯誤的log檔要放哪邊
# 沒設定的話預設是 /var/lib/mysql/{hostame}.err
log-error=/var/log/mariadb/error.log


若資料庫有用到InnoDB的話，要將以下設定

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

改為

default-storage-engine = InnoDB
# 存放 innodb 資料庫檔案的資料夾
innodb_data_home_dir = /var/lib/mysql
# 存放 innodb 資料庫的檔案名稱，第一個 ibdata1 設為初始大小2000M，
# 第二個為 ibdata2，初始大小為10M，會自動擴增
innodb_data_file_path = ibdata1:2000M:ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# buffer_pool_size 會固定占用一塊記憶體，若是與網頁server共用的話不要設太大
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# log_file_size 設為 buffer_pool_size 的25%
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50




重新啟動 MySQL
$ sudo systemctl restart mariadb

檢查 MariaDB 執行狀態
$ sudo systemctl status mariadb

若啟動失敗，可至 /var/log/mariadb/error.log 查看錯誤記錄


如果錯誤記錄裡有
Fatal error: cannot allocate memory for the buffer pool
代表 innodb_buffer_pool_size 設太大了，要改小一點

注意若要修改 innodb_data_file_path 時
需要先備份資料庫後，刪除 /var/lib/mysql/ 下的 ibdata* 與 ib_logfile*
重啟 mariadb 會建立新的 ibdata1
然後再把備份的資料庫還原回來

若是有備份資料庫 mysql 的話
只要還原資料庫即可選原之前設定的使用者

若另一台主機的使用者無法登入的話
重新開機再試試看


新增使用者帳號

若資料庫主機為 host_db，網頁主機為 host_web
網頁與資料庫不是在同一台主機時，不能用 root 登入
要另外建一個有所有權限的管理者帳號

在 host_db 主機登入資料庫
$ mysql -u root -p

新增一個可從 host_web 主機登入的使用者 username ，密碼為 password
MariaDB > CREATE USER 'username'@'host_web' IDENTIFIED BY 'password';

加上所有權限
MariaDB > GRANT ALL PRIVILEGES ON * . * TO  'username'@'host_web' IDENTIFIED BY 'password' WITH GRANT OPTION;

MariaDB > FLUSH PRIVILEGES;


在防火牆設定允許 host_web 的所有連線
iptables -A INPUT -i eth0 -s host_web -j ACCEPT


登入 host_web 主機，安裝 mariadb (不用裝 mariadb-server)
$ sudo yum install mariadb

用剛剛新增的帳號登入資料庫主機 host_db
$ mysql -u username -p -h host_db




MySQL 基本操作

登入mysql
$ mysql -u root -p

列出所有資料庫
MariaDB > SHOW DATABASES;

建立一個資料庫 test_db
MariaDB > create database test_db;

設定使用者 test_user 擁有資料庫 test_db 的所有權限
(若使用者不存在，會自動建立一個)
MariaDB > grant all on test_db.* to 'test_user' identified by 'password';

切換至資料庫 db_name
MariaDB > USE db_name

顯示目前使用的資料庫
MariaDB > SELECT DATABASE();

顯示目前資料庫中的所有資料表
MariaDB > SHOW TABLES;

顯示資料表 table_name 中的所有欄位(以下四種結果相同)
MariaDB > SHOW COLUMNS FROM table_name;
MariaDB > SHOW FIELDS FROM table_name;
MariaDB > DESCRIBE table_name;
MariaDB > EXPLAIN table_name;


備份與還原

使用 mysqldump 備份資料庫
例如要將資料庫 test_db 備份至 test_db.sql 檔
$ mysqldump -u root -p test_db > test_db.sql

先建立資料庫 test_db，
然後使用 mysql 將備份的資料還原
$ mysql -u root -p test_db < test_db.sql


MySQL 時區設定

MySQL 啟動時會讀取系統的時區設定

先修改系統時區
$ sudo timedatectl set-timezone 'Asia/Taipei'
再重啟 MySQL 即可

不想重啟MySQL的話可執行以下指令
MariaDB > SET GLOBAL time_zone = '+8:00';


參考這篇安裝網頁的資料庫管理介面 phpMyAdmin
[CentOS] phpMyAdmin 安裝與設定 - KnucklesNote板 - Disp BBS


參考
https://www.linode.com/docs/databases/mariadb/how-to-install-mariadb-on-centos-7


□ 錯誤修改記錄

要刪除多餘的 mysql-bin.000xxx 檔，不能直接刪除
要登入 mysql 後，使用
MariaDB > PURGE BINARY LOGS TO 'mysql-bin.0000xx';
刪除數字小於 xx 的 binary log 檔



--
※ 作者: Knuckles 時間: 2017-02-13 21:26:08
※ 編輯: Knuckles 時間: 2023-11-16 00:28:19 (台灣)