[MySQL] 工作指令

給我自己看的...
===== 通用指令 =====
於CLI狀態下，指令後打上\G，如SELECT * FROM [table_name] \G;
可自動整理輸出結果

===== 帳號&權限 =====
建立使用者：CREATE USER 'user'@'host' IDENTIFIED BY 'password';
範例1：CREATE USER 'aaa'@'172.17.0.1' IDENTIFIED BY 'aaa123';
範例2：CREATE USER 'bbb'@'%' IDENTIFIED BY 'bbb456';  //bbb可從各IP登入

開權限：GRANT option ON target TO 'user'@'host' ;
範例：GRANT ALL ON *.* TO 'bbb'@'%' ;

改密碼：SET PASSWORD FOR 'user'@'host' = PASSWORD('password') ;
範例：SET PASSWORD FOR 'bbb'@'%' = PASSWORD('bbb123') ;

===== Replication =====
檢查replication狀態：SHOW MASTER STATUS / SHOW SLAVE STATUS

===== DB使用量(data size) =====
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "database_name";

===== DB使用量(row count) =====
SELECT table_name AS "Tables", TABLE_ROWS
FROM information_schema.TABLES
WHERE table_schema = "ims";