How to set remote access permissions for MySQL database

MySQL database remote access permission setting

For beginners, we install mysql to the local service and then use some graphical tools to link.

Under normal circumstances, we can connect successfully; however, in a simulated real environment, our database cannot be installed directly on the local machine, most of which are on cloud servers. In this case, our mysql cannot be installed directly on the local machine. The roadside bus doesn’t just stop when you wave it, but you still make an appointment (buy the ticket first!); that is, your account does not allow remote login and can only log in on localhost. What should I do?

Let us log in to mysql first

Change the table (of course we need to know where the table is first, it applies to the entire database)

• show databases;#Show all databases

• ##use mysql;#Use mysql database

• show tables;#Find this user table

• ## select * from user;

  #View all information in the table

We can clearly understand the corresponding host; user and user permissions

1. Directly modify

update user set host = '%' where user = 'root';#更新root用户的主机访问为任何值

2. Add specified users and specified permissions

insert into user(host,user) values('ip','user');#添加一个用户user地址ip的无权限用户（可以插入新增用户信息时，附带把权限也查进去，执行时会有告警，因为表中有约束，不用深究）

The new addition is successful, but there is no permission.

Authorization

• Direct authorization (mysql8.0 version will report an error, syntax segmentation)

  grant all privileges on *.* to 'user'@'ip' identified by '123456';#授权给主机为ip的user用户开放，所有数据库及对应表的所有权限，并且密码设置为123456（快速简洁）

  Copy after login

• Direct authorization (changed - for new version)

  create user ‘user'@‘ip' identified by ‘123456'；#创建一个主机地址是ip登录密码是123456的user用户
  grant all privileges on *.* to 'user'@'ip';#链接上一步，给他所有权限

  Copy after login

• Specify authorization

  create user ‘mysql'@‘%' identified by ‘123456'；#创建一个主机地址可以时任何地址的登录密码是123456的mysql用户（随时随地，在任何地方的进行登录操作）
  grant select,create on myemployees.* to 'mysql'@'%';#设置mysql用户只用myemployees库的查，创建权限

  Copy after login

flush privilege；#刷新下数据权限

Due to changes in password rules and grammar rules in version 8.0, you need to set it again

alter user ‘mysql'@‘%' identified by ‘123456' passwore expire never；#修改加密规则
alter user ‘mysql'@‘%' identified by ‘123456' mysql_native_password by '123456';#再次重置密码
flush privilege；#刷新下数据权限