How to quickly modify the host attribute of a MySQL user

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

The default host attribute of an 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 statements are as follows:

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

Supplement: mysql modify root password modify account login host

1. Forgot the root password

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

The initial suspicion is that the password of account A in MySQL is not set correctly. Therefore, you need to use the root account to reset the hive account.

The annoying thing is that I didn’t install MySQL. And in the test 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 of all, please note that the MySQL root account and the server root account are not the same concept, so don't confuse them.

First, stop the mysql service:

sudo service mysql stop

If you have a root account on the server, you don't need sudo. The same applies to all the following operations. The above command works on Ubuntu and Debian. Use mysqld instead of mysql in CentOS, Fedora, and RHEL. The same applies to all the following operations.

Then, start mysql in safe mode:

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

This way we can log in directly as 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 account A

After logging in to mysql with the root account, let's 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 |
+------+--------------+

Now I understand. Damn, no wonder I can't log in to the navicat client. The host of the hive account is only 127.0.0.1, so you can only log in locally.

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

Set the hive account to be accessible to all machines, and then refresh the permissions:

mysql> flush privileges;

Let’s take another look:

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

So far, you’re done!

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.