You can use the mysqlbinlog
utility (shipped along with MySQL) to extract the contents from binary logs and apply them to other servers.
Execute a few statements using various binary formats. When you set the binlog_format
at GLOBAL
level, you have to disconnect and reconnect to get the changes. If you want to be connected, set at SESSION
level.
Change to statement-based replication (SBR):
mysql> SET @@GLOBAL.BINLOG_FORMAT='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
Update a few rows:
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE salaries SET salary=salary*2 WHERE emp_no<10002; Query OK, 18 rows affected (0.00 sec) Rows matched: 18Changed: 18Warnings: 0 mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
Change to row-based replication (RBR):
mysql> SET @@GLOBAL.BINLOG_FORMAT='ROW';
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>...