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

Background

I have a project service that uses AWS EC2. Recently, in terms of security and performance, I plan to migrate Tencent Cloud's MySQL database to AWS RDS. Because of the export rules and security groups of AWS, I need to modify the default port 3306 and Bind Address to restrict specific IP access. I queried how to modify it on Stackoverflow, but most of the information on the Internet is old. Does not conform to the current mainstream MySQL version (using MySQL 5.7.27, the operating system uses Ubuntu 18.04.1 LTS)

Process

The answer to the high vote on Stackoverflow is very simple, and the modification can be completed in three steps.

/ etc/my.cnf / / find the configuration file port = 3306 / / modify the content sudo service mysql restart / / restart MySQL

But real-time is not that simple, the above answer may work for the old version, but in the new version you can't find anything under / etc/my.cnf, and the files don't exist.

Then I checked the official document and found the configuration file in the directory: / etc/mysql/my.cnf, but don't think that everything will be all right if you find the configuration file. When you open the file, you will see that the painting style has changed, because there is no content in the configuration file, but the other two configuration folders are referenced. The specific content of / etc/mysql/my.cnf is like this.

! includedir / etc/mysql/conf.d/!includedir / etc/mysql/mysql.conf.d/

Yes, at least if we have a clue, follow the path and look at each file in the two directories. Finally, we see that mysql.conf.d/mysqld.cnf is the file we are looking for (the official document also verifies this point). Open it and you can see the file attributes we need to modify.

[mysqld] prot = 3306bind-address = 127.0.0.1 / / only local access is allowed

After changing it to the configuration parameters I needed, I thought it was over. When I used sudo service mysql restart to restart the service, I found that my intranet machine still could not access. I used netstat-ntlp to check Local Address,Foregin Address and found that my modified configuration did not take effect. I was caught in deep self-doubt, as if the clue was interrupted here.

Then, some netizens said that it may be a file permission problem, if the file permission is too large (globally writable), MySQL will have security considerations will not read the configuration file, and read its own configuration copy file, I execute the command, such as see the following warning

Mysql-- help | grep my.cnfmysql: [Warning] World-writable config file'/ etc/mysql/mysql.conf.d/mysqld.cnf' is ignored.order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf / etc/mysql/my.cnf ~ / .my.cnf

The above means that the file is ignored by MySQL due to the risk of global writeability, and the order in which MySQL reads configuration files is listed. Here, you can see that there are multiple my.cnf configuration files in MySQL, some are global configuration and some are local configuration. After finding the clue, it is much easier to deal with. We change the file permissions and take a look at mysql-help to find that the warning is gone. The specific commands are as follows:

Sudo chmod 644 / etc/mysql/mysql.conf.d/mysqld.cnfmysql-- help | grep my.cnforder of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf / etc/mysql/my.cnf ~ / .my.cnf

Then execute sudo service mysql restart, and then see that Local Address and Foreign Address have become the contents of our configuration in my.cnf, but the modified configuration has successfully taken effect. Confirm that it is caused by a security problem that can be read globally in the file.

Summary

Finally, the security rules on MySQL cause the modification of the configuration not to take effect. Of course, in most cases, we do not know this rule (if we do not read the full documentation on the official website). After this question, I probably have the following summary to help you avoid detours in troubleshooting and find the real problem as soon as possible:

When you encounter problems, you should first look at the latest documents on the official website to check the information. Many materials on the Internet take too long, which may mislead you to check warning messages carefully. It is very important to master the basic knowledge of troubleshooting tools and commands. The deeper the problem, the more you test the programmer's internal skills.

Well, the above is the whole content of this article. I hope the content of this article has a certain reference and learning value for your study or work. 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.