MySQL 命令大全：連接、用戶、庫表操作、備份恢復、數據導入導出、權限管理

MySQL 命令大全：連接、用戶、庫表操作、備份恢復、數據導入導出、權限管理

整理 MySQL 日常運維開發高頻命令，分類索引，備查速查。

一、連接與認證

# 本地連接
mysql -u root -p
# 遠程連接
mysql -h 110.110.110.110 -u root -p'abcd123'
# 指定端口
mysql -u user -p --port=3307
# 退出
exit / \q

二、修改密碼

# 方式 1：mysqladmin（命令行）
mysqladmin -u root -p'oldpwd' password 'newpwd'
# 首次設置密碼（無舊密碼）
mysqladmin -u root password 'newpwd'

# 方式 2：SQL（登錄後）
UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;

三、用戶與權限管理

# 創建用戶（5.7.6+）
CREATE USER 'user'@'host' IDENTIFIED BY 'pwd';

# 授權模板
GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'user'@'host' IDENTIFIED BY 'pwd';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pwd' WITH GRANT OPTION;  -- 超級用戶

# 回收權限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

# 刷新生效
FLUSH PRIVILEGES;

# 刪除用戶
DROP USER 'user'@'host';

# 查看權限
SHOW GRANTS FOR 'user'@'host';
SELECT User, Host FROM mysql.user;

四、數據庫與表操作

# 庫操作
CREATE DATABASE db_name;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;

# 表操作
SHOW TABLES;
DESC table_name;              -- 結構
SHOW CREATE TABLE table_name; -- 完整 DDL
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
ALTER TABLE t ADD COLUMN age INT;
ALTER TABLE t MODIFY age TINYINT NOT NULL;
ALTER TABLE t DROP COLUMN age;
DROP TABLE t;
TRUNCATE TABLE t;  -- 清空表（DDL，不可回滾）

五、數據增刪改查 (CRUD)

INSERT INTO t (name, age) VALUES ('Tom', 18);
INSERT INTO t (name, age) VALUES ('Jerry', 20), ('Bob', 22);  -- 批量

SELECT * FROM t;
SELECT name, age FROM t WHERE age > 18 ORDER BY age DESC LIMIT 10;
SELECT DISTINCT age FROM t;
SELECT COUNT(*) FROM t;
SELECT age, COUNT(*) FROM t GROUP BY age HAVING COUNT(*) > 1;

UPDATE t SET age = age + 1 WHERE name = 'Tom';
DELETE FROM t WHERE age < 18;

六、備份與恢復 (mysqldump)

# 備份整庫
mysqldump -h host -u root -p db_name > backup.sql
# 備份單表
mysqldump -h host -u root -p db_name table_name > table.sql
# 僅結構
mysqldump -u root -p -d db_name > schema.sql
# 僅數據
mysqldump -u root -p -t db_name > data.sql
# 含建庫語句
mysqldump -u root -p --databases db_name > full.sql

# 恢復
mysql -u root -p db_name < backup.sql
# 或登錄後
SOURCE /path/backup.sql;

七、數據導入導出 (文本/CSV)

# 導出純文本（每表兩文件：.sql 結構 + .txt 數據）
mysqldump -T /export_dir -u root -p db_name table_name

# 導入文本 (LOAD DATA)
LOAD DATA INFILE '/path/file.txt' INTO TABLE t 
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'
(col1, col2);  -- 指定列順序

# mysqlimport 工具（對應 LOAD DATA）
mysqlimport -u root -p db_name /path/table_name.txt

八、事務控制

START TRANSACTION;
INSERT ...; UPDATE ...;
COMMIT;  -- 或 ROLLBACK;

# 隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;

九、常用系統函數與信息查詢

SELECT VERSION();          -- 版本
SELECT NOW();              -- 當前時間
SELECT USER();             -- 當前用戶
SELECT DATABASE();         -- 當前庫
SHOW PROCESSLIST;          -- 當前會話
SHOW ENGINE INNODB STATUS; -- InnoDB 狀態
SHOW VARIABLES LIKE 'max_connections';

十、索引操作

CREATE INDEX idx_name ON t(col);
CREATE UNIQUE INDEX uk_name ON t(col);
ALTER TABLE t ADD INDEX idx_name (col);
DROP INDEX idx_name ON t;
SHOW INDEX FROM t;

十一、分區表與分表

# RANGE 分區
CREATE TABLE t (id INT, dt DATE) PARTITION BY RANGE (YEAR(dt)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

十二、性能診斷利器

EXPLAIN SELECT * FROM t WHERE id=1;
ANALYZE TABLE t;           -- 更新統計信息
OPTIMIZE TABLE t;          -- 整理碎片
SHOW PROFILE;              -- 會話資源分析
SELECT * FROM sys.schema_table_statistics WHERE table_schema='db';

收藏此表，覆蓋 95% 日常 MySQL 操作場景，工欲善其事必先利其器。