How to make MySQL my.cnf changes without failure?
Thanks for reading!
I am ultimately trying to reduce my MySQL memory usage in an attempt to avoid periodic "Error Establishing Connection to Database" error on a LAMP / Wordpress install.
I am trying to follow the Linode guide here: https://www.linode.com/docs/products/compute/compute-instances/guides/troubleshooting-memory-issues/
However, when I change ANYTHING in /etc/mysql/my.cnf, it won't restart.
For example, if I simply add the first line (key_buffer = 16K) to
my.cnf, so the entire file is:
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
key_buffer = 16K
…then I get the following error and MySQL won't restart.
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
Running systemctl status mysql.service produces the following:
Nov 15 13:28:58 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Nov 15 13:28:58 systemd[1]: Stopped MySQL Community Server.
Nov 15 13:28:58 systemd[1]: mysql.service: Start request repeated too quickly.
Nov 15 13:28:58 systemd[1]: mysql.service: Failed with result 'exit-code'.
Nov 15 13:28:58 systemd[1]: Failed to start MySQL Community Server.
journalctl -xeu mysql.service command doesn't produce anything more meaningful:
Nov 15 13:28:58 systemd[1]: Failed to start MySQL Community Server.
░░ Subject: A start job for unit mysql.service has failed
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A start job for unit mysql.service has finished with a failure.
░░
░░ The job identifier is 40231 and the job result is failed.
Help, please! If I put any setting at all in my.cnf, MySQL won't restart.
2 Replies
After comparing to my other Linode, running mariadb, it had "[client-server]" in its my.cnf file prior to the config lines.
When I added [client-server] or [mysql] to the my.cnf file on my problematic linode, MySQL was then able to restart without errors.
Should either [client-server] or [mysql] be in the my.cnf file? If so, which one, please?
Much appreciated!!
The my.cnf file (working) from the mariadb server:
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
#https://www.linode.com/docs/products/compute/compute-instances/guides/troubleshooting-memory-issues/
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K
CassandraD
Linode Staff
The way that MariaDB is configured is a bit different when using the Wordpress Marketplace app, at least based on what I'm seeing in my own files. My server is over a year old, and we've updated the app since then, but I think this should help.
What you're seeing is showing what directories that file will look in for configurations, which can be different for different users. I found my configuration file here, and it's likely where you can find yours if you haven't made changes before:
/etc/mysql/mariadb.conf.d/50-server.cnf
There I found the configurations mentioned in the guide you shared. You wouldn't want to delete anything in the file. You'd just want to update those values and remove the # symbol in front of any that have it. Otherwise, those lines won't be read.
However, before doing that I wanted to point out that this is a short term solution for testing and not something you want to change long term. For that reason, I recommend first copying your configuration file as it is to another file and returning the values to the default when you're done. This is mentioned in that guide:
Warning
The settings in this section are designed to help you temporarily test and troubleshoot MySQL. We recommend that you do not permanently use these settings.
If you're looking to make permanent changes that will better utilize memory, I'd instead recommend looking at these resources:
I did a quick search search to make sure this works with MariaDB and I think this third party blog post could be helpful.
Also, you mentioned using a LAMP stack, and another option might be to try to migrate your site to a LEMP stack. NGINX is designed to use fewer resources than Apache and you can choose that option when deploying from the Wordpress Marketplace App.