【MySQL_04】数据库基本操作（用户管理--配置文件--远程连接--数据库信息查看、创建、删除）

本文远程连接Ubuntu主机上安装的MySQL Server，有些内容前面也讲过，可以看历史文章。

MySQL版本：8.4.4 LTS。

软件使用Navicat。

历史文章点击👉：SQL

🐈‍⬛github：https://github.com/mysql

💻官网： https://www.mysql.com

🌏维基百科：https://zh.wikipedia.org/wiki/MySQL

语法简述：SQL语句你看单次意思基本就能明白，语句不区分大小写，但运行时会字段转为大写，大小写看你公司规范。不过表名这种是有大小写的哦。你最好把相关语句的英语单词记住，去查查什么意思，怎么读。

一、MySQL 用户管理

1.1 用户管理

1.11 mysql.user表详解

MySQL的用户信息和权限配置存储在一个特殊的数据库中，这个数据库叫做 mysql 数据库。

mysql 数据库是MySQL系统的核心数据库之一，用于存储与MySQL服务器运行相关的系统信息，其中包括用户账户信息、权限设置、全局变量、插件信息等。

用户信息主要存储在mysql.user表中。

用户yun是我自己添加的（我创建的是一个特权用户，用于所有权限）。

其他几个是默认的，解释如下：

用户名	用途描述	权限范围
mysql.infoschema	用于访问information_schema数据库，提供数据库元数据的只读视图。	仅限访问information_schema，权限较低，主要用于查询元数据。
mysql.session	供MySQL内部使用，用于插件和内部会话操作。	用于服务器内部任务，如管理连接和会话，权限由MySQL内部控制。
mysql.sys	用于sys数据库，提供性能监控和诊断工具。	仅限访问sys数据库，权限较低，用于性能监控和优化。
root	默认的超级用户，拥有最高权限，用于数据库管理和配置。	拥有所有权限，可以执行任何数据库操作，包括创建、删除用户和数据库等。

---

user表共有51列，存储着每个用户的各种信息、权限等内容，本文分为6大类来解释：

（1）用户基本信息

字段名	含义	字段名	含义
Host	用户允许连接的主机地址（如localhost或%表示任意主机）。	User	用户名，用于标识用户。
plugin	认证插件类型（如caching_sha2_password）。	authentication_string	用户的认证凭据（密码哈希值）。

root用户最好只运行本机登录，即Host为localhost或者127.0.0.1这些。而特定的用户可以设置为允许登录的ip地址，或者允许任意主机连接。

authentication_string是用户密码哈希值，是由caching_sha2_password这种插件生成的，你无法查看真实密码，只能通过root用户修改密码。

---

（2）权限相关字段

这些是对用户的权限的比较精细的控制。这些权限默认基本全是拒绝的。

字段名	含义	字段名	含义
Select_priv	是否允许执行SELECT查询。	Insert_priv	是否允许执行INSERT插入操作。
Update_priv	是否允许执行UPDATE更新操作。	Delete_priv	是否允许执行DELETE删除操作。
Create_priv	是否允许创建数据库或表。	Drop_priv	是否允许删除数据库或表。
Reload_priv	是否允许执行FLUSH操作（重新加载权限表）。	Shutdown_priv	是否允许关闭MySQL服务器。
Process_priv	是否允许查看其他用户的进程（如SHOW PROCESSLIST）。	File_priv	是否允许读写文件（如LOAD DATA INFILE）。
Grant_priv	是否允许授予其他用户权限。	References_priv	是否允许创建外键约束（已弃用）。
Index_priv	是否允许创建或删除索引。	Alter_priv	是否允许修改表结构（如ALTER TABLE）。
Show_db_priv	是否允许查看所有数据库（如SHOW DATABASES）。	Super_priv	是否拥有超级权限（如执行管理命令）。
Create_tmp_table_priv	是否允许创建临时表。	Lock_tables_priv	是否允许锁定表。
Execute_priv	是否允许执行存储过程或函数。	Repl_slave_priv	是否允许作为复制从服务器。
Repl_client_priv	是否允许查询主服务器或从服务器的状态。	Create_view_priv	是否允许创建视图。
Show_view_priv	是否允许查看视图定义。	Create_routine_priv	是否允许创建存储过程或函数。
Alter_routine_priv	是否允许修改或删除存储过程或函数。	Create_user_priv	是否允许创建用户。
Event_priv	是否允许创建、修改或删除事件。	Trigger_priv	是否允许创建或删除触发器。
Create_tablespace_priv	是否允许创建表空间。	Create_role_priv	是否允许创建角色。
Drop_role_priv	是否允许删除角色。

---

（3）SSL/TLS相关字段

主要用于加密连接。

字段名	含义	字段名	含义
ssl_type	SSL连接类型（如ANY、X509、SPECIFIED）。	ssl_cipher	使用的SSL加密算法。
x509_issuer	X509证书的颁发者信息。	x509_subject	X509证书的主题信息。

---

（4）资源限制相关字段

字段名	含义	字段名	含义
max_questions	每小时允许的最大查询次数。	max_updates	每小时允许的最大更新次数。
max_connections	每小时允许的最大连接次数。	max_user_connections	用户同时允许的最大连接数。

---

（5）密码管理相关字段

默认都是0，表示无限制。

字段名	含义	字段名	含义
password_expired	密码是否已过期（Y或N）。	password_last_changed	密码最后一次修改的时间。
password_lifetime	密码的有效期（天数）。	Password_reuse_history	密码重用前需要更改的次数。
Password_reuse_time	密码重用前需要等待的天数。	Password_require_current	修改密码时是否需要提供当前密码（Y或N）。

---

（6）其他字段

字段名	含义	字段名	含义
account_locked	账户是否被锁定（Y或N）。	User_attributes	用户的附加属性（以JSON格式存储）。

1.12 添加用户

执行添加、修改、删除用户等操作，你必须有相关权限才行，root用户有所有权限。权限是对mysql.user这张表的权限。有CREATE USER权限可以创建用户；有UPDATE和ALTER权限可以修改其它用户属性，以此类推。

使用 CREATE USER 语句创建新用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username：用户名。
• host：允许连接的主机（如 localhost 或 % 表示所有主机）。
• password：你的用户密码。

创建无密码用户：危，不要这么做

CREATE USER 'newuser'@'localhost';

无密码用户，设置密码：

ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'your_password';

1.13 修改用户权限

权限： 即前面讲的51个字段中的权限相关字段，不要_priv（privilege）后缀，不要下划线，全大写。

如：

• 列名Select_priv对应的权限就是：SELECT；
• Create_view_priv就是CREATE VIEW。

---

🔹使用 GRANT 语句授予用户权限：

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

• 权限列表：可以是具体的权限（如 SELECT、INSERT、UPDATE 等，多个权限逗号分割），也可以是 ALL PRIVILEGES（表示所有权限）。
• 数据库名.表名：指定权限作用的范围。可以使用 . 表示所有数据库和表，数据库名.* 表示某个数据库的所有表。
• 用户名@主机名：指定用户和允许连接的主机（如 ‘test_user’@‘localhost’）。

例：
授予用户 test_user 对所有数据库的查询权限：

GRANT SELECT ON *.* TO 'test_user'@'localhost';

🔹使用 REVOKE 语句撤销权限：

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

🟢修改权限后需要刷新权限以生效：

FLUSH PRIVILEGES;

1.14 删除用户

使用 DROP USER 语句删除用户：

DROP USER 'username'@'host';

1.15 密码问题

注意1.12节说的权限问题。

🔹普通用户的密码：

修改密码：没密码就设置，有秘密就是修改

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';

有密码的可以设置为空，即无密码：

ALTER USER 'username'@'hostname' IDENTIFIED BY '';

记得，修改密码后刷新权限（后面不再重复）：

FLUSH PRIVILEGES;

---

🔹 root 用户的密码 ：

（1）修改：跟前面一样

以root用户登录，然后修改即可

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

（2） 忘记密码： 这个才是常见情况

先停止mysql服务：

sudo systemctl stop mysql

使用 mysqladmin 工具修改密码：

mysqladmin -u root -p password '新密码'

刷新后启动mysql：

sudo systemctl start mysql

或者以跳过权限验证的方式启动mysql（先关闭）：sudo mysqld_safe --skip-grant-tables &
登录root：mysql -u root
改密码：ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘new_password’;
刷新：FLUSH PRIVILEGES;
退出：EXIT;
停止（停止的是以跳过权限检查方式启动的mysql）和重启：sudo systemctl stop mysql
sudo systemctl start mysql

---

二、MySQL 配置文件

2.1 配置文件位置

☘️ （1）Windows

配置文件通常位于以下位置之一：

%basedir%\my.ini
%datadir%\my.ini
C:\ProgramData\MySQL\MySQL Server X.Y\my.ini（X.Y 是 MySQL 版本号）

获取：basedir和datadir路径：

登录mysql：此处 大写 -P 3360是我设置的mysql端口，默认是3306，如果是默认只值就不用指定这个参数。

mysql -u root -p -P 3360

执行语句：

SHOW VARIABLES LIKE 'basedir';
SHOW VARIABLES LIKE 'datadir';

如，我的配置文件在datadir下面：使用notepad++、记事本、vscode等软件即可打开查看

☘️ （2）Linux

命令：

mysql --help | grep 'my.cnf'

有3个位置的配置文件：

• /etc/my.cnf：系统级别的全局配置文件，优先级最低。

• /etc/mysql/my.cnf：特定 MySQL 安装的配置文件，优先级中等。

• ~/.my.cnf：用户(Linux 用户)级别的配置文件，优先级最高。

---

🤭前面的第二个配置文件：在 Debian/Ubuntu 系统中，通常采用分片式配置，即将配置分散到多个文件中，方便管理和维护。主要包括：

• /etc/mysql/my.cnf：主配置文件，通常会包含对其他配置文件的引用。
• /etc/mysql/mysql.conf.d/：包含 MySQL 服务器的配置片段。
  • mysqld.cnf：MySQL 服务器的主要配置。
• /etc/mysql/conf.d/：用户可以添加自定义配置片段。

上图最下面的： /etc/mysql/mysql.conf.d/mysqld.cnf 的作用

mysqld.cnf 文件专门用于配置 MySQL 服务器（mysqld）的行为。它通常包含以下内容：

• 数据目录（datadir）
• 绑定地址（bind-address）
• 端口（port）
• 日志文件路径（如 log_error）
• 字符集（如 character-set-server）
• 其他性能相关配置（如 innodb_buffer_pool_size）

---

2.2 配置文件结构

MySQL 配置文件是一个文本文件，采用 key = value 的格式。

配置文件分为多个部分，每个部分用 [section] 标识。

常见的部分包括：

• [mysqld]: MySQL 服务器的配置。
• [client]: 客户端工具的默认配置（如 mysql、mysqladmin）。
• [mysql]: mysql 命令行客户端的配置。
• [mysqldump]: mysqldump 工具的配置。
• [server]: 服务器相关的配置（较少使用）。

示例：

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
port = 3306
user = mysql
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4[mysql]
default-character-set = utf8mb4

2.3 常用配置参数

☘️ 服务器配置 ([mysqld])

• datadir: 数据目录，存储数据库文件的位置。

  datadir = /var/lib/mysql
  

• port: MySQL 服务器监听的端口号，默认是 3306，你修改了这个，连接数据库的时候就要 -P 参数指定端口；或者在[client]里面吧端口也设置为相同的（指的是你用来连接的电脑的配置文件里面的[client]）。

  port = 3306
  

• bind-address: 服务器绑定的 IP 地址。0.0.0.0 表示监听所有网络接口。如果只允许几个ip，用逗号隔开。

  bind-address = 0.0.0.0
  

• max_connections: 最大连接数。

  max_connections = 200
  

• character-set-server: 服务器默认字符集。

  character-set-server = utf8mb4
  

• collation-server: 服务器默认排序规则。

  collation-server = utf8mb4_unicode_ci
  

• innodb_buffer_pool_size: InnoDB 缓冲池大小，建议设置为系统内存的 50%-70%。

  innodb_buffer_pool_size = 1G
  

• log_error: 错误日志文件路径。

  log_error = /var/log/mysql/error.log
  

• slow_query_log: 是否启用慢查询日志。

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/slow.log
  long_query_time = 2
  

---

☘️ 客户端配置 ([client])

• port: 客户端连接时使用的默认端口。

  port = 3306
  

• socket: 客户端连接时使用的 Unix 套接字文件路径。

  socket = /var/lib/mysql/mysql.sock
  

• default-character-set: 客户端默认字符集。

  default-character-set = utf8mb4
  

☘️ 备份配置 ([mysqldump])

• max_allowed_packet: 备份时允许的最大数据包大小。

  max_allowed_packet = 64M
  

🟢修改配置文件后，需要重启 MySQL 服务以使更改生效：

sudo systemctl restart mysql

如果配置文件有语法错误，MySQL 可能无法启动。可以通过以下命令检查配置文件的正确性：

mysqld --validate-config

某些参数可以在 MySQL 运行时动态调整，无需重启服务，仅限此次运行期间有效例如：

SET GLOBAL max_connections = 300;

---

三、MySQL远程连接

现在其实没啥好讲的了。

1. 用户的Host要允许远程，如 %，而不是localhost；
2. 配置文件里面的：bind-address = 0.0.0.0
3. 服务器放行mysql使用的端口。

本系列的第二篇文章也有讲解。

四、数据库的查看、创建、删除

4.1 查看数据库

MySQL数据库连接成功之后，就可以使用SQL语句来查看当前所有存在的数据库，显示的一些默认的数据库是在 MySQL安装完成之后，在其目录 data 自动创建的几个必要的数据库，输入SQL语句如下：

 SHOW DATABASES;

输出：

这个命令起到一些小作用，可以帮助你查看创建的数据库是否创建成功，选择你创建的数据库等操作。

当然使用Navicat等软件直接就看到数据库了。

这些数据库我在第二篇讲过了：

切换到目标数据库：

USE 数据库名;

查看当前数据库的表：

SHOW TABLES;

---

🟢使用 SHOW TABLE STATUS 查看数据库中所有表的摘要信息：

SHOW TABLE STATUS FROM 数据库名;

4.2 创建、删除数据库

创建：

CREATE DATABASE test;

删除：

DROP DATABASE test;

创建或删除前可以先判断是否存在：

CREATE DATABASE if not  exists test;
DROP DATABASE IF EXISTS test;