Shulou(Shulou.com)06/02 Report--

cause

Today, when accessing the remote database through mysql workbench under the ubuntu16.04 environment, I found that I could not connect. The ideas and methods to solve the problem are as follows. Please give us more advice on the deficiencies.

problem

Enter the password to access the Times through workbench:

Your connection attempt failed for user 'root' from your host to server at 118.89.153.162 Access denied for user 3306: root'@'118.89.153.162' (using password: YES)

Train of thought

Network problems, restart the workbench and MySQL services to connect after changing the network, it is still this mistake. The password is incorrect. Check the password of the remote mysql through the ssh remote login server and find that the password is correct. Remote mysql does not have access rights, and there are generally two methods: table modification and authorization:

Authorization law:

Step 1: try to open the / etc/mysql/my.cnf configuration file. If the file does not exist or the content is empty, open the following file path:

Sudo vim / etc/mysql/my.cnf / / or: sudo vim / etc/mysql/mysql.conf.d/mysqld.cnf

Step 2: look for the following line in the file. If the line is not preceded by a #, you can add a # before the line to make a comment and save the exit.

Bind-address = 127.0.0.1

Table change method:

Mysql > use mysql;mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' WITH GRANT OPTION / / give any host access to and modify all data / / for example, if you want root users to connect to the mysql server from any host using root. / / GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' root' WITH GRANT OPTION;// if you want to allow the user root to connect to the mysql server from the host with ip 192.168.1.124 and use 123456 as the password / / GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.1.124'IDENTIFIED BY' 123456' WITH GRANT OPTION Mysql > flush privileges / / permissions refresh to make the changes take effect immediately sudo / etc/init.d/mysql restart / / or: service mysql restart restart the mysql service

I tried two methods, and the error type became:

Can't connect to MySQL server on...

Seeing this error, I thought of the reason for the remote server firewall, so I had the following ideas:

4. The remote port 3306 cannot be accessed locally due to the firewall of the remote ubuntu server

Sudo ufw disable / / disable the firewall. This command needs to be executed under the authority of root, so use it with caution.

Connect to the remote mysql via workbench again, and the connection is successful.

Reason

My local cannot connect remotely because the remote ubuntu server firewall prohibits external access to port 3306 by default.

Practice

Sudo ufw enable / / enable firewall sudo ufw allow 3306 / / allow external access to port 3306

Summary

1. The general problem of not being able to connect to the remote mysql is caused by the above four willingness, which will not be discussed here.

two。 When you encounter a problem, the first thing to do is not to find a way to solve the problem immediately, but to think about why the problem arises, the cause of the problem, and prescribe the right medicine to the case.

3. I still have a lot of shortcomings. Only when I guard against arrogance and rashness can I make progress.

Additional gift

Common ufw Firewall commands for Ubuntu

/ / installation method sudo apt-get install ufw / / enable sudo ufw enablesudo ufw default deny / / turn on the firewall and turn off all external access to the machine at the same time as the system starts. The external access of the machine is normal. / / disable sudo ufw disable// View Firewall status sudo ufw status// Open / disable appropriate port or service example sudo ufw allow 3306 / / allow external access to port 3306 sudo ufw delete allow 3306 prohibit external access to port 3306 sudo ufw allow from 118.89.153.162 / / allow this IP to access all native ports sudo ufw deny smtp / / disable external access to the smtp service sudo ufw delete allow smtp / / delete a Rule sudo ufw deny proto tcp from 10.0.0.0 port 8 to 192.168.0.1 port 22 / to deny all TCP traffic from 10.0.0.0 8 to port 22 of 192.168.0.1 address / / sudo apt-get install ufwsudo ufw enablesudo ufw default deny is recommended

Reference article: https://www.jb51.net/article/138876.htm

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.