2.安裝MariaDB與配置、資料庫最佳化 / 數據庫優化

--------------------------------
安裝MariaDB與配置
--------------------------------

下載GPG Key
curl -sSL https://mariadb.org/mariadb_release_signing_key.asc | gpg --dearmor > /usr/share/keyrings/mariadb.gpg

加入MariaDB的源
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/mariadb.gpg] https://mirror-cdn.xtom.com/mariadb/repo/10.11/debian $(lsb_release -sc) main" > /etc/apt/sources.list.d/mariadb.list

 

更新系統
apt update

apt upgrade -y

apt dist-upgrade -y

 

安裝MariaDB最新穩定版
apt install mariadb-server mariadb-client

 

瀏覽版本
mariadb -V

已安裝 10.11.8-MariaDB

 

-------------------
安全設置
-------------------

mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
注意：建議所有 MariaDB 運行此腳本的所有部分生產使用中的伺服器！ 請仔細閱讀每個步驟！

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here.
為了登入 MariaDB 並確保其安全，我們需要 root 使用者目前的密碼。如果你剛剛安裝了MariaDB，並且尚未設定root密碼，則只需在此處按Enter 鍵即可。

Enter current password for root (enter for none):

輸入 root 的目前密碼 (不輸入)：

按 Enter

 

OK, successfully used password, moving on...
好的，密碼已成功使用，繼續...

Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation.
設定root密碼或使用 unix_socket 可確保任何人在未經適當授權的情況下都無法登入MariaDB root使用者。

You already have your root account protected, so you can safely answer 'n'.
你的root帳戶已受到保護，因此你可以安全地回答 'n'。

Switch to unix_socket authentication [Y/n] 
切換到 unix_socket 身份驗證 [Y/n]

按 n

 

 

Change the root password? [Y/n] 
更改root密碼？ [Y/n] 
按 y

 

New password: 
輸入你自定義root的密碼

Re-enter new password: 
再次輸入你自定義root的密碼

 

 

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.
預設情況下，MariaDB安裝有一個匿名用戶，允許任何人登入MariaDB，而無需為其建立用戶帳戶。這僅用於測試，並使安裝過程更加順利。你應該在進入生產環境之前刪除它們。

Remove anonymous users? [Y/n] 
刪除匿名用戶？ [Y/n] 
按 y

 

 

Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.
通常，只允許root從 'localhost' 連線。這可確保其他人無法從網路猜測root密碼。

Disallow root login remotely? [Y/n] 
禁止遠端root登入？ [Y/n] 
按n

 

 

By default, MariaDB comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.
預設情況下，MariaDB附帶一個名為 'test' 的資料庫，任何人都可以存取。這也僅用於測試，應在進入生產環境之前將其刪除。

Remove test database and access to it? [Y/n] 
刪除測試資料庫並存取它？
按 y

 

 

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
重新載入權限表將確保迄今為止所做的所有變更都會立即生效。

Reload privilege tables now? [Y/n] 
現在重新載入權限表？ [Y/n] 
按 y

 

 

All done!  If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

 

重啟
systemctl restart mariadb

 

 

--------------
UTF-8
--------------

vi /etc/mysql/mariadb.conf.d/50-client.cnf

在 [client] 底下，加入這一行

default-character-set = utf8mb4

ESC儲存並離開
:wq

 

 

vi /etc/mysql/mariadb.conf.d/50-server.cnf

在 [mysqld] 底下，應該可以看到這兩行

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

ESC儲存並離開
:wq

 

 

 

------------------------------
資料庫最佳化 / 數據庫優化
------------------------------

只是測試有無 最佳化 / 優化 的效果

傳統是寫在 my.cnf，但是手搓LNMP的情況，還不確定

vi /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]

#
# * Basic Settings
#

port        = 3306
user        = mariadb
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp

#
# * Fine Tuning
#

skip-external-locking
key_buffer_size        = 256M
max_allowed_packet     = 1G
table_open_cache = 192
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 96
query_cache_size = 128M
tmp_table_size = 384M
explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
default_storage_engine = InnoDB

#
# * InnoDB
#

innodb_file_per_table = 1
innodb_buffer_pool_size = 384M
innodb_log_file_size = 32M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 1024M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

ESC儲存並離開
:wq

 

重啟
systemctl restart mariadb

開機啟動mariadb
systemctl enable mariadb

 

啟動mariadb
systemctl start mariadb

 

瀏覽狀態
systemctl status mariadb

 

重開機
reboot

 

 

瀏覽狀態
systemctl status mariadb

確認重開機後，MariaDB可以自動啟動

 

 

本帖最后于7月2日，由Jack编辑