Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
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".
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.dbslave 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
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 11211Trying 127.0.0.1...Connected to 127.0.0.1.Escape character is '^]'.set test1 10 0 2t1STORED
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;
| c1 | c2 | c3 | c4 | c5 |
|---|---|---|---|---|
| test1 | t1 | 10 | 2 | 0 |
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;
| c1 | c2 | c3 | c4 | c5 |
|---|---|---|---|---|
| test1 | t1 | 10 | 2 | 0 |
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 3newSTORED
From the slave server, the update is replicated (notice the cas value also
updated):
mysql> select * from test.demo_test;
| c1 | c2 | c3 | c4 | c5 |
|---|---|---|---|---|
| test1 | new | 10 | 3 | 0 |
1 row in set (0.00 sec)
Delete the record with a delete command:
Connected to 127.0.0.1.Escape character is '^]'.delete test1DELETED
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 5againSTOREDset test3 10 0 6again1STORED
In the slave server, confirm these two records are replicated:
mysql> select * from test.demo_test;
| c1 | c2 | c3 | c4 | c5 |
|---|---|---|---|---|
| test2 | again | 10 | 5 | 0 |
| test3 | again1 | 10 | 6 | 0 |
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_allOK
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.
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.