There are almost more or less 277 mysql server system variables . In these variables some of the variables are dynamic and few are not.

I don't under stand what is the reason behind read only variables . why few of mysql system variables are read only. what will happen if they make those variable dynamic.

Is there any deep reason for the read only variables?

For now we can consider :

  • innodb_buffer_pool_size
  • innodb_checksums
  • datetime_format
  • log-bin

and there are many other variable we can find at http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Of course, I know we can't change variable like version. But many other variables like log-bin are not dynamic. For me, it does not make sense if general_log and log_slow_queries are dynamic. Why is log-bin not dynamic? like wise there are many other variables.

1 Answer 1



The InnoDB Storage Engine has too many moving parts to just allow the buffer pool to be resized on the fly because the buffer pool interacts with system tablespace (ibdata1) and the transactions logs (ib_logfile0, ib_logfile1).


In spite of the MySQL Documentation, you can change the effect of log-bin using another variable called SQL_LOG_BIN. Look at the effects:


If you run


this will disable your DB Session from recording binlog events.

If you run


this will disable all incoming DB Sessions from recording binlog events.

General Log / Slow Log

As a DBA, I am very glad that the general log and slow log are dynamic. Otherwise

  • how could do perform any log rotation ?
  • how to stop these logs before diskspace runs out ?

Here are some posts I made about doing log rotations

Datetime Format

mysqld is usually cognizant of timezone and internalization of datetime displays. I do not see a beneficial reason for shifting the datetime format on demand. This may actual cause problems, not so much with mysqld, but with PHP/Python/Ruby scripts that read dates from the MySQL instance and may misinterpret '04/12/2013' as April 12th when it should be Dec 4th. That would be developer's nightmare to deal with. Having datetime format a read-only variable simply provides a safety net against such a death-defying move whereas proper planning of the database and code interoperability with dates would be in order.

  • Where does the (sql_log_bin)session binary logs will store.
    – neotam
    Commented Apr 23, 2013 at 5:17
  • There is only one set of binlogs for mysqld. All DB connections write to the same set of binlogs. Setting SQL_LOG_BIN signals mysqld that my session will not record binlog events. Setting GLOBAL SQL_LOG_BIN signals mysqld to reject all binlog recording. When you run SHOW GLOBAL VARIABLES LIKE 'log_bin';, you will see either on or off, 1 or 0. Commented Apr 23, 2013 at 6:02
  • You mean all session(sql_log_bin) binary logs and global(bin_log) binary logs will store in only single binary log file ? I understand sql_log_bin is a session variable and bin_log is a global variable. To enable the session binary logs we will use sql_log_bin , my question is how to find that binary log file where session binary logs does store
    – neotam
    Commented Apr 23, 2013 at 8:03
  • There is no such thing as session binary logs. There are only global binary logs. Commented Apr 23, 2013 at 11:00

Your Answer

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

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