Configure Row-Based or Mixed Mode Replication

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>

After this step is completed:

1. INSERT a new record on the master in the table important.stuff
2. Within 1 second, that record is replicated to the slave

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

After this step is completed:

1. On the master, the MySQL variable binlog_format has default value STATEMENT
2. On the slave, the MySQL variable binlog_format has default value STATEMENT
3. INSERT a new record containing a UUID on the master in the table important.stuff
4. Within 1 second, replication inserts a different record on the slave

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.

After this step is completed:

1. On the master, /etc/my.cnf sets binlog_format to ROW
2. On the master, the MySQL variable binlog_format has value ROW
3. On the slave, /etc/my.cnf sets binlog_format to ROW
4. On the slave, the MySQL variable 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.

After this step is completed:

1. INSERT a new record containing a UUID on the master in the table important.stuff
2. Within 1 second, that exact record is replicated to the slave

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.

After this step is completed:

1. On the master, /etc/my.cnf sets binlog_format to MIXED
2. On the master, the MySQL variable binlog_format has value MIXED
3. On the slave, /etc/my.cnf sets binlog_format to MIXED
4. On the slave, the MySQL variable 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.

After this step is completed:

1. INSERT a new record containing a UUID on the master in the table important.stuff
2. Within 1 second, that exact record is replicated to the slave