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