Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Using the InnoDB memcached Plugin with Replication

14.17.6 Using the InnoDB memcached Plugin with Replication

Because the InnoDB memcached daemon plugin supports the MySQL binary log, any updates made on a master server through the memcached interface can be replicated for backup, balancing intensive read workloads, and high availability. All memcached commands are supported for binary logging.

You do not need to set up the InnoDB memcached plugin on the slave servers. In this configuration, the primary advantage is increased write throughput on the master. The speed of the replication mechanism is not affected.

The following sections show how to use the binary log capability, to use the InnoDB memcached plugin along with MySQL replication. It assumes you have already done the basic setup described in Section 14.17.3, “Getting Started with InnoDB Memcached Plugin”.

Enable InnoDB Memcached Binary Log with innodb_api_enable_binlog:

  • To use the InnoDB memcached plugin with the MySQL binary log, enable the innodb_api_enable_binlog configuration option on the master server. This option can only be set at server boot time. You must also enable the MySQL binary log on the master server with the --log-bin option. You can add these options to your server configuration file such as my.cnf, or on the mysqld command line.

    mysqld ... --log-bin -–innodb_api_enable_binlog=1
  • Then configure your master and slave server, as described in Section 17.1.2, “Setting Up Binary Log Based Replication”.

  • Use mysqldump to create a master data snapshot, and sync it to the slave server.

    master shell: mysqldump --all-databases --lock-all-tables > dbdump.db
    slave shell: mysql < dbdump.db
  • On the master server, issue show master status to obtain the Master Binary Log Coordinates:

    mysql> show master status;
  • On the slave server, use a change master to statement to set up a slave server with the above coordinates:

           MASTER_PORT = 13000,
  • Then start the slave:

    mysql> start slave;

    If the error log prints output similar to the following, the slave is ready for replication:

    2013-09-24T13:04:38.639684Z 49 [Note] Slave I/O thread: connected to
    master 'root@localhost:13000', replication started in log '0.000001'
    at position 114

Test with the memcached telnet Interface

To test the server with the above replication setup, we use the memcached telnet interface, and also query the master and slave servers using SQL to verify the results.

In our configuration setup SQL, one example table demo_test is created in the test database for use by memcached. We will use this default table for the demonstrations:

  • Use set to insert a record, key test1, value t1, and flag 10:

    telnet 11211
    Connected to
    Escape character is '^]'.
    set test1 10 0 2

In the master server, you can see that the row is inserted. c1 maps to the key, c2 maps to the value, c3 is the flag, c4 is the cas value, and c5 is the expiration.

mysql> select * from test.demo_test;

1 row in set (0.00 sec)

In the slave server, you will see the same record is inserted by replication:

mysql> select * from test.demo_test;

1 row in set (0.00 sec)
  • Use set command to update the key test1 to a new value new:

Connected to
Escape character is '^]'.
set test1 10 0 3

From the slave server, the update is replicated (notice the cas value also updated):

mysql> select * from test.demo_test;

1 row in set (0.00 sec)
  • Delete the record with a delete command:

Connected to
Escape character is '^]'.
delete test1

When the delete is replicated to the slave, the record on the slave is also deleted:

mysql> select * from test.demo_test;
Empty set (0.00 sec)

  • Truncate the table with the flush_all command.

First, insert two records by telnetting to the master server:

Connected to
Escape character is '^]'
set test2 10 0 5
set test3 10 0 6

In the slave server, confirm these two records are replicated:

mysql> select * from test.demo_test;

2 rows in set (0.00 sec)

Call flush_all in the telnet interface to truncate the table:

Connected to
Escape character is '^]'.

Then check that the truncation operation is replicated on the slave server:

mysql> select * from test.demo_test;
Empty set (0.00 sec)

All memcached commands are supported in terms of replication.

Notes for the InnoDB Memcached Binlog

Binlog Format:

  • Most memcached operations are mapped to DML statements (analogous to insert, delete, update). Since there is no actual SQL statement being processed by the MySQL server, all memcached commands (except for flush_all) use Row-Based Replication (RBR) logging. This is independent of any server binlog_format setting.

  • The memcached flush_all command is mapped to the TRUNCATE TABLE command. Since DDL commands can only use statement-based logging, this flush_all command is replicated by sending a TRUNCATE TABLE statement.


  • The concept of transactions has not typically been part of memcached applications. We use daemon_memcached_r_batch_size and daemon_memcached_w_batch_size to control the read and write transaction batch size for performance considerations. These settings do not affect replication: each SQL operation on the underlying table is replicated right after successful completion.

  • The default value of daemon_memcached_w_batch_size is 1, so each memcached write operation is committed immediately. This default setting incurs a certain amount of performance overhead, to avoid any inconsistency in the data visible on the master and slave servers. The replicated records will always be available immediately on the slave server. If you set daemon_memcached_w_batch_size greater than 1, records inserted or updated through the memcached interface are not immediately visible on the master server; to view these records on the master server before they are committed, issue set transaction isolation level read uncommitted.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.