尚拙

一个分享技术、学习成长的个人博客网站

0%

mysql常用命令

发表于 分类于 ，

在日常开发与运维中，MySQL 是最常用的数据库之一。本文整理了 MySQL 常见的管理、查询、备份、权限与主从相关命令，便于快速查阅。

一、MySQL 登录与退出

1、 本地登录

mysql -uroot -p

参数说明：

  • -u：指定用户名
  • -p：提示输入密码
  • root：登录用户

2、指定主机和端口

mysql -h 127.0.0.1 -P 3306 -uroot -p

参数说明：

  • -h：数据库主机地址
  • -P：端口号（默认 3306）
  • -u：用户名
  • -p：密码

3、使用 socket 登录

mysql -uroot -p -S /tmp/mysql.sock

参数说明：

  • -S：指定 socket 文件路径

4、 退出 MySQL

exit;

或：

quit;

二、数据库管理

1、 查看数据库

SHOW DATABASES;

2、创建数据库

CREATE DATABASE db_name
DEFAULT CHARACTER SET utf8mb4;

参数说明：

  • db_name：数据库名称
  • DEFAULT CHARACTER SET：默认字符集
  • utf8mb4：支持完整 UTF-8（推荐）

3、删除数据库

DROP DATABASE db_name;

4、 选择数据库

USE db_name;

三、数据表管理

1、 查看当前数据库所有表

SHOW TABLES;

2、查看表结构

DESC table_name;

或：

SHOW CREATE TABLE table_name;

3、创建表示例

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

参数说明：

  • PRIMARY KEY：主键
  • AUTO_INCREMENT：自增
  • NOT NULL：不能为空
  • DEFAULT：默认值

4、 修改表结构

添加字段：

ALTER TABLE user ADD email VARCHAR(100);

删除字段：

ALTER TABLE user DROP email;

修改字段类型：

ALTER TABLE user MODIFY name VARCHAR(100);

5、 删除表

DROP TABLE table_name;

四、数据操作（CRUD）

1、 插入数据

INSERT INTO user (name, age) VALUES ('Tom', 18);

批量插入：

INSERT INTO user (name, age) VALUES
('Tom', 18),
('Jerry', 20);

2、查询数据

查询全部：

SELECT * FROM user;

条件查询：

SELECT * FROM user WHERE age > 18;

排序：

SELECT * FROM user ORDER BY age DESC;

分页：

SELECT * FROM user LIMIT 10 OFFSET 0;

参数说明：

  • LIMIT：限制返回条数
  • OFFSET：偏移量

3、更新数据

UPDATE user SET age = 25 WHERE id = 1;

⚠️ 不加 WHERE 会更新整张表。

4、 删除数据

DELETE FROM user WHERE id = 1;

清空表（保留结构）：

TRUNCATE TABLE user;

五、用户管理（创建与修改统一版）

MySQL 8.0 默认认证插件：

caching_sha2_password

兼容旧系统可使用：

mysql_native_password

1、 创建用户

①基本创建

CREATE USER 'test'@'%' IDENTIFIED BY 'password123';

参数说明：

  • 'test'：用户名
  • '%'：允许连接的主机
  • IDENTIFIED BY：设置密码

②创建并指定 native 插件

CREATE USER 'test'@'%'
IDENTIFIED WITH mysql_native_password
BY 'password123';

参数说明：

  • IDENTIFIED WITH：指定认证插件
  • mysql_native_password：传统加密方式
  • BY：设置密码

③创建并设置资源限制

CREATE USER 'test'@'%'
IDENTIFIED WITH mysql_native_password
BY 'password123'
WITH
MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100
MAX_UPDATES_PER_HOUR 500
MAX_USER_CONNECTIONS 10;

参数说明：

  • MAX_QUERIES_PER_HOUR：每小时最大查询数
  • MAX_CONNECTIONS_PER_HOUR：每小时连接次数
  • MAX_UPDATES_PER_HOUR：更新次数
  • MAX_USER_CONNECTIONS：最大同时连接数

2、修改用户

①修改密码

ALTER USER 'test'@'%' IDENTIFIED BY 'newpassword';

②修改插件并重置密码

ALTER USER 'test'@'%'
IDENTIFIED WITH mysql_native_password
BY 'newpassword';

③ 修改资源限制

ALTER USER 'test'@'%'
WITH MAX_USER_CONNECTIONS 20;

④设置密码过期策略

永不过期：

ALTER USER 'test'@'%' PASSWORD EXPIRE NEVER;

90 天过期：

ALTER USER 'test'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

⑤锁定 / 解锁账户

锁定：

ALTER USER 'test'@'%' ACCOUNT LOCK;

解锁：

ALTER USER 'test'@'%' ACCOUNT UNLOCK;

3、授权用户

GRANT ALL PRIVILEGES ON db_name.* TO 'test'@'%';

参数说明：

  • ALL PRIVILEGES：全部权限
  • db_name.*：数据库下所有表

刷新权限：

FLUSH PRIVILEGES;

4、 查看用户信息

查看插件：

SELECT user, host, plugin FROM mysql.user;

查看权限：

SHOW GRANTS FOR 'test'@'%';

六、索引管理

创建索引：

CREATE INDEX idx_name ON user(name);

创建唯一索引：

CREATE UNIQUE INDEX idx_email ON user(email);

删除索引：

DROP INDEX idx_name ON user;

查看索引：

SHOW INDEX FROM user;

七、事务控制

START TRANSACTION;
COMMIT;
ROLLBACK;

说明：

  • START TRANSACTION：开启事务
  • COMMIT：提交
  • ROLLBACK：回滚

八、备份与恢复

备份数据库：

mysqldump -uroot -p db_name > db.sql

参数说明：

  • mysqldump：备份工具
  • db_name：数据库名
  • >：重定向到文件

恢复数据库：

mysql -uroot -p db_name < db.sql

九、主从复制命令

主库：

SHOW MASTER STATUS;

从库：

SHOW REPLICA STATUS\G

启动从库：

START REPLICA;

停止从库：

STOP REPLICA;

十、MySQL 服务管理（Linux）

启动：

systemctl start mysql

停止：

systemctl stop mysql

重启：

systemctl restart mysql

查看状态：

systemctl status mysql

十一、常见排查命令

查看当前连接：

SHOW PROCESSLIST;

查看最大连接数：

SHOW VARIABLES LIKE 'max_connections';

查看慢查询是否开启：

SHOW VARIABLES LIKE 'slow_query_log';

查看数据库大小：

SELECT table_schema AS db,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;