2019年 MySQL發怖了重大的改變，釋出 8.0 的版本，功能更強，效能更好。

安裝 MySQL 8.0 Server

執行底下指令進行安裝

sudo apt-get -y install mysql-server-8.0

更改資料庫目錄

資料庫預設目錄在 /var/lib/mysql 中，需使用 sudo su 變更成 root 後才可進入目錄中查看。

sudo su
cd /var/lib/mysql

要將資料庫預設目錄改到其它地方，請先停止 mysql 伺服器，再將 /var/lib/mysql 整個目錄 copy 到其它地方，比如 /data/server 之下。

sudo systemctl stop mysql
sudo mkdir /data/server
cd /data/server
sudo cp -r /var/lib/mysql .
sudo chown mysql:mysql -R mysql

然後必需設定寫入外部檔案的權限, 如下

sudo vim /etc/apparmor.d/usr.sbin.mysqld

於 "}" 裏面新增資料庫目錄位置, 如
/data/server/** rwk,

然後, 一定要reload apparmor
sudo /etc/init.d/apparmor reload

然後更變 mysql 設定檔 mysqld.cnf，請由如下指令開啟設定檔

 sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

然後將 datadir 更改成如下

datadir = /data/server/mysql

最後再重新啟動 mysql

sudo systemctl restart mysql

遠端連線

mysql 8.0 預設無法使用遠端連線，同樣更改設定檔 mysqld.cnf，將 bind-address 改為 0.0.0.0

bind-address           = 0.0.0.0

加速效能

在 mysqld.cnf 最後手動加入如下新設定，可有效的加速 MySQL 效能

skip-log-bin#取消 binary logging
innodb_buffer_pool_size=4G #緩衝區, 可大輻減少磁碟負載, 而且可加速查詢效能, 尤其是在select count(*)，但設太大會造成開機時無法自動啟動。
innodb_log_buffer_size = 256M
innodb_log_file_size = 256M
innodb_autoextend_increment = 1000
innodb_flush_log_at_trx_commit = 0 #加速效能很有效
max_allowed_packet = 2048M #查詢過久斷線解決方式(Lost connection while query) 
key_buffer_size = 256M

儲存後，重新啟動 mysql

sudo systemctl restart mysql

帳號管理

第一次登入

安裝完成後，第一次需在本機使用 root 登入資料庫，而且不用密碼

sudo mysql

新增帳號權限

為了安全性， root 預設只能由本機登入，不可以由網路登錄，所以想要由遠端網路登錄，需創建另一個帳號。請由上面的 root 登入 mysql 後，執行如下 SQL 語法增加一個新帳號

create user '帳號'@'%' identified by '密碼';
grant all privileges on *.* to '帳號'@'%';
flush privileges;

唯讀權限

唯讀權限只能讀取不能寫入，指令如下。

use mysql;
grant select on cloud.* to '帳號'@'%'

刪除帳號

刪除帳號只要使用 drop 即可，如下

use mysql;
drop user '帳號'@'%';

變更帳號密碼

變更帳號的密碼指令如下

use mysql;
set password for 'root'@'localhost'='1234';
set password for '帳號'@'%'='1234';

使用新帳號登入

使用新帳號登入 MySQL

mysql -u 新帳號 -p

常用SQL 指令

底下是維護帳號常用的方法，請新以新帳號登入 mysql

select User, host from mysql.user;#查詢帳號

自動輸入密碼

每次在本機登入，備份、還原都要輸入密碼，實在很麻煩了。以下方法可以不用輸入密碼，不過這只適用Linux系統。

注意喔，只有在本機不用輸入密碼喔。如果在遠端不用輸入密碼，那還得了。

vim ~/.my.cnf

[client]
password=YOUR_MYSQL_PASSWORD

然後就不用輸入密碼了。請注意，mysql 8.0.30 如果有下達 -p 的話，一樣不用輸入密碼。但到了 8.0.31 時，如果有下達 -p 的話，還是會要求手動輸入密碼，所以結論就是不要使用 -p

mysql -u 帳號
mysqldump -u 帳號 --routines wordpress > wordpress.sql
mysql -u 帳號 wordpress< wordpress.sql

防止暴力破解

MySQL 登入沒有限制次數，所以也是駭客常暴力破解的對象。使用 fail2ban 進行封鎖是最佳的解決方案。

啟動 log

MySQL 預設不啟動登入 log，請開啟 mysqld.cnf

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

於最後加入如下設定

general_log = 1
general_log_file = /var/log/mysql/general.log

然後動啟 mysql

sudo systemctl restart mysql

啟重新啟動後，會自動在 /var/log/mysql 下產生 general.log

設定 logrotate

general.log 非常龐大，所以需要 logrotate(日誌輪替)，把舊的 log 檔切掉、備份、壓縮，避免檔案一直變大。請在 /etc/logrotate.d 新增 mysql-general 檔案，然後設定如下

/var/log/mysql/general.log {
    hourly
    rotate 8
    compress
    missingok
    notifempty
    copytruncate
}

然後於 /etc/cron.d 裏增 cleanlog，設定如下每 6 小時打包並清空 log

0 */6 * * * root /usr/sbin/logrotate /etc/logrotate.d/mysql-general

最後重啟排程系統

sudo systemctl restart cron

設定過濾器

開啟 mysqld-auto.conf

sudo vim /etc/fail2ban/filter.d/mysqld-auth.conf

將 failregex 改成如下

failregex = Access denied for user '.*'@'<HOST>'

jail.conf

開啟 jail.conf

sudo vim /etc/fail2ban/jail.conf

新增如下設定

[mysqld-auth]
port     = 3306
#logpath  = %(mysql_log)s
#backend  = %(mysql_backend)s
enabled = true
backend = polling
filter = mysqld-auth
logpath = /var/log/mysql/general.log
maxretry = 3
findtime = 1H
bantime = 24H

最後重啟 fail2ban

sudo systemctl restart fail2ban

Mariadb

sudo apt-get install mariadb-server

設定檔 /etc/mysql/mariadb.conf.d/50-server.cnf

重啟 : sudo /etc/init.d/mysql restart, 也可以使用 sudo systemctl start mariadb

Ubuntu MySQL 8.0