技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

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

 收录于 Sql
   约 4562 字   预计阅读 10 分钟 
https://bing.ee123.net/img/rand?artid=146153338

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

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

MySQL版本：8.4.4 LTS。

软件使用 Navicat 。

历史文章点击👉：

🐈‍⬛github：

💻官网：

🌏维基百科：

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

一、MySQL 用户管理

1.1 用户管理

1.11 mysql.user表详解

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

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

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

https://i-blog.csdnimg.cn/direct/2bace468d92040629c8a640ea50d032e.png

用户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_typeSSL连接类型（如 ANY 、 X509 、 SPECIFIED ）。ssl_cipher使用的SSL加密算法。
x509_issuerX509证书的颁发者信息。x509_subjectX509证书的主题信息。

（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';

https://i-blog.csdnimg.cn/direct/c323ca2aa31c4811a3187a590edd3403.png

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

https://i-blog.csdnimg.cn/direct/32902ceab67b405e8017686fa94340b6.png

☘️ （2）Linux

命令：

mysql --help | grep 'my.cnf'

有3个位置的配置文件：

https://i-blog.csdnimg.cn/direct/308aeee669aa4912be8fcf42d9e325d0.png

  • /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/ ：用户可以添加自定义配置片段。

https://i-blog.csdnimg.cn/direct/281adc0cb8c24bf08d21bab205ab7b70.png

上图最下面的： /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;

输出：

https://i-blog.csdnimg.cn/direct/7b472a10e52b461fadcad6c98c4e6ee9.png

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

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

https://i-blog.csdnimg.cn/direct/aad98f24b34a4362b5278ea73099d035.png

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

https://i-blog.csdnimg.cn/direct/08d445e1f2824f0db61df618e84961f3.png

切换到目标数据库：

USE 数据库名;

查看当前数据库的表：

SHOW TABLES;

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

SHOW TABLE STATUS FROM 数据库名;

https://i-blog.csdnimg.cn/direct/89955d8d3a3846b1bd89922228f4cc8b.png

4.2 创建、删除数据库

创建：

CREATE DATABASE test;

删除：

DROP DATABASE test;

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

CREATE DATABASE if not  exists test;
DROP DATABASE IF EXISTS test;
更新于 2025-03-10
 数据库, Mysql, Adb
返回 | 主页