Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

1 Answer

up vote 1 down vote

innodb_buffer_pool_size

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).

log-bin

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:

SESSION

If you run 

SET SQL_LOG_BIN = 0;

this will disable your DB Session from recording binlog events.

If you run 

SET GLOBAL SQL_LOG_BIN = 0;

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.

share|improve this answer
Where does the (sql_log_bin)session binary logs will store. – usernaveen Apr 23 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. – RolandoMySQLDBA Apr 23 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 – usernaveen Apr 23 at 8:03
There is no such thing as session binary logs. There are only global binary logs. – RolandoMySQLDBA Apr 23 at 11:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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