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

来源:csdn博客 分类: 文章浏览史 发布时间:2020-03-24 12:05:11 最后更新:2020-03-24 浏览:170
转载声明:
本文为摘录自“csdn博客”,版权归原作者所有。
温馨提示:
为了更好的体验,请点击原文链接进行浏览
摘录时间:
2020-03-24 12:05:11

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

 

php技术微信