How to allow external network access to mysql and modify mysql account password

The root account of mysql, I usually use localhost or 127.0.0.1 when connecting. The mysql on the company's test server is also localhost, so I can't access it when I want to, and the test is suspended.

The solution is as follows:

1. Modify the table, log in to the MySQL database, switch to the MySQL database, and use the SQL statement to view "select host, user from user;"

mysql -u root -pvmwaremysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
mysql>flush privileges;

Note: The last sentence is very important to make the changes take effect. If it is not written, remote connection will still not be possible.

2. Authorize the user, you want to connect to the mysql server from any host using the root password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin123' WITH GRANT OPTION;
flush privileges;

If you want to allow user root to connect to the mysql server from the host with ip 192.168.1.104

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.104' IDENTIFIED BY 'admin123' WITH GRANT OPTION;
flush privileges;

Change MySql Password

In MySQL 5.7 the password field has been removed from the mysql.user table and the new field is called "authentication_string".

Select the database:

use mysql;

Update the root password:

update user set authentication_string=password('new password') where user='root' and Host='localhost';

Refresh permissions:

flush privileges;

I hope the above content can help everyone. Thank you for your support of 123WORDPRESS.COM.