【MySQL_04】数据库基本操作（用户管理--配置文件--远程连接--数据库信息查看、创建、删除）
文章目录
- 一、MySQL 用户管理
- 1.1 用户管理
- 1.11 mysql.user表详解
- 1.12 添加用户
- 1.13 修改用户权限
- 1.14 删除用户
- 1.15 密码问题
- 二、MySQL 配置文件
- 2.1 配置文件位置
- 2.2 配置文件结构
- 2.3 常用配置参数
- 三、MySQL远程连接
- 四、数据库的查看、创建、删除
- 4.1 查看数据库
- 4.2 创建、删除数据库
本文远程连接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远程连接
现在其实没啥好讲的了。
- 用户的Host要允许远程，如 %，而不是localhost；
- 配置文件里面的
：bind-address = 0.0.0.0 - 服务器放行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;