3

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?

0

1 Answer 1

1

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)
2
  • Just for clarification. Q2 I referred this link which says -- percona.com/blog/2014/01/28/… it should be in bytes? My configuration file I checked with mysqld --verbose --help | grep -A 1 "Default options" and it same output which you have pasted but still it did not take up the values of buffer pool size.. error log did point out one thing that I gave a wrong value of innodb flush method, could it be because of that value, mysql did not take up other values properly?
    – tesla747
    Jul 6, 2016 at 9:08
  • Yes it says 'innodb_buffer_pool_size should be set in bytes' but that only specifies which dimension it expects its value to be. Is your file that contains the values in the list of what is listed by mysql? aka where is your my.cnf? No, it shouldn't work like that. If one value is not good shouldn't fail others. Either MySQL crash on it or ignore that one. Jul 6, 2016 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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