How can you enhance the session handling of your Python/MySQL application using Redis on Ubuntu?

Introduction

Authentication verifies user identity during login. Users provide a username and password, which are compared with stored database credentials. If matched, access is granted.
While relational databases like MySQL or PostgreSQL commonly store login credentials, this approach has challenges:

– Database Overload: Every login request requires querying the database, impacting performance as other tasks run simultaneously.
– Scalability Issues: Handling thousands of login requests can strain traditional databases. Using Redis, a fast in-memory data store, can alleviate these issues by caching login data. This guide explains how to enhance session handling in a Python/MySQL application using Redis on an Ubuntu 20.04 server.

Prerequisites

Ensure the following before starting:
– Ubuntu 20.04 or later
– Root access to your system

– Installed MySQL and Redis servers

Accessing the Ubuntu Server

You can connect to your Ubuntu server through Web SSH:

1. Log in to your dashboard.
2. Navigate to the environment hosting your Ubuntu server.
3. Select the Web SSH option.

Setting Up Python Database Drivers

1. Install Python modules to interact with MySQL and Redis:
Install pip:

sudo apt install python3-pip

2. Install the MySQL driver:

pip install mysql-connector-python

3. Install the Redis driver:

pip install redis

4. Creating a Sample MySQL Database
Log in to MySQL:

mysql -u root -p

5. Create a database and user:
sql

CREATE DATABASE db_name;
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'db_user_password';
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost';
FLUSH PRIVILEGES;
CREATE DATABASE db_test;
CREATE USER 'db_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'db_user_password';
GRANT ALL PRIVILEGES ON db_name.* TO 'db_test'@'localhost';
FLUSH PRIVILEGES;

Switch to the new database:

USE db_name;

Create a table:
sql

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;

Add sample data:
sql

INSERT INTO system_users (username, first_name, last_name, password) VALUES
('Maggie', 'Micheal', 'Sandy', MD5('iuyrbijy5hd'));
('Chin', 'dave', 'Rogen', MD5('hfehkwjdikjd6')),
('Phobee', Racheal', 'Joey', MD5('9fehkwjdikjd6'));

Check the data in the table to confirm it’s stored correctly:

SELECT user_id, first_name, last_name, password FROM system_users;

Exit the MySQL shell:

QUIT;

Creating a MySQL Gateway Module for Pythonsql

1. Create a project directory to keep your Python code organized. Here, we use accuweb-project as an example. You can use the project name as per your requirements or choice.

mkdir accuweb-test

2. Open a new file named mysql_db.py using a text editor like Nano. This file will contain the module that interacts with MySQL:

vi mysql_db.py

import mysql.connector
class MysqlDb:
def db_con(self):
mysql_con = mysql.connector.connect(
host="localhost",
user="db_user",
password="db-password",
database="db_name",
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]

SELECT * FROM
system_users;
Building the MySQL Gateway Module
Create mysql_db.py:
python
Copy code
import mysql.connector
class MysqlDb:
def db_con(self):
return mysql.connector.connect(
host="localhost",
user="db_user",
password="db_user_password",
database="db_name"
)
def query(self, username, password):
db = self.db_con()
cursor = db.cursor()
cursor.execute(
"SELECT username, password FROM system_users WHERE username=%s AND password=MD5(%s)",
(username, password)
)
result = cursor.fetchone()
return result[1] if result else False
Creating the Redis Module
Create redis_db.py:
python
Copy code
import redis
class RedisDb:
def db_con(self):
return redis.Redis(host='localhost', port=6379, password='your_redis_password')
Application Entry Point
Create index.py:
python
Copy code
import base64
from hashlib import md5
import json
import datetime
from http.server import SimpleHTTPRequestHandler
import socketserver
import mysql_db
import redis_db
class HttpHandler(SimpleHTTPRequestHandler):
def do_GET(self):
self.send_response(200)
self.send_header("Content-type", "application/json")
self.end_headers()
auth_header = self.headers.get('Authorization').split(' ')[1]
auth_user, auth_password = base64.b64decode(auth_header).decode('utf8').split(':')
mysql_server = mysql_db.MysqlDb()
redis_server = redis_db.RedisDb()
redis_client = redis_server.db_con()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
response = {}
if redis_client.exists(auth_user):
if md5(auth_password.encode('utf8')).hexdigest() == redis_client.get(auth_user).decode('utf8'):
response = {"time": now, "authorized by": "Redis server"}
else:
response = {"error": "Invalid username/password"}
else:
mysql_resp = mysql_server.query(auth_user, auth_password)
if not mysql_resp:
response = {"error": "Invalid username/password"}
else:
redis_client.set(auth_user, mysql_resp)
response = {"time": now, "authorized by": "MySQL server"}
self.wfile.write(bytes(json.dumps(response, indent=2), "utf8"))
if __name__ == "__main__":
with socketserver.TCPServer(("", 8080), HttpHandler) as httpd:
print("Server running on port 8080...")
httpd.serve_forever()

Testing the Application

Run the application:

python3 index.py

Test using curl:

curl -X GET -u Chin:hfehkwjdikjd6 http://localhost:8080

The first response should indicate MySQL authentication.

Subsequent responses should indicate Redis caching.

Conclusion

This guide demonstrates how Redis can optimize session handling in Python/MySQL applications, reducing database load and improving performance. This approach is ideal for scalable, high-performance systems.