Cache MySQL Data with the PHP Memcached Library
Introduction
Memcached is a distributed memory caching system that you can use to speed up your web applications. Memcached works as a high-speed data storage layer since it utilizes your server's RAM (Random Access Memory), which is several times faster than even the fastest SSD (Solid State Drive).
When clients connect to your web application for the first time, Memcached connects to your primary storage location and caches the data in a key-value memory to serve future data requests faster. This reduces disk I/O operations and makes Memcached one of the best applications for caching data.
In this guide, you'll use the php-memcache library to cache and retrieve MySQL data with PHP on Ubuntu 20.04. You'll then output the data in JSON (JavaScript Object Notation) format, which is easy for machines and humans to read and write.
Prerequisites
To follow along with this guide, make sure that you have got the following requirements:
- An Ubuntu 20.04 server.
- A sudo user.
- A LAMP Stack.
- A Memcached Server.
Install the php-memcache Library
First, SSH to your server and install the php-memcache library. This is a PHP module that allows you to use the Memcached functionalities inside your PHP code.
$ sudo apt update
$ sudo apt install -y php-memcacheRestart the Apache web server to load the php-memcache library.
$ sudo systemctl restart apache2Set Up a test_db Database
Next, connect to your MySQL server as a root user.
$ sudo mysql -u root -pThen, key in the root password of your MySQL server and press Enter to continue. Once you get the mysql> prompt, run the command below to create a test_db database.
mysql> CREATE DATABASE test_db;Create a non-root test_db_user user for your MySQL server. Remember to change EXAMPLE_PASSWORD with a strong value. You'll use these credentials to connect to MySQL with PHP.
mysql> CREATE USER 'test_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'localhost';
mysql> FLUSH PRIVILEGES;If you're using the MariaDB server as a drop-in replacement for MySQL, change the command above to the syntax below to create the test_db_user user.
MariaDB> GRANT ALL PRIVILEGES on test_db.* TO 'test_db_user'@'localhost' identified by 'EXAMPLE_PASSWORD';Switch to the test_db database.
mysql> USE test_db;Next, create a products table. This table will store data persistently in your MySQL database. Since MySQL data stores data into your server's disk, it might become slow over time when you add thousands of products or when your web application goes viral, and several users are hitting the database server simultaneously. Later in this guide, you will create a cache for this table using the Memcached server in a PHP file.
For now, just set up the products table.
mysql> CREATE TABLE products
(
product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
retail_price DOUBLE
) ENGINE = InnoDB;Populate the products table with some records.
mysql> INSERT INTO products(product_name, retail_price) VALUES ('LEATHER JACKET', '99.95');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('LED MOUSE', '22.65');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('MOUSE PAD', '4.95');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('PURE COTTON BUDS', '2.85');Run a SELECT statement against the products table to ensure the data is in place.
mysql> SELECT
product_id,
product_name,
retail_price
FROM products;Make sure all the items are displayed as shown below.
+------------+------------------+--------------+
| product_id | product_name | retail_price |
+------------+------------------+--------------+
| 1 | LEATHER JACKET | 99.95 |
| 2 | LED MOUSE | 22.65 |
| 3 | MOUSE PAD | 4.95 |
| 4 | PURE COTTON BUDS | 2.85 |
+------------+------------------+--------------+
4 rows in set (0.00 sec)Exit from the MySQL command-line interface.
mysql> QUIT;You've defined a database, created a sample table, and populated it with some data. You'll now create a PHP script that connects to your server to fetch and display data in JSON format.
Create a PHP File
Use nano to create a new /var/www/html/products.php file in the root directory of your web server.
$ sudo nano /var/www/html/products.phpNext, open a new <?php tag and add a Content-Type:application/json to inform web clients to treat the returned data from this file in JSON format.
<?php
header("Content-Type:application/json");Then, open a new try {.. block and declare the database variables. Match the database variables with the database name, user, and password that you created earlier in this guide.
try {
$db_name = 'test_db';
$db_user = 'test_db_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';Next, initiate a new Memcache class and call the addServer method to connect to your Memcached server on port 11211.
$memcache = new Memcache();
$memcache->addServer("127.0.0.1", 11211);Then define a SELECT SQL statement. You'll use this command to query the products table and retrieve the items stored in your MySQL server.
$sql = 'SELECT
product_id,
product_name,
retail_price
FROM products
';Next, create a new name for your Memcached key by passing your SQL statement's value in an md5 function. This creates a small key that is easier to troubleshoot in case of a problem.
$key = md5($sql);Next, type the code snippet below to check if the Memcached server contains a key with the name that you've generated above. In case the key doesn't contain a null value(!= null), return the cached data from the Memcached server. Then, use the $response['Memcache Data'] statement to mark your data so that you know when the script returns it from the cache.
$cached_data = $memcache->get($key);
$response = [];
if ($cached_data != null) {
$response['Memcache Data'] = $cached_data;Otherwise, use the code below to retrieve data from the MySQL server using the PDO Library. After retrieving the data for the first time from the MySQL database, use the $memcache->set($key, $response, false, 5); command to cache the data for 5 seconds. You may increase this time to live value depending on your business logic. Then, use the $response['MySQL Data'] statement to mark data when it is returned from the MySQL server.
} else {
$pdo = new PDO("mysql:host=" . $db_host . ";dbname=" . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare($sql);
$stmt->execute();
$products = [];
while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
$products[] = $row;
}
$memcache->set($key, $products, false, 5);
$response['MySQL Data'] = $products;
}Finally, echo out the JSON encoded data either from the MySQL or the Memcached Cache. In case of any PDO error, catch it and display it as well.
echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
} catch(PDOException $e) {
$error = [];
$error['message'] = $e->getMessage();
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
}When you've entered everything into your /var/www/html/products.php file it should be similar to the following content.
<?php
header("Content-Type:application/json");
try {
$db_name = 'test_db';
$db_user = 'test_db_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$memcache = new Memcache();
$memcache->addServer("127.0.0.1", 11211);
$sql = 'SELECT
product_id,
product_name,
retail_price
FROM products
';
$key = md5($sql);
$cached_data = $memcache->get($key);
$response = [];
if ($cached_data != null) {
$response['Memcache Data'] = $cached_data;
} else {
$pdo = new PDO("mysql:host=" . $db_host . ";dbname=" . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare($sql);
$stmt->execute();
$products = [];
while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
$products[] = $row;
}
$memcache->set($key, $products, false, 5);
$response['MySQL Data'] = $products;
}
echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
} catch(PDOException $e) {
$error = [];
$error['message'] = $e->getMessage();
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
}Save and close the file when you're done by pressing Ctrl + X, then Y and Enter. The proceed to the next step to see if the file works as expected.
Test the Memcached Data
Call the products.php file two times to check the output returned. To do this, run the curl command below.
$ curl http://localhost/products.phpWhen you request the data for the first time, it should come from the MySQL server("MySQL Data" [...]) as shown below.
{
"MySQL Data": [
{
"product_id": 1,
"product_name": "LEATHER JACKET",
"retail_price": 99.95
},
{
"product_id": 2,
"product_name": "LED MOUSE",
"retail_price": 22.65
},
{
"product_id": 3,
"product_name": "MOUSE PAD",
"retail_price": 4.95
},
{
"product_id": 4,
"product_name": "PURE COTTON BUDS",
"retail_price": 2.85
}
]
}Run the same again before the Memcached key expires.
$ curl http://localhost/products.phpThis time around, your data should come from Memcache("Memcache Data": [...]).
{
"Memcache Data": [
{
"product_id": 1,
"product_name": "LEATHER JACKET",
"retail_price": 99.95
},
{
"product_id": 2,
"product_name": "LED MOUSE",
"retail_price": 22.65
},
{
"product_id": 3,
"product_name": "MOUSE PAD",
"retail_price": 4.95
},
{
"product_id": 4,
"product_name": "PURE COTTON BUDS",
"retail_price": 2.85
}
]
}As you can see from the outputs above, your PHP file is working as expected and you're able to cache data with the Memcached server.
Conclusion
In this tutorial, you've used the PHP php-memcache library to cache and display MySQL data in JSON format on Ubuntu 20.04 server. You may use the caching logic in this guide to speed up your web applications or dynamic websites that use MySQL as a backend server.