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
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 theUSE
statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, theUPDATE
statement's effects are not written to the binary log, which means that no changes to thesales.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.