FreeBSD default MySQL basedir, port, etc., for my.cnf

Question

I'm working on a FreeBSD 9.2 server and have run mysqltuner.pl to check the memory and cache needs of MySQL. Now I need to add a my.cnf file to FreeBSD so it uses that instead of the auto cnf file.

I can copy the my-default.cnf this way:

cp /usr/local/share/mysql/my-default.cnf /usr/local/etc/my.cnf

and then my.cnf will load.

The my-default.cnf file shows these values (which will carry over to my.cnf):

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# *the above innodb line I can set*

# *But where do I get the values below?*

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

Where do I get the values above? I know they must come from the current MySQL server, but how do I find these settings?

Should this question be at https://dba.stackexchange.com/ ?

Answer 1

You can get your current configuration by login to mysql and run query

SHOW VARIABLES;

To search specific configuration, you can use SQL syntax. For example to know current datadir use:

SHOW VARIABLES LIKE '%datadir%';

Reference: Official MySQL documentation

Answer 2

In general you haven't to define those parameters because they are hardly compiled into mysql builded as FreeBSD's port. You have to change parameters responsible for the DB engine tunes.

Here is my config for example

[client]
  port            = *****
[mysqld]
  port            = *****
  skip-external-locking
  key_buffer_size         = 128M
  max_allowed_packet      = 1M
  table_open_cache        = 512
  join_buffer_size        = 2M
  sort_buffer_size        = 2M
  read_buffer_size        = 2M
  read_rnd_buffer_size    = 8M
  myisam_sort_buffer_size = 32M
  query_cache_limit       = 2M
  query_cache_size        = 32M
  thread_cache_size       = 4
  thread_concurrency      = 8
  event-scheduler         = ON
  server-id               = 1
[mysqldump]
  quick
  max_allowed_packet      = 16M
[mysql]
  no-auto-rehash
[myisamchk]
  key_buffer_size         = 128M
  sort_buffer_size        = 128M
  read_buffer             = 2M
  write_buffer            = 2M
[mysqlhotcopy]
  interactive-timeout

All I've change is the tcp-port to avoid stupid h4xor's knocking on the standard 3306

Answer 3

I know this post is few years old and many things changed since FreeBSD 9.2 (actually version 11.1 as per November 2017), you can find the my.cnf file in

/usr/local/etc/mysql