14.21.6 The InnoDB memcached Plugin and Replication (original) (raw)

14.21.6 The InnoDB memcached Plugin and Replication

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

You do not need to set up the daemon_memcached plugin on replica servers. The primary advantage of this configuration is increased write throughput on the source. The speed of the replication mechanism is not affected.

The following sections show how to use the binary log capability when using the daemon_memcached plugin with MySQL replication. It is assumed that you have completed the setup described in Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

Enabling the InnoDB memcached Binary Log

  1. To use the daemon_memcached plugin with the MySQL binary log, enable theinnodb_api_enable_binlog configuration option on the source server. This option can only be set at server startup. You must also enable the MySQL binary log on the source server using the--log-bin option. You can add these options to the MySQL configuration file, or on themysqld command line.
mysqld ... --log-bin -–innodb_api_enable_binlog=1
  1. Configure the source and replica server, as described inSection 16.1.2, “Setting Up Binary Log File Position Based Replication”.
  2. Use mysqldump to create a source data snapshot, and sync the snapshot to the replica server.
source $> mysqldump --all-databases --lock-all-tables > dbdump.db  
replica $> mysql < dbdump.db
  1. On the source server, issue SHOW MASTER STATUS to obtain the source binary log coordinates.
mysql> SHOW MASTER STATUS;
  1. On the replica server, use a CHANGE MASTER TO statement to set up a replica server using the source binary log 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;
  1. Start the replica.
mysql> START SLAVE;

If the error log prints output similar to the following, the replica 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

Testing the InnoDB memcached Replication Configuration

This example demonstrates how to test theInnoDB memcached replication configuration using the memcached and telnet to insert, update, and delete data. A MySQL client is used to verify results on the source and replica servers.

The example uses the demo_test table, which was created by theinnodb_memcached_config.sql configuration script during the initial setup of thedaemon_memcached plugin. Thedemo_test table contains a single example record.

  1. Use the set command to insert a record with a key of test1, a flag value of10, an expiration value of0, a cas value of 1, and a value oft1.
telnet 127.0.0.1 11211  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
set test1 10 0 1  
t1  
STORED
  1. On the source server, check that the record was inserted into the demo_test table. Assuming thedemo_test table was not previously modified, there should be two records. The example record with a key of AA, and the record you just inserted, with a key of test1. Thec1 column maps to the key, thec2 column to the value, thec3 column to the flag value, thec4 column to the cas value, and thec5 column to the expiration time. The expiration time was set to 0, since it is unused.
mysql> SELECT * FROM test.demo_test;  
+-------+--------------+------+------+------+  
| c1    | c2           | c3   | c4   | c5   |  
+-------+--------------+------+------+------+  
| AA    | HELLO, HELLO |    8 |    0 |    0 |  
| test1 | t1           |   10 |    1 |    0 |  
+-------+--------------+------+------+------+
  1. Check to verify that the same record was replicated to the replica server.
mysql> SELECT * FROM test.demo_test;  
+-------+--------------+------+------+------+  
| c1    | c2           | c3   | c4   | c5   |  
+-------+--------------+------+------+------+  
| AA    | HELLO, HELLO |    8 |    0 |    0 |  
| test1 | t1           |   10 |    1 |    0 |  
+-------+--------------+------+------+------+
  1. Use the set command to update the key to a value of new.
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  
new  
STORED

The update is replicated to the replica server (notice that the cas value is also updated). 

mysql> SELECT * FROM test.demo_test;  
+-------+--------------+------+------+------+  
| c1    | c2           | c3   | c4   | c5   |  
+-------+--------------+------+------+------+  
| AA    | HELLO, HELLO |    8 |    0 |    0 |  
| test1 | new          |   10 |    2 |    0 |  
+-------+--------------+------+------+------+
  1. Delete the test1 record using adelete command.
telnet 127.0.0.1 11211  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
delete test1  
DELETED

When the delete operation is replicated to the replica, the test1 record on the replica is also deleted. 

mysql> SELECT * FROM test.demo_test;  
+----+--------------+------+------+------+  
| c1 | c2           | c3   | c4   | c5   |  
+----+--------------+------+------+------+  
| AA | HELLO, HELLO |    8 |    0 |    0 |  
+----+--------------+------+------+------+
  1. Remove all rows from the table using theflush_all command.
telnet 127.0.0.1 11211  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
flush_all  
OK  
mysql> SELECT * FROM test.demo_test;  
Empty set (0.00 sec)
  1. Telnet to the source server and enter two new records.
telnet 127.0.0.1 11211  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'  
set test2 10 0 4  
again  
STORED  
set test3 10 0 5  
again1  
STORED
  1. Confirm that the two records were replicated to the replica server.
mysql> SELECT * FROM test.demo_test;  
+-------+--------------+------+------+------+  
| c1    | c2           | c3   | c4   | c5   |  
+-------+--------------+------+------+------+  
| test2 | again        |   10 |    4 |    0 |  
| test3 | again1       |   10 |    5 |    0 |  
+-------+--------------+------+------+------+
  1. Remove all rows from the table using theflush_all command.
telnet 127.0.0.1 11211  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
flush_all  
OK
  1. Check to ensure that the flush_all operation was replicated on the replica server.
mysql> SELECT * FROM test.demo_test;  
Empty set (0.00 sec)

InnoDB memcached Binary Log Notes

Binary Log Format:

Transactions: