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
Ben
  • 3,800
  • 18
  • 65
  • 96
  • Specifics about the distro you are using would be helpful. – Tim Jan 04 '12 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 04 '12 at 21:10

8 Answers8

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.

Erwin Mayer
  • 376
  • 3
  • 6
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.

Paweł Brodacki
  • 6,511
  • 20
  • 23
  • I get `-bash: locate: command not found`... I've tried `find / my.cnf` but it finds nothing. – Ben Jan 04 '12 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 04 '12 at 20:04
  • `locate: command not found` is not an updatedb problem, he doesn't have the package. – Tim Jan 04 '12 at 20:09
  • install "slocate" package. – TheCompWiz Jan 04 '12 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

ckliborn
  • 2,778
  • 4
  • 25
  • 37
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”.

JayRizzo
  • 123
  • 5
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • `-bash: updatedb: command not found` – Ben Jan 04 '12 at 20:22
  • Updated my answer. Please try again !!! – RolandoMySQLDBA Jan 04 '12 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 04 '12 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
Sven
  • 98,649
  • 14
  • 180
  • 226
  • 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 04 '12 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.

Nils
  • 7,695
  • 3
  • 34
  • 73
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?

ckliborn
  • 2,778
  • 4
  • 25
  • 37
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!

J Baron
  • 338
  • 1
  • 7