15.19.7 The InnoDB memcached Plugin and Replication

Inhoudsopgave

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 the innodb_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.
1. mysql> SHOW MASTER STATUS;
On the slave server, use a CHANGE MASTER TO statement to set up a slave server using the master binary log coordinates.
1. mysql> CHANGE MASTER TO
2. MASTER_HOST='localhost',
3. MASTER_USER='root',
4. MASTER_PASSWORD='',
5. MASTER_PORT = 13000,
6. MASTER_LOG_FILE='0.000001,
7. MASTER_LOG_POS=114;

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

Inhoudsopgave Haut

Testing the InnoDB memcached Replication Configuration

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 of test1, a flag value of 10, an expiration value of 0, a cas value of 1, and a value of t1.
```
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 the demo_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. The c1 column maps to the key, the c2 column to the value, the c3 column to the flag value, the c4 column to the cas value, and the c5 column to the expiration time. The expiration time was set to 0, since it is unused.
1. mysql> SELECT * FROM test.demo_test;
2. +-------+--------------+------+------+------+
3. | c1 | c2 | c3 | c4 | c5 |
4. +-------+--------------+------+------+------+
5. | AA | HELLO, HELLO | 8 | 0 | 0 |
6. | test1 | t1 | 10 | 1 | 0 |
7. +-------+--------------+------+------+------+
Check to verify that the same record was replicated to the slave server.
1. mysql> SELECT * FROM test.demo_test;
2. +-------+--------------+------+------+------+
3. | c1 | c2 | c3 | c4 | c5 |
4. +-------+--------------+------+------+------+
5. | AA | HELLO, HELLO | 8 | 0 | 0 |
6. | test1 | t1 | 10 | 1 | 0 |
7. +-------+--------------+------+------+------+
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 slave server (notice that the cas value is also updated).
1. mysql> SELECT * FROM test.demo_test;
2. +-------+--------------+------+------+------+
3. | c1 | c2 | c3 | c4 | c5 |
4. +-------+--------------+------+------+------+
5. | AA | HELLO, HELLO | 8 | 0 | 0 |
6. | test1 | new | 10 | 2 | 0 |
7. +-------+--------------+------+------+------+
Delete the test1 record using a delete 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, the test1 record on the slave is also deleted.
1. mysql> SELECT * FROM test.demo_test;
2. +----+--------------+------+------+------+
3. | c1 | c2 | c3 | c4 | c5 |
4. +----+--------------+------+------+------+
5. | AA | HELLO, HELLO | 8 | 0 | 0 |
6. +----+--------------+------+------+------+
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
```
1. mysql> SELECT * FROM test.demo_test;
2. Empty set (0.00 sec)

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.
1. mysql> SELECT * FROM test.demo_test;
2. +-------+--------------+------+------+------+
3. | c1 | c2 | c3 | c4 | c5 |
4. +-------+--------------+------+------+------+
5. | test2 | again | 10 | 4 | 0 |
6. | test3 | again1 | 10 | 5 | 0 |
7. +-------+--------------+------+------+------+

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.
1. mysql> SELECT * FROM test.demo_test;
2. Empty set (0.00 sec)

Inhoudsopgave Haut

InnoDB memcached Binary Log Notes

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 server binlog_format setting.
The memcached flush_all command is mapped to the TRUNCATE TABLE command in MySQL 5.7 and earlier. Since DDL commands can only use statement-based logging, the flush_all command is replicated by sending a TRUNCATE TABLE statement. In MySQL 8.0 and later, flush_all is mapped to DELETE but is still replicated by sending a TRUNCATE TABLE statement.

Transactions:

The concept of transactions has not typically been part of memcached applications. For performance considerations, daemon_memcached_r_batch_size and daemon_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 underlying InnoDB table is replicated after successful completion.
The default value of daemon_memcached_w_batch_size is 1, 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 set daemon_memcached_w_batch_size to a value greater than 1, 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, issue SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-memcached-replication.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut