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

朴灿烈づ我的快乐病毒、 2024-03-22 20:42 44阅读 0赞

**注意：** 都是基于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：密码名。

<table> 
 <thead> 
  <tr> 
   <th>Host</th> 
   <th>User</th> 
   <th>Password</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%</td> 
   <td>root</td> 
   <td>*55B565DA3839E5955A68EA96EB735</td> 
  </tr> 
  <tr> 
   <td>localhost</td> 
   <td>root</td> 
   <td>*55B565DA3839E5955A68EA96EB735</td> 
  </tr> 
  <tr> 
   <td>127.0.0.1</td> 
   <td>root</td> 
   <td>*55B565DA3839E5955A68EA96EB735</td> 
  </tr> 
  <tr> 
   <td>::1</td> 
   <td>root</td> 
   <td>*26C378D308851D5C717C13623EFD6</td> 
  </tr> 
 </tbody> 
</table>

(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表示没有权限。

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

#### 3.3 安全列 ####

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

#### 3.4 资源控制列 ####

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

### 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. 权限列表 #####

管理员特有的权限

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

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

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

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

虽然 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

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jzfBv8ce-1690278150458)(E:\\PRD\\Images\\image-20230609092347757.png)\]][img-jzfBv8ce-1690278150458_E_PRD_Images_image-20230609092347757.png]

输入密码，然后回车

修改数据库账户密码  
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

[img-jzfBv8ce-1690278150458_E_PRD_Images_image-20230609092347757.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d3e83cf0d0ae4c72934085c930ef80d1.png