Introduction

In cPanel servers, each user may have multiple MySQL databases associated with their account. During migration, restoration, or troubleshooting, we may need to take a backup of all databases owned by a particular cPanel user and restore them from an available backup.

This can be done using a simple shell script from the command line. The script will identify the databases owned by the specified user, take a backup of the current databases, and then restore the databases from the available backup files.

Prerequisites

Before proceeding, make sure you have the following:

  • Root SSH access to the server
  • MySQL or MariaDB service running
  • The cPanel username
  • Available database backup files
  • Sufficient disk space to store database backups
  • Basic knowledge of Linux commands

Important Note

Before restoring any database, always take a backup of the existing database. This helps to avoid data loss if the restore process fails or if the wrong backup file is restored.

Also, do not directly copy SQL files into /var/lib/mysql for restoration. The proper method is to restore the database using the mysql command.

Steps to Back Up and Restore Databases

Step 1: Create the Script File

Create a script file using the following command:

vi db_backup_restore.sh

Step 2: Add the Script

Replace username with the actual cPanel username.

#!/bin/bash

USER="username"
BACKUP_DIR="/home/$USER/db-backup-$(date +%F-%H-%M)"
RESTORE_DIR="/backup/cpbackup/monthly/$USER/mysql"

mkdir -p "$BACKUP_DIR"

echo "Fetching database list for user: $USER"

mysql -N -e "SHOW DATABASES LIKE '${USER}\_%';" > /tmp/${USER}_dblist.txt

echo "Taking backup of current databases..."

for db in $(cat /tmp/${USER}_dblist.txt)
do
    echo "Backing up database: $db"
    mysqldump "$db" > "$BACKUP_DIR/$db.sql"
done

echo "Current database backups saved in: $BACKUP_DIR"

echo "Restoring databases from available backup..."

for db in $(cat /tmp/${USER}_dblist.txt)
do
    BACKUP_FILE="$RESTORE_DIR/$db.sql"

    if [ -f "$BACKUP_FILE" ]; then
        echo "Restoring database: $db"
        mysql "$db" < "$BACKUP_FILE"
    else
        echo "Backup file not found for database: $db"
    fi
done

echo "Database backup and restore process completed."

Step 3: Save and Exit

Save the file and exit from the editor.

If you are using vi, press:

Esc
:wq

Step 4: Give Execute Permission

Run the following command:

chmod +x db_backup_restore.sh

Step 5: Execute the Script

Run the script as the root user:

./db_backup_restore.sh

The script will:

  1. Fetch the list of databases owned by the cPanel user.
  2. Take a backup of the current databases.
  3. Store the backup under the user’s home directory.
  4. Restore the databases from the available backup path.
  5. Show the restore status for each database.

Example Backup Location

The current database backups will be stored under:

/home/username/db-backup-date-time/

Example:

/home/username/db-backup-2026-06-25-10-30/

Conclusion

This script is useful when you need to back up and restore all databases owned by a specific cPanel user. It helps reduce manual work and ensures that the current databases are backed up before restoring from an available backup.

Always verify the backup path and database names before running the restore process to avoid accidental data loss.

Post Views: 102

Leave a Reply