Using Putty command line to open up remote MySQL access

Using Putty command line to open up remote MySQL access

In Linux, after MySQL is installed by default, it cannot be remotely accessed. It can only be accessed locally, that is, it can only initiate database connections from 127.0.0.1. To solve this problem, let's change our thinking, let the Windows operating system look like a local Access, so that we can use a graphical interface such as SQLYog to manipulate the database, and then open the remote Access permission.

First, Putty is used to connect to the server from a Windows working machine. Putty has the port forwarding function (Tunnel) and can send packets to the local port 3306, forward to port 3306 of the remote server localhost Nic. All you need is to add the following command line-L 127.0.0.1: 3306: 127.0.0.1: 3306, note that L of-L must be capitalized. The complete Putty command line is as follows:
Putty-2-4-P 22-pw ××××× root@172.18.94.14-L 127.0.0.1: 3306: 127.0.0.1: 3306
In the above command line-2-4-P 22 this heap parameter does not say,-pw × root@172.18.94.14 this heap parameter is plaintext to specify the login that machine, users and passwords that need to be kept confidential can be deleted as appropriate. It is easy for me to write this code.
After using Putty to log on to the server, the first thing is to check whether the port we are using is blocked by the firewall. MySQL uses port 3306 by default. For example, I am logging on to CentOS, use chkconfig iptables off to disable the firewall. If not, simply use service iptables stop to disable the iptables service. For non-RH Linux servers, you can use the/etc/init. d/iptables script to disable or enable the Linux Firewall Service.
At this point, we can connect the local 3306 to the remote 3306. Accessing the local 3306 is equivalent to accessing port 3306 through 127.0.0.1 on the remote server. Now we use a graphical interface similar to SQLYog to open port 3306 of 127.0.0.1. If it is an initial installation, it will be accessible without a password. If it has been added by someone, add the ignore Password Logon option to the MySQL configuration file (which will be detailed later ).

● Now we have finally cheated the server to access the database at a disguised cost.
After entering the database, you need to open the remote access permission, which is nothing more than that. Find mysql in the mysql database. in the user table, change the random root user prefix access range from localhost or 127.0.0.1 to %, and then specify the password, please use the function string PASSWORD ("××××××") to forcibly assign values to the column of the PASSWORD. Of course, don't forget to flush privileges to use it.
-- If you do not want to use a PASSWORD, you can replace PASSWORD ("specify new PASSWORD") with the null parameter;
 
● If you forget the password, you can log on to the configuration file (such as/etc/my. cnf) in the [mysqld] section, add this option skip-grant-tables and restart the database process.
Then you can directly access the database without a password, and then execute a series of actions such as modifying the new password (or re-authorization. Of course, after modification, it is best to add # in front to shield the option that ignores the password (restart), otherwise it will not be safe enough.

● Putty is used to forward the packets sent to the remote server port to the local Nic and port through a tunnel. The above process is exactly the opposite, the complete command line is as follows:
Putty-pw ××××× root@172.18.94.14-R 0.0.0.0: 1237: 0.0.0.0: 2234

● If you must use SQL commands to complete authorization, refer to the following statements:
① Change password:
Update user set password = PASSWORD ("secret") where user = 'root ';
Flush privileges;

② Here is the authorization and new password:
Grant all privileges on *. * TO 'user '@' % 'identified by 'new password' with grant option;
Flush privileges;

③ You can also authorize only (without resetting the password)
Grant all privileges on *. * TO 'root' @ '%' with grant option;
Flush privileges;

④ Use existing root users to create other users (with the same permissions)
Insert into user select "%", "other users", PASSWORD ("specify new PASSWORD"), Select_priv, primary, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, file_priv, Grant_priv, primary, Index_priv, Alter_priv, primary, Super_priv, primary, primary, Execute_priv, primary, Create_user_priv, Event_priv, primary, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections from user as u where u. user = "root" and host = "127.0.0.1 ";
Flush privileges;
-- If you do not want to use a PASSWORD, you can replace PASSWORD ("specify new PASSWORD") with the null parameter;
If it is used in earlier versions of MySQL, some fields will prompt that they do not exist. You only need to delete the query of this field in the statement.