Mysql用户管理远程连接

1:查询用户权限

mysql> select host,user,authentication_string from user;

2:创建用户

mysql> use mysql;
Database changed
mysql> create user 'oracle'@'%' identified with mysql_native_password by 'oracle';
Query OK, 0 rows affected (0.01 sec)
修改密码
mysql> alter user 'oracle'@'%' identified with mysql_native_password by 'y123456y';
Query OK, 0 rows affected (0.00 sec)

3:授权

mysql> grant all privileges on *.* to 'oracle'@'%';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看授权

mysql> show grants for 'jack'@'%';
+----------------------------------+
| Grants for jack@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `jack`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

4：远程连接

mysql --host 10.0.0.166 --user=jack -p --port=3306

5：连接报错解决

root@yuan-virtual-machine:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf

事实上，以上的步骤都是在 localhost 访问。
如果这时在远程访问，会发现，访问被拒绝。
这是因为 mysql的配置文件中 /etc/mysql/mysql.conf.d/mysqld.cnf 的 bind-address为 127.0.0.1 换回地址，只会监听来自本地的请求，我们将其改为 0.0.0.0 ，监听所有来源的请求。

5.2修改root为远程连接

mysql> update user set host = '%' where user = 'root';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string from user;

6：关闭防火墙

root@yuan-virtual-machine:~# ufw allow 3306
防火墙规则已更新
规则已更新(v6)
root@yuan-virtual-machine:~# ufw status
状态：不活动
root@yuan-virtual-machine:~#