使用的環境

系統與使用工具
Centos 7.6
MySQL 8.0.13

一、進入資料庫

# 這個是在終端機上下指令
$ mysql -u <USER> -p

二、使用者

(1) 查詢資料庫使用者

SELECT User FROM mysql.user;

(2) 查詢資料庫使用者（刪除重複的）

SELECT DISTINCT User FROM mysql.user;

(3) 查詢資料庫使用者與來源主機

SELECT User, Host FROM mysql.user;

(4) 建立使用者帳號，並設定密碼

CREATE USER '[USER]'@'[x.x.x.x]' IDENTIFIED BY '[PASSWORD]';

(5) 更改使用者密碼

ALTER USER '[USER]'@'[x.x.x.x] IDENTIFIED BY '[NEW_PASSWORD]';
# 讓設定生效
FLUSH PRIVILEGES;

(6) 查詢使用者的細部權限

SHOW GRANTS FOR '[USER]'@'[x.x.x.x]';

(7) 刪除使用者

DROP USER '[USER]'@'[x.x.x.x]';

(8) MySQL 使用者連線

# USER@ 後面的 ip 是要連到資料庫的 web 主機 ip
# 先建立使用者
CREATE USER '[USER]'@'[X.X.X.X]' IDENTIFIED BY '[PASSWORD]';
# 選擇資料庫
USE [DATABASES];
# 給使用者權限
GRANT ALL PRIVILEGES ON *.* TO '[USER]'@'[x.x.x.x]' WITH GRANT OPTION;
# 讓設定生效
FLUSH PRIVILEGES;

三、資料庫

(1) 新增資料庫

CREATE DATABASE `[DATABASE_NAME]`;

(2) 列出所有資料庫

SHOW DATABASES;

(3) 刪除資料庫

DROP DATABASE [DATABASE_NAME];

(4) 使用資料庫

USE [DATABASE_NAME];

(5) 查看所有資料庫的使用大小（MB）

SELECT TABLE_SCHEMA `DATABASE`, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 "SIZE IN MB" FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;

(6) 查看某資料庫的使用大小

SELECT TABLE_SCHEMA `DATABASE`, SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024 "SIZE IN MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=`[DATABASE_NAME]` GROUP BY TABLE_SCHEMA;

四、資料表

(1) 列出資料表

SHOW TABLES;

(2) 列出資料表的內容

SELECT * FROM [TABLE_NAME];

(3) 刪除資料表

DROP TABLE [TABLE_NAME];

(4) 清空資料表（只清除資料並保留結構、欄位、索引）

TRUNCATE TABLE [TABLE_NAME];

(5) 查看某資料庫的所有資料表的使用大小

SELECT TABLE_NAME AS "TABLE", ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS "SIZE (MB)" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = [DATABASE_NAME]" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

五、進階語法

(1) 查看正在執行的行程

SHOW PROCESSLIST;

(2) 查看 Master 狀態

SHOW MASTER STATUS;

(3) 查看 Slave 狀態

SHOW SLAVE STATUS\G;

(4) 查看 binlog 狀態

SHOW BINARY LOGS;

(5) 清除過時的 binlog

# 將刪除 mysql-bin.000001 ~ mysql-bin.000005 的 binlog
PURGE BINARY LOGS TO 'mysql-bin.000006';

六、MySQL mysqldump（資料匯出工具）語法

要在終端機上面下指令

(1) 備份某個資料庫

# 這樣的匯出檔案，沒有create database選項，只有create table選項
$ mysqldump -u root -p [DATABASE_NAME] > [DATABASE_NAME].sql;

(2) 還原某個資料庫（需先建好還原資料庫）

# 需先建好還原資料庫
$ mysql -u root -p [DATABASE_NAME] < [DATABASE_NAME].sql;

(3) 備份所有資料庫

# 這樣的匯出檔案，有 create database 選項，也有 create table 選項
$ mysqldump -u root -p --all-databases > all_db_backup.sql;

(4) 還原多個資料庫

# 檢查匯出 all_db_backup.sql 內是否已有 CREATE DATABASE 指令，有才可進行還原，沒有的話則要先去建好還原資料庫
$ mysql -u root -p < all_db_backup.sql

(5) 輸出 SQL 結構

$ mysqldump -u root -p --no-data --all-databases > list.sql
# 和底下這個指令效果一樣
$ mysqldump -u root -p -d --all-databases > list.sql

(6) 備份資料庫中的某個資料表

$ mysqldump -u root -p [DATABASE_NAME] [TABLE_NAME] > db_table_backup.sql;

(7) 備份資料庫中的多個資料表

$ mysqldump -u root -p [DATABASE_NAME] [TABLE_NAME1] [TABLE_NAME2] [TABLE_NAME3] > db_table_backup.sql;

(8) 還原加上字元編碼參數

$ mysql -u root -p --default-character-set=utf8 [DATABASE_NAME] < [DATABASE_NAME]_backup.sql

(9) 輸出 schema

$ mysqldump [DATABASE_NAME] --no-data > [DATABASE_NAME]_schema.sql

七、MySQL mysqladmin（管理工具）語法

尚未 :P

八、MySQL mysqlbinlog（管理日誌工具）語法

尚未 :P

參考資料