Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 9.0

...

Binlog scope can be set during binlog creation by adding LOG and IGNORE clauses to the CREATE BINLOG statement.

More Examples for CREATE BINLOG
/* log all changes on the entire system */
master> CREATE BINLOG 'binny';
 
/* only log changes to database `test` */
master> CREATE BINLOG 'binny' LOG(`test`);
 
/* only log changes to databases `test` and `baz` */
master> CREATE BINLOG 'binny' LOG(`test`, `baz`);
 
/* log all changes except changes to database `test` */
master> CREATE BINLOG 'binny' IGNORE(`test`);
 
/* log all changes except changes to databases `test` and `baz` */
master> CREATE BINLOG 'binny' IGNORE(`test`, `baz`);      

Binlog scope is independent of binlog format.

...

/* these both work */
master> CREATE BINLOG 'binny' LOG(`test`), FORMAT='ROW';
master> CREATE BINLOG 'binny' LOG(`test`), FORMAT='STATEMENT';         

Create a table scope binlog

...

following this sample:

/* only log changes to table `test`.`foo` */
master> CREATE BINLOG 'binny' LOG(`test`.`foo`);
 
/* log all changes except changes to tables `test`.`foo` and `baz`.`bar` */
master> CREATE BINLOG 'binny' IGNORE(`test`.`foo`, `baz`.`bar`);

Table scope and database scope can be combined.

...

/* only log changes to database `test` excluding changes to table `test`.`foo` */
master> CREATE BINLOG 'binny' LOG(`test`), IGNORE(`test`.`foo`);
 
/* only log changes to database `test` and table `baz`.`bar` */
master> CREATE BINLOG 'binny' LOG(`test`, `baz`.`bar`); 

...

Binlog scope can be completely reset by executing ALTER BINLOG statements with LOG and IGNORE clauses.

...

/* get rid of the existing configuration and log everything */
master> ALTER BINLOG 'binny' LOG ALL;

/* get rid of the existing configuration and log nothing */
master> ALTER BINLOG 'binny' IGNORE ALL;

/* get rid of the existing configuration and only log changes to database `test` */
master> ALTER BINLOG 'binny' LOG(`test`);

/* get rid of the existing configuration and log everything except changes to database `test` */
master> ALTER BINLOG 'binny' IGNORE(`test`);

...

Multiple directives can be included in one ALTER BINLOG; they are applied left-to-right.

Multiple directives to binlog scope            
/* remove database `baz` and add database `test` to the current LOG list */
master> ALTER BINLOG 'binny' DROP LOG(`baz`), ADD LOG(`test`);

/* get rid of the existing configuration and only log changes to databases `baz` and `test`, excluding changes to table `test`.`foo` */
master> ALTER BINLOG 'binny' IGNORE ALL, ADD LOG(`baz`, `test`), ADD IGNORE(`test`.`foo`);

...

There are two ways to check the LOG and IGNORE lists for a binlog. The first is to use SHOW MASTER STATUS.

...

master> CREATE BINLOG 'binny' LOG(`test`, `baz`.`bar`), IGNORE(`test`.`foo`), FORMAT='ROW';
Query OK, 0 rows affected (0.10 sec)

master> SHOW MASTER STATUS 'binny';
+--------------+----------+---------------+------------------+
| File         | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+--------------+----------+---------------+------------------+
| binny.000001 |        4 | baz.bar, test | test.foo         | 
+--------------+----------+---------------+------------------+
1 row in set (0.05 sec)

...