The option binlog_ignore_db can be used to exclude certain information from the binary log by setting the option equal to a given database name. However, we advise our clients to not use this option. Why?
- The binary log is a valuable resource for point in time recovery and for taking incremental backups. Filtering out any information from the binary log leaves these files useless.
- If used in replication, the master and replicate will be out of sync. It is commonly believed that binlog_ignore_db serves to ignore only changes made to the specific database defined under this option. In reality, if the ignored database is currently set as the default (by the USE command), any changes made while this current database is active will be ignored! That includes changes to tables in other databases which have explicitly been defined.
For example, if a configuration file has been edited to include binlog_ignore_db=databaseA and the following statements are carried out sequentially, the second statement below will be ignored and not written to the binary log:
- USE databaseA; INSERT INTO tblA (color, number) VALUES (‘red’, 200);
- INSERT INTO databaseB.tblB (color, number) VALUES (‘green’, 100);
Although a database has explicitly been stated in the second statement above, the current database (databaseA) has been set to be ignored and filtered from the binary log. Therefore the statement is not recorded to the binary log.
- Replication will break. Another example involves setting a default database, one that is not being filtered, and qualifying the name of the filtered database in the query. In this case, the statement is not For example:
USE databaseB; UPDATE databaseA.tblA, SET color=’orange’ WHERE number=200;
The fact that databaseB has been called as the current database means that the statement executed above will be recorded to the binary log even though the changes involve the to-be-ignored databaseA. If the replicate does not have the database, or it has an inconsistent version of the database, replication will stop and an error will be thrown.
Summary: The binary logs serve several purposes. They can be used for replication and more importantly, they can be used for incremental backups and point in time recovery of a database. The option binlog_ignore_db should never be used. The filtering mechanism is based on the default database, which is the one currently set by the USE statement. There is no reason to filter the binary log on a standalone database and if the filtering is desired for only certain databases to be replicated, the replicate itself can be configured to filter the information.