Linux 下 MySQL 常用命令大全：安裝、備份、優化、維護

安裝與啟停

# 下載編譯
./configure --prefix=/app/mysql-5.0.51a --with-charset=utf8 --with-extra-charsets=gbk,gb2312,utf8
make && make install

# 啟動/關閉
/path/mysqld_safe --user=mysql &
/path/mysqladmin -p shutdown

# 修改 root 密碼
mysqladmin -u root -p password '新密碼'

遠程連接與庫表操作

# 本機/遠程連接
mysql -u root -p
mysql -h 遠程IP -u user -p

# 庫表增刪查
CREATE DATABASE db_name;
DROP DATABASE [IF EXISTS] db_name;
CREATE TABLE items (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, symbol CHAR(4) NOT NULL, username CHAR(20), INDEX sym(symbol), UNIQUE(username)) ENGINE=InnoDB;
SHOW DATABASES; SHOW TABLES; DESC table_name;
SHOW CREATE TABLE tbl_name;

用戶權限管理

# 創建用戶並授權
GRANT SELECT,INSERT,UPDATE,DELETE,ALTER ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY 'abc';
GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%' IDENTIFIED BY 'abc';
FLUSH PRIVILEGES;

# 修改/刪除用戶
UPDATE user SET password=PASSWORD('111111') WHERE user='test1';
DELETE FROM user WHERE User='testuser' AND Host='localhost';
SHOW GRANTS FOR 'user1';

備份與恢復

# 完整備份
mysqldump -u user -p -B db_name > export.sql

# 單表備份
mysqldump -u user -p db_name table_name > table.sql

# 只導結構
mysqldump -u user -p -d --add-drop-table db_name > structure.sql

# 恢復
mysql -u root -p db_name < backup.sql
gunzip < backup.sql.gz | mysql -u root -p db_name

忘記 root 密碼重置

# 停止服務
killall mysqld

# 跳過權限啟動
mysqld_safe --skip-grant-tables &

# 登錄修改
mysql -u root
USE mysql;
UPDATE user SET password=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;

# 正常重啟
service mysqld restart

常用查詢與函數速查

• 去重：SELECT DISTINCT ...
• 日期格式化：DATE_FORMAT(NOW(), '%Y-%m-%d')
• 時間戳轉換：UNIX_TIMESTAMP() / FROM_UNIXTIME()
• 加密：PASSWORD()、MD5()
• 聚合：COUNT, AVG, SUM, MIN, MAX
• 字符串：LENGTH, TRIM, SUBSTRING, CONCAT
• 條件：IF(expr, true_val, false_val)、CASE WHEN ... END
• 批量替換：UPDATE wp_posts SET post_content=REPLACE(post_content, 'old', 'new')

性能調優關鍵參數

[mysqld]
max_connections=500
wait_timeout=10
table_cache=64
query_cache_size=256M
thread_cache=40
key_buffer=16M
sort_buffer_size=4M
read_buffer_size=4M
join_buffer_size=8M
skip-locking
skip-name-resolve
thread_concurrency=8  # CPU核心數*2
long_query_time=10

# 慢查詢日志
log-slow-queries=/var/log/mysql/slow.log
long_query_time=1
log-queries-not-using-indexes

分析工具：mysqldumpslow -s c -t 20 slow.log（按訪問次數 Top20）、mysqldumpslow -s r -t 20 slow.log（按返回行數 Top20）。