2

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/ ?

4

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

  • Thanks; SHOW VARIABLES; dumped everything I needed. – markratledge May 22 '14 at 1:08
0

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

  • The port shouldn't matter for random scans that, you should be firewalling it if your server is directly on the internet... – Gert van den Berg Jan 7 '16 at 7:09
0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.