MySQL 資料庫

• MySQL 資料庫
  • 在Linux環境下登入
  • 檢查編碼
  • 更改時區指令
  • MYSQL 8 預設table AI可能發生碰撞
  • Blob資料轉移錯誤
  • Table 資料表無痛轉移
  • 匯出資料表成.gz壓縮檔
  • Query 紀錄
  • Slow Query

在Linux環境下登入

使用root帳號登入

mysql -u root -p

使用fooUser帳號登入44.55.66.77的Database

mysql -u fooUser -p -h 44.55.66.77

檢查編碼

What is the (default) charset for:

• MySQL database
• MySQL table
• MySQL column

To see default collation of the database:

USE db_name;
SELECT @@character_set_database, @@collation_database;

To see collation of the table:

SHOW TABLE STATUS where name like 'table_name';

查看全部COLUMN的資訊

SHOW FULL COLUMNS FROM table_name;

示意圖

查看建表的指令

SHOW CREATE TABLE table_name;

參考

更改時區指令

/*!40103 SET TIME_ZONE='+08:00' */;
此指令為時區+8

MYSQL 8 預設table AI可能發生碰撞

innodb_autoinc_lock_mode 變量 有三種可能的設置 。設置為 0、1 或 2，分別表示 “傳統”、“連續”或 “交錯”鎖定模式。從 MySQL 8.0 開始，交錯鎖模式 ( innodb_autoinc_lock_mode=2) 是默認設置。在 MySQL 8.0 之前，連續鎖定模式是默認的 ( innodb_autoinc_lock_mode=1)。

所以調整設定成innodb_autoinc_lock_mode=1即可
需要restart

Blob資料轉移錯誤

因:轉移DB 和目標DB不同
將mariaDB轉為mysql後則正常匯入
DB轉換參考

Table 資料表無痛轉移

1. 鎖定要轉移的資料表
2. 更名資料表
3. 解鎖資料表

lock tables stopwatch write,stopwatch_2 write;--鎖定要轉移的資料表

ALTER TABLE `stopwatch`--更名資料表
                RENAME TO `stopwatch_20201022`;

ALTER TABLE `stopwatch_2`
                RENAME TO `stopwatch`;

unlock tables; --解鎖資料表

匯出資料表成.gz壓縮檔

sudo mysqldump -u root -p level stopwatch_20201022  | gzip > stopwatch_20201022.gz

參考
參考2
參考3

Query 紀錄

紀錄所有SQL Query紀錄
P.S. 此方式會產生大量資料 注意硬碟空間並使用

先確認紀錄方式

show variables like '%general%';

結果如下

SET GLOBAL log_output = 'TABLE';

or

SET GLOBAL log_output = 'FILE';

成功畫面如下

接著開啟General log

SET GLOBAL general_log = 'ON';

之後就可以select  mysql.general_log 這張表或者是在檔案中查詢了

可以再次確認紀錄方式

show variables like '%general%';

因為general log的量很大(所有connections的所有Query)，所以要不要常態開啟需要自行評估一下

參考

Slow Query

https://www.opencli.com/mysql/mysql-slow-query-log