Rechercher dans le manuel MySQL
15.19.7 The InnoDB memcached Plugin and Replication
Because the daemon_memcached
plugin supports
the MySQL binary log,
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 with binary logging.
You do not need to set up the daemon_memcached
plugin on slave servers.
The primary advantage of this configuration 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
when using the daemon_memcached
plugin with
MySQL replication. It is assumed that you have completed the setup
described in Section 15.19.3, “Setting Up the InnoDB memcached Plugin”.
Enabling the InnoDB memcached Binary Log
To use the
daemon_memcached
plugin with the MySQL binary log, enable theinnodb_api_enable_binlog
configuration option on the master server. This option can only be set at server startup. You must also enable the MySQL binary log on the master server using the--log-bin
option. You can add these options to the MySQL configuration file, or on the mysqld command line.mysqld ... --log-bin -–innodb_api_enable_binlog=1
Configure the master and slave server, as described in Section 17.1.2, “Setting Up Binary Log File Position Based Replication”.
Use mysqldump to create a master data snapshot, and sync the snapshot 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.On the slave server, use a
CHANGE MASTER TO
statement to set up a slave server using the master binary log coordinates.Start the 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
This example demonstrates how to test the
InnoDB
memcached
replication configuration using the memcached
and telnet to insert, update, and delete data. A MySQL client is
used to verify results on the master and slave servers.
The example uses the demo_test
table, which
was created by the
innodb_memcached_config.sql
configuration
script during the initial setup of the
daemon_memcached
plugin. The
demo_test
table contains a single example
record.
Use the
set
command 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 master 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 ofAA
, and the record you just inserted, with a key oftest1
. 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.Check to verify that the same record was replicated to the slave server.
Use the
set
command 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 slave server (notice that the
cas
value is also updated).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 slave, thetest1
record on the slave is also deleted.Remove all rows from the table using the
flush_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
Telnet to the master 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 slave server.
Remove all rows from the table using the
flush_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
Check to ensure that the
flush_all
operation was replicated on the slave server.
Binary Log 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, which is independent of any serverbinlog_format
setting.The memcached
flush_all
command is mapped to theTRUNCATE TABLE
command in MySQL 5.7 and earlier. Since DDL commands can only use statement-based logging, theflush_all
command is replicated by sending aTRUNCATE TABLE
statement. In MySQL 8.0 and later,flush_all
is mapped toDELETE
but is still replicated by sending aTRUNCATE TABLE
statement.
Transactions:
The concept of transactions has not typically been part of memcached applications. For performance considerations,
daemon_memcached_r_batch_size
anddaemon_memcached_w_batch_size
are used to control the batch size for read and write transactions. These settings do not affect replication. Each SQL operation on the underlyingInnoDB
table is replicated after successful completion.The default value of
daemon_memcached_w_batch_size
is1
, which means that each memcached write 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 master and slave servers. The replicated records are always available immediately on the slave server. If you setdaemon_memcached_w_batch_size
to a value greater than1
, records inserted or updated through memcached are not immediately visible on the master server; to view the records on the master server before they are committed, issueSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-memcached-replication.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.