mysql创建用户并给用户分配权限

时间:2022-01-11 08:49:19

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)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| confluence |
| editestdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 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