MySQL :: MySQL 5.7 Reference Manual :: 14.21.6 The InnoDB memcached Plugin and Replication

10bet网址

version 5.7

8.0 current
5.6

8.0 Japanese
5.6 Japanese

MySQL 5.7 Reference Manual/.../ The InnoDB memcached Plugin and Replication

14.21.6 The InnoDB memcached Plugin and Replication

Because thedaemon_memcachedplugin supports the MySQLbinary log, updates made on a source server through thememcachedinterface can be replicated for backup, balancing intensive read workloads, and high availability. Allmemcachedcommands are supported with binary logging.

You do not need to set up thedaemon_memcachedplugin 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 thedaemon_memcachedplugin with MySQL replication. It is assumed that you have completed the setup described inSection 14.21.3, “Setting Up the InnoDB memcached Plugin”.

Enabling the InnoDB memcached Binary Log

To use thedaemon_memcachedplugin with the MySQLbinary log, enable theinnodb_api_enable_binlogconfiguration 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-binoption. You can add these options to the MySQL configuration file, or on themysqldcommand line.
```
mysqld ... --log-bin -–innodb_api_enable_binlog=1
```
Configure the source and replica server, as described inSection 16.1.2, “Setting Up Binary Log File Position Based Replication”.

Usemysqldumpto 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

On the source server, issueSHOW MASTER STATUS获得二进制日志源坐标。
```
mysql> SHOW MASTER STATUS;
```

On the replica server, use aCHANGE MASTER TOstatement 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;

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 theInnoDBmemcachedreplication configuration using thememcachedand telnet to insert, update, and delete data. A MySQL client is used to verify results on the source and replica servers.

The example uses thedemo_testtable, which was created by theinnodb_memcached_config.sqlconfiguration script during the initial setup of thedaemon_memcachedplugin. Thedemo_testtable contains a single example record.

Use thesetcommand to insert a record with a key oftest1, 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
```
On the source server, check that the record was inserted into thedemo_testtable. Assuming thedemo_testtable was not previously modified, there should be two records. The example record with a key ofAA, and the record you just inserted, with a key oftest1. Thec1column maps to the key, thec2column to the value, thec3column to the flag value, thec4column to the cas value, and thec5column 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 | +-------+--------------+------+------+------+
```

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 | +-------+--------------+------+------+------+

Use thesetcommand to update the key to a value ofnew.

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 thecasvalue 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 | +-------+--------------+------+------+------+

Delete thetest1record using adeletecommand.

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

When thedeleteoperation is replicated to the replica, thetest1record on the replica is also deleted.

mysql> SELECT * FROM test.demo_test; +----+--------------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +----+--------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | +----+--------------+------+------+------+

从表中删除所有行使用flush_allcommand.

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)

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

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 | +-------+--------------+------+------+------+

从表中删除所有行使用flush_allcommand.

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

Check to ensure that theflush_alloperation 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:

Mostmemcachedoperations are mapped toDMLstatements (analogous to insert, delete, update). Since there is no actual SQL statement being processed by the MySQL server, allmemcachedcommands (except forflush_all) use Row-Based Replication (RBR) logging, which is independent of any serverbinlog_formatsetting.
Thememcachedflush_allcommand is mapped to theTRUNCATE TABLEcommand. SinceDDLcommands can only use statement-based logging, theflush_allcommand is replicated by sending aTRUNCATE TABLEstatement.

Transactions:

The concept oftransactionshas not typically been part ofmemcachedapplications. For performance considerations,daemon_memcached_r_batch_sizeanddaemon_memcached_w_batch_sizeare used to control the batch size for read and write transactions. These settings do not affect replication. Each SQL operation on the underlyingInnoDBtable is replicated after successful completion.
The default value ofdaemon_memcached_w_batch_sizeis1, which means that eachmemcachedwrite operation is committed immediately. This default setting incurs a certain amount of performance overhead to avoid inconsistencies in the data that is visible on the source and replica servers. The replicated records are always available immediately on the replica server. If you setdaemon_memcached_w_batch_sizeto a value greater than1, records inserted or updated throughmemcachedare not immediately visible on the source server; to view the records on the source server before they are committed, issueSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

PREV HOME UP NEXT