Skip navigation links
14.2.9.7 Internals of the InnoDB memcached Plugin »
Section Navigation      [Toggle]

14.2.9.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 binlogging.

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 binlog capability, to use the InnoDB memcached plugin along with MySQL replication. It assumes you have already done the basic setup described in Section 14.2.9.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 16.1.1, “How to Set Up 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: 

    mysql> CHANGE MASTER TO
       MASTER_HOST='localhost',
       MASTER_USER='root',
       MASTER_PASSWORD='',
       MASTER_PORT = 13000,
       MASTER_LOG_FILE='0.000001,
       MASTER_LOG_POS=114;

  • Then start the slave: 

    mysql> start slave;

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

    111002 18:47:15 [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 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set test1 10 0 2
t1
STORED

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;
c1c2c3c4c5
test1t11020
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;
c1c2c3c4c5
test1t11020
1 row in set (0.00 sec)

  • Use set command to update the key test1 to a new value new: 

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

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

mysql> select * from test.demo_test;
c1c2c3c4c5
test1new1030
1 row in set (0.00 sec)

  • Delete the record with a delete command: 

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

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 127.0.0.1.
Escape character is '^]'
set test2 10 0 5
again
STORED
set test3 10 0 6
again1
STORED

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

mysql> select * from test.demo_test;
c1c2c3c4c5
test2again1050
test3again11060
2 rows in set (0.00 sec)

Call flush_all in the telnet interface to truncate the table: 

Connected to 127.0.0.1.
Escape character is '^]'.
flush_all
OK

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.

Transactions:

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

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices