MySQL8.0+Backup/Restore

前言

經過了漫長的等待及當兵的時間後，我終於順利退伍回來繼續當個碼農了

而現在是真的開始正式的工作了，因此想要好好的把工作上的筆記給整理一番

而無論是寫什麼程式甚至是WordPress最後都脫離不了與資料庫的間接

這次就來好好的從安裝到備份到還原一次記錄起來

Ubuntu

這邊使用的是GCP平台，因此在建立Ubuntu時可先賦予root密碼

1	$ sudo passwd root

可切換至root帳號

$ su -

這樣在操作下列步驟時，執行指令前就不用另外加sudo

環境

首先我們先來安裝MySQL，不過以下我會用5.7和8.0的版本來做範例

5.7

5.7的版本單純很多基本上只要直接安裝即可

更新APT

1	$ sudo apt-get update

安裝MySQL

1	$ sudo apt-get install mysql-server

8.0

首先切換到暫存目錄

$ cd /tmp

下載MySQL提供的軟體包

1	$ curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

必須透過dpkg來安裝軟體包

1	$ sudo dpkg -i mysql-apt-config*

-i: install

dpkg: 本機軟體包管理器 / apt: 線上軟體包管理器 / .deb: Debian的軟體包格式(Debian又為Ubuntu父版本)

這時候你會看到這個畫面，採用預設的設定直接選最後一項ok就行

更新APT

1	$ sudo apt-get update

安裝完後也可以刪除MySQL設定安裝檔

1	$ sudo rm mysql-apt-config*

這時候就可以依據配置好的設定檔安裝我們的主角MySQL

1	$ sudo apt-get install mysql-server -y

這時候MySQL會要你輸入root密碼，記住這是MySQL自己的root帳號，跟Ubuntu的root是不一樣的歐！

再次輸入密碼

再來MySQL還會問你是否要使用8.0版的caching_sha2_password還是5.7的mysql_native_password驗證方式，這會影響你其他Server連接MySQL時的相容性，這裡選第二個舊版的選項

查看MySQL狀態

1	$ service mysql status

會看到綠色的active (running)顯示正在運行

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2021-05-27 20:08:22 UTC; 9min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4863 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 4918 (mysqld)
   Status: "Server is operational"
    Tasks: 37 (limit: 4671)
   CGroup: /system.slice/mysql.service
           └─4918 /usr/sbin/mysqld

May 27 20:08:22 mysql-test systemd[1]: Starting MySQL Community Server...
May 27 20:08:22 mysql-test systemd[1]: Started MySQL Community Server.

開機時自動啟動MySQL

1	$ systemctl enable mysql

初始化

這時你可以選擇更安全的初始化設定，讓你的MySQL有更好的安全機制
接下來就會有一系列的設定要操作，如果現在不想要就直接跳過一個環節就好

1	$ mysql_secure_installation

輸入剛剛設定的root密碼後，接著可以選擇是否要配置更嚴謹的密碼規則

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

選擇是之後，會提供三種不同的規則給你配置：

弱：8位數以上(含)
中：8位數以上(含)，至少要有一個數字、小寫英文、大寫英文、特殊符號
強：8位數以上(含)，至少要有一個數字、小寫英文、大寫英文、特殊符號，配置密碼字典文件

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

如果你剛剛在設定root密碼不符合你選擇的規則時，它會問你要不要重新設一組新的密碼

1	Change the password for root ? ((Press y\|Y for Yes, any other key for No) : n

MySQL在安裝過程有建立一組匿名帳號為了做系統測試，測試完成後是可以直接刪除的

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

接著要選擇是否禁止以遠端方式登入root帳號，只允許透過localhost登入
這裡我建議選擇是，如果想要遠端登入的話再另外創一組使用者就好，來提升安全性

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

MySQL也同時建立了一個測試用的資料庫test，我們一樣可以把它給直接刪除掉

By default, MySQL 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.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

最後我們要重新加載我們的權限表，選擇是就完成我們所有的設定了

 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

Database

首先進入MySQL CLI

1	$ mysql -u root -p

u: user / p: password

在這裡我們新建一個資料庫

1	CREATE DATABASE <DATABASE> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

這時就能看到新建立的Database

1	SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_example      |
| performance_schema |
| sys                |
+--------------------+

進入剛剛建立的Database

1	USE <DATABASE>;

建立一張資料表

CREATE TABLE customers (
  C_Id INT,
  Name varchar(50),
  Address varchar(255),
  Phone varchar(20)
);

查看資料表

1	SHOW TABLES;

+-------------------------+
| Tables_in_mysql_example |
+-------------------------+
| customers               |
+-------------------------+

如果要刪除資料庫可以這麼做

1	DROP DATABASE <DATABASE>;

如果是資料表

1	DROP TABLE <TABLE NAME>;

如果只想保留資料表結構刪除資料

1	TRUNCATE TABLE <TABLE NAME>;

接下來在這裡你就可以開始操作熟悉的SQL語法了
關於SQL教學的部分我非常推薦Fooish 程式技術
大家有興趣可以去裡面探索，基本上我的語法都是在那裡學的

User

在先前的安全設定中，我們將root設為只允許localhost登入
因此如果我們要從外部連線進來時，我們必須建立一組可以遠端登入的User

首先確認User狀態

1	SELECT user, host FROM mysql.user;

可以看到root的host為localhost

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

建立User

1	CREATE USER '<USER NAME>'@'%' IDENTIFIED BY '<PASSWORD>';

這裡host為%代表所有ip都能使用這組帳號連線，你也可以設為localhost

如果你想更改User密碼可以這樣做

1	ALTER USER '<USER NAME>'@'<HOST>' IDENTIFIED BY '<PASSWORD>';

另外你可以建立mysql_native_password驗證模式的User

1	CREATE USER '<USER NAME>'@'%' IDENTIFIED WITH mysql_native_password BY '<PASSWORD>';

如果想更改現有User的密碼為mysql_native_password驗證模式則是

1	ALTER USER '<USER NAME>'@'<HOST>' IDENTIFIED WITH mysql_native_password BY '<PASSWORD>';

這時就可以看到新建立好的使用者

1	SELECT user, host FROM mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| user_example     | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

如果要刪除使用者的話

1	DROP USER '<USER NAME>'@'<HOST>';

權限

賦予User Database權限

1	GRANT ALL PRIVILEGES ON <DATABASE>.* TO '<USER NAME>'@'%';

更新權限表

1	FLUSH PRIVILEGES;

可執行exit或quit退出MySQL
就能遠端重新登入了

1	$ sudo mysql -u <USER NAME> -p

這時候就可以看到剛剛賦予權限的資料庫

1	SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql_example      |
+--------------------+

Backup

備份指定資料庫

1	$ mysqldump -u <USER> -p <DATABASE> > <BACK_SQL>.sql

備份所有資料庫

1	$ mysqldump -u <USER> -p --all-databases > <BACK_SQL>.sql

遠端備份資料庫

1	$ mysqldump -u <USER> -p -h <HOST> <DATABASE> > <BACK_SQL>.sql

備份指定資料表出來

1	$ mysqldump -u <USER> -p <DATABASE> <TABLE NAME> > <BACK_SQL>.sql

Google Cloud SQL備份資料庫

1	$ mysqldump -u <USER> -p -h <HOST> --port <PORT> --databases <DATABASE> --hex-blob --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 --column-statistics=0 > <BACK_SQL>.sql

設定排程備份backup.sh

#!/bin/sh
# database info
_USER=<USER>
_PASSWORD=<PASSWORD>
_DATABASE=<DATABASE>
_HOST=<HOST>
# backup info
_WORK_DIR=<WORK_DIR>
_MYSQLDUMP_DIR=<MYSQLDUMP_DIR>
_ZIP_DIR=<ZIP_DIR>
_ZIP_PASSWORD=<ZIP_PASSWORD>
_DATE=$(date "+%Y%m%d_%H%M%S")
_FILE=${_DATABASE}_${_DATE}
# shell
${_MYSQLDUMP_DIR}/mysqldump -u ${_USER} -p${_PASSWORD} ${_DATABASE} --host ${_HOST} > ${_WORK_DIR}/${_FILE}.sql
${_ZIP_DIR}/zip -m -j -P ${_ZIP_PASSWORD} ${_WORK_DIR}/${_FILE}.zip ${_WORK_DIR}/${_FILE}.sql

Restore

還原指定資料庫

1	$ mysql -u <USER> -p <DATABASE> < <BACK_SQL>.sql

還原多個資料庫

1	$ mysql -u <USER> -p < <BACK_SQL>.sql