12

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
2
  • Specifics about the distro you are using would be helpful.
    – Tim
    Jan 4, 2012 at 20:10
  • Pretty sure debian/*buntu/rhel/cent wouldn't be using /usr/local from a package install. That sounds more like slack or freebsd.
    – Tim
    Jan 4, 2012 at 21:10

8 Answers 8

25

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.

3

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.

4
  • I get -bash: locate: command not found... I've tried find / my.cnf but it finds nothing.
    – Ben
    Jan 4, 2012 at 20:03
  • This assumes that your locatedb has been updated since the my.cnf file was create. Usually this is done daily via cron. If you need to update it manually run "updatedb" as root
    – ckliborn
    Jan 4, 2012 at 20:04
  • locate: command not found is not an updatedb problem, he doesn't have the package.
    – Tim
    Jan 4, 2012 at 20:09
  • install "slocate" package.
    – TheCompWiz
    Jan 4, 2012 at 20:13
2

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

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”.

3
  • -bash: updatedb: command not found
    – Ben
    Jan 4, 2012 at 20:22
  • Updated my answer. Please try again !!! Jan 4, 2012 at 20:25
  • /usr/bin/which: no updatedb in (/sbin:/usr/sbin:/usr/local/sbin:/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/gnome/bin:/opt/kde3/bin:/usr/lib/java/bin:/usr/local/apache2/bin:/usr/local/firefox:/usr/local/mysql/bin:/usr/games:/usr/local/fetter/bin)
    – Ben
    Jan 4, 2012 at 20:37
1

How did you install MySQL and on what platform?

Brute force method on an unixoid OS:

find / -name my.cnf -print
1
  • I didn't install it, it's on a machine that's a few years old. It's on Linux mars 2.6.5-7.147-bigsmp #1 SMP Thu Jan 27 09:19:29 UTC 2005 i686 i686 i386 GNU/Linux
    – Ben
    Jan 4, 2012 at 19:31
1

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

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?

0

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!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .