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

This article mainly explains "how to solve the problem that Linux modifying MySQL configuration does not take effect". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the problem that Linux modifying MySQL configuration does not take effect".

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.cnf order 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.

Check the information time, a lot of information on the Internet for too long, may mislead you

Check warning messages carefully and master troubleshooting tools and commands

Basic knowledge is very important, and the deeper the problem is, the more it tests the programmer's internal skills.

Thank you for your reading, the above is the content of "how to solve the problem that Linux modified MySQL configuration does not take effect". After the study of this article, I believe you have a deeper understanding of how to solve the problem that Linux modified MySQL configuration does not take effect, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.