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)
============================
