常用Mysql命令

前言

本文列举了一些常见的mysql操作

正文

一、连接和登录 MySQL

1. 使用命令行登录 MySQL

注意：需要将mysql的bin目录导入到环境变量中

mysql -u 用户名 -p

示例：

mysql -u root -p

执行上述命令后，系统会提示输入密码，输入正确的密码即可登录到 MySQL 控制台。
如果密码忘记则可以直接跳到六

2. 指定主机和端口登录

mysql -u 用户名 -p -h 主机地址 -P 端口号

示例：

mysql -u root -p -h localhost -P 3306

二、查看 MySQL 用户

1. 查看所有用户

登录到 MySQL 后，执行以下 SQL 语句查看所有用户：

SELECT User, Host FROM mysql.user;

说明：

• User 列显示用户名。
• Host 列显示用户可以从哪些主机连接。

2. 查看用户及其权限

SHOW GRANTS FOR '用户名'@'主机';

示例：

SHOW GRANTS FOR 'root'@'localhost';

三、创建和删除用户

1. 创建新用户

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

示例：

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'Test@1234';

说明：

• '用户名'：新用户的名称。
• '主机'：指定用户可以从哪些主机连接，'localhost'表示只能从本地主机连接，'%'表示可以从任何主机连接。
• '密码'：为用户设置的密码。

2. 删除用户

DROP USER '用户名'@'主机';

示例：

DROP USER 'testuser'@'localhost';

四、修改用户密码

在 MySQL 8.0 之前和之后，修改用户密码的方式有所不同。以下是两种不同版本中的密码修改方法：

MySQL 8.0 之前

在 MySQL 8.0 之前，使用 SET PASSWORD 命令或 UPDATE 命令来修改用户密码。常见的方式如下：

1. 使用 SET PASSWORD 命令：

   SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');
   

   例如：

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
   

2. 使用 UPDATE 命令直接更新 mysql.user 表：

   UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'username' AND Host = 'host';
   FLUSH PRIVILEGES;
   

   例如：

   UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
   FLUSH PRIVILEGES;
   

MySQL 8.0 及以后

从 MySQL 8.0 开始，推荐使用 ALTER USER 语句来修改用户密码。mysql.user 表中的密码字段也改名为 authentication_string。

1. 使用 ALTER USER 命令：

   ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
   

   例如：

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   

2. 不再需要使用 FLUSH PRIVILEGES：
   在 MySQL 8.0 及以后，使用 ALTER USER 修改密码后，不需要再执行 FLUSH PRIVILEGES 命令。

其他说明

• 在 MySQL 8.0 之前，PASSWORD() 函数用于生成加密后的密码值；在 MySQL 8.0 中则不再使用该函数。
• 从 MySQL 5.7 开始，MySQL 默认使用 mysql_native_password 插件进行密码验证，而在 MySQL 8.0 中则默认使用 caching_sha2_password 插件。

五、权限管理

1. 授予权限

GRANT 权限 ON 数据库.表 TO '用户名'@'主机';

示例：

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'testuser'@'localhost';

说明：

• 权限：可以是单个权限，如 SELECT，也可以是多个权限，用逗号分隔，或者使用 ALL PRIVILEGES 授予所有权限。
• 数据库.表：指定权限适用的数据库和表，*.* 表示所有数据库的所有表。

2. 撤销权限

REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机';

示例：

REVOKE SELECT, INSERT ON mydatabase.* FROM 'testuser'@'localhost';

3. 刷新权限

在修改权限后，执行以下命令使权限生效：

FLUSH PRIVILEGES;

4. 查看用户权限

SHOW GRANTS FOR '用户名'@'主机';

示例：

SHOW GRANTS FOR 'testuser'@'localhost';

六、查看当前连接和会话

1. 查看当前连接的会话

SHOW PROCESSLIST;

说明：
该命令显示当前正在运行的线程信息，包括线程 ID、用户、主机、数据库、命令、执行时间、状态和信息。

2. 查看当前连接数

SHOW STATUS LIKE 'Threads_connected';

七、启动和停止 MySQL 服务

在 Windows 下

启动 MySQL 服务：

net start mysql

停止 MySQL 服务：

net stop mysql

八、配置 MySQL 服务器设置

MySQL 的配置文件通常位于 /etc/my.cnf（Linux）或 C:\ProgramData\MySQL\MySQL Server X.X\my.ini（Windows）。

常见配置选项：

[mysqld]
port=3306
bind-address=0.0.0.0
max_connections=200
default_storage_engine=InnoDB
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

说明：

• port：设置 MySQL 服务的端口号。
• bind-address：设置 MySQL 监听的 IP 地址，0.0.0.0 表示监听所有地址。
• max_connections：设置最大连接数。
• default_storage_engine：设置默认存储引擎。
• character-set-server 和 collation-server：设置默认字符集和校对规则。

修改配置文件后，需要重启 MySQL 服务使配置生效。

九、备份和恢复数据库

1. 备份数据库

mysqldump -u 用户名 -p 数据库名 > backup.sql

示例：

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. 恢复数据库

mysql -u 用户名 -p 数据库名 < backup.sql

示例：

mysql -u root -p mydatabase < mydatabase_backup.sql

---