How to Find Existing MySQL Settings and Override Them

Based on the output of the mysql --help | grep "Default options" command, the order in which MySQL reads configuration files is:

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. ~/.my.cnf (the current user’s home directory configuration file)

The configuration settings read later in this sequence will take precedence over those read earlier. This means that if you want to ensure your custom settings are not overridden by other configuration files, you should place them in a file that is read last. Therefore, you should put your settings in ~/.my.cnf.

Steps to Ensure Your Settings Are Not Overridden

  1. Edit/Create ~/.my.cnf

Open (or create) the ~/.my.cnf file:

nano ~/.my.cnf
  1. Add Your Custom Settings

Here you can add your MySQL configuration settings. For example:

[client]
user = yourusername
password = yourpassword

[mysqld]
max_connections = 500
bind-address = 127.0.0.1
  1. Ensure Proper Permissions

Make sure that the file has appropriate permissions to prevent unauthorized access:

chmod 600 ~/.my.cnf

Explanation of Sections

  • [client]: Settings under this section apply to MySQL client programs, such as mysql command-line client.
  • [mysqld]: Settings under this section apply to the MySQL server daemon.

Example Content for ~/.my.cnf

Here is an example of what a ~/.my.cnf file might look like with typical settings:

[client]
user = dbuser
password = dbpassword

[mysqld]
max_connections = 500
bind-address = 127.0.0.1
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Validating Changes

After making changes to the ~/.my.cnf, you may want to restart the MySQL service to ensure the settings are applied:

sudo systemctl restart mysql

Double-Check Active Settings

To confirm the settings were applied as expected, you can log into MySQL and check the effective settings:

mysql -u yourusername -p

Then, run the following command inside the MySQL shell:

SHOW VARIABLES LIKE 'max_connections';

This will show the current value of max_connections as recognized by MySQL after restart.

Notes

  • If ~/.my.cnf does not exist by default, you’ll need to create it.
  • Be cautious with sensitive information like passwords; ensure permissions are restricted.
  • If multiple users need these settings, you may also distribute an appropriate .my.cnf in their home directories or configure /etc/my.cnf and /etc/mysql/my.cnf accordingly if system-wide settings are more appropriate.

By placing your settings in the ~/.my.cnf file, you ensure that they are read last and thus not overridden by other configuration files listed earlier in the sequence.