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.
You may have to resort to using this:
SET SQL_LOG_BIN=0;
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.