How to modify host in mysql

Mysql method to modify host: 1. Stop the mysql service through "sudo service mysql stop"; 2. Start mysql in safe mode, and then reset the root password; 3. Pass "update user set Host=' %' where User='hive';" statement can be used to modify the host.

The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.

mysql How to modify host?

Quickly modify the host attribute of the MySQL user:

When you log in to MySQL remotely, the account you use must have special requirements.

The default host attribute of the account is localhost, which means that this account can only be used locally. If you want to use an account to log in remotely, you must change the host attribute value of the account to %.

The executed sql statement is as follows:

update user set host = '%' where user = 'root';

Supplement: mysql changes the root password and changes the account to log in to the host

1. Forgot The root password is set

The remote server has a mysql service with a hive account in it. From the remote server, I can use the command line to log in using mysql -hlocalhost -uxxx -pxxx, but how can I log in using the navicat client? I can't log in, and navicat still displays the IP address of my own machine.

Initial suspicion is that the password of account A in mysql is not set correctly. So you need to use the root account to reset the hive account.

The trick is that mysql was not created by me. And in the testing environment, I don’t know who to ask for the password. Then use the ultimate weapon: change the root password.

2. Reset mysql root password.

First thing to note: the root account of mysql and the root account of the server are not the same concept, so do not confuse them.

First, stop the mysql service:

sudo service mysql stop

If you have a root account for the server, sudo is not needed. The same is true for all the following operations. The above command works on Ubuntu and Debian. Use mysqld to replace mysql under CentOS, Fedora and RHEL. The same goes for all the operations below.

Then, start mysql in safe mode:

sudo mysqld_safe --skip-grant-tables --skip-networking &

So we can log in directly with root without a password:

mysql -u root

In this way, we logged in to mysql with the root account.

Then, you can reset the root password:

mysql> use mysql; 
mysql> update user set password=PASSWORD("mynewpassword") where User='root'; 
mysql> flush privileges;

After the reset is complete, exit mysql. Then start the mysql service:

sudo service mysql restart

Next, log in with the root account:

mysql -u root -pmynewpassword

3. Modify the relevant permissions of the A account

After logging in to mysql with the root account, take a look at the relevant information of account A:

mysql> use mysql;
Database changed
mysql> select User, Host from user where User='hive';
+------+--------------+
| User | Host     |
+------+--------------+
| hive | 127.0.0.1  |
+------+--------------+

You will understand it now. No wonder the navicat client cannot be logged in. The host of the hive account is only 127.0.0.1, so naturally you can only log in on this machine.

mysql> update user set Host='%' where User='hive';

Set the hive account so that all machines can log in, and then refresh the permissions:

mysql> flush privileges;

Check again:

mysql> select User, Host from user where User='hive';
+------+------+
| User | Host |
+------+------+
| hive | %  |
+------+------+

So far, you’re done!

The above is personal experience, I hope it can give everyone a reference. If there are any errors or incomplete considerations, please feel free to enlighten me.

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of How to modify host in mysql. For more information, please follow other related articles on the PHP Chinese website!