Solution to the problem that a Linux modification of MySQL configuration does not take effect

background

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

process

The most popular answer on Stackoverflow is very simple and can be modified in just three steps

/etc/my.cnf // Find the configuration file port = 3306 // Modify the content sudo service mysql restart // Restart MySQL

But it's not that simple in real time. The above answer may work for the old version, but in the new version you can't find anything under /etc/my.cnf at all. The file does not exist.

Then I went to check the official documentation and found the configuration file in the directory: /etc/mysql/my.cnf. But don’t think that everything is fine after finding the configuration file. When you open the file, you will see that the style has changed, because there is no content in the configuration file, but it references two other configuration folders. The specific content of /etc/mysql/my.cnf is as follows

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

OK, at least we have a clue. We follow the path and look at each file in the two directories. Finally, we see that mysql.conf.d/mysqld.cnf seems to be the file we are looking for (the official documentation also verifies this). When we open it, we can see the file properties we need to modify.

[mysqld]
prot = 3306
bind-address = 127.0.0.1 // Only allow local access

After changing the configuration parameters to what I needed, I thought the matter was over. When I restarted the service using sudo service mysql restart, I found that my intranet machine was still inaccessible. I used netstat -ntlp to check the Local Address and Foregin Address, and found that the configuration I modified did not take effect. I fell into deep self-doubt, as if the clue was interrupted here.

Then, some netizens mentioned that it might be a file permission problem. If the file permission is too large (globally writable), MySQL will not read the configuration file for security reasons, but read its own configuration copy file. I executed the command and saw the following warning

mysql --help | grep my.cnf
mysql: [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 because of the risk of being globally writable, and lists the order in which MySQL reads the configuration file. Here we can see that MySQL has multiple my.cnf configuration files, some of which are global configurations and some are local configurations. After finding the clues, the handling is much simpler. We change the file permissions and look at mysql --help again to find that the warning is gone. The specific commands are as follows:

sudo chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf
mysql --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 check that Local Address and Foreign Address have become the contents configured in my.cnf, but the modified configuration has been successfully implemented, confirming that the security issue of the file being globally readable is the cause.

Summarize

Finally, the MySQL security rules caused the configuration modification to fail to take effect. Of course, in most cases, we don’t know this rule (if we haven’t read the official website documentation in full). After this problem, I have the following summary to help you avoid detours in troubleshooting and find the real problem as soon as possible:

1. If you encounter a problem, you should first look at the latest documentation on the official website
2. Check the information time. A lot of information on the Internet is too long and may mislead you.
3. Carefully read warning messages and master troubleshooting tools and commands
4. Basic knowledge is very important. The deeper the problem, the more it tests the programmer's inner strength.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.