我的学习空间

2/26/2026

MySQL 基础命令与运维高频命令大全

一、连接与登录命令

1.1 基础连接

# 本地连接（默认socket）
mysql -u root -p

# 指定socket连接
mysql -u root -p -S /tmp/mysql.sock

# TCP/IP连接
mysql -h 127.0.0.1 -P 3306 -u root -p

# 远程连接
mysql -h 192.168.1.100 -P 3306 -u username -p

# 连接后立即执行命令退出
mysql -u root -p -e "SHOW DATABASES;"

1.2 安全连接配置

# 创建客户端配置文件 ~/.my.cnf

[client]
socket=/tmp/mysql.sock
user=root
password=your_password
host=localhost
port=3306

# 设置权限 chmod 600 ~/.my.cnf

参数说明

参数	含义	说明
`[client]`	配置段	作用于所有 MySQL 客户端工具
`socket=/tmp/mysql.sock`	Socket 文件路径	本地连接使用的 Unix Socket
`user=root`	用户名	登录数据库的用户
`password=your_password`	密码	用户的密码（明文！）
`host=localhost`	主机名	指定连接的主机
`port=3306`	端口号	MySQL 服务端口

配置前：

mysql -u root -p
Enter password: ******  # 每次都要输密码

配置后：

mysql
# 直接进入 MySQL，无需输密码！

mysqldump database > backup.sql
# 备份也无需输密码！

mysqladmin status
# 管理命令同样免密

二、数据库管理命令

2.1 数据库操作

-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE db_name;
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
/
-- 选择数据库
USE db_name;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库
DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;

-- 修改数据库字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2.2 表操作

-- 查看所有表
SHOW TABLES;
SHOW TABLES FROM db_name;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESC table_name;
DESCRIBE table_name;
SHOW CREATE TABLE table_name;
SHOW COLUMNS FROM table_name;

-- 修改表结构
ALTER TABLE table_name ADD column_name INT;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name VARCHAR(100);
ALTER TABLE table_name CHANGE old_name new_name INT;
ALTER TABLE table_name RENAME TO new_table_name;

-- 删除表
DROP TABLE table_name;
TRUNCATE TABLE table_name;  -- 清空表数据，重置自增ID

三、数据操作命令（CRUD）

3.1 插入数据

-- 插入单条
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@email.com', 25);

-- 插入多条
INSERT INTO users (name, email, age) VALUES 
    ('李四', 'lisi@email.com', 30),
    ('王五', 'wangwu@email.com', 28);

-- 插入或更新
INSERT INTO users (id, name, email) VALUES (1, '赵六', 'zhao@email.com')
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 替换（先删除后插入）
REPLACE INTO users (id, name, email) VALUES (1, '新名字', 'new@email.com');

3.2 查询数据

-- 基础查询
SELECT * FROM users;
SELECT id, name, age FROM users WHERE age > 18;

-- 条件查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE email IS NOT NULL;

-- 排序
SELECT * FROM users ORDER BY age DESC, id ASC;

-- 分组统计
SELECT age, COUNT(*) as count FROM users GROUP BY age;
SELECT age, AVG(age) as avg_age FROM users GROUP BY age HAVING avg_age > 20;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 第21-30条
SELECT * FROM users LIMIT 20, 10;        -- 同上（偏移量, 条数）

-- 联合查询
SELECT * FROM table1 UNION SELECT * FROM table2;

-- 子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

3.3 更新和删除

-- 更新数据
UPDATE users SET age = 26 WHERE name = '张三';
UPDATE users SET age = age + 1 WHERE id > 100;

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;

-- 带限制的删除（安全）
DELETE FROM users WHERE status = 0 LIMIT 1000;

四、索引管理

-- 查看索引
SHOW INDEX FROM table_name;

-- 创建索引
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 组合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 删除索引
DROP INDEX idx_name ON users;

-- 分析索引使用情况
EXPLAIN SELECT * FROM users WHERE name = '张三';

五、用户与权限管理

5.1 用户操作

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 修改密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

-- 删除用户
DROP USER 'username'@'localhost';

5.2 权限管理

-- 授予权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
GRANT SELECT, INSERT, UPDATE ON db_name.* TO 'username'@'%';
GRANT ALL PRIVILEGES ON db_name.table_name TO 'username'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER();

-- 撤销权限
REVOKE INSERT ON db_name.* FROM 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

六、备份与恢复（运维高频）

6.1 备份命令

# 单库备份
mysqldump -u root -p db_name > /backup/db_name.sql

# 多库备份
mysqldump -u root -p --databases db1 db2 > /backup/multi_db.sql

# 全库备份
mysqldump -u root -p --all-databases > /backup/all_db.sql

# 备份特定表
mysqldump -u root -p db_name table1 table2 > /backup/tables.sql

# 只备份结构（无数据）
mysqldump -u root -p --no-data db_name > /backup/structure.sql

# 只备份数据（无结构）
mysqldump -u root -p --no-create-info db_name > /backup/data.sql

# 压缩备份
mysqldump -u root -p db_name | gzip > /backup/db_name.sql.gz

# 带时间戳的备份
mysqldump -u root -p db_name > /backup/db_name_$(date +%Y%m%d_%H%M%S).sql

# 主从备份时跳过GTID
mysqldump -u root -p --set-gtid-purged=OFF db_name > db.sql

6.2 常用备份参数

# 常用参数组合
mysqldump -u root -p \
  --single-transaction \    # InnoDB一致性备份
  --routines \              # 包含存储过程
  --triggers \              # 包含触发器
  --events \                # 包含事件
  --master-data=2 \         # 记录binlog位置
  db_name > backup.sql

6.3 恢复命令

# 基础恢复
mysql -u root -p db_name < /backup/db_name.sql

# 恢复压缩的备份
gunzip < /backup/db_name.sql.gz | mysql -u root -p db_name

# 恢复所有数据库
mysql -u root -p < /backup/all_db.sql

# 恢复时显示进度
pv /backup/db_name.sql | mysql -u root -p db_name

七、性能监控与诊断

7.1 查看状态变量

-- 查看MySQL运行状态
SHOW STATUS;
SHOW GLOBAL STATUS;

-- 查看慢查询次数
SHOW STATUS LIKE 'Slow_queries';

-- 查看连接数
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看缓存命中率
SHOW STATUS LIKE 'Qcache%';

7.2 查看进程和锁

-- 查看当前连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看正在运行的查询
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

-- 查看锁等待
SHOW ENGINE INNODB STATUS\G
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 杀死进程
KILL 12345;  -- 12345是线程ID

7.3 分析查询

-- 查看慢查询日志设置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE age > 20;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;

-- 查看查询优化器信息
SHOW WARNINGS;

7.4 表分析优化

-- 分析表（更新索引统计）
ANALYZE TABLE table_name;

-- 优化表（整理碎片）
OPTIMIZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

-- 修复表
REPAIR TABLE table_name;

八、配置查看与修改

8.1 查看变量

-- 查看所有变量
SHOW VARIABLES;
SHOW GLOBAL VARIABLES;

-- 查看特定变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看字符集设置
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

8.2 动态修改配置

-- 动态修改（重启失效）
SET GLOBAL max_connections = 500;
SET SESSION sort_buffer_size = 262144;

-- 查看当前设置
SELECT @@global.max_connections;
SELECT @@session.sort_buffer_size;

九、日志管理

9.1 二进制日志

-- 查看binlog状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 查看binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 刷新binlog
FLUSH LOGS;

-- 设置binlog过期时间
SET GLOBAL expire_logs_days = 7;
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);

9.2 错误日志查看

# 查看错误日志位置
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error'"

# 实时查看错误日志
tail -f /var/log/mysql/error.log

# 查看最近错误
tail -50 /var/log/mysql/error.log | grep -i error

十、系统信息查看

-- 查看MySQL版本
SELECT VERSION();

-- 查看当前用户
SELECT USER(), CURRENT_USER();

-- 查看数据库大小
SELECT 
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables 
GROUP BY table_schema;

-- 查看表大小
SELECT 
    table_name AS '表名',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables 
WHERE table_schema = 'db_name'
ORDER BY (data_length + index_length) DESC;

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

十一、运维脚本示例

11.1 自动备份脚本

#!/bin/bash
# mysql_backup.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="password"
DB_NAME="your_database"
KEEP_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

# 记录日志
echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz" >> $BACKUP_DIR/backup.log

11.2 慢查询分析

#!/bin/bash
# analyze_slow_query.sh

SLOW_LOG="/var/log/mysql/slow-query.log"
OUTPUT_DIR="/tmp/slow_analysis"
DATE=$(date +%Y%m%d)

# 使用mysqldumpslow分析
mysqldumpslow -t 10 $SLOW_LOG > $OUTPUT_DIR/top10_$DATE.txt

# 按查询时间排序
mysqldumpslow -s t -t 20 $SLOW_LOG > $OUTPUT_DIR/by_time_$DATE.txt

# 按锁时间排序
mysqldumpslow -s l -t 20 $SLOW_LOG > $OUTPUT_DIR/by_lock_$DATE.txt

echo "Analysis completed: $OUTPUT_DIR"

十二、常用组合命令

# 1. 批量杀死空闲连接
mysql -u root -p -e "SHOW PROCESSLIST" | grep Sleep | awk '{print $1}' | xargs -I {} mysql -u root -p -e "KILL {}"

# 2. 导出指定表数据
mysql -u root -p -e "SHOW TABLES FROM db_name" | grep -v "Tables_in" | while read table; do
    mysqldump -u root -p db_name $table > /backup/${table}.sql
done

# 3. 实时监控MySQL查询
watch -n 1 'mysql -u root -p -e "SHOW PROCESSLIST"'

# 4. 查看InnoDB状态（每5秒）
while true; do mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LATEST DETECTED DEADLOCK"; sleep 5; done

十三、故障排查命令

# 1. MySQL无法启动时
tail -100 /var/log/mysql/error.log
journalctl -u mysql -n 50

# 2. 检查表是否损坏
mysqlcheck -u root -p --all-databases

# 3. 修复表
mysqlcheck -u root -p --repair --all-databases

# 4. 检查磁盘空间
df -h
du -sh /var/lib/mysql

# 5. 检查连接数是否超限
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Threads_connected';"

这个汇总涵盖了日常MySQL运维中90%以上的常用命令。建议收藏备用，遇到问题时快速查阅！

xuankin

7.2 数据库基础操作, 第7章 MySQL数据库

MySQL 基础命令与运维高频命令大全

一、连接与登录命令

1.1 基础连接

1.2 安全连接配置

参数说明

二、数据库管理命令

2.1 数据库操作

2.2 表操作

三、数据操作命令（CRUD）

3.1 插入数据

3.2 查询数据

3.3 更新和删除

四、索引管理

五、用户与权限管理

5.1 用户操作

5.2 权限管理

六、备份与恢复（运维高频）

6.1 备份命令

6.2 常用备份参数

6.3 恢复命令

七、性能监控与诊断

7.1 查看状态变量

7.2 查看进程和锁

7.3 分析查询

7.4 表分析优化

八、配置查看与修改

8.1 查看变量

8.2 动态修改配置

九、日志管理

9.1 二进制日志

9.2 错误日志查看

十、系统信息查看

十一、运维脚本示例

11.1 自动备份脚本

11.2 慢查询分析

十二、常用组合命令

十三、故障排查命令

发表回复 取消回复

发表回复取消回复