您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 常用命令大全：從安裝部署到日常運維的完整速查手冊

編輯：MySQL 資料庫相關知識

一、資料庫備份與還原

1. 導出整庫

mysqldump -u root -p --default-character-set=utf8mb4 --single-transaction --quick \
  --routines --triggers --events db_name > /backup/db_name_$(date +%F).sql

2. 導出單表

mysqldump -u root -p db_name table_name > /backup/table_name.sql

3. 僅導出結構（不含資料）

mysqldump -u root -p -d --add-drop-table db_name > /backup/db_struct.sql

4. 導入 SQL 檔（三種方式）

# 方式 A：source 命令（進入 mysql 後）
mysql> USE db_name;
mysql> SOURCE /backup/db_name.sql;

# 方式 B：管道導入
mysql -u root -p db_name < /backup/db_name.sql

# 方式 C：mysqldump 反向（少用）
mysqldump -u root -p db_name < /backup/db_name.sql  # 錯誤示範，mysqldump 不支援 stdin 導入

二、服務啟停與連線

作業系統	啟動	停止	重啟	狀態
Linux systemd	`systemctl start mysqld`	`systemctl stop mysqld`	`systemctl restart mysqld`	`systemctl status mysqld`
Linux SysV	`service mysqld start`	`service mysqld stop`	`service mysqld restart`	`service mysqld status`
Windows	`net start mysql`	`net stop mysql`	`net stop mysql && net start mysql`	`sc query mysql`

連線參數速查

mysql -h host -P port -u user -p'password' -D db_name
# 常用簡寫
mysql -u root -p
mysql -u root -p'pwd' -h 127.0.0.1 -P 3306

三、資料庫/表/欄位 DDL 操作

-- 建庫
CREATE DATABASE `db_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 顯示所有庫
SHOW DATABASES;

-- 刪庫
DROP DATABASE `db_name`;

-- 切換庫
USE `db_name`;

-- 顯示當前庫
SELECT DATABASE();

-- 顯示所有表
SHOW TABLES;

-- 建表（標準範例）
CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
  `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '郵箱',
  `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '狀態 0正常 1禁用',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

-- 查看表結構
DESC `user`;
SHOW COLUMNS FROM `user`;
SHOW CREATE TABLE `user`;

-- 刪表
DROP TABLE `user`;

-- 清空表（保留結構、重置自增、不產生 Undo）
TRUNCATE TABLE `user`;

-- 加欄位
ALTER TABLE `user` ADD `phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手機' AFTER `email`;

-- 改欄位型別/預設/註釋
ALTER TABLE `user` MODIFY `phone` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '手機號';

-- 刪欄位
ALTER TABLE `user` DROP `phone`;

-- 重命名表
RENAME TABLE `user` TO `user_v2`;

四、資料增刪改查 (DML)

-- 插入單行
INSERT INTO `user` (`name`, `email`) VALUES ('張三', 'zhangsan@example.com');

-- 批次插入（高效）
INSERT INTO `user` (`name`, `email`) VALUES 
('李四', 'lisi@example.com'), ('王五', 'wangwu@example.com');

-- 插入指定主鍵（覆蓋衝突）
INSERT INTO `user` (`id`, `name`, `email`) VALUES (100, '趙六', 'zhaoliu@example.com')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `email`=VALUES(`email`);

-- 查詢所有
SELECT * FROM `user`;

-- 條件查詢
SELECT `id`, `name` FROM `user` WHERE `status` = 0 ORDER BY `id` DESC LIMIT 10;

-- 分頁查詢（大偏移優化：記住上一頁最大 id）
SELECT `id`, `name` FROM `user` WHERE `id` > 1000 ORDER BY `id` LIMIT 20;

-- 更新
UPDATE `user` SET `status` = 1 WHERE `id` = 1;
UPDATE `user` SET `name` = REPLACE(`name`, '舊字', '新字');

-- 刪除
DELETE FROM `user` WHERE `id` = 1;

-- 統計
SELECT COUNT(*) FROM `user`;
SELECT COUNT(*) FROM `user` WHERE `status` = 0;

五、使用者與權限管理

-- 建立用戶
CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongP@ssw0rd';

-- 授權（最小權限）
GRANT SELECT, INSERT, UPDATE, DELETE ON `biz_db`.* TO 'app_user'@'10.0.0.%';

-- 刷新權限
FLUSH PRIVILEGES;

-- 查看用戶權限
SHOW GRANTS FOR 'app_user'@'10.0.0.%';

-- 回收權限
REVOKE INSERT, UPDATE ON `biz_db`.* FROM 'app_user'@'10.0.0.%';

-- 刪除用戶
DROP USER 'app_user'@'10.0.0.%';

-- 修改密碼（5.7+）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongP@ssw0rd';

-- 修改密碼（5.6 及以下）
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongP@ssw0rd');

六、資訊查看與效能診斷

-- 顯示變數
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 顯示狀態計數器
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- 顯示行程（找阻塞/慢查詢）
SHOW FULL PROCESSLIST;
-- 或
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

-- 顯示引擎狀態（InnoDB 詳細）
SHOW ENGINE INNODB STATUS\G

-- 顯示索引使用統計
SHOW INDEX FROM `user`;

-- 顯示表大小統計
SELECT TABLE_NAME, 
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS total_mb,
       TABLE_ROWS
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='biz_db' ORDER BY total_mb DESC;

-- 慢查詢日誌分析（需啟用）
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- pt-query-digest /var/log/mysql/slow.log

七、主從同步常用命令

-- 主庫：建立同步用戶
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplP@ssw0rd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 主庫：查看 Binlog 位置
SHOW MASTER STATUS;

-- 從庫：配置同步
CHANGE MASTER TO 
  MASTER_HOST='10.0.0.1', MASTER_PORT=3306,
  MASTER_USER='repl', MASTER_PASSWORD='ReplP@ssw0rd',
  MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1234;

-- 從庫：啟動同步
START SLAVE;

-- 從庫：查看同步狀態
SHOW SLAVE STATUS\G
-- 關鍵指標：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

-- 從庫：跳過錯誤（謹慎！需確認數據一致性）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

八、常用系統變數快速設定（運行時生效，持久化需寫 my.cnf）

SET GLOBAL max_connections = 2000;
SET GLOBAL innodb_buffer_pool_size = 25769803776;  -- 24G，單位 bytes
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL sql_log_bin = 0;  -- 當前會話不寫 binlog（慎用）

九、Windows 服務安裝/移除

# 安裝服務（指定配置檔）
mysqld --install MySQL57 --defaults-file="C:\Program Files\MySQL\MySQL Server 5.7\my.ini"

# 移除服務
mysqld --remove MySQL57

十、緊急救援：跳過授權表重置 root 密碼

# 1. 停止服務
# 2. 建立初始化檔
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPwd123!';" > /tmp/init.sql

# 3. 啟動載入初始化檔
mysqld_safe --init-file=/tmp/init.sql &

# 4. 驗證新密碼
mysql -u root -p'NewPwd123!'