Pitfalls and solutions for upgrading MySQL 5.7.23 in CentOS 7

Preface

Recently, I found a pitfall in upgrading MySQL 5.7.23 under CentOS 7. Take a cluster upgraded to MySQL 5.7.23 as an example.

In our environment, the parameter open_files_limit for the number of open file descriptors is uniformly configured to 65535 in MySQL 5.6.21, while the default value of the open_files_limit parameter installed in MySQL 5.7.23 on CentOS 7 is 5000

Otherwise, in a cluster with a large number of partition tables, if the number of open files is too large, the database will report an error.

Here are the reasons:

1. CentOS 7 installs MySQL 5.7.23, and the service management has changed from sysvinit (service mysql start) to systemd (systemctl start mysqld.service)

2. The configuration of Limit under CentOS 7 is determined by the following configuration file

Global settings: /etc/systemd/system.conf (such as DefaultLimitNOFILE=65535)

Service settings: [Service] The default value is 5000, and LimitNOFILE should be changed to 65535

【test】

1. Global configuration file /etc/systemd/system.conf

2. Service configuration file /usr/lib/systemd/system/mysqld.service

3. MySQL configuration file /etc/my.cnf

【in conclusion】

1. When managing MySQL service with systemd, when setting the open_files_limit parameter in the following three configuration files at the same time, the service configuration file will prevail in the end

2. When the service-related configuration value is commented out, the global configuration file will prevail.

【Solution】

1. Single unit repair method, follow the steps below

#Modify configuration vim /usr/lib/systemd/system/mysqld.service

#Reload systemctl daemon-reload

#Restart the service to take effect systemctl restart [email protected]

2. Installation package repair method: modify the following two source files, increase 5000 to 65535, repackage and release, so that the new version will keep the same configuration as MySQL5.6.21 by default

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.