DEV Community

Janak Shrestha
Janak Shrestha

Posted on

Day 74: Jenkins Database Backup Job

#devops #jenkins

There is a requirement to create a Jenkins job to automate the database backup. Below you can find more details to accomplish this task:

Click on the Jenkins button on the top bar to access the Jenkins UI. Login using username admin and password Adm!n321.

  1. Create a Jenkins job named database-backup.

  2. Configure it to take a database dump of the kodekloud_db01 database present on the App server (stapp01) in Stratos Datacenter, the database user is kodekloud_roy and password is asdfgdsd.

  3. The dump should be named in db_$(date +%F).sql format, where date +%F is the current date.

  4. Copy the db_$(date +%F).sql dump to the Storage server (ststor01) under location /home/natasha/db_backups.

  5. Further, schedule this job to run periodically at */10 * * * * (please use this exact schedule format).

Note:

  1. You might need to install some plugins and restart Jenkins service. So, we recommend clicking on Restart Jenkins when installation is complete and no jobs are running on plugin installation/update page i.e update centre. Also, Jenkins UI sometimes gets stuck when Jenkins service restarts in the back end. In this case please make sure to refresh the UI page.

  2. Please make sure to define you cron expression like this */10 * * * * (this is just an example to run job every 10 minutes).

  3. For these kind of scenarios requiring changes to be done in a web UI, please take screenshots so that you can share it with us for review in case your task is marked incomplete. You may also consider using a screen recording software such as loom.com to record and share your work.

Understanding the Architecture

Components Overview

Component Specification
Job Name database-backup
Database kodekloud_db01 on stapp01
Database User kodekloud_roy
Database Password asdfgdsd
Dump Format db_$(date +%F).sql
Destination /home/natasha/db_backups on ststor01
Schedule */10 * * * *

Architecture Diagram 

┌─────────────────────────────────────────────────────────────────────────────┐
│                         Jenkins Server                                     │
│                                                                              │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  Jenkins Job: database-backup                                         │ │
│  │  Schedule: */10 * * * * (Every 10 minutes)                            │ │
│  │                                                                       │ │
│  │  Build Steps:                                                         │ │
│  │  1. SSH to App Server 1 → Run mysqldump → Create db_YYYY-MM-DD.sql   │ │
│  │  2. SCP from App Server 1 → Storage Server                          │ │
│  │  3. Verify dump on Storage Server                                    │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  App Server 1 (stapp01)                                               │ │
│  │  ┌──────────────────────────────────────────────────────────────────┐ │ │
│  │  │  MySQL Database: kodekloud_db01                                  │ │ │
│  │  │  User: kodekloud_roy                                             │ │ │
│  │  │  Password: asdfgdsd                                              │ │ │
│  │  │  Dump File: db_YYYY-MM-DD.sql                                    │ │ │
│  │  └──────────────────────────────────────────────────────────────────┘ │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  Storage Server (ststor01)                                            │ │
│  │  ┌──────────────────────────────────────────────────────────────────┐ │ │
│  │  │  Destination: /home/natasha/db_backups/                          │ │ │
│  │  │  File: db_YYYY-MM-DD.sql                                         │ │ │
│  │  └──────────────────────────────────────────────────────────────────┘ │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Step-by-Step Implementation

Step 1: Access Jenkins UI

  1. Click the Jenkins button on the top bar
  2. Login with:
    • Username: admin
    • Password: Adm!n321

Step 2: Install Required Plugins (If Needed)

  1. Navigate to Manage Jenkins → Plugins
  2. Click on Available plugins
  3. Search for and install:
    • Publish Over SSH
    • SSH plugin
  4. Restart Jenkins if prompted

Note: When installing plugins, select Restart Jenkins when installation is complete and no jobs are running.

Step 3: Configure SSH Server for Storage Server

  1. Go to Manage Jenkins → Configure System
  2. Scroll to Publish over SSH section
  3. Click Add next to SSH Servers
  4. Configure the following:
    • Name: storage-server
    • Hostname: ststor01.stratos.xfusioncorp.com
    • Username: natasha
    • Remote Directory: /home/natasha/db_backups
  5. Under Advanced, check Use password authentication
  6. Enter the password: Bl@kW
  7. Click Test Configuration to verify the connection
  8. Click Save

Step 4: SSH to Jenkins Server (For Key-Based Configuration) 

ssh jenkins@jenkins
# Password: j@rv!s
Enter fullscreen mode Exit fullscreen mode

Step 5: Generate SSH Key for Jenkins User 

# Check if SSH key exists
ls -la ~/.ssh/id_rsa

# If not, generate it
ssh-keygen -t rsa -b 4096 -N "" -f ~/.ssh/id_rsa
Enter fullscreen mode Exit fullscreen mode

Output:

Generating public/private rsa key pair.
Your identification has been saved in /var/lib/jenkins/.ssh/id_rsa
Your public key has been saved in /var/lib/jenkins/.ssh/id_rsa.pub
Enter fullscreen mode Exit fullscreen mode

Step 6: Copy SSH Key to App Server 1 

# Copy public key to stapp01
ssh-copy-id -o StrictHostKeyChecking=no tony@stapp01
# Password: Ir0nM@n

# Verify passwordless access
ssh -o StrictHostKeyChecking=no tony@stapp01 hostname
Enter fullscreen mode Exit fullscreen mode

Output:

stapp01
Enter fullscreen mode Exit fullscreen mode

Step 7: Copy SSH Key to Storage Server 

# Copy public key to ststor01
ssh-copy-id -o StrictHostKeyChecking=no natasha@ststor01
# Password: Bl@kW

# Verify passwordless access
ssh -o StrictHostKeyChecking=no natasha@ststor01 hostname
Enter fullscreen mode Exit fullscreen mode

Output:

ststor01
Enter fullscreen mode Exit fullscreen mode

Step 8: Create Backup Directory on Storage Server 

# Create and set permissions for backup directory
ssh -o StrictHostKeyChecking=no natasha@ststor01 "mkdir -p /home/natasha/db_backups && chmod 755 /home/natasha/db_backups"
Enter fullscreen mode Exit fullscreen mode

Step 9: Test the Full Command Chain 

# Test database dump
ssh -o StrictHostKeyChecking=no tony@stapp01 "mysqldump -u kodekloud_roy -pasdfgdsd kodekloud_db01 > /tmp/test.sql"

# Copy to storage server
scp -o StrictHostKeyChecking=no tony@stapp01:/tmp/test.sql natasha@ststor01:/home/natasha/db_backups/

# Verify on storage server
ssh -o StrictHostKeyChecking=no natasha@ststor01 "ls -la /home/natasha/db_backups/"
Enter fullscreen mode Exit fullscreen mode

Output:

total 16
drwxr-xr-x 2 natasha natasha 4096 Sep 13 15:36 .
drwx------ 1 natasha natasha 4096 Sep 13 15:36 ..
-rw-r--r-- 1 natasha natasha 1319 Sep 13 15:36 test.sql
Enter fullscreen mode Exit fullscreen mode

Step 10: Create the Jenkins Job

  1. From Jenkins dashboard, click New Item
  2. Enter name: database-backup
  3. Select Freestyle project
  4. Click OK

Step 11: Configure Build Trigger

  1. Under Build Triggers, check Build periodically
  2. Enter cron expression: 
   */10 * * * *
Enter fullscreen mode Exit fullscreen mode

Step 12: Configure Build Step

  1. Under Build, click Add build step → Execute shell
  2. Enter the following script: 
#!/bin/bash

# Set variables
DB_NAME="kodekloud_db01"
DB_USER="kodekloud_roy"
DB_PASS="asdfgdsd"
DATE=$(date +%F)
DUMP_FILE="db_${DATE}.sql"

# Take database dump on App Server 1
ssh -o StrictHostKeyChecking=no tony@stapp01 "mysqldump -u ${DB_USER} -p${DB_PASS} ${DB_NAME} > /tmp/${DUMP_FILE}"

# Copy the dump to Storage Server
scp -o StrictHostKeyChecking=no tony@stapp01:/tmp/${DUMP_FILE} natasha@ststor01:/home/natasha/db_backups/

# Verify the dump exists on Storage Server
ssh -o StrictHostKeyChecking=no natasha@ststor01 "ls -la /home/natasha/db_backups/${DUMP_FILE}"
Enter fullscreen mode Exit fullscreen mode

Step 13: Save and Build

  1. Click Save
  2. Click Build Now
  3. Check Console Output

Successful Console Output:

Started by user admin
Running as SYSTEM
Building in workspace /var/lib/jenkins/workspace/database-backup
[database-backup] $ /bin/bash /tmp/jenkins4173084968184636824.sh
-rw-r--r-- 1 natasha natasha 1319 Sep 13 15:37 /home/natasha/db_backups/db_2026-09-13.sql
Finished: SUCCESS
Enter fullscreen mode Exit fullscreen mode

Verification

Check Backup File on Storage Server 

ssh natasha@ststor01 "ls -la /home/natasha/db_backups/"
Enter fullscreen mode Exit fullscreen mode

Expected Output:

-rw-r--r-- 1 natasha natasha 1319 Sep 13 15:37 db_2026-09-13.sql
Enter fullscreen mode Exit fullscreen mode

Check Dump Content 

ssh natasha@ststor01 "head -20 /home/natasha/db_backups/db_$(date +%F).sql"
Enter fullscreen mode Exit fullscreen mode

Understanding the Script

Variables

Variable Value Purpose
DB_NAME kodekloud_db01 Database to backup
DB_USER kodekloud_roy Database user
DB_PASS asdfgdsd Database password
DATE $(date +%F) Current date (YYYY-MM-DD)
DUMP_FILE db_${DATE}.sql Dump filename

Steps

  1. Database Dump: SSH to App Server 1 and run mysqldump to create the dump file.
  2. File Transfer: SCP the dump from App Server 1 to Storage Server.
  3. Verification: List the file on Storage Server to confirm it exists.

Cron Expression Reference

Expression Meaning
*/10 * * * * Every 10 minutes
*/5 * * * * Every 5 minutes
0 * * * * Every hour at minute 0
0 0 * * * Every day at midnight
0 0 * * 0 Every Sunday at midnight

Troubleshooting

Issue 1: SSH Connection Failed

Error: Permission denied (publickey)

Solution: Ensure SSH keys are configured correctly:

# Test SSH connectivity
ssh -o StrictHostKeyChecking=no tony@stapp01 hostname
ssh -o StrictHostKeyChecking=no natasha@ststor01 hostname

# If fails, re-copy the key
ssh-copy-id -o StrictHostKeyChecking=no tony@stapp01
Enter fullscreen mode Exit fullscreen mode

Issue 2: mysqldump Command Not Found

Error: mysqldump: command not found

Solution: Install MySQL client on App Server 1:

ssh tony@stapp01 "sudo yum install -y mysql"
Enter fullscreen mode Exit fullscreen mode

Issue 3: Permission Denied on Destination

Error: Permission denied

Solution: Ensure the destination directory exists and is writable:

ssh natasha@ststor01 "mkdir -p /home/natasha/db_backups && chmod 755 /home/natasha/db_backups"
Enter fullscreen mode Exit fullscreen mode

Issue 4: Database Connection Failed

Error: Access denied for user 'kodekloud_roy'

Solution: Verify database credentials:

ssh tony@stapp01 "mysql -u kodekloud_roy -pasdfgdsd -e 'SHOW DATABASES;'"
Enter fullscreen mode Exit fullscreen mode

Issue 5: Jenkins UI Gets Stuck

Solution: Refresh the UI page. If Jenkins service restarts, it may take a moment to come back online.

Best Practices

1. Secure Credentials

Avoid hardcoding passwords in scripts. Use Jenkins credentials or environment variables.

2. Compression

Compress large database dumps to save storage space:

mysqldump -u user -ppass db | gzip > db_$(date +%F).sql.gz
Enter fullscreen mode Exit fullscreen mode

3. Retention Policy

Implement a retention policy to delete old backups:

find /home/natasha/db_backups -name "*.sql" -mtime +7 -delete
Enter fullscreen mode Exit fullscreen mode

4. Monitoring

Set up notifications for job failures:

  • Configure email notifications in Jenkins
  • Use monitoring tools to track backup status

5. Testing

Regularly test restoring backups to ensure they are valid.

Useful Commands Reference

Command Purpose
ssh-keygen -t rsa -b 4096 -N "" -f ~/.ssh/id_rsa Generate SSH key
ssh-copy-id -o StrictHostKeyChecking=no user@host Copy SSH key to remote host
ssh -o StrictHostKeyChecking=no user@host hostname Verify passwordless SSH
mysqldump -u user -ppass db > file.sql Create database dump
scp -o StrictHostKeyChecking=no source destination Copy files between hosts
ssh user@host "mkdir -p /path" Create directory on remote host
find /path -name "*.sql" -mtime +7 -delete Delete old backups

Summary

In this challenge, we successfully:

  1. Accessed the Jenkins UI and logged in as admin
  2. Installed the Publish Over SSH plugin
  3. Configured SSH server for the Storage Server
  4. Generated SSH keys on the Jenkins server
  5. Copied SSH keys to App Server 1 and Storage Server
  6. Created the backup directory on the Storage Server
  7. Created a Jenkins job named database-backup
  8. Configured the job to take a MySQL database dump
  9. Set up the job to copy the dump to the Storage Server
  10. Scheduled the job to run every 10 minutes
  11. Verified the backup was created successfully

This automated database backup solution ensures that database backups are taken regularly and stored securely on a separate server. The job can be extended to include additional databases, compression, and retention policies.

Top comments (0)