MySQL添加新用户、创建数据库、为新用户分配权限

布满荆棘的人生 2022-12-19 09:12 185阅读 0赞

1.登录Mysql

[root@xufeng Desktop]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6

2.添加新的用户  
允许本地 IP访问localhost的Mysql数据库

mysql> create user 'editest'@'localhost' identified by 'editest123456';
    Query OK, 0 rows affected (0.06 sec)

允许外网IP访问数据库editest，本命令包含上面的命令，是所有的IP都可以访问该数据库

mysql> create user 'editest'@'%' identified by 'editest123456';
    Query OK, 0 rows affected (0.00 sec)

用户创建完成后，刷新授权

mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

3.创建一个新的数据库，并使用show databases命令查看数据库是否创建OK

mysql> create database editestdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
    Query OK, 1 row affected (0.01 sec)

4.将改用户editest赋权给数据库editestdb，并刷新授权

mysql> grant all privileges on `editestdb`.* to 'editest'@'localhost' identified by 'editest123456' with grant option;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on `editestdb`.* to 'editest'@'%' identified by 'editest123456' with grant option;
    Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

5.退出 root 重新登录，使用editest登录检查本地登录是否正常，输入密码后，正常登录，并使用show databases命令查看数据库赋权是否正常。

[root@xufeng Desktop]# mysql -u editest -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.7.22 MySQL Community Server (GPL)
     
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
     
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
     
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
     
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | editestdb          |
    +--------------------+
    2 rows in set (0.00 sec)

6.验证远程IP地址登录是否正常，使用MySQL Workbench配置数据库并进行验证OK

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1MTYwOA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA1MTYwOA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/e98ba4c0845349c592d92a0a24e4fb1d.png