MySQL 命令示例

连接管理命令

基本连接

本地连接

bash

# 使用默认端口连接本地MySQL
mysql -u root -p

# 指定端口连接本地MySQL
mysql -u root -p -P 3306

远程连接

bash

# 连接远程MySQL服务器
mysql -u username -p -h hostname

# 指定端口连接远程MySQL服务器
mysql -u username -p -h hostname -P 3306

# 使用SSL连接MySQL服务器
mysql -u username -p -h hostname --ssl

连接选项

bash

# 连接时指定数据库
mysql -u username -p database_name

# 禁用自动重连
mysql -u username -p --skip-reconnect

# 设置连接超时
mysql -u username -p --connect-timeout=10

连接状态管理

查看当前连接

sql

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看当前连接（详细）
SHOW FULL PROCESSLIST;

终止连接

sql

-- 终止指定连接
KILL process_id;

-- 强制终止指定连接
KILL QUERY process_id;

连接统计

sql

-- 查看连接统计信息
SHOW GLOBAL STATUS LIKE 'Threads%';

-- 查看连接错误信息
SHOW GLOBAL STATUS LIKE 'Connection_errors%';

数据库操作命令

数据库管理

创建数据库

sql

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS database_name;

删除数据库

sql

-- 删除数据库
DROP DATABASE database_name;

-- 删除数据库（如果存在）
DROP DATABASE IF EXISTS database_name;

查看数据库

sql

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

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

-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

切换数据库

sql

-- 切换到指定数据库
USE database_name;

表操作

创建表

sql

-- 创建表
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建表（如果不存在）
CREATE TABLE IF NOT EXISTS table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表

sql

-- 添加列
ALTER TABLE table_name ADD COLUMN email VARCHAR(255);

-- 修改列
ALTER TABLE table_name MODIFY COLUMN age INT UNSIGNED;

-- 删除列
ALTER TABLE table_name DROP COLUMN age;

-- 修改表名
ALTER TABLE table_name RENAME TO new_table_name;

删除表

sql

-- 删除表
DROP TABLE table_name;

-- 删除表（如果存在）
DROP TABLE IF EXISTS table_name;

-- 清空表数据
TRUNCATE TABLE table_name;

查看表

sql

-- 查看当前数据库的所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE table_name;

-- 查看表结构（详细）
SHOW CREATE TABLE table_name;

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

数据操作

插入数据

sql

-- 插入单行数据
INSERT INTO table_name (name, age) VALUES ('John', 30);

-- 插入多行数据
INSERT INTO table_name (name, age) VALUES 
('John', 30),
('Jane', 25),
('Bob', 35);

-- 插入数据（如果不存在）
INSERT IGNORE INTO table_name (id, name) VALUES (1, 'John');

-- 插入或更新数据
REPLACE INTO table_name (id, name) VALUES (1, 'John Updated');

更新数据

sql

-- 更新数据
UPDATE table_name SET age = 31 WHERE name = 'John';

-- 更新多行数据
UPDATE table_name SET age = age + 1 WHERE age > 25;

-- 带LIMIT的更新
UPDATE table_name SET age = 30 WHERE name LIKE 'J%' LIMIT 10;

删除数据

sql

-- 删除数据
DELETE FROM table_name WHERE name = 'John';

-- 删除多行数据
DELETE FROM table_name WHERE age > 30;

-- 带LIMIT的删除
DELETE FROM table_name WHERE age > 30 LIMIT 10;

查询数据

sql

-- 查询所有列
SELECT * FROM table_name;

-- 查询指定列
SELECT id, name FROM table_name;

-- 带条件查询
SELECT * FROM table_name WHERE age > 25;

-- 排序查询
SELECT * FROM table_name ORDER BY age DESC;

-- 分组查询
SELECT age, COUNT(*) FROM table_name GROUP BY age;

-- 分页查询
SELECT * FROM table_name LIMIT 10 OFFSET 20;

用户和权限管理命令

用户管理

创建用户

sql

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

-- 创建用户（使用密码哈希）
CREATE USER 'username'@'localhost' IDENTIFIED BY PASSWORD '*HASHED_PASSWORD';

-- 创建用户（使用认证插件）
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

修改用户

sql

-- 修改用户密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- 修改用户认证插件
ALTER USER 'username'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

-- 重命名用户
RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost';

删除用户

sql

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

-- 删除用户（如果存在）
DROP USER IF EXISTS 'username'@'localhost';

查看用户

sql

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

-- 查看用户详细信息
SHOW CREATE USER 'username'@'localhost';

权限管理

授予权限

sql

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- 授予表的指定权限
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'localhost';

-- 授予所有数据库权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- 授予权限并允许授权
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' WITH GRANT OPTION;

撤销权限

sql

-- 撤销数据库所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- 撤销表的指定权限
REVOKE SELECT, INSERT ON database_name.table_name FROM 'username'@'localhost';

-- 撤销所有数据库权限
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

查看权限

sql

-- 查看用户权限
SHOW GRANTS FOR 'username'@'localhost';

-- 查看当前用户权限
SHOW GRANTS;

-- 查看权限表
SELECT * FROM mysql.user WHERE user = 'username';
SELECT * FROM mysql.db WHERE user = 'username';
SELECT * FROM mysql.tables_priv WHERE user = 'username';

刷新权限

sql

-- 刷新权限
FLUSH PRIVILEGES;

索引管理命令

创建索引

普通索引

sql

-- 创建普通索引
CREATE INDEX idx_name ON table_name(name);

-- 为多个列创建索引
CREATE INDEX idx_name_age ON table_name(name, age);

唯一索引

sql

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON table_name(email);

主键索引

sql

-- 创建表时指定主键
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 为现有表添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);

全文索引

sql

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON table_name(content);

-- 使用全文索引查询
SELECT * FROM table_name WHERE MATCH(content) AGAINST('keyword');

查看索引

sql

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看索引创建语句
SHOW CREATE TABLE table_name;

删除索引

sql

-- 删除普通索引
DROP INDEX idx_name ON table_name;

-- 删除唯一索引
DROP INDEX idx_email ON table_name;

-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;

配置管理命令

查看配置

查看全局变量

sql

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

-- 查看指定全局变量
SHOW GLOBAL VARIABLES LIKE 'innodb%';

-- 查看缓冲池大小
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

查看会话变量

sql

-- 查看所有会话变量
SHOW SESSION VARIABLES;

-- 查看指定会话变量
SHOW SESSION VARIABLES LIKE 'sql_mode';

查看状态变量

sql

-- 查看全局状态变量
SHOW GLOBAL STATUS;

-- 查看指定全局状态变量
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- 查看会话状态变量
SHOW SESSION STATUS;

修改配置

修改全局变量

sql

-- 修改全局变量（需要特权）
SET GLOBAL innodb_buffer_pool_size = 1073741824;

-- 修改全局变量（会话重启后生效）
SET PERSIST innodb_buffer_pool_size = 1073741824;

修改会话变量

sql

-- 修改会话变量
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 修改当前会话变量
SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

备份和恢复命令

备份命令

使用mysqldump备份

bash

# 备份整个数据库
mysqldump -u root -p database_name > backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > backup.sql

# 备份表结构
mysqldump -u root -p --no-data database_name > structure.sql

# 备份数据
mysqldump -u root -p --no-create-info database_name > data.sql

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

使用mysqlpump备份

bash

# 并行备份数据库
mysqlpump -u root -p --parallel-schemas=2 database_name > backup.sql

# 备份指定表
mysqlpump -u root -p database_name --tables table1 table2 > backup.sql

使用xtrabackup备份

bash

# 全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=password

# 增量备份
xtrabackup --backup --target-dir=/backup/incremental --incremental-basedir=/backup/full --user=root --password=password

恢复命令

使用mysql恢复

bash

# 恢复数据库
mysql -u root -p database_name < backup.sql

# 恢复压缩备份
gzip -d < backup.sql.gz | mysql -u root -p database_name

# 从SQL文件中执行指定命令
mysql -u root -p database_name -e "source backup.sql"

使用xtrabackup恢复

bash

# 准备全量备份
xtrabackup --prepare --target-dir=/backup/full

# 恢复备份
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql

性能监控命令

查看服务器状态

sql

-- 查看服务器状态
SHOW STATUS;

-- 查看服务器版本
SELECT VERSION();

-- 查看服务器 uptime
SHOW GLOBAL STATUS LIKE 'Uptime';

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

查看存储引擎状态

sql

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看MyISAM状态
SHOW ENGINE MYISAM STATUS;

查看查询性能

查看慢查询

sql

-- 查看慢查询设置
SHOW GLOBAL VARIABLES LIKE 'slow_query%';

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

查看执行计划

sql

-- 查看执行计划
EXPLAIN SELECT * FROM table_name WHERE age > 25;

-- 查看扩展执行计划
EXPLAIN EXTENDED SELECT * FROM table_name WHERE age > 25;

-- 查看分区执行计划
EXPLAIN PARTITIONS SELECT * FROM table_name WHERE age > 25;

查看查询统计

sql

-- 查看查询执行统计
SET SESSION profiling = 1;
SELECT * FROM table_name WHERE age > 25;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

维护命令

表维护

检查表

sql

-- 检查表
CHECK TABLE table_name;

-- 快速检查表
CHECK TABLE table_name QUICK;

-- 完整检查表
CHECK TABLE table_name EXTENDED;

修复表

sql

-- 修复表
REPAIR TABLE table_name;

-- 快速修复表
REPAIR TABLE table_name QUICK;

-- 完整修复表
REPAIR TABLE table_name EXTENDED;

优化表

sql

-- 优化表
OPTIMIZE TABLE table_name;

-- 在线优化表（InnoDB）
ALTER TABLE table_name ENGINE=InnoDB;

分析表

sql

-- 分析表
ANALYZE TABLE table_name;

-- 在线分析表（InnoDB）
ALTER TABLE table_name ANALYZE PARTITION ALL;

日志管理

查看日志设置

sql

-- 查看错误日志设置
SHOW GLOBAL VARIABLES LIKE 'log_error';

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

-- 查看二进制日志设置
SHOW GLOBAL VARIABLES LIKE 'log_bin%';

-- 查看通用查询日志设置
SHOW GLOBAL VARIABLES LIKE 'general_log%';

管理二进制日志

sql

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看当前二进制日志
SHOW MASTER STATUS;

-- 刷新二进制日志
FLUSH BINARY LOGS;

-- 清除二进制日志
PURGE BINARY LOGS TO 'binlog.000005';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

常见问题（FAQ）

Q1: 如何在命令行中执行SQL文件？

A1: 可以使用以下命令执行SQL文件：

bash

mysql -u username -p database_name < file.sql

Q2: 如何查看MySQL服务器的端口号？

A2: 可以使用以下命令查看MySQL服务器的端口号：

sql

SHOW GLOBAL VARIABLES LIKE 'port';

Q3: 如何查看MySQL数据库的大小？

A3: 可以使用以下命令查看MySQL数据库的大小：

sql

SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

Q4: 如何查看MySQL表的大小？

A4: 可以使用以下命令查看MySQL表的大小：

sql

SELECT table_name AS 'Table', 
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
ORDER BY (data_length + index_length) DESC;

Q5: 如何导出MySQL查询结果到文件？

A5: 可以使用以下方法导出MySQL查询结果到文件：

使用SELECT INTO OUTFILE语句：

sql

SELECT * FROM table_name INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

使用mysql命令的重定向：

bash

mysql -u username -p -e "SELECT * FROM database_name.table_name" > /path/to/file.txt

MySQL 命令示例 ​

连接管理命令 ​

基本连接 ​

本地连接 ​

远程连接 ​

连接选项 ​

连接状态管理 ​

查看当前连接 ​

终止连接 ​

连接统计 ​

数据库操作命令 ​

数据库管理 ​

创建数据库 ​

删除数据库 ​

查看数据库 ​

切换数据库 ​

表操作 ​

创建表 ​

修改表 ​

删除表 ​

查看表 ​

数据操作 ​

插入数据 ​

更新数据 ​

删除数据 ​

查询数据 ​

用户和权限管理命令 ​

用户管理 ​

创建用户 ​

修改用户 ​

删除用户 ​

查看用户 ​

权限管理 ​

授予权限 ​

撤销权限 ​

查看权限 ​

刷新权限 ​

索引管理命令 ​

创建索引 ​

普通索引 ​

唯一索引 ​

主键索引 ​

全文索引 ​

查看索引 ​

删除索引 ​

配置管理命令 ​

查看配置 ​

查看全局变量 ​

查看会话变量 ​

查看状态变量 ​

修改配置 ​

修改全局变量 ​

修改会话变量 ​

MySQL 命令示例

连接管理命令

基本连接

本地连接

远程连接

连接选项

连接状态管理

查看当前连接

终止连接

连接统计

数据库操作命令

数据库管理

创建数据库

删除数据库

查看数据库

切换数据库

表操作

创建表

修改表

删除表

查看表

数据操作

插入数据

更新数据

删除数据

查询数据

用户和权限管理命令

用户管理

创建用户

修改用户

删除用户

查看用户

权限管理

授予权限

撤销权限

查看权限

刷新权限

索引管理命令

创建索引

普通索引

唯一索引

主键索引

全文索引

查看索引

删除索引

配置管理命令

查看配置

查看全局变量

查看会话变量

查看状态变量

修改配置

修改全局变量

修改会话变量