Home / server / Questions / 346647
Accepted
Ben
Asked: 2012-01-05 11:22:25 +0800 CST

MySQL where's the my.cnf path?

  • 772

I've managed to locate my install directory for MySQL: /usr/local/mysql/

Where can I find the path to my.cnf to know where I should configure the server? I've tried creating a /etc/my.cnf(as shown below) and it had no affect

[mysqld]

#charset
collation_server=utf8_general_ci
character_set_server=utf8
default_character_set=utf8
mysql my.cnf

8 Answers

  1. Best Answer

    As per this article:

    Running this command from the command line / terminal will show where MySQL will look for the my.cnf file on Linux/BSD/OS X systems:

    mysql --help | grep "Default options" -A 1

    This will output something like this:

    Default options are read from the following files in the given order: 
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

    You can now check for files using the above output at /etc/my.cnf, then /etc/mysql/my.cnf and so on. If there isn't one at one of those locations, you can create one and know MySQL will use it.

    • 25

  2. On a Linux system 'locate my.cnf" will be the fastest solution.

    If there are several my.cnf files, all looking likely (e.g. in /etc, /etc/mysql, /opt/mysql/etc, etc.), then you can run strace to see where MySQL binary tries to find its configuration file, but I do think that's an overkill.

    • 3

  3. It seems like the file your cnf created is being superseded by another one. Check out hte default order below.

    From

    mysqld --verbose --help

    Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf /etc/my.cnf

    • 2

  4. OK a wild shot in the dark:

    If the database is installed in /usr/local/mysql, then try looking in /etc/local for my.cnf

    Here is how you can tell if you have a my.cnf

    Run this query (<= 5.6.7)

    SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN ('wait_timeout',
'innodb_buffer_pool_size','innodb_log_file_size');

    OR (>= 5.6.8) it moved to the performance schema.

    SELECT * FROM PERFORMANCE_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN ('wait_timeout',
'innodb_buffer_pool_size','innodb_log_file_size');

    SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';

    If you should get:

    You are running with defaults and there is a possibility that there may not be a my.cnf present.

    NOTE:

    As of MySQL 5.7.6, information available from the tables described here is also available from the Performance Schema. The INFORMATION_SCHEMA tables are deprecated in preference to the Performance Schema tables and will be removed in a future MySQL release. For advice on migrating away from the INFORMATION_SCHEMA tables to the Performance Schema tables, see Section 25.20, “Migrating to Performance Schema System and Status Variable Tables”.

    • 2

  5. How did you install MySQL and on what platform?

    Brute force method on an unixoid OS:

    find / -name my.cnf -print
    • 1

  6. strace -fe open /etc/init.d/mysql start 2>&1|grep my.cnf should show you the system call used to open the file.

    • 1

  7. How are you sure that your cnf file is not being read?

    Have you restarted mysqld since the change?

    Have you tried to create a new db and looked at its values?

    • 1

  8. To install locate and updatedb to be able to run it..

    Using fedora, centos run as root:

    yum install mlocate

    if you are running Ubuntu run:

    sudo apt-get install mlocate

    After you finish the install run updatedb Fedora (as root):

    $updatedb

    Ubuntu:

    sudo updatedb

    then you can run the locate command as listed above. Again the my.cnf is typically located in /etc. Are you sure mysql is installed? if not do the commands above to install it and replace mlocate with mysqld best of luck!

    • 0