MySQL 常用指令

• 初次設定root密碼
  mysqladmin -u root password '密碼'
• 修改root密碼
  mysqladmin -u root -p password '新密碼'
  (輸入後，系統會要求輸入原密碼，成功後即修改為新密碼)
• 顯示資料庫清單
  mysqlshow -u 帳號 -p
• 顯示某資料庫底下的資料表清單
  mysqlshow 資料庫名稱 -u 帳號 -p
• 顯示資料表欄位結構
  mysqlshow 資料庫名稱 資料表名稱 -u 帳號 -p
• 查詢MySQL版本
  mysql> SELECT version();
  mysql> SELECT @@version;
  mysql> SHOW GLOBAL VARIABLES LIKE '%version%';
• 查詢資料庫檔案的資料夾路徑位置
  mysql> SHOW VARIABLES LIKE 'datadir';
• 顯示所有 MySQL 資料庫
  mysql> SHOW DATABASES;
• 切換目前使用的資料庫(目前預設的資料庫)
  mysql> USE 資料庫名稱;
• 查詢目前使用的資料庫名稱(目前預設的資料庫名稱)
  mysql> SELECT DATABASE();
• 顯示目前資料庫的資料表列表
  mysql> SHOW TABLES;
  mysql> SHOW TABLES FROM 資料庫名稱;
• 顯示資料表的欄位(Field, Type, Null, Key, Default, Extra)結構
  mysql> desc 資料表名稱;
  mysql> describe 資料表名稱;
  mysql> SHOW COLUMNS FROM 資料表名稱;
  mysql> SHOW COLUMNS FROM 資料表名稱 FROM 資料庫名稱;
• 顯示資料表的欄位(Field, Type, Null, Key, Default, Extra,Collation,Privileges,Comment)結構
  mysql> SHOW FULL FIELDS FROM 資料庫名稱.資料表
  mysql> SHOW FULL FIELDS FROM 資料表FROM 資料庫名稱
  mysql> SHOW FULL FIELDS FROM 資料表
• 顯示系統狀態(詳細)
  mysql> show [global | session] status;
  mysql> show global status where Variable_name='opened_tables';
• 顯示系統狀態(簡單)
  mysql> status;
• 顯示系統變數設定值
  mysql> show variables;
  mysql> show variables like '%max%';
• 顯示 process list(目前正在運作的進程)
  mysql> show processlist;
• 新增使用者帳號
  新增帳號同時設定權限：
  mysql> GRANT ALL PRIVILEGES ON dbName.* TO userID@localhost IDENTIFIED BY 'PW';
  mysql> GRANT ALL ON dbName.* TO userID@localhost IDENTIFIED BY 'PW';
  
  在 MySQL 8 上，新增帳號和設定權限要分開寫：
  mysql> CREATE USER 'userID'@'localhost' IDENTIFIED BY 'PW';
  mysql> GRANT ALL PRIVILEGES ON dbName.* TO 'userID'@'localhost' WITH GRANT OPTION;
  mysql> FLUSH PRIVILEGES;
  
  若 MySQL Server 的版本太高，連接 MySQL 的程式不支援 MySQL Server 密碼的加密方式(例如：Node.js 出現 Error: ER_NOT_SUPPORTED_AUTH_MODE 錯誤)，可加入 WITH mysql_native_password 指定使用原生加密方式：
  mysql> CREATE USER 'userID'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PW';
• 設定權限
  mysql> GRANT ALL PRIVILEGES ON dbName.* TO userID@localhost [WITH GRANT OPTION];
  說明：若 WITH GRANT OPTION，則得到權限的帳號(userID@localhost)，可再將權限授予給其他帳號。
  mysql> GRANT ALL ON dbName.* TO userID@localhost [WITH GRANT OPTION];
  mysql> GRANT SELECT ON dbName.* TO userID@localhost [WITH GRANT OPTION];
• 刪除權限
  mysql> REVOKE ALL PRIVILEGES ON dbName.* TO userID@localhost;
  mysql> REVOKE ALL ON dbName.* TO userID@localhost;
  mysql> REVOKE SELECT ON dbName.* TO userID@localhost;
• 顯示帳號的權限資訊
  mysql> SHOW GRANTS FOR 帳號名稱@localhost;
  mysql> SHOW GRANTS FOR root@localhost; //查看root權限，可發現還有 PROXY 權限
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '密碼' WITH GRANT OPTION
  GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
• 建立跟root相同權限的帳號
  mysql> CREATE USER 'root'@'192.168.0.%' IDENTIFIED BY '密碼';
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' WITH GRANT OPTION;
  mysql> GRANT PROXY ON ''@'' TO 'root'@'192.168.0.%' WITH GRANT OPTION ;//若不須創建代理用戶權限，可省略
  mysql> FLUSH PRIVILEGES;
• 刪除帳號 (刪除後，不會自動將該帳號已建立的連線斷線，須等該帳號下次重登時，才會無法登入)
  mysql> DROP USER userID@localhost;
  參考：MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.3 DROP USER Syntax
• 顯示權限有那些
  mysql> show privileges;
• 更新權限
  mysql> flush privileges;
• 顯示資料表格式
  mysql> show table types;
• 顯示當初資料庫如何建立
  mysql> show create database 資料庫名稱;
• 顯示當初資料表如何建立
  mysql> show create table 資料表名稱;
• 顯示資料表索引
  mysql> show index from 資料表名稱;
• 顯示每個表的訊息。包括表類型和表的最新更新時間
  mysql> show table status from 資料庫名稱;
• 顯示某個資料表所使用的儲存引擎
  mysql> SHOW TABLE STATUS WHERE Name = '資料表名稱';
• 顯示可使用的儲存引擎和預設儲存引擎
  mysql> SHOW ENGINES;
  mysql> SHOW STORAGE ENGINES
• 顯示innoDB存儲引擎的狀態
  mysql> show innodb status; --MySQL5.5移除
  mysql> SHOW ENGINE INNODB STATUS;
• 顯示最後一個執行的語句所產生的錯誤、警告和通知訊息log
  mysql> SHOW WARNINGS [LIMIT [offset,] row_count];
• 顯示BDB存儲引擎的日誌
  mysql> show logs;
• 只顯示最後一個執行語句所產生的錯誤
  mysql> show errors;
• 顯示Store Procedure基本訊息
  mysql> show procedure status;
• 顯示某一個Store Procedure的詳細訊息
  mysql> show create procedure sp名稱;
• 出現  Incorrect key file for table、 Table is marked as crashed and should be repaired 錯誤訊息(修復後資料好像會不見，修復前還是先備份)
  repair table 資料表名稱;
  如果不行
  repair table 資料表名稱 use_frm;
• mysqlcheck
  mysqlcheck [OPTIONS] database [tables];
  mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...];
  mysqlcheck [OPTIONS] --all-databases;
  OPTIONS：
  -a：Analyze given tables.
  -c：Check table for errors.
  -o：Optimize table.
  -r：repair.Can fix almost anything except unique keys that aren't unique.
• 在語法後面加「\G」，可垂直列出結果，例如：
  mysql> show master status\G;
• 新建資料庫的 CHARACTER(字符集)、COLLATE (校對規則)
  mysql> CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET 字符集] [COLLATE 校對規則]]
  mysql> ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET 字符集] [COLLATE 校對規則]
  
  若有設定 CHARACTER，但沒設定 COLLATE，則 COLLATE 依該 CHARACTER 預設的
  COLLATE 決定。
  每個 CHARACTER 預設的 COLLATE ，可由以下指令查得：
  mysql> SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]
• 查詢此連線的ID (thread ID)
  mysql> SELECT CONNECTION_ID();
  或
  mysql> SELECT CONNECTION_ID() FROM dual; 
• mysqldump 備份資料庫，並用 gzip 壓縮
  mysqldump -h 127.0.0.1 -u帳號 -p密碼 資料庫 | gzip -c > /home/db/檔案名稱-`date +%Y-%m-%d-%H_%I_%S`.gz
  
  
  

參考:

• https://dev.mysql.com/doc/refman/8.0/en/charset-table.html
  MySQL :: MySQL 8.0 Reference Manual :: 10.3.4 Table Character Set and Collation
• https://dev.mysql.com/doc/refman/8.0/en/show-character-set.html
  MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.3 SHOW CHARACTER SET Statement
• https://serverfault.com/questions/106595/mysqldump-to-a-tar-gz
  mysql - mysqldump to a tar.gz - Server Fault
• https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_connection-id
  MySQL :: MySQL 8.0 Reference Manual :: 12.16 Information Functions