It’s been almost 4.5 years since, during some code refactoring, it was decided (or accidentally changed?) that sql_log_bin should become a GLOBAL variable as well as a SESSION one. Almost 2 years ago, during MySQL 5.5 upgrades at Twitter, I filed MySQL Bug 67433 describing in detail how stupid and dangerous this change was, and asking for a reversal.
Nothing has been changed or reversed, so SET GLOBAL sql_log_bin continues to be allowed. I continue to directly see, and hear about damage caused by incorrect use of SET GLOBAL sql_log_bin. So, let me describe just how stupid and dangerous it really is.
What really happens when you run SET GLOBAL sql_log_bin?
If you have a master-slave replication configuration, your master will have binary logging enabled (log_bin) and be recording each transaction to its local binary logs, which the slaves read (more or less in real time) in order to replicate. This log must be complete for replication to be useful and trustworthy. If it is incomplete, serious data loss may occur.
It’s been possible for a long time to use SET [SESSION] sql_log_bin=0 within a connection (with a SUPER user) to temporarily disable binary logging of a session. This can be quite useful to be able to run e.g. ALTER TABLE commands without having them immediately replicated to the slaves.
Most people would guess that running the very similar SET GLOBAL sql_log_bin=0 would disable binary logging for all sessions (you know, GLOBAL?) immediately. However, that’s not quite the case.
As background, there are essentially three different scopes of variables in MySQL server:
- Global-only: The variable only exists, and only makes sense, in a global scope. An example is innodb_buffer_pool_size — there is only one buffer pool, so it makes no sense in any session context. These variables may also be read-only, so that they can’t be changed at runtime.
- Global and session: The global variable defines the session default, and is copied to the session variable on connect. A user may change the variable within their session if they want, and there may be restrictions on doing so. If the global variable is changed, it will only affect new sessions; existing sessions will keep the variable they copied at session start. A typical example of this is max_allowed_packet.
- Session-only: There is no global variable associated with the session variable. A user may change the variable only within their session.
The sql_log_bin variable was previously Session-only and with the commit mentioned above, it was made Global and session.
There are a few implications of this:
- When a session is initiated, it copies its sql_log_bin value from the global scope to its session scope copy.
- If the DBA issues SET GLOBAL sql_log_bin=0, it only affects new sessions, any already-connected sessions will continue logging. This makes it pretty useless for managing a failover.
- Changes to the global variable using SET GLOBAL do not even affect the currently connected session; issuing SET GLOBAL sql_log_bin=0 within a session does not prevent the following commands in that session from being binary logged. This is actually often the first sign that something didn’t work as the DBA expected—they suddenly find all slaves to be executing something they thought they had prevented from being replicated.
- If a connection is made while sql_log_bin=0 and the DBA then issues SET GLOBAL sql_log_bin=1 the sessions which started while it was 0 will continue to not log anything to the binary log for as long as they are allowed to stay connected. This also makes it pretty useless for managing a failover.
- If the DBA accidentally issues SET GLOBAL sql_log_bin=0 briefly, even for milliseconds, the entire master-slave replication topology may be irreparably damaged and if this is done without immediately noticing it, permanent and severe data loss may occur.
Is anyone putting it to good use?
The only comment that MySQL Bug 67433 has received from Oracle was from Sveta Smirnova, saying:
Regarding to “revert” I don’t agree: with global access it is possible to make a server master online, without bringing it down.
If you Google search for “set global sql_log_bin” you can find plenty of examples of people talking about it in various languages. I couldn’t find a single example of a script or person using or describing its use correctly, and I looked through pages and pages of examples.
So here’s a Hall of Shame instead:
- Akshay Suryawanshi: Beware !!! Danger ahead “sql_log_bin”… describes a Percona customer accidentally issuing SET GLOBAL sql_log_bin instead of SESSION: “We had no choice but to refresh the slaves, but the data was huge 1.2T, after 3 days of boredom, we finally had the slaves in sync.”
- 这几年犯的错 (“recent mistakes”) describes someone who accidentally used SET GLOBAL sql_log_bin in a production environment and had to spend a lot of time repairing the damage.
- Gavin Towey: How can MySQL Replication Break? complains “It should be hard to disable the binlog on a master that is currently replicating — but it’s not: SET GLOBAL SQL_LOG_BIN=0; Ooops. None of the servers will complain.”
- Easiest way to sync a dev MySQL db to a production MySQL db? someone describes a sketchy way of managing a desynced prod-staging environment.
- MySQL Replication FAQ shows someone’s DBA notes, using it incorrectly.
- 总结：MySQL备份与恢复的三种方法 describes exactly the wrong and extremely dangerous procedure for executing a mysqldump on only the master.
- 标题：MySQL 参数浅析之 sql_log_bin describes how to properly use sql_log_bin, with a stern warning about the GLOBAL version of it being dangerous.
- ZabbixのDB(MySQL)をパーティショニングする provides a Zabbix monitoring script, which if used with the provided sample SET GLOBAL sql_log_bin commands will completely destroy your production environment.
- 标题: MySQL备份与还原 shows someone else providing bad advice on a forum.
- リストアの高速化 shows yet another person providing bad advice for how to make restores go faster.
Also notably, the MySQL manual text for sql_log_bin also still does not adequately describe or warn about its behavior.
Your move, Oracle
Do the right thing, and revert this change. We even provided a patch.
Update: Morgan Tocker, MySQL Community Manager at Oracle is requesting feedback on the future of SET GLOBAL sql_log_bin. Go and make your opinion known!