mysql: need to avoid storing temp tables on binary logs

Question

On mysql, I have two data bases "parque_test" and "tabelas_temporais", and binary logs are activated.

Every action that modifies an InnoDB table belonging to "parque_test" is recorded on the binary log. However, "parque_test" has stored procedures that use temporary tables to retrieve a result (they are not used to perform update, delete or insert).

To avoid recording the activity of the temporary tables on the bin log, I have set the "/etc/mysql/my.cnf" file so that mysql register all the activities on "parque_test" with the exception of "tabelas_temporais".

cat /etc/mysql/my.cnf"

...

#log_bin = /var/log/mysql/mysql-bin.log

log_bin=/mysql-log/bin-log

binlog_do_db=parque_test

binlog_do_db=parque_prod

expire_logs_days = 10

max_binlog_size = 100M

#binlog_do_db = include_database_name

#binlog_ignore_db = include_database_name

binlog_ignore_db=tabelas_temporais

...

All the temporary tables are created on the "tabelas_temporais" schema; however, the binary log still records the activities on "tabelas_temporais" when for example a stored procedure from "parque_test" is executed a containing a command such as

DROP TEMPORARY TABLE IF EXISTS tabelas_temporais.temp_mod_user;

Any help would be much appreciated!

mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2

Answer 1

Database filtering in the MySQL binary log can be somewhat unexpected if you don't know exactly how it works. from the manual

When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with --binlog-ignore-db=sales and you issue the following statements:

USE prices;UPDATE sales.january SET amount=amount+1000;

The UPDATE statement is logged in such a case because --binlog-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, the UPDATE statement's effects are not written to the binary log, which means that no changes to the sales.january table are logged; in this instance, --binlog-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored for purposes of binary logging.

In short: it seems you might want to look into ROW based logging instead of STATEMENT or MIXED. However:

You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-ignore-db always apply in determining whether or not the statement is logged.

DROP is also a DDL which gets logged. So, does that mean there's no way? On the contrary:

.... temporary tables are logged only when using statement-based replication, whereas with row-based replication they are not logged. With mixed replication, temporary tables are usually logged; exceptions happen with user-defined functions (UDFs) and with the UUID() function....

So, in short, for 'normal' tables this becomes next to impossible while working in a schema that is logged, however, TEMPORARY tables are discarded in ROW based replication by default. This means: switch to ROW based replication, and you don't need to use a different schema for true temporary tables.

However, if you need to switch from STATEMENT / MIXED to ROW based replication, do check performance of this, and if you often do a bulk update (a lot of rows affected), your binlogs will quite a bit larger, as it will log every row changed rather then the single 'simple' UPDATE statement which caused it.