• Linux v.s MySQL Workbench：安裝、匯入匯出與權限設定完整比較

Linux 中操作

Linux 安裝

sudo apt install mariadb(mysql)-server

從Linux中，設置shell，觀看多個表格

for table_name in `mysql -uroot -p000000 -D your_database -e "show tables like 'employee%';"`
do
     echo "Table Name: $tablename"
done

for table_name in `mysql -uroot -p000000 -D your_database -e "show tables like 'employee2%';"`
do
     echo "Table Name: $tablename"
done

從Linux 直接觀看 Mysql table (p後面是密碼, D 後面是database)

mysql -uroot -p000000 -D test -e "select * from <tablename>;" -s

從Linux 直接導出Mysql 內容

mysql -uroot -p000000 -D test -e 
"select * from employee;" -s | 
sed "s/\t/,/g" > employee_linux.csv

<> 從 Linux 中匯入csv檔到Mysql DATABASE test => TABLE members

mysqlimport -uroot -p000000 -D test
--local 
--fields-terminated-by="," 
--lines-terminated-by="\r\n" members 
/tmp/employee.csv

Mysql 中操作

從Linux 切換到 Mysql

sudo mysql -uroot -p000000

檢查自己的 mysql port

sudo netstat -atpn |grep mysql

更改 mysql root密碼

use mysql 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

創建使用者、使用者密碼

CREATE USER 'username' IDENTIFIED BY 'password';

刪除使用者

use mysql 
DROP USER 'username'@localhost;

給予用戶權限 (”可以改成“)

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';   (所有權限)
GRANT SELECT, INSERT ON mydb.* TO 'username'@'%';
select Host,User,authentication_string from mysql.user (查詢權限)

取消權限

REVOKE SELECT ON *.* FROM 'username'@'localhost';

顯示權限

SHOW GRANTS FOR 'username'@'localhost';

練習 : 開新read_user 帳號，只能讀取 DB test => TABLE employee，密碼000000

GRANT SELECT ON test.employee TO `read_user`@`localhost` IDENTIFIED BY '000000';

ALL PRIVILEGES：執行所有操作
SELECT：檢索數據。
INSERT：插入新數據
UPDATE：更新數據
DELETE：刪除數據
CREATE：創建新數據庫和表
DROP：刪除數據庫和表
ALTER：更改表結構
GRANT OPTION：將其擁有的權限授予其他用戶
SHOW DATABASES：查看可用的數據庫列表
SHOW TABLES：查看特定數據庫中的表列表
CREATE USER：創建新用戶
SUPER：root權限
REPLICATION CLIENT：複製客戶端狀態
REPLICATION SLAVE：複製伺服器
FILE：讀取和寫入系統文件
PROCESS：查看所有正在運行的進度
SHUTDOWN：關閉數據庫服務器
CREATE TEMPORARY TABLES：創建臨時表
LOCK TABLES：鎖定表，防止其他用戶對表進行更改
EXECUTE：執行存儲過程
REFERENCES：引用外鍵
CREATE ROUTINE：創建儲存過程和函數
ALTER ROUTINE：更改儲存過程和函數
EVENT：創建和管理事件
TRIGGER：創建觸發器

 

顯示在線人數、時長

SHOW PROCESSLIST;

如果用AWS/GCP，要putty外部連線

下載 putty gen ，輸入主機的外部IP產金鑰，再到AWS/GCP設定金鑰即可

直接看網友文章補充~
[教學] 使用 PuTTYgen 產生 SSH 連線 RSA、DSA 公鑰與私鑰

假設要連到遠端

mysql -ucatalina -p000000 -h203.0.113.1 -P6603

Mysql中，直接匯出表格到csv檔

SELECT * FROM employee
INTO OUTFILE '/tmp/employee.csv' 
FIELDS TERMINATED BY ','     (分隔符號) 
ENCLOSED BY '"'                 (資料括弧符號)
LINES TERMINATED BY '\r\n';     (換行符號)

<> Mysql中，抓取Linux的csv檔，直接匯入 Mysql table

LOAD DATA LOCAL INFILE  '/tmp/employee.csv' 
INTO TABLE employee
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

優化儲存格式

優化儲存格式: 更改數據類別

CREATE TABLE future_k1 (
 date date ,
 time time , 
 open smallint,
 high smallint,
 low smallint,
 close smallint,
 volume tinyint
);

優化儲存格式: 增加INDEX
很常操作的表格，可以設置INDEX()，優點:會加速操作、缺點:佔容量
CREATE INDEX()

CREATE INDEX idx_last_name_first_name ON users (last_name, first_name);

SELECT *
FROM users
WHERE last_name = 'Wang' AND first_name = 'Catalina';

DROP INDEX()

DROP INDEX idx_last_name_first_name ON users;

優化儲存格式: 分區表格

CREATE TABLE sales_data (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE,
    product_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)     PS PRIMARY KEY 要包括分區鍵!!!
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

優化儲存格式: 壓縮 (會增加CPU使用，讀取和寫入數據時，需要進行壓縮+解壓縮)

CREATE TABLE compressed_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

假設要壓縮一個現有的表

ALTER TABLE employee ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

優化儲存格式: 主鍵+唯一鍵

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

備份資料庫

匯出資料

Linux匯出

單一 Table
mysqldump -u cata -p travel ifoodie_v03 > /home/catalinakuowork/Dump20230711_ifoodie_v03.sql

如果要一個 DB
mysqldump -u cata -p travel > /path/to/exported/file.sql

如果要所有 DB
mysqldump -u ```cata -p --all-databases > /path/to/exported/file.sql

Mysql Workbench 匯出

單一 Table 分開存
上面選單 "Server" 
=> "Data Export - "Export Options" 選自己要的db 和 table => "Export to Self-Contained File" - "Start Export"

Linux匯入

單一 Table
mysql -u cata -p travel < /home/catalinakuowork/Dump20230710_6.sql

一個 DB
mysql -u cata -p --database=travel < /home/catalinakuowork/Dump20230709.sql

所有 DB 
mysql -u cata -p --database="Dump20230709.sql" < /home/catalinakuowork/Dump20230709.sql

Mysql Workbench 匯入

單一 Table 分開存
上面選單 "Server" 
=> "Data Ixport - "Import Options" 選自己要的db 和 table => "Import to Self-Contained File" - "Start Export"

如果匯出時在 workbench 選folder 而非 Self-Contained File"
先zip,  zip -r Dump20230709.zip Dump20230709

如果字符集錯誤（地端到雲端可能會出現的問題）
mysql -V 確定一下版本

在MariaDB 10.2.2 版本之後，字符集排序規則 ‘utf8mb4_0900_ai_ci’ 被引入作為默認的 utf8mb4 字符集排序規則。然而，有時候可能會遇到一些舊的導出文件使用了較舊版本的字符集排序規則，導致在較新版本的MariaDB中無法識別

為了解決這個問題，可以修改導出文件中的字符集排序規則，替換為支持新版本的規則，可以把 ‘utf8mb4_0900_ai_ci’ 替換為 ‘utf8mb4_general_ci’ 或 ‘utf8mb4_unicode_ci’