MySQL 設定參數

在 Debian 9 封裝的 mysql-server 認證模式預設採用 auth_socket(或unix_socket)，傳統的認證為 mysql_native_password 方式，可以透過下列方式更改，說明詳見此頁討論：

# mysql -u root -p

> USE mysql;
> SELECT User, Host, plugin FROM mysql.user;
> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
> UPDATE user SET password =PASSWORD('foobar') WHERE user = 'root'; // MariaDB 10
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'foobar'; // MariaDB 10.4
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('foobar');
> FLUSH PRIVILEGES;

# /etc/init.d/mysql restart

[ 匯出資料或架構Schema ]

mysqldump -u root -p databaseX > dbX_dump.sql
mysqldump -u root -p databaseX --no-data > dbX_dump.sql

[ 帳戶權限操作 ]
權限設成 localhost 僅限從 mysql server 本機登入，反之權限設定為 % 代表可從任何遠端主機連入；在 mysql 系統上兩個帳號視為不同且可並存。

select user,host from mysql.user;
create user 'jack'@'localhost' identified by 'NEW_PASSWORD';
create user 'jack'@'%' identified by 'NEW_PASSWORD';

設定遠端連入：/etc/mysql/mariadb.conf.d/50-server.cnf

bind-address  = 0.0.0.0

將 MysQL 中所有資料列塞進 Memory 內加快查詢

有點暴力的作法，基本上就是將資料存一份在磁碟中，於資料庫啟動後把所有資料全倒進記憶體內，加快查詢速度。

(1) 調整 heap table 的大小，因為 MySQL 的 Memory Engine 使用這個資料結構。

# my.ini
max_heap_table_size = 512MB

(2) 將資料倒入

# 建立表格 mem_parkTable 其 Schema 延用 parkTable
mysql> CREATE TABLE mem_parkTable LIKE parkTable;

# 設定儲存引擎為記憶體
mysql> ALTER TABLE mem_parkTable ENGINE = MEMORY;

# 開始倒資料進來
mysql> INSERT INTO mem_parkTable SELECT * FROM parkTable;

(3) 基本上到這邊就完成了，注意一下如果 MySQL 重新啟動後，在記憶體內的資料會完全不見(廢話)，需要重新匯入。

匯入「100萬筆」記錄至 MySQL 資料庫

手上的樣本檔有150萬筆資料量，檔案(csv格式)大小約100MB，用 phpMyAdmin 匯入當然是失敗！硬著頭皮看有什麼指令可用...

(1) 因為安全考量 MySQL 預設關閉從檔案匯入資料，所以先調整 my.ini 設定檔：

[mysql]
local-infile=1
[mysqld]
local-infile=1

(2) 建立資料庫(parkDB)後，再建立表格(parkTable)存放記錄：

'第一欄位序號(SN)作為主鍵並具遞增性質
CREATE TABLE parkTable
( SN integer AUTO_INCREMENT PRIMARY KEY ,
  PARK_DATE varchar(10), PARK_TIME varchar(10),
  CAR_NO varchar(10), BRAND varchar(20),
  COLOR varchar(20) );

(3) 從檔案匯入資料庫

# mysql -u root -p
mysql> use parkDB
mysql> LOAD DATA LOCAL INFILE 'SOURCE.csv' INTO TABLE parkTable
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
    -> (PARK_DATE, PARK_TIME, CAR_NO, BRAND, COLOR);

參數相關說明：

• 從檔案 SOURCE.csv 匯入資料到表格(parkTable)
• 欄位區分是以逗號(,)來作識別。例如資料 A,B,C 看作三欄。
• 每欄資料是用雙引號(")圍起來。例如資料 "XYZ-123"。
• 換行符號是 "\n"
• 匯入資料對應欄位為 (PARK_DATE, PARK_TIME ...)

(4) 資料量很大時，為加快查詢請記得建立索引。

'索引欄位 CAR_NO
CREATE INDEX idx_CAR_NO ON parkTable (CAR_NO);