Bug #56476 Add additional example to binlog-do-db manual section
Submitted: 2 Sep 2010 1:21 Modified: 17 Sep 2010 18:00
Reporter: Roel Van de Paar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:5.0+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[2 Sep 2010 1:21] Roel Van de Paar
Description:
http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...

Lists:

=========
An example of what does not work as you might expect when using statement-based logging: If the server is started with --binlog-do-db=sales and you issue the following statements, the UPDATE statement is not logged:

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

Please add this to the above:

=========
Another example which may not be self-evident is where you expect a certain database not to be replicated since it was never specifically listed. For example, if the server is started with --binlog-do-db=sales again, the following UPDATE statement will still be logged: 

USE sales;
UPDATE prices.discounts SET percentage=percentage+10;

Even though prices was never listed for inclusion in the binary log using binlog-do-db.
=========

How to repeat:
Verified the same in 5.0.36, 5.0.90 and in 5.1.50

============================ 5.0.36 example
roel@roel-ubuntu-vm:/mysql/mysql-enterprise-5.0.36-linux-i686-icc-glibc23$ cat my.cnf
[...]
[mysqld]
log-bin=log-bin.log
binlog-do-db=db1

[create databases, restart etc.]

mysql> FLUSH LOGS; SHOW MASTER STATUS;
Query OK, 0 rows affected (0.01 sec)

+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000003 |       98 | db1          |                  | 
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> USE db2
Database changed
mysql> CREATE TABLE table2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> USE db1;
Database changed
mysql> INSERT INTO db2.table2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000003 |      193 | db1          |                  | <=== Indicates something was logged to binary log.
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

roel@roel-ubuntu-vm:/mysql/mysql-enterprise-5.0.36-linux-i686-icc-glibc23/data$ ../bin/mysqlbinlog log-bin.000003 
[...]
use db1/*!*/;
[...]
INSERT INTO db2.table2 VALUES (1)/*!*/;

============================ 5.0.90 example
mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000002 |      193 | db1          |                  | 
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

roel@roel-ubuntu-vm:/mysql/mysql-5.0.90-linux-i686-icc-glibc23$ ./bin/mysqlbinlog ./data/log-bin.000002 
[...]
use db1/*!*/;
[...]
INSERT INTO db2.table2 VALUES (1)

============================ 5.1.50 example
mysql>  SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000002 |      201 | db1          |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

roel@roel-ubuntu-vm:/mysql/mysql-5.1.50-linux-i686-glibc23$ ./bin/mysqlbinlog 
[...]
use db1/*!*/;
[...]
INSERT INTO db2.table2 VALUES (1)
============================
[2 Sep 2010 7:02] Jon Stephens
I'll take this one.

Set prio/target.
[2 Sep 2010 8:17] Jon Stephens
See also BUG#56357.
[17 Sep 2010 18:00] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.