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

Redis is an open-source and in-memory data structure store that can be used for caching, real-time analytics, searching, and machine learning. Integrate Redis with PHP and MySQL will improve your application performance because Redis stores data in RAM.

You can use it with databases like MySQL or MariaDB. Redis provides a mechanism to cache your queries. For example, when a user requests your application page the first time, a MySQL query is performed on the server, and Redis caches this query to RAM. When another user requests the same page, you don’t need to query the database again.

Prerequisites

• An Ubuntu 20.04 VPS (we’ll be using our SSD 2 VPS plan)
• Access to the root user account (or access to an admin account with root privileges)

Step 1: Log in to the Server & Update the Server OS Packages

First, log in to your Ubuntu 20.04 server via SSH as the root user:

ssh root@IP_Address -p Port_number

You will need to replace ‘IP_Address’ and ‘Port_number’ with your server’s respective IP address and SSH port number. Additionally, replace ‘root’ with the username of the admin account if necessary.

Before starting, you have to make sure that all Ubuntu OS packages installed on the server are up to date. You can do this by running the following commands:

apt-get update -y
apt-get upgrade -y

Step 2: Install LAMP Server

First, you will need to install the LAMP server in your system. You can install it with the following command:

apt-get install apache2 php libapache2-mod-php php-pdo php-json php-cli mysql-server -y

Once the installation has been finished, you can proceed to the next step.

Step 3: Install and Configure Redis

By default, the latest version of Redis is not included in the Ubuntu default repository. So you will need to add the Redis repository to your system.

First, install the required packages with the following command:

apt-get install software-properties-common gnupg2 -y

Next, add the Redis repository with the following command:

add-apt-repository ppa:chris-lea/redis-server

Once the repository is added, install the Redis server with the following command:

apt-get install redis-server -y

Next, install the Redis extensions of PHP and MySQL with the following command:

apt-get install php-redis php-mysql -y

Once both packages are installed, start the Redis service and enable it to start at system reboot:

systemctl start redis-server
systemctl enable redis-server

Next, restart the Apache service to apply the configuration changes:

systemctl restart apache2

Step 4: Create a MySQL Database

Next, you will need to create a database, user and table to test the Redis caching.

First, connect to the MySQL with the following command:

mysql

Next, create a database and user with the following command:

mysql> CREATE DATABASE testdb;
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Next, grant all the privileges to the testdb database with the following command:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

Next, flush the privileges with the following command:

mysql> FLUSH PRIVILEGES;

Next, change the database to testdb and create a table named student with the following command:

mysql> use testdb;
mysql> CREATE TABLE student ( student_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name  VARCHAR(50) );

Next, insert some data in the table with the following command:

mysql> INSERT INTO student (first_name, last_name) VALUES ('John', 'Thompson');
mysql> INSERT INTO student (first_name, last_name) VALUES ('Greg', 'Smith');
mysql> INSERT INTO student (first_name, last_name) VALUES ('Ray', 'Brown');

You can now verify all data with the following command:

mysql> SELECT student_id, first_name, last_name from student;

You should get the following output:

+------------+------------+-----------+
| student_id | first_name | last_name |
+------------+------------+-----------+
|          1 | John     | Thompson    |
|          2 | Greg    | Smith      |
|          3 | Ray     | Brown    |
+------------+------------+-----------+
3 rows in set (0.00 sec)

Now, exit from the MySQL with the following command:

mysql> EXIT;

Step 5: Create a PHP Application to Fetch Data from MySQL

Next, you will need to create a PHP application and host it with an Apache webserver. This application will connect to the MySQL database and query the student table which we have created earlier.

First, create a PHP application in the Apache web root directory:

nano /var/www/html/student.php

Add the following codes:

<html>
  <head>
    <title>Using Redis Server with PHP and MySQL</title>
  </head> 
  <body>

    <h1 align = 'center'>Students' Register</h1>

    <table align = 'center' border = '2'>        

    <?php 
        try {

            $data_source = '';

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

            $sql = 'select
                    student_id,
                    first_name,
                    last_name                                 
                    from student
                    ';

            $cache_key = md5($sql);

            if ($redis->exists($cache_key)) {

                $data_source = "Data from Redis Server";
                $data = unserialize($redis->get($cache_key));

            } else {

                $data_source = 'Data from MySQL Database';

                $db_name     = 'testdb';
                $db_user     = 'testuser';
                $db_password = 'password';
                $db_host     = 'localhost';

                $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $stmt = $pdo->prepare($sql);
                $stmt->execute();
                $data = []; 

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

                $redis->set($cache_key, serialize($data)); 
                $redis->expire($cache_key, 10);        
           }

           echo "<tr><td colspan = '3' align = 'center'><h2>$data_source</h2></td></tr>";
           echo "<tr><th>Student Id</th><th>First Name</th><th>Last Name</th></tr>";

           foreach ($data as $record) {
              echo '<tr>';
              echo '<td>' . $record['student_id'] . '</td>';
              echo '<td>' . $record['first_name'] . '</td>';
              echo '<td>' . $record['last_name']  . '</td>';                     
              echo '</tr>'; 
           }              


        } catch (PDOException $e) {
            echo 'Database error. ' . $e->getMessage();
        }
   ?>

    </table>
  </body>
</html>

Save and close the file when you are finished.

The above application will connect to the MySQL database and cache the data to Redis.

Step 6: Test Redis Caching

Next, you will need to test whether the Redis caching is working or not. Open your web browser and access your application using the URL

http://your-server-ip/student.php. When you access the application first time, it should fetch from the MySQL database. When you access the application again, it should open faster because you will get data from the Redis cache.

Of course, you don’t have to install setup Redis with MySQL if you use one of our Managed Redis Hosting, in which case you can simply ask our expert Linux admins to install and configure this for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on How To Set Up Redis as a Cache for MySQL with PHP on Ubuntu 20.04, please share it with your friends on social networks or simply leave a reply below. Thanks.