MySQL 常用命令速查手冊：登入、庫表操作、資料增刪改查、備份遷移、權限管理

MySQL 常用命令速查手冊

1. 連線與退出

# Windows
mysql -u root -p
# Linux
mysql -h host -u user -p
# 退出
exit / quit

2. 密碼修改

mysqladmin -u root -p password 'newpwd'

3. 資料庫操作

SHOW DATABASES;
CREATE DATABASE db_name;
DROP DATABASE db_name;
USE db_name;
SELECT DATABASE(); -- 顯示當前庫

4. 表操作

SHOW TABLES;
DESC tbl_name;
CREATE TABLE tbl (col1 type, col2 type...);
DROP TABLE tbl;
INSERT INTO tbl VALUES (val1, val2...);
SELECT * FROM tbl WHERE ...;
UPDATE tbl SET col=new_val WHERE ...;
DELETE FROM tbl WHERE ...;

5. 檔案匯入匯出

# 匯入 .sql
USE db_name;
SOURCE /path/to/file.sql;

# 匯入文字資料
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE tbl;

# 匯出 (mysqldump)
mysqldump -h host -u user -p db_name > backup.sql

# 遠端備份範例
mysqldump -h 10.201.10.243 -u discuz -p discuz > discuz_69.sql

6. 使用者權限

-- 新增唯讀用戶
GRANT SELECT ON db.* TO 'user'@'localhost' IDENTIFIED BY 'pass';

-- 新增全權限用戶
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'pass';

-- 指定表權限
GRANT SELECT,INSERT,UPDATE ON db.tbl TO 'user'@'localhost' IDENTIFIED BY 'pass';

-- 回收權限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';
FLUSH PRIVILEGES;

7. 備份遷移實戰

# 遠端備份
mysqldump -h 10.201.10.243 -u discuz -p discuz > discuz_69.sql

# 本地還原
mysql -u shenweiyan -p
USE newucdb;
SOURCE /home/shenweiyan/mysql-bk/discuzdb_3_2.sql;

收藏此手冊，日常運維開發快速查閱。