Is there a way to test changes to the mysqld configuration files before applying them? I'm thinking about something equivalent to httpd -t
but for mysqld
.
This link //www.percona.com/blog/2011/02/17/how-to-syntax-check-your-my-cnf-file/ suggests:
mysqld --help
error: Found option without preceding group in config file: /etc/my.cnf.d/mariadb-server.cnf at line: 6
...
This indeed works however the return code is unchanged and always 0 therefore not ideal to use in a script.
asked Mar 31, 2020 at 9:16
3
Redirect the stderr output of mysql --help
to stdout and you can grep it.
mysqld --help 2>&1 | grep -ci error
This will result in 0
with no errors and the amount of errors if there are any.
If you want to rely on return codes instead you can use grep -qi
. It is however a little counterintuitive, since grep will return 0
when it did find the string error
, and it will return 1
when it did NOT find the string.
answered Mar 31, 2020 at 9:40
Gerald SchneiderGerald Schneider
19.8k8 gold badges52 silver badges79 bronze badges
You can pass your own code, however one more option hope you are interested to explore
mysqld --validate-config
If no errors are found, the server terminates with an exit code of 0. If an error is found, the server displays a diagnostic message and terminates with an exit code of 1.
shell> mysqld --validate-config --no-such-option
2030-03-31T11:50:12.738919Z 0 [ERROR] [MY-000068] [Server] unknown option '--no-such-option'.
2030-03-31T11:50:12.738962Z 0 [ERROR] [MY-010119] [Server] Aborting
One more example
shell> mysqld --validate-config
2030-03-31T12:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable 'tx_read_only=ON'.
2030-03-31T12:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting
If you want to test my.cnf which is on different path before applying, use like below.
shell> mysqld --defaults-file=./my.cnf-test --validate-config
answered Mar 31, 2020 at 9:23
asktyagiasktyagi
2,4011 gold badge5 silver badges19 bronze badges
3
Skip to end of metadata
- Created by on Jun 11, 2021
Go to start of metadata
settings are managed via a user-editable configuration file such as /etc/mysql/mysql.conf.d/mysqld.cnf
possible places of the cnf file
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- [datadir]/my.cnf
- ~/.my.cnf
for my Ubuntu installation
/etc/mysql/my.cnf | symbolic link to the one below |
/etc/alternatives/my.cnf | symbolic link to the one below |
/etc/mysql/mysql.cnf | this last is the real file |
for my CentOS installation
/etc/my.cnf | this file includes the directory /etc/my.cnf.d/ |
client.cnf | in the included dir |
mysql-server.cnf | in the included dir |
mysql-default-authentication-plugin.cnf | in the included dir |
~/.my.cnf | not present |
to determine the location of the MySQL configuration file:
>mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- No labels
After installing MySQL and in order to provide configuration to it, we usually want to locate the famous my.cnf configuration file; if you are running a GNU/Linux distribution like Ubuntu, then you can use the following command to know where is the absolute path of this file: The output of this command should be something like this: The important path here is /etc/mysql/my.cnf and
if you open this file with an editor like nano: It is normal this file doesn’t have all the configuration, instead if could point to other directories or files, for example this is a configuration file from a fresh Ubuntu install: If you notice there are other directories with configuration files, in particular our extra config is available in: In the /etc/mysql/mysql.conf.d/mysqld.cnf file you will find all the parameters used by
MySQL, for example port, cache limits, logs paths, SSL, etc. So, there you go! Continue customizing your MySQL instance. See you next time. Alex Arriagafind / -name my.cnf
/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/var/lib/dpkg/alternatives/my.cnf
nano /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# 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.
#
# For explanations see
# //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/
!includedir /etc/mysql/mysql.conf.d/
cat /etc/mysql/mysql.conf.d/mysqld.cnf