How to set remote connection root permissions in mysql
Today, I will talk to you about how to set remote connection root permissions in mysql. Many people may not know much about it. In order to make you understand better, the editor summarized the following content for you. I hope you can get something according to this article.
You should have encountered it when connecting to mysql remotely. Root users cannot remotely connect to mysql, but can only connect locally and refuse to connect to the outside world.
You need to establish a database account that allows remote login so that you can manipulate the database remotely.
The methods are as follows:
By default, the system database of MYSQL database mysql system table user user rights only provide localhost local login
Permissions need to be changed to connect to the MYSQL database remotely.
You can confirm it in the following ways:
Root#mysql-h localhost-uroot-p
Enter password: *
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 4 to server version: 4.0.20a-debug
Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.
Mysql > use mysql; (this DB stores various configuration information of MySQL)
Database changed
Mysql > select host,user from user; (view the permissions of the user)
Mysql > select host, user, password from user
+-- +
| | host | user | password | |
+-- +
| | localhost | root | * 4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| | 127.0.0.1 | root | * 4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| | localhost |
+-- +
4 rows in set (0.01sec)
As can be seen from this, it can only be accessed as a host of localhost.
Solution:
Mysql > Grant all privileges on *. * to identified by 'kdm001' with grant option
(% means all external machines, if you specify a machine, change% to the corresponding machine name; 'root' refers to the user name to be used,)
> flush privileges; (run this sentence to take effect, or restart MySQL)
Query OK, 0 rows affected (0.03 sec)
Check again.
Mysql > select host, user, password from user
+-- +
| | host | user | password | |
+-- +
| | localhost | root | * 4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| | 127.0.0.1 | root | * 4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| | localhost |
| |% | root | * 4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-- +
4 rows in set (0.01sec)
You can see that a new user has been added
Check whether mysqld snooping mode is set to only listen on localhost.
Use netstat to see if it is. Find mysql's configuration file my.cnf, and change bind address to modify the real IP of this machine.
Or comment out the bind address item directly. A restart is required to take effect.
After reading the above, do you have any further understanding of how to set remote connection root permissions in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
