By default, MySQL binary logging and replication is statement-based: when the master server commits a change, it writes the SQL statement into its binary log, and any slaves that replicate it execute the same SQL statement into their own database.
MySQL also supports row-based replication: the master server logs the data affected by a change (information to INSERT or UPDATE, the identity of rows to DELETE), and the slave applies those changes directly to its database.
Row-based replication was introduced to provide perfect replication of data that is non-deterministic: when the same statement was executed on the master and slave, the outcome was different.
It can also have a performance impact. Short SQL queries that affect a lot of rows would require more bandwidth to transmit as row-based replication. For example, if replicated by row, this statement would have to uniquely identify 10,000 rows; it would be much more efficiently transmitted as statement-based: DELETE FROM important.stuff WHERE id BETWEEN 1 AND 10000;
But difficult to evaluate queries that change relatively little data would be much faster to apply to slaves if the master told them what to change instead of forcing them to repeat the work. For example, this statement requires a table scan and a hash calculation on every row; slaves could save considerable processor time if the master just told them the outcome of all those calculations using row-based replication: DELETE FROM user WHERE MD5(User) = "c498faa0787b2eaf054b81f814b1aa12";
The MySQL documentation recommends you use Mixed Mode replication. In Mixed Mode replication, most queries are replicated by statement. But transactions MySQL knows are non-deterministic are replicated by row.
Mixed Mode uses row-based replication for any transaction that:
UUID()
, USER()
, or CURRENT_USER()
functionsLOAD_FILE
(which otherwise assumes every slave has the exact same file on the local file system and doesn't replicate the data)auto_increment
columns (the binlog format only carries one auto_increment
value per statement)The slave server is already replicating from the master server.
To test, SSH to master.example.com
and connect to MySQL.
master ~ $ mysql -u root Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
What format is master using for its binary logs?
mysql> show variables like "binlog_format"; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec)
We're going to look at the format of a statement-based binary log. Tell master to end the current log file and start a new one.
mysql> flush logs; Query OK, 0 rows affected (0.06 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>
Insert a new record.
mysql> insert into important.stuff set details = 'testing statement-based replication'; Query OK, 1 row affected (0.02 sec)
Exit MySQL and take a look at this statement in the binary log, using the log file you learned from show master status
mysql> exit Bye master ~ $ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #130423 17:40:36 server id 10 end_log_pos 107 Start: binlog v 4, server v 5.5.30-log created 130423 17:40:36 # Warning: this binlog is either in use or was not closed properly. BINLOG ' lMd2UQ8KAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #130423 17:40:49 server id 10 end_log_pos 171 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1366738849/*!*/; SET @@session.pseudo_thread_id=6/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 171 #130423 17:40:49 server id 10 end_log_pos 199 Intvar SET INSERT_ID=296/*!*/; # at 199 #130423 17:40:49 server id 10 end_log_pos 337 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1366738849/*!*/; insert into important.stuff set details = 'testing statement-based replication' /*!*/; # at 337 #130423 17:40:49 server id 10 end_log_pos 364 Xid = 307 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
You can see in this statement-based binary log file that replication carries a lot metadata to make sure that the statement works the same on the slave:
SET INSERT_ID
will ensure the row's id
is the same on both servers.
SET TIMESTAMP
will ensure the row's happened
column shows the same time on both servers.
Finally, you can see the statement itself in the binary log, ready to be replicated.
Now SSH to slave.example.com
and verify that the row you just inserted has been replicated.
slave ~ $ mysql -u root mysql> select * from important.stuff order by id desc limit 1; +-----+---------------------------+---------------------+ | id | details | happened | +-----+---------------------------+---------------------+ | 296 | testing happy replication | 2013-04-12 14:09:26 | +-----+---------------------------+---------------------+ 1 row in set (0.00 sec) mysql>
important.stuff
Now we're going to intentionally use a function that is not compatible with statement-based replication.
The UUID()
function creates a universally unique identifier. By definition, the output of the UUID() function should be different every time it is run.
(Note, RAND()
doesn't have this problem; MySQL logs the random number generator seed to the binary log; using the same seed, the slave generates the same pseudorandom number.)
Insert a new row in the master containing a UUID.
master ~ $ mysql -u root mysql> insert into important.stuff set details=UUID(); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 287 | aebd063b-a960-11e2-bcc6-12313909faab | 2013-04-20 02:19:08 | +-----+--------------------------------------+---------------------+ 1 row in set (0.00 sec) mysql>
Now view the same record on the slave:
mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 287 | aec0309a-a960-11e2-aef4-1231390f21fa | 2013-04-20 02:19:08 | +-----+--------------------------------------+---------------------+ 1 row in set (0.00 sec) mysql>
You can see that the details
column for the newest row is different between the two servers. (They're similar because UUID is built from timestamp and network address, and the statement replicated quickly between servers with similar network characteristics.)
binlog_format
has default value STATEMENT
binlog_format
has default value STATEMENT
UUID
on the master in the table important.stuff
Configure the master for row-based replication:
mysql> exit Bye master ~ $ sudoedit /etc/my.cnf
Insert this line immediately after [mysql]
:
/etc/my.cnf[mysqld] binlog_format = row relay_log = mysql-relay-bin log_slave_updates = 1 log_bin = mysql-bin server_id = 10 ......some content not shown
Then restart the MySQL service on the master and verify the change:
master ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] master ~ $ mysql -u root mysql> show variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) mysql>
Complete the same procedure on the slave.
/etc/my.cnf
sets binlog_format
to ROW
binlog_format
has value ROW
/etc/my.cnf
sets binlog_format
to ROW
binlog_format
has value ROW
The master will have started a new binary log file during the service restart, so let's run two interesting transactions.
First, insert another row on the master containing a UUID, to show that row-based replication solves that problem:
mysql> insert into important.stuff set details=UUID(); Query OK, 1 row affected (0.02 sec) mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 288 | 5445c7d9-a961-11e2-82fd-12313909faab | 2013-04-20 02:23:46 | +-----+--------------------------------------+---------------------+ 1 rows in set (0.00 sec) mysql>
Look up the new row on the slave:
mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 288 | 5445c7d9-a961-11e2-82fd-12313909faab | 2013-04-20 02:23:46 | +-----+--------------------------------------+---------------------+ 1 rows in set (0.00 sec) mysql>
This time, the slave replicated a perfect copy of the UUID.
Now, on the master, we'll perform a large DELETE query to see a downside of row-based replication.
mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 288 | +----------+ 1 row in set (0.01 sec) mysql> delete from important.stuff where id between 100 and 199; Query OK, 100 rows affected (0.04 sec) mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 188 | +----------+ 1 row in set (0.00 sec) mysql>
And verify that the slave applied the same change:
mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 188 | +----------+ 1 row in set (0.00 sec) mysql>
Back on the master, look at how these two changes are represented in the binary log.
The master will have started a new binary log file during the service restart, these two transactions are the only content in the latest log file.
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 3168 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> exit Bye master ~ $ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000003 ......some content not shown /*!*/; # at 180 # at 233 #130424 4:25:55 server id 10 end_log_pos 233 Table_map: `important`.`stuff` mapped to number 33 #130424 4:25:55 server id 10 end_log_pos 308 Write_rows: table id 33 flags: STMT_END_F BINLOG ' 0153URMKAAAANQAAAOkAAAAAACEAAAAAAAEACWltcG9ydGFudAAFc3R1ZmYAAwMPBwL/AAI= 0153URcKAAAASwAAADQBAAAAACEAAAAAAAEAA//4KAEAACQwZTIyNGE5OS1hYzk3LTExZTItYjJl Ny0xMjMxMzkwYTA1NDDTXndR '/*!*/; # at 308 #130424 4:25:55 server id 10 end_log_pos 335 Xid = 9 COMMIT/*!*/; ......some content not shown
Near the top of the file, you'll see the transaction where you inserted the working row with a UUID. You'll also notice that there's no obvious way to figure out what is going on reading the log file, even with the help of mysqlbinlog
. Because this log file contains only one INSERT, it's possible find that transaction by looking for Write_rows
.
The row data itself is passed in the three-line BINLOG
block.
The next transaction is the large DELETE:
......some content not shown #130424 4:32:08 server id 10 end_log_pos 671 Table_map: `important`.`stuff` mapped to number 33 #130424 4:32:08 server id 10 end_log_pos 1708 Delete_rows: table id 33 #130424 4:32:08 server id 10 end_log_pos 2747 Delete_rows: table id 33 #130424 4:32:08 server id 10 end_log_pos 3566 Delete_rows: table id 33 flags: STMT_END_F BINLOG ' SGB3URMKAAAANQAAAJ8CAAAAACEAAAAAAAEACWltcG9ydGFudAAFc3R1ZmYAAwMPBwL/AAI= SGB3URkKAAAADQQAAKwGAAAAACEAAAAAAAAAA//4ZAAAABJkcmFnIGR1dHkgcHJ5IGZvaWwBNTxQ +GUAAAAScGluayBzaGUgbWVhbCByb29mSjs+UPhmAAAAE25vb2sgYnVmZiBxdWl0IGd1bGbaAUBQ +GcAAAATaGFuZCB0ZWFyIGNhbWUgZGFzaEj1QVD4aAAAABJnbG93IHZlbnQgd2lsbCBodWceNERQ +GkAAAARdGhlbSBidW4gYmlyZCB0b3naxkVQ+GoAAAASeW91ciBwbGFuIGp1bXAgaW9uYxRHUPhr AAAAEWluY2ggbG9zcyB0ZW4gY2FyzaBIUPhsAAAAE3BvbmQgc3dhbiBwZWVyIHdhcmTeZEpQ+G0A ......some content not shown '/*!*/; # at 3566 #130424 4:32:08 server id 10 end_log_pos 3593 Xid = 22 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; master ~ $
The huge binlog
data block (which we've reproduced barely a fraction of here) contains the information the slave will need to uniquely identify each of the 100 affected rows.
UUID
on the master in the table important.stuff
In Mixed Mode, MySQL uses statement-based replication for most queries, switching to row-based replication only when statement-based replication would cause an inconsistency (or corruption). It is a compromise between the two replication types we've already explored.
Turn on Mixed Mode on the master by editing /etc/my.cnf
master ~ $ sudoedit /etc/my.cnf
Update the binlog_format
setting to be mixed
:
/etc/my.cnf[mysqld] binlog_format = row binlog_format = mixed relay_log = mysql-relay-bin log_slave_updates = 1 log_bin = mysql-bin server_id = 10 ......some content not shown
Then restart the MySQL service on the master and verify the change:
master ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] master ~ $ mysql -u root mysql> show variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.00 sec) mysql>
Repeat this procedure on the slave.
/etc/my.cnf
sets binlog_format
to MIXED
binlog_format
has value MIXED
/etc/my.cnf
sets binlog_format
to MIXED
binlog_format
has value MIXED
Now insert another row on the master containing a UUID:
mysql> insert into important.stuff set details=UUID(); Query OK, 1 row affected (0.02 sec) mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 288 | 5445c7d9-a961-11e2-82fd-12313909faab | 2013-04-20 02:23:46 | +-----+--------------------------------------+---------------------+ 2 rows in set (0.00 sec) mysql>
Now verify that the new row, as replicated to the slave, has a details
column that matches the master:
mysql> select * from important.stuff order by id desc limit 1; +-----+--------------------------------------+---------------------+ | id | details | happened | +-----+--------------------------------------+---------------------+ | 288 | 5445c7d9-a961-11e2-82fd-12313909faab | 2013-04-20 02:23:46 | +-----+--------------------------------------+---------------------+ 2 rows in set (0.00 sec) mysql>
Then run another large DELETE on the master:
mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 188 | +----------+ 1 row in set (0.01 sec) mysql> delete from important.stuff where id between 200 and 299; Query OK, 100 rows affected (0.04 sec) mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 88 | +----------+ 1 row in set (0.00 sec) mysql>
And verify that the slave applied the same change:
mysql> select count(*) from important.stuff; +----------+ | count(*) | +----------+ | 88 | +----------+ 1 row in set (0.00 sec) mysql>
Then view the log on the master:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 532 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
master ~ $ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000004
......some content not shown
BEGIN
/*!*/;
# at 171
# at 224
#130424 4:56:08 server id 10 end_log_pos 224 Table_map: `important`.`stuff` mapped to number 33
#130424 4:56:08 server id 10 end_log_pos 299 Write_rows: table id 33 flags: STMT_END_F
BINLOG '
6GV3URMKAAAANQAAAOAAAAAAACEAAAAAAAEACWltcG9ydGFudAAFc3R1ZmYAAwMPBwL/AAI=
6GV3URcKAAAASwAAACsBAAAAACEAAAAAAAEAA//4KgEAACQ0NmUzYzI2Ny1hYzliLTExZTItODk0
Yy0xMjMxMzkwYTA1NDDoZXdR
'/*!*/;
# at 299
#130424 4:56:08 server id 10 end_log_pos 326 Xid = 12
COMMIT/*!*/;
# at 326
#130424 4:56:39 server id 10 end_log_pos 390 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1366779399/*!*/;
BEGIN
/*!*/;
# at 390
#130424 4:56:39 server id 10 end_log_pos 505 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1366779399/*!*/;
delete from important.stuff where id between 200 and 299
/*!*/;
# at 505
#130424 4:56:39 server id 10 end_log_pos 532 Xid = 15
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
master ~ $
In this binary log, you can see the INSERT used row-based replication, because it contained a call to the UUID() function. You can still find this transaction by the tell-tale Write_rows
marker.
Meanwhile, the DELETE used the more efficient statement-based replication because it did not contain any non-deterministic warning signs.
UUID
on the master in the table important.stuff