服務管理
| 操作系統 | 啟動 | 停止 | 重啟 | 狀態 |
|---|
| Windows | net start MySQL | net stop MySQL | net restart MySQL | sc query MySQL |
| Linux (SysV) | service mysqld start | service mysqld stop | service mysqld restart | service mysqld status |
| Linux (systemd) | systemctl start mysqld | systemctl stop mysqld | systemctl restart mysqld | systemctl status mysqld |
| 二進制直啟 | mysqld_safe & | mysqladmin shutdown | - | mysqladmin ping |
登錄與認證
# 交互式輸入密碼
mysql -uroot -p
# 直接指定密碼（不安全，僅腳本用）
mysql -uroot -p'StrongPass#2024'
# 指定庫直接進入
mysql -uroot -p mydb
# 遠程連接
mysql -h 192.168.1.10 -P 3306 -uroot -p mydb
賬戶與權限管理
創建用戶（5.7+ 推薦）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass#2024';
授權模板
-- 全庫全表讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
-- 僅讀
GRANT SELECT ON mydb.* TO 'readonly'@'%';
-- 單表特定權限
GRANT UPDATE (status), DELETE ON mydb.orders TO 'ops_user'@'%';
-- 管理權限
GRANT RELOAD, PROCESS, REPLICATION SLAVE ON *.* TO 'admin'@'%';
-- 刷新生效
FLUSH PRIVILEGES;
查看權限
SHOW GRANTS FOR 'app_user'@'%';
SELECT User, Host FROM mysql.user;
修改密碼
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass#2024';
-- 或 5.6 風格
SET PASSWORD FOR 'app_user'@'%' = PASSWORD('NewPass#2024');
庫表結構操作
-- 庫
SHOW DATABASES;
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE mydb;
DROP DATABASE mydb;
-- 表
SHOW TABLES;
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DESC users; -- 結構
SHOW CREATE TABLE users; -- 完整 DDL
-- 修改
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY name VARCHAR(60) NOT NULL;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO members;
數據操作 (DML)
-- 插入
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');
-- 查詢
SELECT id, name FROM users WHERE id=1;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- 更新
UPDATE users SET name='Alice Updated' WHERE id=1;
-- 刪除
DELETE FROM users WHERE id=1; -- 帶條件
TRUNCATE TABLE users; -- 清空表，重置自增，不可回滾
-- 批量導入
LOAD DATA LOCAL INFILE '/path/data.txt' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES (name, email);
-- 執行 SQL 文件
SOURCE /path/script.sql;
資訊查看與診斷
-- 系統變量
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES;
-- 運行狀態
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Qcache%';
-- 進程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; -- 顯示完整 SQL
-- 鎖等待
SHOW ENGINE INNODB STATUS\G
-- 慢查詢
SHOW VARIABLES LIKE 'slow_query_log%';
備份與恢復
# 備份單庫
mysqldump -uroot -p --single-transaction mydb > mydb_$(date +%F).sql
# 備份全庫
mysqldump -uroot -p -A --single-transaction --master-data=2 > full_$(date +%F).sql
# 恢復
mysql -uroot -p mydb < mydb_2024-01-15.sql
開機自啟
# SysV
chkconfig --add mysqld
chkconfig mysqld on
# systemd
systemctl enable mysqld
