{"id":56,"date":"2015-11-09T07:04:40","date_gmt":"2015-11-09T07:04:40","guid":{"rendered":"http:\/\/cloudkul.com\/blog\/?p=56"},"modified":"2017-04-13T08:20:46","modified_gmt":"2017-04-13T08:20:46","slug":"cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached","status":"publish","type":"post","link":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/","title":{"rendered":"Cache MySql data using Memcached : A more practical approach to Memcached"},"content":{"rendered":"

In my previous blog, i talked about the brief introduction of memcached and how does it help us to make database access faster in most of the web applications. If you have\u2019nt read the blog yet, you can follow the link:- http:\/\/cloudkul.com\/blog\/memcached-a-distributed-memory-object-caching-system<\/a><\/p>\n

Today, let us focus on some practical aspects of memcached. I will start with the basic memcached installation on Ubuntu 14.04 followed by a practical approach to cache data from MySQL using Memcached and PHP script.<\/p>\n

Here we go !!<\/p>\n

PREREQUISITES:<\/strong><\/p>\n

Before we get started, you must have a ubuntu machine with the basic LAMP server configured on it.<\/p>\n

INSTALL MEMCACHED ON UBUNTU 14.04 :<\/strong><\/p>\n

Run the following commands to install memcached and it\u2019s component :-<\/p>\n

sudo apt-get update\r\nsudo apt-get install memcached php5-memcached<\/pre>\n
Restart Memcached service using the following command :-<\/p>\n
service memcached restart<\/pre>\n
CHECK THE INSTALLATION :<\/strong><\/p>\n
In order to verify the installation, create a info.php file in apache\u2019s default document root i.e \/var\/www\/html :-<\/p>\n
sudo nano \/var\/www\/html\/info.php<\/pre>\n
To call a php function that collects and prints information about our server into web browser, write out the following in this file :-<\/p>\n
<?php\r\n\r\nphpinfo();\r\n\r\n?><\/pre>\n
Hit the URL :-<\/strong><\/p>\n
http:\/\/server_domain_name_or_IP\/info.php<\/pre>\n
If you scroll down or search for the \u201cmemcached\u201d section header, you should find something that looks like this:<\/p>\n
\"\"<\/a><\/p>\n
This means that the memcached extension is enabled and being found by the web server.<\/p>\n
Also check whether memcached service is running by typing the following command :-<\/p>\n
ps aux | grep memcached<\/pre>\n
CREATE SAMPLE DATA IN MYSQL :<\/strong><\/p>\n
Login into mysql :-<\/p>\n
mysql -u root -p<\/pre>\n
Create a database, give it a name of your choice and select it for further use.Here, we have named it as \u2018memcached_test\u2019.Run the following commands on MySQL prompt :-<\/p>\n
CREATE DATABASE memcached_test;\r\n\r\nUSE memcached_test;<\/pre>\n
Create a user \u2018test\u2019 with a password \u2018mytesting123\u2019 that has access to the database we created :-<\/p>\n
GRANT ALL ON memcached_test.* TO test@localhost IDENTIFIED BY \u2018mytesting123\u2019;<\/pre>\n
Create a table in database and insert some sample data using the following commands :-<\/p>\n
CREATE TABLE sample_data (id int, name varchar(20));\r\n\r\nINSERT INTO sample_data VALUES (100, \u201cJoe Phillips\u201d);<\/pre>\n
Exit MySQL by simply typing exit command.<\/p>\n
CREATE A PHP SCRIPT TO CACHE MYSQL DATA :<\/strong><\/p>\n
Create a php script named \u2018database_test.php\u2019 in our document root as :-<\/p>\n
sudo nano \/var\/www\/html\/database_test.php<\/pre>\n
Now,we\u2019re going to create a PHP memcached instance and then tell it where the memcached service running on our server is located. Memcached runs on 11211 port by default.Write out the following lines in database_test.php :-<\/p>\n
<?php\r\n\r\n$mem = new Memcached();\r\n\r\n$mem->addServer(\u201c127.0.0.1\u201d, 11211);\r\n\r\n?><\/pre>\n
Next, we have to define how PHP can connect to our MySQL database.We need to specify the login credentials for the user we created and then we\u2019ll need to tell it which database to use :-<\/p>\n
<?php\r\n\r\n$mem = new Memcached();\r\n\r\n$mem->addServer(\u201c127.0.0.1\u201d, 11211);\r\n\r\nmysql_connect(\u201clocalhost\u201d, \u201ctest\u201d, \u201cmytesting123\u201d) or die(mysql_error());\r\n\r\nmysql_select_db(\u201cmemcached_test\u201d) or die(mysql_error());\r\n\r\n?><\/pre>\n
In order to fetch the data we have inserted into database, we have to create a query and store it in a $query variable.Also,create a $querykey variable to store the key that memcached will use to reference our information.<\/p>\n
<?php\r\n\r\n$mem = new Memcached();\r\n\r\n$mem->addServer(\u201c127.0.0.1\u201d, 11211);\r\n\r\nmysql_connect(\u201clocalhost\u201d, \u201ctest\u201d, \u201cmytesting123\u201d) or die(mysql_error());\r\n\r\nmysql_select_db(\u201cmemcached_test\u201d) or die(mysql_error());\r\n\r\n$query = \u201cSELECT name FROM sample_data WHERE id = 100\u201d;\r\n\r\n$querykey = \u201cKEY\u201d . md5($query);\r\n\r\n?><\/pre>\n
Next, we\u2019ll create a $result variable that will hold the result from our memcached query.We are now ready to implement the actual testing logic that will determine what will happen when the result is found in memcached. If the results are found, we want to print the data that we pulled out and tell the user that we were able to retrieve it from memcached directly:<\/p>\n
<?php\r\n\r\n$mem = new Memcached();\r\n\r\n$mem->addServer(\u201c127.0.0.1\u201d, 11211);\r\n\r\nmysql_connect(\u201clocalhost\u201d, \u201ctest\u201d, \u201cmytesting123\u201d) or die(mysql_error());\r\n\r\nmysql_select_db(\u201cmemcached_test\u201d) or die(mysql_error());\r\n\r\n$query = \u201cSELECT name FROM sample_data WHERE id = 100\u201d;\r\n\r\n$querykey = \u201cKEY\u201d . md5($query);\r\n\r\n$result = $mem->get($querykey);\r\n\r\nif ($result) {\r\n\r\nprint \u201c<p>Data was: \u201d . $result[0] . \u201c<\/p>\u201d;\r\n\r\nprint \u201c<p>Caching success!<\/p><p>Retrieved data from memcached!<\/p>\u201d;\r\n\r\n}\r\n\r\n?><\/pre>\n
If the results are not found, we want to use the query that we crafted to ask MySQL for the data. We will store this into the $result variable we made.After we have the result of the query, we need to add that result to memcached so that the data will be there the next time.<\/p>\n
In order to accomplish this, we have to supply the key $querykey variable to reference the data, the data itself as stored in $result variable and the time to cache the data in seconds.<\/p>\n
<?php\r\n\r\n$mem = new Memcached();\r\n\r\n$mem->addServer(\u201c127.0.0.1\u201d, 11211);\r\n\r\nmysql_connect(\u201clocalhost\u201d, \u201ctest\u201d, \u201ctesting123\u201d) or die(mysql_error());\r\n\r\nmysql_select_db(\u201cmem_test\u201d) or die(mysql_error());\r\n\r\n$query = \u201cSELECT name FROM sample_data WHERE id = 1\u201d;\r\n\r\n$querykey = \u201cKEY\u201d . md5($query);\r\n\r\n$result = $mem->get($querykey);\r\n\r\nif ($result) {\r\n\r\nprint \u201c<p>Data was: \u201d . $result[0] . \u201c<\/p>\u201d;\r\n\r\nprint \u201c<p>Caching success!<\/p><p>Retrieved data from memcached!<\/p>\u201d;\r\n\r\n}\r\n\r\nelse {\r\n\r\n$result = mysql_fetch_array(mysql_query($query)) or die(mysql_error());\r\n\r\n$mem->set($querykey, $result, 20);\r\n\r\nprint \u201c<p>Data was: \u201d . $result[0] . \u201c<\/p>\u201d;\r\n\r\nprint \u201c<p>Data not found in memcached.<\/p><p>Data retrieved from MySQL and stored in memcached for next time.<\/p>\u201d;\r\n\r\n}\r\n\r\n?><\/pre>\n
This is our completed script. First,it will attempt to get data from memcached and print it on the screen. If the data could not be found in memcached, it will query from MySQL directly and cache the results for 20 seconds.<\/p>\n
TEST THE SCRIPT :<\/strong><\/p>\n
Hit the URL :-<\/p>\n
http:\/\/server_domain_name_or_IP\/database_test.php<\/pre>\n
The first time we visit the page, we should see output :-<\/p>\n
\"\"<\/a><\/p>\n
\ufffcIf we refresh this within 20 seconds,the page should now display the different message :-<\/p>\n
\"\"<\/a><\/p>\n
\ufffcAfter waiting for a while, the cache content will expire and will be removed from memcached again. If we refresh at this point, it will display the first message again since the server must go back to the database to retrieve the data values.<\/p>\n
By now, you might have an idea of how memcached works and how you can utilize it to keep your web server from hitting the database repeatedly for the same content.<\/p>\n
 <\/p>\n
FOR ANY TYPE OF QUERY OR HELP, KINDLY CONTACT US<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
In my previous blog, i talked about the brief introduction of memcached and how does […]<\/a><\/p>\n","protected":false},"author":5,"featured_media":332,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"yoast_head":"\nCache MySql data using Memcached : A more practical approach to Memcached - Cloudkul<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cache MySql data using Memcached : A more practical approach to Memcached - Cloudkul\" \/>\n<meta property=\"og:description\" content=\"In my previous blog, i talked about the brief introduction of memcached and how does [...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/\" \/>\n<meta property=\"og:site_name\" content=\"Cloudkul\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-09T07:04:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T08:20:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cloudkul.com\/blog\/wp-content\/uploads\/2015\/11\/Check-Memcached-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"848\" \/>\n\t<meta property=\"og:image:height\" content=\"422\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Naina Johari\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/\",\"url\":\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/\",\"name\":\"Cache MySql data using Memcached : A more practical approach to Memcached - Cloudkul\",\"isPartOf\":{\"@id\":\"https:\/\/cloudkul.com\/blog\/#website\"},\"datePublished\":\"2015-11-09T07:04:40+00:00\",\"dateModified\":\"2017-04-13T08:20:46+00:00\",\"author\":{\"@id\":\"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/38f7cddff574c7fe989d6ca2df61fc57\"},\"breadcrumb\":{\"@id\":\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cloudkul.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cache MySql data using Memcached : A more practical approach to Memcached\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cloudkul.com\/blog\/#website\",\"url\":\"https:\/\/cloudkul.com\/blog\/\",\"name\":\"Cloudkul\",\"description\":\"Host your eCommerce Store on AWS with Optimized Performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cloudkul.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/38f7cddff574c7fe989d6ca2df61fc57\",\"name\":\"Naina Johari\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c7a0be3afff58963975900f809e57046?s=96&d=https%3A%2F%2Fs.gravatar.com%2Favatar%2F6148c37469011bc2f8e491ca8f5de495%3Fs%3D80&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c7a0be3afff58963975900f809e57046?s=96&d=https%3A%2F%2Fs.gravatar.com%2Favatar%2F6148c37469011bc2f8e491ca8f5de495%3Fs%3D80&r=g\",\"caption\":\"Naina Johari\"},\"url\":\"https:\/\/cloudkul.com\/blog\/author\/naina-johari379\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Cache MySql data using Memcached : A more practical approach to Memcached - Cloudkul","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/","og_locale":"en_US","og_type":"article","og_title":"Cache MySql data using Memcached : A more practical approach to Memcached - Cloudkul","og_description":"In my previous blog, i talked about the brief introduction of memcached and how does [...]","og_url":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/","og_site_name":"Cloudkul","article_published_time":"2015-11-09T07:04:40+00:00","article_modified_time":"2017-04-13T08:20:46+00:00","og_image":[{"width":848,"height":422,"url":"https:\/\/cloudkul.com\/blog\/wp-content\/uploads\/2015\/11\/Check-Memcached-1.png","type":"image\/png"}],"author":"Naina Johari","twitter_card":"summary_large_image","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/","url":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/","name":"Cache MySql data using Memcached : A more practical approach to Memcached - Cloudkul","isPartOf":{"@id":"https:\/\/cloudkul.com\/blog\/#website"},"datePublished":"2015-11-09T07:04:40+00:00","dateModified":"2017-04-13T08:20:46+00:00","author":{"@id":"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/38f7cddff574c7fe989d6ca2df61fc57"},"breadcrumb":{"@id":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/cloudkul.com\/blog\/cache-mysql-data-using-memcached-a-more-practical-approach-to-memcached\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cloudkul.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Cache MySql data using Memcached : A more practical approach to Memcached"}]},{"@type":"WebSite","@id":"https:\/\/cloudkul.com\/blog\/#website","url":"https:\/\/cloudkul.com\/blog\/","name":"Cloudkul","description":"Host your eCommerce Store on AWS with Optimized Performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cloudkul.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/38f7cddff574c7fe989d6ca2df61fc57","name":"Naina Johari","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cloudkul.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c7a0be3afff58963975900f809e57046?s=96&d=https%3A%2F%2Fs.gravatar.com%2Favatar%2F6148c37469011bc2f8e491ca8f5de495%3Fs%3D80&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c7a0be3afff58963975900f809e57046?s=96&d=https%3A%2F%2Fs.gravatar.com%2Favatar%2F6148c37469011bc2f8e491ca8f5de495%3Fs%3D80&r=g","caption":"Naina Johari"},"url":"https:\/\/cloudkul.com\/blog\/author\/naina-johari379\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/posts\/56"}],"collection":[{"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":15,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":419,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions\/419"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/media\/332"}],"wp:attachment":[{"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloudkul.com\/blog\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}