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!
While the GLOBAL option does still exist, I much prefer the option to simply mark events to be skipped by slaves which are configured to skip events.
Quite useful for archive servers which can then be set to ignore purge events. And as percona pointed out recently, even more beneficial for Galera Cluster:
I have been bitten by this as well, and can’t agree more with reverting the change. Well said.
Thanks Jeremy for bringing this to our attention.
I think reverting will be hard and I kind of like the feature of being able to enable binary logging without restarting MySQL.
There are a lot a ways a SUPER user can shoot himself in the foot and this is one more. Maybe reducing the numbers of those would be suited. With others, I am often discussing about a SUPER-SUPER mode with some commands only runnable in this mode. Maybe SET GLOBAL sql_log_bin should be one of those commands. This mode would work as follow:
-log to mysql,
-doing a SET GLOBAL sql_log_bin would return an error,
-to go in SUPER-SUPER mode, one would need to do SET SUPER-SUPER = 1,
-then SET GLOBAL sql_log_bin would succeed.
I see other use-cases for the SUPER-SUPER mode:
-and probably a lot more…
I am not sure about the need for a super-super mode — then you could argue that a super-super-super mode is also useful for the really truly dangerous stuff… it may never end. :)
I do agree there should be a way to enable/disable binary logging at runtime, my main point is that the current incarnation of SET GLOBAL sql_log_bin doesn’t even really do that. Aside from it being incredibly dangerous it’s quite poor for this purpose due to it being global and session. For binary logging, ideally it would be a global-only feature that disables binary logging or enables it for all threads simultaneously (and safely).
We may have a need for that feature, and may end up developing it in a safe way.
SET GLOBAL sql_log_bin – We need your feedback! | InsideMySQL
Hello, thank you for the attention. I never issued “set sql_log_bin=0″ with GLOBAL but I don’t know how dangerous it is…
Since this is very useful info, I translated the post into Japanese.
If you have problems for the translation, please let me know and I can delete it or do something you want on the translation.
FYI, BUG#67433 has now been closed. SET GLOBAL SQL_LOG_BIN will go away (as in, the statement will fail with an error) in the next 5.5, 5.6, and 5.7 releases. You’ll still be able to *read* the global variable for a while, but this too will go away, in MySQL-$next.
Thanks, Oracle, for fixing the stupid and dangerous SET GLOBAL sql_log_bin! – Jeremy Cole
youdidwhatwithtsql.com » Blog Archive » The behavior of gtid_strict_mode in MariaDB
Keep Sensitive Data Secure in a Replication Setup - Percona Database Performance Blog
Mantenga seguros los datos confidenciales en una configuración de replicación - Es mi PC