How to Speed Up Python/MySQL Application Session Handling With Redis On Ubuntu 22.04

Tutorials
MySQL Python Redis Ubuntu

Introduction

Authentication is the process of verifying users’ identity during login requests. In an authentication process, users submit their credentials as usernames and passwords. Then, the application matches those login credentials with stored database entries. The application grants users access to the system if there is a match.

Storing login credentials in a relational database like MySQL or PostgreSQL without a caching mechanism is still a common and practical approach, but it comes with the following limitations:

  • Overloading the database. The application must make a roundtrip to the database server to verify the users’ credentials from a database table every time a user submits a login request. Because the database might still serve other read/write requests, the entire process overloads the database and makes it slow.

  • Traditional disk-based databases have scalability issues. When your application receives thousands of requests per second, disk-based databases do not perform optimally.

To overcome the above challenges, you can use Redis to cache the users’ login credentials so that your application doesn’t have to contact the backend database during each login request. Redis is one of the most popular ultrafast data stores that utilizes your computer’s RAM to store data in key-value pairs. In this guide, you’ll use the Redis database to speed up session handling in your Python/MySQL application on the Ubuntu 22.04 server.

Prerequisites

Before you begin this tutorial, you will need to setup following:

Step 1 — Installing Python Database Drivers for Redis and MySQL

This application permanently stores users’ credentials, such as names and passwords, in a MySQL database server. When a user logs in to the application, a Python script queries the MySQL database and matches the details with stored values. Then, the Python script caches the user’s login credentials in a Redis database to serve other future requests. To complete that logic, your Python scripts require database drivers (Python modules) to communicate with the MySQL and Redis servers. Follow the steps below to install the drivers:

  1. Update your package information index and run the following command to install python3-pip, a Python package manager allowing you to install additional modules not part of the Python standard library.
sudo apt install python3-pip
  1. Install the MySQL driver for Python:
pip install mysql-connector-python
  1. Install the Redis driver for Python:
pip install redis

After installing the necessary drivers for communicating with MySQL and Redis, proceed to the next step and initialize a MySQL database.

Step 2 — Setting Up a Sample MySQL Database

For this guide, you require one MySQL table. In a production environment, you can have dozens of tables that serve other requests. Set up a database and create the table by executing the following commands:

  1. Log in to the MySQL database server as a root user:

    sudo mysql -u root -p

  2. Enter your MySQL server’s root password when prompted and press ENTER to proceed. Then, run the following command to create a sample company database and a company_user account. Replace example-mysql-password with a strong password:

  1. CREATE DATABASE company;

  2. CREATE USER 'company_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'example-mysql-password';

  3. GRANT ALL PRIVILEGES ON company.* TO 'company_user'@'localhost';

  4. FLUSH PRIVILEGES;

  1. Ensure you receive the following output to confirm that the previous commands have run successfully:

    Output
    Query OK, 1 row affected (0.01 sec)

  2. Switch to the new company database:

    1. USE company;

  3. Confirm you’re connected to the new database by verifying the following output:

    Output
    Database changed

  4. Create a system_users table. The user_id column serves as a PRIMARY KEY to uniquely identify each user. The username and password columns are the login credentials that users must submit to log in to the application. The first_name and last_name columns store the users’ names:

    custom_prefix(mysql>)
CREATE TABLE system_users (
    user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    password VARCHAR(50)
) ENGINE = InnoDB;

  5. Ensure you’ve created the new table by verifying the following output:

    Output
    Query OK, 0 rows affected (0.03 sec)

  6. Populate the system_users table with sample data. Use the MySQL inbuilt MD5(...) function to hash the password for security purposes:

    1. INSERT INTO system_users (username, first_name, last_name, password) VALUES ('john_doe', 'JOHN', 'DOE', MD5('password_1'));

    2. INSERT INTO system_users (username, first_name, last_name, password) VALUES ('mary_henry', 'MARY', 'HENRY', MD5('password_2'));

    3. INSERT INTO system_users (username, first_name, last_name, password) VALUES ('peter_jade', 'PETER', 'JADE', MD5('password_3'));

  7. Verify the output below:

    Output
    Query OK, 1 row affected (0.00 sec)

  8. Query the system_users table to ensure the data is in place:

    1. SELECT

    2.     user_id,

    3.     first_name,

    4.     last_name,

    5.     password

    6. FROM system_users;

  9. Verify the following output:

    Output
    +---------+------------+-----------+----------------------------------+
| user_id | first_name | last_name | password                         |
+---------+------------+-----------+----------------------------------+
|       1 | JOHN       | DOE       | 57210b12af5e06ad2e6e54a93b1465aa |
|       2 | MARY       | HENRY     | 259640f97ac2b4379dd540ff4016654c |
|       3 | PETER      | JADE      | 48ef85c894a06a4562268de8e4d934e1 |
+---------+------------+-----------+----------------------------------+
3 rows in set (0.00 sec)

  10. Log out from the MySQL database:

    1. QUIT;

You’ve now set up the right MySQL database for your application. In the next step, you’ll build a Python module communicating with your sample database.

Step 3 — Creating a Central MySQL Gateway Module for Python

When coding any Python project, you should create a separate module for each task to promote code reusability. In this step, you’ll set up a central module that allows you to connect and query the MySQL database from a Python script. Follow the steps below:

  1. Create a project directory. This directory separates your Python source code files from the rest of the system files:

    1. mkdir project

  2. Switch to the new project directory:

    1. cd project

  3. Use nano text editor to open a new mysql_db.py file. This file hosts the Python module that talks to the MySQL database:

    nano mysql_db.py

  4. Enter the following information into the mysql_db.py file. Replace example-mysql-password with the correct MySQL password for the company_user account:

    ~/project/mysql_db.py
    
import mysql.connector

class MysqlDb:

def db_con(self):

    mysql_con = mysql.connector.connect(
        host     = "localhost",
        user     = "company_user",
        password = "example-mysql-password",
        database = "company",
        port     = "3306"
    )

    return mysql_con

def query(self, username, password):

    db = self.db_con()
    db_cursor = db.cursor()

    db_query  = "select username, password from system_users where username = %s and password = md5(%s)"
    db_cursor.execute(db_query, (username, password))

    result = db_cursor.fetchone()
    row_count = db_cursor.rowcount

    if  row_count < 1:
        return False
    else:
        return result[1]

  5. Save and close the mysql_db.py file.

The mysql_db.py module file has one class (MysqlDb:) with two methods:
– db_con(self):, connects to the sample company database that you created earlier and returns a reusable MySQL connection using the return mysql_con statement.
– query(self, username, password):, a method that accepts a username and password and queries the system_users table to find if there is a match. The conditional if row_count < 1: ... else: return result[1] statement returns the boolean False value if a user doesn’t exist in the table or the user’s password (result[1]) if the application finds a match.

With the MySQL module ready, follow the next step to set up a similar Redis module that communicates to the Redis key-value store.

Step 4 — Creating a Central Redis Module for Python

In this step, you’ll code a module that connects to the Redis server. Execute the following steps:

  1. Open a new redis_db.py file:

    nano redis_db.py

  2. Enter the following information into the redis_db.py file. Replace example-redis-password with the correct password for the Redis server:

    ~/project/redis_db.py
    import redis
class RedisDb:
    def db_con(self):
        r_host = 'localhost'
        r_port = 6379
        r_pass = 'example-redis-password'
        redis_con = redis.Redis(host = r_host, port = r_port, password = r_pass)
        return redis_con

  3. Save and close the redis_db.py file.

  • The above file has one class (RedisDb:).

  • Under this class, the db_con(self): method uses the provided credentials to connect to the Redis server and returns a reusable connection using the return redis_con statement.

After setting up the Redis class, create the main file for your project in the next step.

Step 5 — Creating the Application’s Entry Point

Every Python application must have an entry point or the main file that executes when the application runs. In this file, you’ll create a code that shows the current server’s time for authenticated users. This file uses the custom MySQL and Redis modules you created to authenticate users. Follow the steps below to create the file:

  1. Open a new index.py file:

    nano index.py

  2. Enter the following information into the index.py file:

    ~/project/index.py
    from encodings import utf_8
import base64
from hashlib import md5
import json
import datetime
import http.server
from http import HTTPStatus
import socketserver
import mysql_db
import redis_db

class HttpHandler(http.server.SimpleHTTPRequestHandler):
    def do_GET(self):
        self.send_response(HTTPStatus.OK)
        self.send_header('Content-type', 'application/json')
        self.end_headers()
        authHeader = self.headers.get('Authorization').split(' ');
        auth_user, auth_password = base64.b64decode(authHeader[1]).decode('utf8').split(':')
        mysql_server = mysql_db.MysqlDb()
        redis_server = redis_db.RedisDb()
        redis_client =  redis_server.db_con()
        now = datetime.datetime.now()
        current_time = now.strftime("%Y-%m-%d %H:%M:%S")
        resp = {}
        if redis_client.exists(auth_user):
            if md5(auth_password.encode('utf8')).hexdigest() != redis_client.get(auth_user).decode('utf8'):
                resp = {"error": "Invalid username/password."}
            else:
                resp = {"time": current_time, "authorized by": "Redis server"}
        else:
            mysql_resp  = mysql_server.query(auth_user, auth_password)
            if mysql_resp == False:
                 resp =  {"error": "Invalid username/password."}
            else:
                resp = {"time": current_time, "authorized by": "MySQL server"}
                redis_client.set(auth_user, mysql_resp)
        self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
httpd = socketserver.TCPServer(('', 8080), HttpHandler)
print("Web server is running on port 8080...")

try:
    httpd.serve_forever()
except KeyboardInterrupt:
    httpd.server_close()
    print("Web server has stopped runing.")

  3. Save and close the index.py file.

  • In the index.py file, the import... section adds the following modules to your project:

    • utf_8, base64, md5, and json, text encoding and formatting modules.

    • http.server, HTTPStatus, and socketserver, web server modules.

    • datetime, time/date module.

    • mysql_db and redis_db, custom modules that you previously created to access the MySQL and Redis servers.

  • The HttpHandler(http.server.SimpleHTTPRequestHandler): is a handler class for the HTTP server. Under the class, the do_GET(self): method servers the HTTP GET requests and displays the system’s date/time for authenticated users.

  • In the if ... : else: ... logic, the Python script runs the logical if redis_client.exists(auth_user): statement to check if the user’s credentials exist in the Redis server. If the user details exist and the Redis stored password doesn’t match the user’s submitted password, the application returns the {"error": "Invalid username/password."} error.

If the user details do not exist in the Redis server, the application queries the MySQL database server using the mysql_resp = mysql_server.query(auth_user, auth_password) statement. In case the user’s supplied password doesn’t match the database stored value, the application returns the {"error": "Invalid username/password."} error. Otherwise, the application caches the user’s details in the Redis server using the redis_client.set(auth_user, mysql_resp) statement.

  • In all cases where the user’s credentials match the Redis/MySQL details, the application displays the system’s current date/time using the {"time": current_time, ...} statement. The authorized by entry in the output allows you to see the database server that authenticates the users in the application.

      if redis_client.exists(auth_user):
      if md5(auth_password.encode('utf8')).hexdigest() != redis_client.get(auth_user).decode('utf8'):
          resp = {"error": "Invalid username/password."}
      else:
          resp = {"time": current_time, "authorized by": "Redis server"}
  else:
      mysql_resp  = mysql_server.query(auth_user, auth_password)
      if mysql_resp == False:
           resp =  {"error": "Invalid username/password."}
      else:
          resp = {"time": current_time, "authorized by": "MySQL server"}
          redis_client.set(auth_user, mysql_resp)

You have now set up the main file for the application. In the next step, you’ll test the application.

Step 6 — Testing the Application

In this step, you’ll run your application to see if the Redis caching mechanism works. Execute the commands below to test the application:

  1. Use the following python3 command to run the application:

    python3 index.py

  2. Ensure the application’s custom web server is running:

    Output
    Web server is running on port 8080...

  3. Establish another SSH connection to your server in a new terminal window and run the following curl commands to send four GET request using john_doe's credentials. Append [1-4] at the end of the http://localhost:8080/ URL to send the four requests in a single command:

    curl -X GET -u john_doe:password_1  http://localhost:8080/[1-4]

  4. Verify the following outputs. The MySQL server only serves the first authentication request. Then, the Redis database serves the next three requests.

    Output
    [1/4]
{
  "time": "2023-11-07 10:04:38",
  "authorized by": "MySQL server"
}
[4/4]
{
  "time": "2023-11-07 10:04:38",
  "authorized by": "Redis server"
}

Your application logic is now working as expected.

Conclusion

In this guide, you built a Python application that uses the Redis server to cache users’ login credentials. Redis is a highly available and scalable database server that can perform thousands of transactions per second. With the Redis caching mechanism in your application, you can highly reduce traffic in your backend database server. To learn more about Redis applications, refer to our Redis tutorials .

Source:
https://www.digitalocean.com/community/tutorials/how-to-speed-up-python-mysql-application-session-handling-with-redis-on-ubuntu-22-04