100 Days of DevOps: Day 74

Automating Database Backup in Jenkins

Overview

The Jenkins job, database-backup, has been successfully implemented to automate database backups for kodekloud_db01 and securely transfer the dump to the Backup Server. This solution successfully navigated a major administrative hurdle: the Jenkins Server lacked the mysqldump utility, and the jenkins user was blocked from using sudo to install it.

---

I. Problem & Solution Strategy

The Challenge	The Solution
mysqldump: command not found on Jenkins Server.	Remote Execution: Use SSH to execute mysqldump on the Database Server (stdb01) where the utility exists.
Password prompts break automated jobs.	Passwordless SSH: Configure key-based authentication from the Jenkins Server to both target servers.

Infrastructure Details Used

Server	Hostname	User	Password	Purpose
Jenkins	jenkins.stratos.xfusioncorp.com	jenkins	j@rv!s	CI/CD
Database	stdb01.stratos.xfusioncorp.com	peter	Sp!dy	DB Server
Backup	stbkp01.stratos.xfusioncorp.com	clint	H@wk3y3	Backup Server

---

II. Command Line Steps (Prerequisites)

All following commands are executed while SSHed into the Jenkins Server as the jenkins user.

Step 1: Generate the SSH Key Pair

# 1. Generate an RSA 4096-bit key pair.
jenkins@jenkins:~$ ssh-keygen -t rsa -b 4096

# IMPORTANT: Press ENTER twice to leave the passphrase EMPTY!

# Key files are saved to: /var/lib/jenkins/.ssh/

Step 2: Install Public Key on Target Servers

The public key (/var/lib/jenkins/.ssh/id_rsa.pub) is installed on both target machines to enable passwordless access.

Target	User	Command	Password to Enter
DB Server	peter	ssh-copy-id -i /var/lib/jenkins/.ssh/id_rsa.pub [e-mail]	Sp!dy
Backup Server	clint	ssh-copy-id -i /var/lib/jenkins/.ssh/id_rsa.pub [e-mail]	H@wk3y3

---

III. Jenkins Job Configuration

Step 3: Job Setup and Scheduling

1. Create Job: Create a new Freestyle Project named database-backup.

2. Scheduling: In the Build Triggers section, enable "Build periodically" and set the schedule:
   

   */10 * * * *
   

Step 4: Execute Shell Script (Remote Execution)

In the Build section, add an "Execute shell" build step and use the following script. This script executes mysqldump remotely on the DB Server and pipes the output back to the Jenkins Server.

#!/bin/bash

# --- Database Details ---
DB_NAME="kodekloud_db01"
DB_USER="kodekloud_roy"
DB_PASS="asdfgdsd"
DB_HOST="stdb01.stratos.xfusioncorp.com"
DB_USER_SSH="peter" 

# --- Backup Details ---
BACKUP_HOST="stbkp01.stratos.xfusioncorp.com"
BACKUP_USER="clint"
BACKUP_DIR="/home/clint/db_backups"

# --- File Naming ---
DATE_FORMAT=$(date +%F)
DUMP_FILE="db_${DATE_FORMAT}.sql"

# 1. Execute DUMP REMOTELY on the DB Server and PIPE output to the Jenkins Server
echo "Executing remote database dump on ${DB_HOST} and piping output..."
ssh -T "${DB_USER_SSH}"@"${DB_HOST}" "mysqldump -u ${DB_USER} -p${DB_PASS} ${DB_NAME}" > "${DUMP_FILE}"

if [ $? -ne 0 ]; then
    echo "ERROR: Remote database dump failed. Check SSH key setup or DB credentials."
    rm -f "${DUMP_FILE}"
    exit 1
fi

echo "Database dump successful and saved locally: ${DUMP_FILE}"

# 2. Copy the dump file from the Jenkins Server to the Backup Server
echo "Copying ${DUMP_FILE} to Backup Server (${BACKUP_HOST})..."
scp "${DUMP_FILE}" "${BACKUP_USER}"@"${BACKUP_HOST}":"${BACKUP_DIR}/"

if [ $? -eq 0 ]; then
    echo "File successfully copied to ${BACKUP_HOST}:${BACKUP_DIR}/${DUMP_FILE}"
    # Clean up the local dump file
    rm "${DUMP_FILE}"
else
    echo "ERROR: Failed to copy the dump file via SCP."
    exit 1
fi

IV. Final Result

The job successfully ran and achieved the required outcome:

[database-backup] $ /bin/bash /tmp/jenkins...sh
Executing remote database dump on stdb01.stratos.xfusioncorp.com and piping output...
Database dump successful and saved locally: db_2025-10-21.sql
Copying db_2025-10-21.sql to Backup Server (stbkp01.stratos.xfusioncorp.com)...
File successfully copied to stbkp01.stratos.xfusioncorp.com:/home/clint/db_backups/db_2025-10-21.sql
Finished: SUCCESS