How To Set Up Redis as a Cache for MySQL with PHP on Ubuntu 20.04

Here is the article.

By FRANCIS NDUNGU

Introduction

Redis (Remote Dictionary Server) is a fast open-source, in-memory database that you can use as a key-value store for a highly scalable and performance-oriented system. Some of Redis’ use cases include: caching, high-speed transactions, real-time analytics, live notifications, machine learning, searching, and queue/job processing. Since Redis is an in-memory key-value store, its performance makes it suitable for caching data in your application.

Caching is storing data temporarily in a high-speed storage layer (for example, in a computer RAM) to serve data faster when clients make the same future requests. This enhances the re-use of previously computed data instead of fetching it each time from the disk.

When you’re working with PHP and MySQL, using Redis as a cache improves your application performance because Redis stores data in RAM, which is several times faster than a hard disk (HDD) or a solid-state drive (SSD). Caching also reduces database costs—that is, the number of round trips made to the back-end database—and avoids overloading the backend.

Caching data is an integral design feature when you’re designing web applications with higher reads than writes. Such applications include blogs, online stores, and social media sites.

In this tutorial, you’ll use Redis to cache MySQL data with PHP on Ubuntu 20.04.

Prerequisites

To complete this tutorial, you’ll need the following:

• An Ubuntu 20.04 server with a non-root user with sudo privileges. Set this up following the Initial Server Setup with Ubuntu 20.04 guide.

• A LAMP stack. Follow the How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 20.04 tutorial. For this guide, you may skip Step 4 — Creating a Virtual Host for your Website.

• A Redis Server set up by following the How To Install and Secure Redis on Ubuntu 20.04 - Quickstart tutorial.

Step 1 — Installing the Redis Library for PHP

To begin you’ll install the php-redis extension, which will allow you to use PHP to communicate with Redis. Run the following commands to update your server and install the extension:

sudo apt update
sudo apt install php-redis

Confirm the installation and restart the Apache web server to load the extension:

sudo systemctl restart apache2

Now that you have installed your dependencies, you’ll set up your database.

Step 2 — Setting Up a Test Database, Table, and Sample Data

In this step, you’ll create a MySQL database to store data permanently to disk. You’ll also create some tables and a user account with full privileges to the database.

First, log in to your MySQL server as a root user:

sudo mysql -u root -p

Enter the root password of your MySQL server that you set up in the LAMP prerequisite. Then, press ENTER to continue.

Next, create a test_store database with the following command:

CREATE database test_store;

Make sure the action is successful by confirming the output:

Output
Query OK, 1 row affected (0.00 sec)

Next, create a user for your database. We’ll call this user test_user in this tutorial. Replace PASSWORD with a strong password as well:

CREATE USER 'test_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Then grant test_user full privileges to the test_store database with:

GRANT ALL PRIVILEGES ON test_store.* TO 'test_user'@'localhost';

Finally run the following command to reload the grant tables in MySQL:

FLUSH PRIVILEGES;

Ensure you get the following output after each successful command:

Output
Query OK, 0 rows affected (0.01 sec)

End the MySQL root session:

quit;

You’ll receive the word Bye and the system will take you back to the server’s command line interface.

Log back in to the MySQL server with the credentials for the test_user that you just created:

mysql -u test_user -p

Enter the password for the test_user to proceed. Then, switch to the test_store database when you’re in the mysql> prompt:

USE test_store;

Ensure you receive the following output:

Output
Database Changed.

Next, you’ll create a products table with three columns. You’ll use the product_id column to uniquely identify each product. To avoid assigning the IDs manually, you’ll use the AUTO_INCREMENT keyword. Then, you’ll use the BIGINT data type for the product_id column to support a large data set. The BIGINT data type can hold a minimum value of -2^63 and a maximum value of 2^63 - 1.

The product_name field will hold the actual names of your items. In this case, a VARCHAR data type with a length of 50 characters will be enough. The last column in the products table is the price—you’ll use the DOUBLE data type to accommodate prices with decimals (for example, 16.33).

To create the products table, run the following command:

CREATE table products
(
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(50),
price DOUBLE
) Engine = InnoDB;

You will receive the following output:

Output
Query OK, 0 rows affected (0.01 sec)

Now you’ll populate the products table with some records for testing purposes.

You don’t need to enter data to the product_id column manually since the AUTO_INCREMENT column will complete this. Run the following commands one by one:

INSERT INTO products(product_name, price) VALUES ('Virtual Private Servers', '5.00');
INSERT INTO products(product_name, price) VALUES ('Managed Databases', '15.00');
INSERT INTO products(product_name, price) VALUES ('Block Storage', '10.00');
INSERT INTO products(product_name, price) VALUES ('Managed Kubernetes', '60.00');
INSERT INTO products(product_name, price) VALUES ('Load Balancer', '10.00');

After running each command, ensure you get this output:

Output
Query OK, 1 row affected (0.00 sec)

Verify the data using the SELECT command:

SELECT * FROM products;

You will receive output similar to the following:

Output
+------------+-------------------------+-------+
| product_id | product_name            | price |
+------------+-------------------------+-------+
|          1 | Virtual Private Servers |     5 |
|          2 | Managed Databases       |    15 |
|          3 | Block Storage           |    10 |
|          4 | Managed Kubernetes      |    60 |
|          5 | Load Balancer           |    10 |
+------------+-------------------------+-------+
5 rows in set (0.00 sec)

End the MySQL session for the test_user:

quit;

Once you’ve set up the test_store database, products table, and test_user, you’ll code a PHP script to retrieve data from the MySQL database and cache it to Redis.

Step 3 — Designing a PHP Script for Fetching and Caching MySQL Data

In this step, you’ll create a PHP script for retrieving the sample data that you’ve created in the previous step.

When you run the script for the first time, it will read the data from MySQL (that is, from disk) and then cache it to Redis. As a result subsequent reads of the products’ data will be from Redis (that is, from system RAM). System memory is multiple times faster than even the fastest solid-state drive, thus data will be retrieved faster from the Redis cache than reading from the system disk.

Note: While you might not get any performance boost, since you are retrieving just a few records from the MySQL database, several benchmarks prove that retrieving cached data from Redis is several times faster than reading it from MySQL when dealing with several hundred thousand records.

Create a products.php file in the root directory of your website:

sudo nano /var/www/html/products.php

To start, enter the following information to connect and create an instance of Redis and store it as an object in a $redis variable.

The address 127.0.0.1 connects to the localhost. You may change this value if you’re running Redis from a remote server. Remember to replace REDIS_PASSWORD with the specific password for Redis set in the /etc/redis/redis.conf configuration file.

Also, enter the appropriate port number. By default, Redis runs on port 6379:

/var/www/html/products.php

<?php

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('REDIS_PASSWORD');

Note: In this guide, the $redis->auth('REDIS_PASSWORD') command sends your password to Redis in plain text. In a production environment, you may consider securing end-to-end communication between Redis and the client server running PHP code with a more powerful access control layer, such as TLS (Transport Layer Security). Also, when configuring your Redis password in the /etc/redis/redis.conf file, make sure you set a long and strong value to prevent brute-force attacks.

The next step is initializing a PHP variable you’ll use as a key in Redis.

As mentioned earlier in this guide, Redis acts as a key-value database and therefore you must have a unique key for the data that you intend to store and retrieve from it.

So, define a PRODUCTS key by adding the following information to the /var/www/html/products.php file. You are free to use any name in place of PRODUCTS key.

Your PHP script will use this key to cache information to Redis once data gets retrieved from the MySQL database:

/var/www/html/products.php

...
$key = 'PRODUCTS';

Next, include a conditional PHP if...else statement to check if the PRODUCTS key exists in Redis:

/var/www/html/products.php

...
if (!$redis->get($key)) {
    $source = 'MySQL Server';
    $database_name     = 'test_store';
    $database_user     = 'test_user';
    $database_password = 'PASSWORD';
    $mysql_host        = 'localhost';

    $pdo = new PDO('mysql:host=' . $mysql_host . '; dbname=' . $database_name, $database_user, $database_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM products";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $products[] = $row;
    }

    $redis->set($key, serialize($products));
    $redis->expire($key, 10);

} else {
     $source = 'Redis Server';
     $products = unserialize($redis->get($key));

}

echo $source . ': <br>';
print_r($products);

If the key doesn’t exist in Redis, the script connects to the database that you created earlier, queries the products table, and stores the data in Redis using the $redis->set($key, serialize($products)) command.

The $redis->expire($key, 10); command sets the expiration to 10 seconds. You may tweak this value depending on your cache policy.

The $source variable helps you to identify the source of the data once it is echoed as an array at the end of the script using the echo $source and print_r($products) commands.

Once you’ve put everything together, your /var/www/html/products.php file will be as follows:

/var/www/html/products.php

<?php

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('REDIS_PASSWORD');

$key = 'PRODUCTS';

if (!$redis->get($key)) {
    $source = 'MySQL Server';
    $database_name     = 'test_store';
    $database_user     = 'test_user';
    $database_password = 'PASSWORD';
    $mysql_host        = 'localhost';

    $pdo = new PDO('mysql:host=' . $mysql_host . '; dbname=' . $database_name, $database_user, $database_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM products";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $products[] = $row;
    }

    $redis->set($key, serialize($products));
    $redis->expire($key, 10);

} else {
     $source = 'Redis Server';
     $products = unserialize($redis->get($key));

}

echo $source . ': <br>';
print_r($products);

Save and close the file.

You’ve now set up a PHP script that will connect to MySQL and cache data to Redis. You’ll test your script in the next step.

Step 4 — Testing the PHP Script

To test if Redis is caching data from the MySQL database, you’ll enter the path of the PHP script in a browser window. Remember to replace your_server_IP with the public IP address of your server, like so: http://your_server_IP/products.php.

When you run the script for the first time, you will receive the following output that displays data from the MySQL database because, at this point, the PHP script has not yet cached any data in Redis:

MySQL Server
Array ( [0] => Array ( [product_id] => 1 [product_name] => Virtual Private Servers [price] => 5 ) [1] => Array ( [product_id] => 2 [product_name] => Managed Databases [price] => 15 ) [2] => Array ( [product_id] => 3 [product_name] => Block Storage [price] => 10 ) [3] => Array ( [product_id] => 4 [product_name] => Managed Kubernetes [price] => 60 ) [4] => Array ( [product_id] => 5 [product_name] => Load Balancer [price] => 10 ) )

Once you run the script again, you’ll get an output confirming that it’s reading data from Redis, which is acting as a cache for MySQL.

Redis Server
Array ( [0] => Array ( [product_id] => 1 [product_name] => Virtual Private Servers [price] => 5 ) [1] => Array ( [product_id] => 2 [product_name] => Managed Databases [price] => 15 ) [2] => Array ( [product_id] => 3 [product_name] => Block Storage [price] => 10 ) [3] => Array ( [product_id] => 4 [product_name] => Managed Kubernetes [price] => 60 ) [4] => Array ( [product_id] => 5 [product_name] => Load Balancer [price] => 10 ) )

Remember that the key will expire after 10 seconds and data will again be retrieved from MySQL.

Conclusion

In this guide, you’ve used Redis to cache MySQL data with PHP on Ubuntu 20.04. You may use the coding in this guide to set up a caching mechanism for your MySQL data, which is especially useful for high-traffic web applications.

 

XAMPP tutorial: installation and first steps

XAMPP is the title used for a compilation of free software. The name is an acronym, with each letter representing one of the five key components. The software packet contains the web server Apache, the relational database management system MySQL (or MariaDB), and the scripting languages Perl and PHP. The initial X stands for the operating systems that it works with: Linux, Windows, and Mac OS X.

• Apache: ihe open source web server Apache is the most widely used server worldwide for delivery of web content. The server application is made available as a free software by the Apache Software Foundation.

• MySQL/MariaDB: in MySQL, XAMPP contains one of the most popular relational database management systems in the world. In combination with the web server Apache and the scripting language PHP, MySQL offers data storage for web services. Current XAMPP versions have replaced MySQL with MariaDB (a community-developed fork of the MySQL project, made by the original developers).

• PHP: the server-side programming language PHP enables users to create dynamic websites or applications. PHP can be installed on all platforms and supports a number of diverse database systems.

• Perl: the scripting language Perl is used in system administration, web development, and network programming. Like PHP, Perl also enables users to program dynamic web applications.

Alongside these core components, this free-to-use Apache distribution contains some other useful tools, which vary depending on your operating system. These tools include the mail server Mercury, the database administration tool phpMyAdmin, the web analytics software solutions Webalizer, OpenSSL, and Apache Tomcat, and the FTP servers FileZilla or ProFTPd.

PHPMyAdmin | Bringing MySQL to the web

 phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.

phpMyAdmin comes with a wide range of documentation and users are welcome to update our wiki pages to share ideas and howtos for various operations. The phpMyAdmin team will try to help you if you face any problem; you can use a variety of support channels to get help.

phpMyAdmin is also very deeply documented in a book written by one of the developers – Mastering phpMyAdmin for Effective MySQL Management, which is available in English and Spanish.

To ease usage to a wide range of people, phpMyAdmin is being translated into 72 languages and supports both LTR and RTL languages.

phpMyAdmin is a mature project with a stable and flexible code base; you can find out more about the project and its history and the awards it earned. When the project turned 15, we published a celebration page.

The phpMyAdmin project is a member of Software Freedom Conservancy. SFC is a not-for-profit organization that helps promote, improve, develop, and defend Free, Libre, and Open Source Software (FLOSS) projects.

Features

• Intuitive web interface
• Support for most MySQL features:
  • browse and drop databases, tables, views, fields and indexes
  • create, copy, drop, rename and alter databases, tables, fields and indexes
  • maintenance server, databases and tables, with proposals on server configuration
  • execute, edit and bookmark any SQL-statement, even batch-queries
  • manage MySQL user accounts and privileges
  • manage stored procedures and triggers
• Import data from CSV and SQL
• Export data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, LATEX and others
• Administering multiple servers
• Creating graphics of your database layout in various formats
• Creating complex queries using Query-by-example (QBE)
• Searching globally in a database or a subset of it
• Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
• And much more...