| |
Most people have heard of NoSQL database, which is the most widely used tool for Memcached, you add a caching layer by layer between the application and database Memcached again. MySQL 5.6 from the start, you can get a brand new plug-in that integrates the MySQL and Memcached. In this article, we will learn how to install the plug-in Linux, how to do some basic configuration.
prerequisites
Install libevent.
Translator's Note: The following commands are provided by the translator.
Command is as follows:
yum install libevent -y
yum install php -y
yum install policycoreutils-python -y
Translator's Note: My version of the operating system is RHEL 6.5, and the author of the OS version is CentOS. Here is my version of MySQL.
mysql> SHOW VARIABLES LIKE '% version%';
+ ------------------------- + ----------------------- ------- +
| Variable_name | Value |
+ ------------------------- + ----------------------- ------- +
| Innodb_version | 5.6.21 |
| Protocol_version | 10 |
| Slave_type_conversions ||
| Version | 5.6.21 |
| Version_comment | MySQLCommunityServer (GPL) |
| Version_compile_machine | x86_64 |
| Version_compile_os | Linux |
+ ------------------------- + ----------------------- ------- +
7 rows inset (0.00 sec)
installation
Install Memcached support, we need to create some tables MySQL and Memcached integration services. MySQL table already contains these files (innodb_memcached_config.sql) is created, you can find this file in your basedir subdirectory. To find your basedir where you are, run the following command:
mysql> SHOW VARIABLES LIKE 'basedir';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Basedir | / usr |
+ --------------- + ------- +
1 row inset (0.00 sec)
If you install MySQL by release warehouse, the path to the file as follows: $ basedir / share / mysql / innodb_memcached_config.sql
If you are using a binary version of MySQL, the path of this file is as follows: $ basedir / share / innodb_memcached_config.sql
Now, we will run the SQL file. By default, the script creates a test table in the test database, but in our tests, we will use memcached database.
Translator's Note: The operation log with the original time may differ, the following logs from their own experiments.
mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)
mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.14 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.00 sec)
mysql>
Now, let's create our own table, used to store data Memcached:
mysql> CREATE DATABASE IF NOT EXISTS memcached;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE memcached.dados LIKE test.demo_test;
Query OK, 0 rows affected (0.02 sec)
mysql> UPDATE innodb_memcache.containers SET db_schema = 'memcached', \
-> Db_table = 'dados' WHERE name = 'aaa' LIMIT 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> DROP DATABASE test;
Query OK, 1 row affected (0.09 sec)
The next step is to install the plug-in MySQL Memcached. To achieve this, we will use INSTALL PLUGIN command:
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.03 sec)
Verify that the plug-in is successfully installed, we can run the following command:
mysql> \ netstat -tunap |! grep LIST | grep mysql
tcp 000.0.0.0:112110.0.0.0:* LISTEN 1858 / mysqld
tcp 00 ::: 11211 ::: * LISTEN 1858 / mysqld
tcp 00 ::: 3306 ::: * LISTEN 1858 / mysqld
Configure and use
Now, we will pass a programming language --PHP, such as using it this way:
[Root @ mysql memcache] # cat test1.php
Translator's Note: The original is a new Memcached (), here to new Memcache (), the following PHP script are new Memcache ().
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
$ M-> set ( 'key1', 'Testing memcached');
echo 'Value of key1 is:' $ m-> get ( 'key1') "\ n";..
?>
[Root @ mysql memcache] # php test1.php
Value of key1 is: Testing memcached
Now, let us look at what is stored in MySQL?
mysql> SELECT * FROM memcached.dados;
+ ------ + ------- + ------ + ------ + ------ +
| C1 | c2 | c3 | c4 | c5 |
+ ------ + ------- + ------ + ------ + ------ +
| Key1 | Testing memcached | 0 | 1 | 0 |
+ ------ + ------- + ------ + ------ + ------ +
1 row inset (0.00 sec)
If we change a record in MySQL manually, what will happen?
mysql> UPDATE memcached.dados \
-> SET c2 = 'Entry modified directly on MySQL';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1Changed: 1Warnings: 0
[Root @ mysql memcache] # cat test2.php
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
echo 'Value of key1 is:' $ m-> get ( 'key1') "\ n";..
?>
[Root @ mysql memcache] # php test2.php
Value of key1 is: Entry modified directly on MySQL
[Root @ mysql memcache] #
If we want to store the entry to a different MySQL table, what will happen?
We just need to create a new table, add a new container and use in config_options database table definition innodb_memcache delimiter.
mysql> SELECT * FROM innodb_memcache.config_options \
-> WHERE name = 'table_map_delimiter';
+ --------------------- + ------- +
| Name | value |
+ --------------------- + ------- +
| Table_map_delimiter |. |
+ --------------------- + ------- +
1 row inset (0.00 sec)
mysql> CREATE TABLE memcached.dados2 LIKE memcached.dados;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, \
-> Key_columns, value_columns, flags, cas_column, expire_time_column, \
-> Unique_idx_name_on_key) \
-> VALUES ( 'bbb', 'memcached', 'dados2', 'c1', 'c2', 'c3', 'c4', 'c5', 'PRIMARY');
Query OK, 1 row affected (0.06 sec)
We have created a new table named dados2, and added a new container named bbb point to that table, and now we just need to use it as a prefix to Memcached.
[Root @ mysql memcache] # cat test3.php
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
$ M-> set ( '@@ bbb.key1', 'Should be stored on dados2 table');
echo 'Value of bbb.key1 is:' $ m-> get ( '@@ bbb.key1') "\ n";..
?>
[Root @ mysql memcache] # php test3.php
Value of bbb.key1 is: Should be stored on dados2 table
mysql> SELECT * FROM memcached.dados2;
+ ------ + ------ + ------------ + ------ + ------ +
| C1 | c2 | c3 | c4 | c5 |
+ ------ + ------ + ------------ + ------ + ------ +
| Key1 | Should be stored on dados2 table | 0 | 2 | 0 |
+ ------ + ------ + ------------ + ------ + ------ +
1 row inset (0.00 sec)
We can also map this table, the value is stored into a separate domain.
mysql> SELECT * FROM innodb_memcache.config_options \
-> WHERE name = 'separator';
+ ----------- + ------- +
| Name | value |
+ ----------- + ------- +
| Separator |||
+ ----------- + ------- +
1 row inset (0.00 sec)
We will use this character to store values into the different columns. Let's create a table, to add a new container (we will specify a new separator - comma ',' to store our data):
mysql> CREATE TABLE products \
-> (Id varchar (128), \
-> Name varchar (255), \
-> Value varchar (15), \
-> C3 int, \
-> C4 bigint, \
-> C5 int, \
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO innodb_memcache.containers \
-> (Name, db_schema, db_table, key_columns, value_columns, \
-> Flags, cas_column, expire_time_column, unique_idx_name_on_key) \
-> VALUES ( 'products', 'memcached', 'products', 'id', 'name, value', \
-> 'C3', 'c4', 'c5', 'PRIMARY');
Query OK, 1 row affected (0.06 sec)
Now, let's create a product array, and then add the data to the Memcached.
[Root @ mysql memcache] # cat test4.php
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
$ Products = array (
array ( '1', 'TV', '1999,00'),
array ( '2', 'Hack', '399,00'),
array ( '3', 'Table', '599,00'),
array ( '4', 'Chair', '99,00')
);
foreach ($ products as $ product)
{
$ Key = '@@ products.' $ Product [0].;
$ Value = $ product [1] '|' $ product [2].;.
$ M-> set ($ key, $ value);
}
?>
[Root @ mysql memcache] # php test4.php
mysql> SELECT * FROM memcached.products;
+ ---- + --------- + ------- + ------ + ------ + ------ +
| Id | name | value | c3 | c4 | c5 |
+ ---- + --------- + ------- + ------ + ------ + ------ +
| 1 | TV | 1999,00 | 0 | 3 | 0 |
| 2 | Hack | 399,00 | 0 | 4 | 0 |
| 3 | Table | 599,00 | 0 | 5 | 0 |
| 4 | Chair | 99,00 | 0 | 6 | 0 |
+ ---- + --------- + ------- + ------ + ------ + ------ +
4 rows inset (0.00 sec)
Server / service restart
Let's see if we restart MySQL service (reboot the server Universal applicable) what will happen?
These data are stored in Memcached in the service restarts after MySQL will still exist?
[Root @ mysql memcache] # service mysql restart
Shutting down MySQL ...... SUCCESS!
StartingMySQL. SUCCESS!
[Root @ mysql memcache] # cat test5.php
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
echo 'Value of key1 is:' $ m-> get ( 'key1') "\ n";..
?>
[Root @ mysql memcache] # php test5.php
Value of key1 is: Entry modified directly on MySQL
in other words! Even if the service restart or reboot the server, the data will still exist.
SELinux
In SELinux enabled environment, prevents Memcached and MySQL integration, since it is not listening Memcached port, the following is an example of how to allow the listener Memcached port (I use CentOS Linux distribution):
Find mysqld and denied entry contains keywords in /var/log/audit/audit.log, if you can find, enter the following command to create a new SELinux module to allow:
type = AVC msg = audit (1423266535.066: 5): avc: denied {name_bind} for \
pid = 1123 comm = "mysqld" src = 11211 scontext = system_u: system_r: mysqld_t: s0 \
tcontext = system_u: object_r: memcache_port_t: s0 tclass = tcp_socket
type = SYSCALL msg = audit (1423266535.066: 5): arch = c000003e syscall = 49 \
success = noexit = -13 a0 = 2f a1 = 7f3aec043230 a2 = 10 a3 = 7f3af61fa75c \
items = 0 ppid = 999 pid = 1123 auid = 4294967295 uid = 27 gid = 27 euid = 27 suid = 27 \
fsuid = 27 egid = 27 sgid = 27 fsgid = 27 tty = (none) ses = 4294967295 comm = "mysqld" \
exe = "/ usr / sbin / mysqld" subj = system_u: system_r: mysqld_t: s0 key = (null)
[Root @ mysql ~] # audit2why < /var/log/audit/audit.log
type = AVC msg = audit (1423266535.066: 5): avc: denied {name_bind} for \
pid = 1123 comm = "mysqld" src = 11211 scontext = system_u: system_r: mysqld_t: s0 \
tcontext = system_u: object_r: memcache_port_t: s0 tclass = tcp_socket
It was caused by:
Missing type enforcement (TE) allow rule.
You can use audit2allow to generate a loadable module to allow this access.
[Root @ mysql ~] # cd / root /
[Root @ mysql ~] # mkdir selinux-custom
[Root @ mysql ~] # cd selinux-custom
[Root @ mysql selinux-custom] # audit2allow -a -M mysql-memcache
******************** IMPORTANT ***********************
To make this policy package active, execute:
semodule -i mysql-memcache.pp
[Root @ mysql selinux-custom] # semodule -i mysql-memcache.pp
Translator's Note: The following is the translator added for testing in the SELinux environment, MySQL and Memcached integration.
[Root @ mysql selinux-custom] # sestatus
SELinux status: enabled
SELinuxfs mount: / selinux
Current mode: enforcing
Modefrom config file: enforcing
Policy version: 24
Policyfrom config file: targeted
[Root @ mysql selinux-custom] # getenforce
Enforcing
[Root @ mysql selinux-custom] # / etc / init.d / mysql restart
Shutting down MySQL .... [OK]
StartingMySQL. [OK]
[Root @ mysql memcache] # cd ~ / memcache
[Root @ mysql memcache] # cat test6.php
< ? Php
$ M = newMemcache ();
$ M-> addServer ( 'localhost', 11211);
echo 'Value of key1 is:' $ m-> get ( 'key1') "\ n";..
?>
[Root @ mysql selinux-custom] # netstat -tunap | grep LIST | grep mysql
tcp 000.0.0.0:112110.0.0.0:* LISTEN 7820 / mysqld
tcp 00 ::: 11211 ::: * LISTEN 7820 / mysqld
tcp 00 ::: 3306 ::: * LISTEN 7820 / mysqld
[Root @ mysql memcache] # php test6.php
Value of key1 is: Entry modified directly on MySQL
Memcached option
If you want to change any Memcached special options, you can add daemon_memcached_option parameter in the MySQL configuration file, such as changing Memcached port:
# In the configuration file, my.cnf generally
daemon_memcached_option = "- p11222"
It is so, I hope you have learned how to install and configure MySQL and Memcached plug-in integration work. |
|
|
|