2008年7月25日 星期五

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






參考:


張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)