How to turn off the binary log for mysqld_multi instances?

Q:

MySQL supports running multiple mysqld on the same server. One of the ways is to use mysqld_multi.

If the default MySQL server instance (as configured in the [mysqld] section in my.cnf) uses log-bin, it enables the binary log for all the other instances ([mysqld1], [mysqld2], etc). How can we override the setting for the other instances? We tried putting log-bin= or log-bin=OFF under[mysqld1], but that won‘t disable the binary log.

A:

You may have to resort to using this:

SET SQL_LOG_BIN=0;

/*
SET sql_log_bin = {0|1}
The sql_log_bin variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this variable.

Beginning with MySQL 5.5.5, it is no longer possible to set @@session.sql_log_bin within a transaction or subquery. (Bug #53437)
*/

Run this each time you connect to mysql, and all queries you run during this session will not be recorded in the binary logs.

You could run the following command once in any instance of mysql in mysqld1 and disable binary logging for all connections thereafter until the next time mysqld1 is restarted:

SET SQL_LOG_BIN=0;
SET GLOBAL SQL_LOG_BIN=0;

This DOES NOT disable binary loggging. It just bypasses recording DDL, INSERTs, UPDATEs, and DELETEs. At least, you get to perform queries faster because of not having to record them.

I actually tried it out

lwdba@localhost (DB information_schema) :: show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 720 |
| mysql-bin.000002 | 4279 |
| mysql-bin.000003 | 128935976 |
+------------------+-----------+
3 rows in set (0.00 sec)

lwdba@localhost (DB information_schema) :: create database test8; show binary logs;
Query OK, 1 row affected (0.00 sec)

+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 720 |
| mysql-bin.000002 | 4279 |
| mysql-bin.000003 | 128936061 |
+------------------+-----------+
3 rows in set (0.00 sec)

lwdba@localhost (DB information_schema) :: set sql_log_bin=0; create table test8.dummy (a int) engine=MyISAM; insert into test8.dummy values (1),(2),(3); show binary logs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 720 |
| mysql-bin.000002 | 4279 |
| mysql-bin.000003 | 128936061 |
+------------------+-----------+
3 rows in set (0.00 sec)

Notice how the create database command was recorded and the size of the last binary log changed. Then, notice how I disabled the binary log and created a table in the new test database, and added 3 rows to it. Yet, the last binary log stayed the same size.

Again, this will not disable binary logging for the instance, but this has to be the next best thing.

参考：

http://serverfault.com/questions/217783/how-to-turn-off-the-binary-log-for-mysqld-multi-instances

https://dev.mysql.com/doc/refman/5.5/en/set-sql-log-bin.html