When I run the command 'SHOW VARIABLES', it lists all the require variables, but my.cnf is empty. So should I put those variables in my.cnf?
My server is Amazon EC2 Debian 10.
Here is some of the 'SHOW VARIBLE' output:
admin_port 33062
admin_tls_version TLSv1.2,TLSv1.3
caching_sha2_password_private_key_path private_key.pem
hostname myweb.com
lc_messages_dir /usr/share/mysql-8.0/
mysqlx_socket /var/run/mysqld/mysqlx.sock
sha256_password_private_key_path private_key.pem
sha256_password_public_key_path public_key.pem
socket /var/run/mysqld/mysqld.sock
ssl_ca ca.pem
ssl_cert server-cert.pem
ssl_key server-key.pem
Here is the mv.cfg:
# Copyright (c) 2015, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
The short answer is no, it's not necessary to specify values for every variable, and it can be a bad idea.
Every variable has a default value that is compiled into the MySQL Server product. Each variable uses its default value if you don't change it by writing a line in your my.cnf.
If you were to write lines in your my.cnf for every variable, you would probably choose to set them to their default values anyway, if you have no specific reason to change it.
Having such a long my.cnf would obscure the cases where you had set a variable to a non-default value for some important reason. A new engineer inspecting your configuration would have to hunt for which ones you had changed to a non-default value. You could use comments to identify them, but most experienced engineers know that comments are not reliable, so they'd have to double-check them all.
From time to time, the default values change as new releases of MySQL Server come out. Sometimes through testing or experience, the engineers at MySQL decide a different default is more appropriate. Or code in MySQL Server changes, and the default is changed to suit the new code.
If you were to specify a value for every variable in your my.cnf, you would prevent yourself from using the default value. Your assigned value for a given variable would take priority, even if the default would be a better choice for the new software.
You could update your my.cnf every time you upgrade to a new version of MySQL Server, and make sure they are set to the current default values, just in case the defaults change in a given new version. But there are 628 variables (as of MySQL 8.0.29)! I wouldn't want to have to check so many, especially given that 98% of them are at their defaults anyway.
When I write my.cnf files, I prefer to keep the file as short as possible. Specify values only for variables I need to change, and write comments explaining the reason for using a value that is not the default. If I have no specific reason to change a given variable, leave it out of the my.cnf file, so it uses its default value.