ubuntu14.0安装mysq和innodb memcached plugin

ubuntu14.0安装mysq和innodb memcached plugin

Introduction

The general idea of using memcached andits standalone server implementation with MySQL has been described in many finearticles such as the one How To Install and Use Memcache on Ubuntu14.04. However, memcached as a standalone server works as an intermediary infront of the MySQL client access layer and manages information only in thememory without an option to store it persistently. This makes it suitable fortasks such as caching the results of duplicate MySQL queries. This savesresources and optimizes the performance of busy sites.

However, in this article we'll bediscussing something different. Memcached will be installed as a MySQL pluginand tightly integrated into MySQL. It will provide a NoSQL style access layerfor managing information directly in regular MySQL InnoDB tables. This hasnumerous benefits as we'll see later in the article.

Basic Understanding

To be able to follow this article you willneed some basic understanding of what NoSQL and memcached are. Put simply,NoSQL works with information in the form of key-value(s) items. This obviouslysimpler approach than the standard SQL suggests better performance andscalability, which are especially sought after for working with large amountsof information (Big Data).

However, NoSQL's good performance is notenough to replace the usual SQL. The simplicity of NoSQL makes it unsuitablefor structured data with complex relations in it. Thus, NoSQL is not areplacement of SQL but rather an important addition to it.

As to memcached, it can be regarded as apopular implementation of NoSQL. It's very fast and has excellent cachingmechanisms as its name suggests. That's why it makes a great choice forbringing NoSQL style to the traditional MySQL.

Some understanding of the memcachedprotocol is also needed. Memcached works with items which have the followingparts:

·        A key — Alphanumerical value which will be thekey for accessing the value of the item.

·        A value — Arbitrary data where the essentialpayload is kept.

·        A flag — Usually a value used for setting upadditional parameters related to the main value. For example, it could be aflag whether or not to use compression.

·        An expiration time — Expiration time in seconds. Recall thatmemcached was initially designed with caching in mind.

·        A CAS value — Unique identifier of each item.

Prerequisites

This guide has been tested on Ubuntu14.04. The described installation and configuration would be similar on otherOS or OS versions, but the commands and location of configuration files mayvary.

You will need the following:

·        Ubuntu 14.04 fresh install

·        Non-root user with sudo privileges

All the commands in this tutorial shouldbe run as a non-root user. If root access is required for the command, it willbe preceded by sudo. If you don't already have that set up,follow this tutorial: Initial Server Setup with Ubuntu 14.04.

Step 1 — Installing MySQL 5.6

The memcached plugin in MySQL is availablein versions of MySQL above 5.6.6. This means that you cannot use the MySQLpackage (version 5.5) from the standard Ubuntu 14.04 repository. Instead,you'll have to:

1.     Add the MySQL official repository

2.     Install the MySQL server, client, andlibraries from it

First, go to the MySQL apt repository page and download the package that will add theMySQL repository to your Ubuntu 14.04 system. You can download the packagedirectly on your Droplet:

·        wgethttps://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb

·         

Next, install it with dpkg:

·        sudo dpkg -imysql-apt-config_0.3.5-1ubuntu14.04_all.deb

·         

When you run the above command, a textmode wizard appears with two questions in it:

·        Which MySQL product do you wish to configure? Answer with Server.

·        Which server version do you wish to receive? Answer with mysql-5.6.

Once you answer these two questions you'llreturn to the first question about which product you wish to install. Answerwith Apply, the bottom choice, to confirm yourchoices and exit the wizard.

Now that you have the new MySQL repo,you'll have to update the apt cache, i.e. the information about the availablepackages for installation in Ubuntu. Thus, when you opt to install MySQL itwill be retrieved from the new repository. To update the apt cache, run thecommand:

·        sudo apt-get update

·         

After that you are ready to install MySQL5.6 on Ubuntu 14.04 with the command:

·        sudo apt-get install mysql-server

·         

Once you run the above command you'll beasked to pick a MySQL root (administrator) password. For convenience, you maychoose not to set a password at this point and when prompted just press ENTER.However, once you decide to turn this server in production, it's recommendedthat you run the commandsudomysql_secure_installation to secure your MySQL installation andconfigure a root password.

When the installation process completesyou will have MySQL server 5.6 installed along with its command line client andnecessary libraries. You can verify it by starting the client with the command:

·        mysql -u root

·         

If you set a password, you will need touse the following command and enter your MySQL root password when prompted:

·        mysql -u root -p

·         

You should see:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.25 MySQL Community Server (GPL)

...

While still in the MySQL monitor (clientterminal), create a new database called test:

·        CREATE DATABASE test;

·         

We'll need this database later for ourtesting.

To exit the MySQL client type:

·        quit

·         

Finally, as a dependency for the memcachedplugin, you will also need to install the development package for theasynchronous event notification library — libevent-dev. To make this happen run the command:

·        sudo apt-get install libevent-dev

·         

Step 2 — Installing the memcached Plugin in MySQL

To prepare for the memcached plugininstallation you first have to execute the queries found in the file/usr/share/mysql/innodb_memcached_config.sql. Start the MySQL client:

·        mysql -u root

·         

or, if you set a password:

mysql -u root -p

and execute:

·        source/usr/share/mysql/innodb_memcached_config.sql;

·         

This will create all the necessarysettings for the plugin in the database innodb_memcache and also insert some example data in our newly createddatabase test.

After that you can perform theinstallation of the memcached plugin from the MySQL terminal with the followingcommand:

·        install plugin daemon_memcached soname"libmemcached.so";

·         

Exit the MySQL session:

·        quit

·         

This installs the memcached plugin whichis found in the directory /usr/lib/mysql/plugin/ in Ubuntu 14.04. This file is availableonly in MySQL version 5.6 and up.

Once the installation is complete, youhave to configure the memcached plugin listener. You will need it to connect tothe memcached plugin. For this purpose, open the file /etc/mysql/my.cnf with your favorite editor like this:

·        sudo vim /etc/mysql/my.cnf

·         

Somewhere after the [mysqld] line add a new line containing:

/etc/mysql/my.cnf

daemon_memcached_option="-p11222 -l 127.0.0.1"

The above configures the memcached pluginlistener on port 11222 enabled only for the loopback IP 127.0.0.1. This meansthat only clients from the Droplet will be able to connect. If you omit thepart about the IP (-l 127.0.0.1), the new listener will be freelyaccessible from everywhere, which is a serious security risk. If you arefurther concerned about the security of the memcached plugin please check its security documentation.

To start the new listener process for thememcached plugin, restart the MySQL server with the command:

·        sudo service mysql restart

·         

Step 3 — Testing the memcached Plugin

To verify the installation is successfulrun the following MySQL command from the MySQL client (start the client with mysql -u root or mysql -u root -p):

·        show plugins;

·         

If everything is fine, you should see inthe output:

| daemon_memcached           | ACTIVE | DAEMON             |libmemcached.so | GPL     |

If you don't see this, make sure that youare using MySQL version 5.6 or up and that you have followed the installationinstructions precisely.

You can also try to connect to the newmemcached plugin interface with Telnet from your Droplet like this:

·        telnet localhost 11222

·         

Upon success you should see output suchas:

Connected to localhost.

Escape character is '^]'.

Now you can run a generic command such as stats, for statistics,to see how this connection works. To exit the prompt press simultaneously thecombination of CTRL and ] on your keyboard. After that typequit to exit the Telnet client itself.

Telnet gives you simplest way to connectto the memcached plugin and to the MySQL data itself. It is good for testing,but when you decide to use it professionally you should use the readily availablelibraries for popular programming languages like PHP and Python.

Step 4 — Running NoSQL Queries in MySQL via memcachedPlugin

If you go back to the installation part ofthe memcached plugin in this article, you will see that we executed thestatements from the file /usr/share/mysql/innodb_memcached_config.sql. These statements created a new table demo_test in the test database. The demo_test table has the following columns in compliance with thememcached protocol:

·        c1 implements the key field.

·        c2 implements the value field.

·        c3 implements the flag field.

·        c4 implements the CAS field.

·        c5 implements the expiration field.

The table demo_test will be the one we'll be testing with. First, let's openthe database/table with the MySQL client with the following command:

·        mysql -u roottest

·         

Or, if you have a MySQL password set:

·        mysql -u roottest -p

·         

There should be already one row in the demo_test table:

·        SELECT * FROM demo_test;

·         

The results should look like:

+-------------+--------------+------+------+------+

| c1          | c2           | c3   | c4  | c5   |

+-------------+--------------+------+------+------+

| AA          | HELLO, HELLO |    8 |   0 |    0 |

+-------------+--------------+------+------+------+

1 rows in set (0.00 sec)

 

Exit the MySQL session:

·        quit

·         

Now, let's create a second record usingthe memcached NoSQL interface and telnet. Connect again to localhost on TCPport 11222:

·        telnet localhost 11222

·         

Then use the following syntax:

set [key] [flag] [expiration] [length in bytes]

[value]

Note that the value has to be on a new row. Also, for eachrecord you have to specify the length in bytes for the value when working inthe above manner.

As an example, let's create a new item(database row) with key newkey, value 0 for flag, and value 0 for expiration (never to expire). The value will be 12bytes in length.

set newkey 0 0 12

NewTestValue

Of course, you can also retrieve valuesvia this NoSQL interface. This is done with the get command which is followed by the name of the key you wantto retrieve. While still in the Telnet session, type:

get newkey

The result should be:

VALUE newkey 0 12

NewTestValue

The above set and get commands are valid for every memcached server. These werejust a few simple examples how to insert and retrieve records in a NoSQL style.

Now let's connect again to the MySQLclient with the command mysql -u root test ormysql -u root test -p and see the content of the demo_test table again with run the qyery:

·        SELECT * FROM demo_test WHEREc1="newkey";

·         

There you should see the newly created rowlike this:

+--------+--------------+------+------+------+

| c1     | c2           | c3   | c4  | c5   |

+--------+--------------+------+------+------+

| newkey | NewTestValue |    0|    1 |    0 |

+--------+--------------+------+------+------+

By now you may wonder how the memcachedplugin knows which database and table to connect to and how to map informationto the table columns. The answer is in the database innodb_memcache and its tablecontainers.

Execute this select statement:

·        select * from containers \G

·         

You will see the following:

*************************** 1. row ***************************

                  name: aaa

             db_schema: test

              db_table: demo_test

           key_columns: c1

         value_columns: c2

                 flags: c3

            cas_column: c4

    expire_time_column: c5

unique_idx_name_on_key: PRIMARY

1 row in set (0.00 sec)

To learn more on how to create differentmappings and find out advanced features of the memcached plugin please checkout the memcached plugin internals page.

Benefits of Integrating MySQL with the memcached Plugin

The above information and examples outlinea few important benefits of integrating MySQL with NoSQL through the memcachedplugin:

·        All your data (MySQL and NoSQL) can be kept in one place.You don't have to install and maintain additional software for NoSQL data.

·        Data persistence, recovery, and replication for NoSQLdata is possible thanks to the powerful InnoDB storage engine.

·        The incredibly fast memcached data access layer can bestill used so that you can work with higher volumes of information compared towhen working with the slower MySQL client.

·        NoSQL data can be managed with MySQL interface andsyntax. Thus you can include NoSQL data in more complex SQL queries such asleft joins.

Conclusion

By the end of this article you should beacquainted with the new possibilities for working with NoSQL data provided byMySQL. This may not be an universal solution to replace dedicated NoSQL serverssuch as MongoDB, but it certainly has its merits.