MySQL – Persistent values in the.cnf

MySQLmysql-5.6percona-server

I have a small doubt regarding making variables persistent in Percona Server 5.6 in Linux.

  1. I need to make log-bin-trust-function-creators persistent so that I need not make it to true everytime on instance restart.

Doubt is that in my.cnf, should I give

log-bin-trust-function-creators=1

or

log_bin_trust_function_creators=1

Currently I have set log_bin_trust_function_creators=1, and it doesn't take in effect after restart.

  1. InnoDB buffer pool cache should be in bytes only or it can be in G (currently I have set 12G and it is not working). Should I change to bytes?

  2. In my.cnf, does the order in which the variables are given matters? or it takes randomly? Say, I can give buffer pool in the last in [mysqld] and InnoDB buffer size as first – does it matter?

Best Answer

1) The format with dashes are for command line and with underscore in my.cnf and as global variable. Any of the following has to work (tested on Percona Server 5.6.16):

log-bin-trust-function-creators=1
log-bin-trust-function-creators
log_bin_trust_function_creators=1
log_bin_trust_function_creators

(since it's a boolean you don't need to specify =1)

2) You can use G, M, k just fine. MySQL will pick that up. If you have a wrong config it will complain won't just ignore it silently.

3) Variable order doesn't matter with the obvious exception if you specify something twice under the same section. In that case the one coming later will override the previous value:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_size = 2G

With this the buffer pool size will be 2G.

From what you described it seems your server is not picking up your my.cnf at all. Check if you have the files at the right place:

[root@server ~]# $(which mysqld) --verbose --help | grep -A 1 "Default options"

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

Or another alternative:

[root@server ~]# timeout 3 sudo -u mysql strace $(which mysqld) 2>&1 | grep stat|grep cnf
stat("/etc/my.cnf", 0x7fff9e681280)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=5454, ...}) = 0
stat("/usr/etc/my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.mylogin.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
Related Question