MySQL数据库配置及创建用户和授权

注意： 都是基于MySQL8.0以上版本

1、检查是否安装过sql

[root@localhost ~]# rpm -[qa](https://so.csdn.net/so/search?q=qa&spm=1001.2101.3001.7020) | grep mysql

[root@localhost ~]# rpm -qa | grep [mariadb](https://so.csdn.net/so/search?q=mariadb&spm=1001.2101.3001.7020)

[root@localhost ~]# whereis mysql                                             

2、修改MySQL配置

需要注意的是，修改 MySQL 的配置参数时需要谨慎，如果参数配置不合理，可能会导致数据库性能下降或出现其他问题。修改 MySQL 的配置参数时，最好具有一定的经验或者了解相关的 MySQL 参数调优知识。

vi /etc/my.cnf

第一步：
cd /etc #进入etc目录下

第二步：
cp my.cnf my.cnf.bak #备份复制my.cnf文件

第三步：
①vi my.cnf #编辑my.cnf文件
②修改port=3306------------> port=更改的端口号
③ :wq #保存退出

第四步：
/usr/sbin/setenforce 0 #关掉selinux

第五步：
systemctl restart mysqld #重启MySQL服务

service mysql restart

第六步：
systemctl status mysqld #查看mysql服务的状态

第七步：
netstat -nltp #查看mysql端口号

第八步：
iptables-save #查看防火墙策略
（备注1：如果防火墙策略没有新添加的策略就执行第九步添加防火墙策略）

第九步：
firewall-cmd --zone=public --add-port=80/tcp --permanent #将端口号80做防火墙策略
（延申：firewall-cmd --zone=public --remove-port=80/tcp --permanent#将端口号80删除防火墙策）

第十步：
firewall-cmd --reload #更新防火墙策略
（备注1：更新完之后可以再执行第八步进行查看是否有防火墙策略）
（备注2：可以利用mysql连接工具连接进行测试）
（备注3：在mysql数据库中看端口号 show global variables like ‘port’;）

[mysql]
#MySQL 提示符配置

#用户名@主机名+数据库名
#prompt="\\u@\\h [\\d]>"

#用户名@主机名+mysql版本号+数据库名
prompt=\\u@\\h \\v [\\d]>\\_

#用户名@主机名+当前时间+mysql版本号+数据库名
#prompt="(\\u@\\h) \\R:\\m:\\s \\v [\\d] \n>"

# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld]
#mysql安装根目录
basedir = /usr/local/mysql/mysql-8.0.30/

#mysql数据文件所在位置
datadir = /usr/local/mysql/mysql-8.0.30/data/

#设置socke文件所在目录
socket = /tmp/mysql.sock

#数据库默认字符集, 主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）服务端使用的字符集默认为UTF8 
character-set-server = utf8mb4

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci

#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'

# MySQL设置免密登录
skip-grant-tables

# 用于设置数据传输时的最大数据包大小,默认值为 4MB（即 4 * 1024 * 1024），可以通过修改这个值来提高 SQL 语句执行时的性能。设置为 1000M（即 1000 * 1024 * 1024），表示最大数据包大小为 1GB，这意味着 MySQL 可以在一个查询中发送多达 1GB 的数据，对于需要传输大量数据的应用场景，这种设置可以带来非常大的性能优势。
max_allowed_packet=1000M

# 允许最大连接数
max_connections=10000

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

#设置表名不区分大小写
show variables like '%case_table%';
#端口设置
port = 3306

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

3、MySQL中user表详解

3.1 用户列（用户连接MySQL数据库需要输入的信息）

Host：主机名，双主键之一，值为%时表示匹配所有主机。User：用户名，双主键之一。Password：密码名。

Host	User	Password
%	root	*55B565DA3839E5955A68EA96EB735
localhost	root	*55B565DA3839E5955A68EA96EB735
127.0.0.1	root	*55B565DA3839E5955A68EA96EB735
::1	root	*26C378D308851D5C717C13623EFD6

(root,%)，表示可以远程登录，并且是除服务器外的其他任何终端，%表示任意IP都可登录。
(root,localhost)， 表示可以本地登录，即可以在服务器上登陆，localhost则只允许本地登录。
(root,127.0.0.1 )，表示可以本机登录，即可以在服务器上登陆
(root,sv01)，表示主机名为sv1可以登录，sv01具体指的哪台机器，可以在cat /etc/hostname查看
(root,::1) ， 表示本机可以登录， 看密码都是相同嘛，具体::1代表意义，待查

3.2 权限列

权限列决定了用户的权限，描述了用户在全局范围内允许对数据库和数据库表进行的操作，字段类型都是枚举Enum，值只能是Y或N，Y表示有权限，N表示没有权限。

权限字段名称	备注说明
Select_priv	确定用户是否可以通过SELECT命令选择数据
Insert_priv	确定用户是否可以通过INSERT命令插入数据
Delete_priv	确定用户是否可以通过DELETE命令删除现有数据
Update_priv	确定用户是否可以通过UPDATE命令修改现有数据
Create_priv	确定用户是否可以创建新的数据库和表
Drop_priv	确定用户是否可以删除现有数据库和表
Reload_priv	确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表重新加载权限表
Shutdown_priv	确定用户是否可以关闭MySQL服务器在将此权限提供给root账户之外的任何用户时,都应当非常谨慎
Process_priv	确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令

3.3 安全列

安全字段名称	备注说明
ssl_type	支持ssl标准加密安全字段
ssl_cipher	支持ssl标准加密安全字段
x509_issuer	支持x509标准字段
x509_subject	支持x509标准字段
password_expired	密码是否过期。Y：说明该用户密码已过期 N：没有过期
plugin	5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
authentication_string	通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)

3.4 资源控制列

控制字段名称	字段类型	是否为空	默认值	备注说明
max_questions	int(11)unsigned	NO	0	每小时允许执行多少次查询：0表示无限制
max_updates	int(11)unsigned	NO	0	每小时可以执行多少次更新：0表示无限制
max_connections	int(11)unsigned	NO	0	每小时可以建立的多少次连接：0表示无限制
max_user_connections	int(11)unsigned	NO	0	单用户可以同时具有的连接数：0表示无限制

4、创建用户和组以及授权

4.1 创建用户

1. 使用CREATE USER语句创建用户

可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：

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

其中，username 为新用户的用户名，password 为新用户的密码。此外，@‘localhost’ 表示该用户只能从本地连接到 MySQL，如果想要允许该用户从其它 IP 地址连接，则需要将 localhost 替换成对应的 IP 地址。

举例：

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

2. 使用 INSERT 语句新建用户

可以使用 INSERT 语句将用户的信息添加到 mysql.user 表中，但必须拥有对 mysql.user 表的 INSERT 权限。通常 INSERT 语句只添加 Host、User 和 authentication_string 这 3 个字段的值。

MySQL 5.7 的 user 表中的密码字段从 Password 变成了 authentication_string，如果你使用的是 MySQL 5.7 之前的版本，将 authentication_string 字段替换成 Password 即可。

使用 INSERT 语句创建用户的代码如下：

INSERT INTO mysql.user(User, Host, authentication_string, ssl_cipher, x509_issuer, x509_subject, ssl_type, Super_priv, Create_priv, Insert_priv, Update_priv, Delete_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, Grant_priv, References_priv, Alter_priv, Show_db_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Index_priv, Alter_routine_priv, Create_view_priv, Show_view_priv, create_role_priv,  Trigger_priv, password_last_changed)
VALUES('admin1', 'localhost', SHA2('123456', 512), '', '', '', '', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',  'Y', 'Y', 'Y', 'Y', 'N', 'N', CURRENT_TIMESTAMP);

由于 mysql 数据库的 user 表中，ssl_cipher、x509_issuer 和 x509_subject 这 3 个字段没有默认值，所以向 user 表插入新记录时，一定要设置这 3 个字段的值，否则 INSERT 语句将不能执行。

3.为什么user表删除的用户还能进行登录操作

1. 该用户被其他用户重新授权。在MySQL数据库中，授权是一项独立的权限，它可以被授予给其他用户。如果一个被删除的用户的权限被授予给了其他用户，那么该用户就可以使用该被删除用户的身份信息进行登录。
2. 垃圾数据残留。有时候，我们在删除用户时，可能会犯一些错误，例如删除不完整或者删除错误的用户。在这种情况下，这些被删除用户的信息仍然存在于数据库中，如果没有及时清理，可能会导致其可以进行登录。

4.2 GRANT授权

1. 权限列表

管理员特有的权限

权限	说明
SELECT	允许用户读取指定的表中的数据。
INSERT	允许用户插入表中的数据。
UPDATE	允许用户修改表中的数据。
DELETE	允许用户删除表中的数据。
CREATE	允许用户创建新的数据库或表。
DROP	允许用户删除数据库或表。
INDEX	允许用户创建或删除数据库中的索引。
ALTER	允许用户修改数据库的结构，如更改表的列、添加或删除索引等。
GRANT OPTION	允许用户将自己拥有的权限授予给其他用户。
ALL PRIVILEGES	允许用户执行所有的操作。

除了上述基本权限之外，也存在一些特殊的权限，比如：

权限	说明
CREATE TEMPORARY TABLES	允许用户创建临时表。
EXECUTE	允许用户执行存储过程或函数。
FILE	允许用户将数据写入服务器文件系统。
PROCESS	允许用户查看所有的 MySQL 进程。
RELOAD	允许用户重新加载服务端配置或刷新日志。
REPLICATION CLIENT	允许用户查看 MySQL 复制状态。
REPLICATION SLAVE	允许用户作为 MySQL 复制从库连接到主库。
SHOW DATABASES	允许用户查看服务器上所有的数据库。
SHUTDOWN	允许用户关闭 MySQL 服务器。

表格中的每个权限都描述了该权限的作用和用途。希望这个表格对你有所帮助！

虽然 CREATE USER 和 INSERT INTO 语句都可以创建普通用户，但是这两种方式不便授予用户权限。于是 MySQL 提供了 GRANT 语句。

2. 使用 GRANT 语句进行用户授权的基本语法形式如下:

GRANT <permissions> ON <database>.<table> TO <user>@[<host>];

其中，各个部分的含义如下：

• <permissions>：指定用户的权限，可以是特定的权限（如SELECT、INSERT、UPDATE、DELETE等），也可以是ALL PRIVILEGES表示拥有全部权限。
• <database>.<table>：指定用户的权限作用的数据库和表。
• <user>：指定要创建的用户的名称。
• <host>：指定用户的连接主机。可以是本地主机（localhost）、特定的IP地址，或者是通配符（%）表示所有主机。
• <password>：为用户指定密码。

下面是一个示例：

GRANT SELECT, INSERT ON mydatabase.mytable TO 'myuser'@'localhost';

这条语句会授予名为’myuser’、连接主机为localhost的用户在mydatabase数据库的mytable表上执行SELECT和INSERT操作的权限，并指定了用户的密码为’mypassword’。请根据实际情况进行相应调整。

3. 刷新权限

注意： （每次更新权限都需要刷新一次，不然可能不生效）

FLUSH PRIVILEGES;

4.3 删除GRANT授权

要删除之前授予的权限，可以使用REVOKE语句。下面是REVOKE语句的基本语法形式：

REVOKE <permissions> ON <database>.<table> FROM <user>@[<host>];

其中，各个部分的含义与GRANT语句相同。

下面是一个示例，演示如何撤销之前授予的SELECT和INSERT权限：

REVOKE SELECT, INSERT ON test.test FROM 'myuser'@'localhost';

执行这条语句将从用户’myuser’@'localhost’身上撤销在test数据库的test表上的SELECT和INSERT权限。

请注意，REVOKE语句只会撤销已经授予的权限，而不会删除用户本身。如果需要删除用户，可以使用DROP USER语句。例如，DROP USER 'myuser'@'localhost';将删除’myuser’@'localhost’用户。

在执行REVOKE或DROP USER语句时，请确保你有足够的权限执行这些操作。只有具有足够权限的用户才能撤销或删除其他用户的权限。

4.4 查看用户权限

SHOW GRANTS FOR 'username'@'host';

将 'username'@'host' 替换为你要查看权限的用户名和主机信息。对于 'host' 部分，可以使用 'localhost' 表示本地连接，也可以使用实际的 IP 地址或主机名。

另外，你还可以通过查询 MySQL 的权限表来查看用户的权限信息。可以运行以下命令：

SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'host';

将 'username' 和 'host' 替换为你要查询的用户名和主机信息。这将返回与给定用户和主机相对应的权限记录。

2、修改数据库密码

第一步：

mysql -uroot -p

输入密码，然后回车

修改数据库账户密码
4.1 更新密码

语法：update user set authentication_string=password('新密码') where user = '用户名';

mysql > update user set authentication_string=password('新密码') where user = '用户名';
    
mysql5.7以下版本：UPDATE mysql.user SET Password=PASSWORD('新密码') where USER='用户名';

mysql5.7版本：UPDATE mysql.user SET authentication_string=PASSWORD('新密码') where USER='用户名';

mysql8.0版本：update mysql.user set authentication_string=‘新密码’ where user=‘root’;
mysql8.0版本：ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY '新密码';

4.2 刷新权限

mysql > flush privileges;

然后就可以使用新密码登录数据库了

3、设置允许远程登录

mysql> use mysql
mysql> update user set user.Host='%'where user.User='root';
mysql> flush privileges;
mysql> quit

https://blog.csdn.net/weixin_44234912/article/details/109078167

https://blog.csdn.net/qq_57389269/article/details/126248678

https://blog.csdn.net/qq_38914940/article/details/127216367