Use MySQL to open/modify port 3306 and open access permissions in Ubuntu/Linux environment

Operating system: Ubuntu 17.04 64-bit

MySQL version: MySQL 5.7

1. Check whether port 3306 is open

netstat -an | grep 3306

If you see something like the following, the port is not open:

2. Modify access permissions

Enter the directory "etc/mysql/mysql.conf.d/", as shown below:

In this directory, there is a configuration file "mysqld.cnf", as shown below:

Open this configuration file:

sudo vim mysqld.cnf

There is a long comment after opening the file. Don't worry about it. Just look at the part in the picture below:

Note the red comment in the first line of the image above:

"By default we only accept connections from localhost", these sentences mean "by default we only allow local services to access MySQL", so we need to comment out the following configuration, just add a pound sign in front of it:

# bind-address = 127.0.0.1

As shown in the figure below, this configuration has also become a comment:

To expand our thinking, if we want to restrict access to MySQL to only a certain application server for security reasons, then we actually just need to adjust this configuration item.

3. Modify the port number

Still in this configuration file, see the configuration items in the middle part of this configuration file:

We need to add a port configuration:

port = 3306

After adding, the entire configuration file looks like this:

Remember to save the file after modifying it.

4. Open access rights to the root account

In the third step, we only removed the local access restrictions, but we still did not set the account permissions.

Restart the MySQL service and enter the MySQL console:

service mysql stop
service mysql start

mysql -h 127.0.0.1 -u root -p 

Switch to the system database "mysql":

use mysql; 

Take a look at all the tables in the database:

show tables; 

We need to modify the last table "user" in the figure above and see what fields this table has:

desc user; 

There are so many fields that I won’t list them all. We are only going to use the "Host" and "User" fields:

select host,user from user; 

In this table, we see that the root user can only access the MySQL service locally, so we need to change it to "%", which means that the root account can access the database service no matter where it is:

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

Note that this modification is not recommended in a real production environment because the security risk is too great. I suggest changing the host item of the root user to a specified IP address, or keep localhost.

The last setting opens all permissions for the root account:

grant all privileges on *.* to 'root'@'%' identified by 'your root account password';

Make various permission settings take effect immediately:

flush privileges;​

5. Confirm the status of port 3306 again

netstat -an | grep 3306

If you see the following picture, it’s OK:

This is the end of this article about using MySQL to open/modify port 3306 and open access permissions in Ubuntu/Linux environment. For more information about opening MySQL 3306 and opening access permissions under Linux, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!