2019年 MySQL發怖了重大的改變, 釋出 8.0 的版本, 功能更強, 效能更好. 所以本篇分mysql5.7及mysql8.0二個主題說明

請注意, MySQL 8.0在ubuntu 18.04版, 直到2019/9止, 尚未支援arm64的作業系統, 比如ubuntu-mate for Raspberry, 只能安裝5.7的版本

而ubuntu20.04已可支援 MysQL8.0的版本了

MySQL8.0

安裝

若是 ubuntu20.04, 請執行下面的指令

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

資料庫目錄

sudo su
cd /var/lib/mysql

更改資料庫目錄

先停止mysql 伺服器, 再將/var/lib/mysql整個目錄copy 到/data/server之下

sudo /etc/init.d/mysql stop
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設定檔 sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

datadir = /data/server/mysql
bind-address = 0.0.0.0

最後再重新啟動mysql :
sudo /etc/init.d/mysql restart

新增帳號權限

為了安全性, root預設只能由本機登入, 不可以由網路登錄, 所以想要由網路端登錄, 需創建另一個帳號。請由如下指令登入 mysql

sudo mysql -u root

登入 mysql 後，請用如下 SQL 語法增加另一個新的帳號

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

遠端連線

mysql 8.0預設無法使用遠端連線, 請更改設定檔
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf , 將bind-address 改為 0.0.0.0

bind-address           = 0.0.0.0

加速效能

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf , 手動加入如下新的設定

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

使用新帳號登入

使用新帳號登入 MySQL

mysql -u 新帳號 -p

常用SQL 指令

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

select User, host from mysql.user;#查詢帳號
drop 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

MySQL5.7

sudo apt-get install mysql-server

第一次登入

cat debian.cnf
mysql -u debian-sys-maint -p
密碼就在debian.cnf裏面

注意, root好像不能使用了, 還在測試中. 但可直接新增一個帳號, 如下新增遠端登入說明

update mysql.user set authentication_string=password(‘newpassword’) where user=’root’;

設定

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

bind-address = 127.0.0.1 改為 bind-address = 192.168.1.2
上述允許遠端進行連線，若無此設定，只能在本地端登入

防火牆

ubuntu預設並未啟動防火牆. 但若伺服器置於IP分享器之後, 記得將IP分享器的 3306 port 指向伺服器的虛擬IP

重啟

sudo /etc/init.d/mysql restart

忘了root 密碼

sudo dpkg-reconfigure mysql-server-5.1

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