MySQL 運維命令速查：安裝、服務、備份、權限、GIS、去重、配置參數

DBA 桌面必備：MySQL 高頻運維命令分類匯總

覆蓋 Windows/Linux 安裝、服務管理、備份恢復、權限控制、GIS 空間、數據去重、核心參數調優，實戰提煉。

Windows 服務安裝與管理

REM 安裝服務（指定配置文件）
mysqld --install MySQL --defaults-file="E:\mysql\my.ini"

REM 5.7+ 初始化數據目錄（生成隨機密碼）
mysqld --defaults-file="E:\mysql\my.ini" --initialize-insecure
mysqld --install MySQL --defaults-file="E:\mysql\my.ini"

REM 服務控制
net start MySQL
net stop MySQL
mysqld --remove MySQL
sc delete MySQL

Root 密碼修改四大法

REM 1. SET PASSWORD（需登入）
mysql -uroot -p
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

REM 2. mysqladmin（命令行直接）
mysqladmin -uroot -poldpass password newpass

REM 3. UPDATE user 表（需 FLUSH）
UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

REM 4. 跳過權限表（忘記密碼救援）
mysqld_safe --skip-grant-tables &
mysql -uroot mysql
UPDATE user SET authentication_string=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

權限授予模板

REM 遠程 root 全權限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

REM 普通用戶單庫讀寫
GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'app'@'%' IDENTIFIED BY 'pass';

REM 查看權限
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS;  -- 當前用戶

核心參數動態調整

SET GLOBAL max_allowed_packet = 20*1024*1024;  -- 20MB
SET GLOBAL innodb_buffer_pool_size = 4*1024*1024*1024;  -- 需重啟持久化

GIS 空間數據操作

-- 添加幾何列
ALTER TABLE stations ADD shape GEOMETRY AFTER lat;

-- 經緯度構建 POINT
UPDATE stations SET shape = ST_GeomFromText(CONCAT('POINT(', lng, ' ', lat, ')'));

-- 查看 WKT 格式
SELECT ST_AsText(shape), id FROM stations;

-- 空間索引（MyISAM/InnoDB 5.7+）
ALTER TABLE stations ADD SPATIAL INDEX idx_shape(shape);

-- 範圍查詢：矩形內的站點
SELECT * FROM stations WHERE MBRContains(ST_GeomFromText('POLYGON((...))'), shape);

數據去重經典模式

-- 單字段去重保留最小 ID
DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY dup_col);

-- 多字段去重
DELETE a FROM t a
JOIN (SELECT MIN(id) AS min_id, col1, col2 FROM t GROUP BY col1, col2 HAVING COUNT(*)>1) b
ON a.col1=b.col1 AND a.col2=b.col2 AND a.id > b.min_id;

-- 查重不刪
SELECT col1, col2, COUNT(*) FROM t GROUP BY col1, col2 HAVING COUNT(*)>1;

備份與恢復

REM 邏輯備份
mysqldump -uroot -p db table1 table2 > backup.sql
mysqldump -uroot -p -B db1 db2 > dbs.sql  -- 含 CREATE DATABASE

REM 物理備份（需停庫或 xtrabackup）
tar czf datadir.tar.gz /var/lib/mysql

REM 導入
mysql -uroot -p db < backup.sql
source /path/backup.sql  -- 在 mysql 客戶端內

常用清理 SQL

-- 刪除關聯表中孤兒數據
DELETE p FROM parent p LEFT JOIN child c ON p.id=c.pid WHERE c.pid IS NULL;

-- 提交事務（手動事務模式）
COMMIT;

建議保存為 mysql-cheatsheet.md，IDE 內側邊欄固定，需要時秒級調用。