PSSB Backup of Mysql and Scylla

Pre-requisite: Full Backup

1. Take Full Backup

Run the following command on the root node to create a full backup:

xtrabackup --backup --target-dir=/data/backups/

2. Transfer Backup to the Backup Node

Copy the backup files from the root node to the backup node using scp:

scp -i /home/devopsadmin/.ssh/id_rsa -P <port_number> -r /data/backups/ devopsadmin@<backup-host>:/data/backups/

Replace the placeholders:

  • <port_number> with the SSH port (e.g., 22).
  • <backup-host> with the IP address or hostname of the backup node.

3. Prepare the Full Backup on the Backup Node

Once the backup files are transferred, prepare the full backup on the backup node to make it ready for restoration:

xtrabackup --prepare --target-dir=/data/backups/

4. Restore the Backup to the MySQL Data Directory

Sync the prepared backup files to the MySQL data directory on the backup node:

rsync -avrP /data/backups/ <mysql_datadir>

Replace <mysql_datadir> with the path to the MySQL data directory (e.g., /var/lib/mysql/).

5. Prepare the Incremental Backup

On the backup node, prepare the incremental backup to apply the changes to the base backup:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base_<last_date>

This command applies the incremental changes to the base backup directory.

MySQL Incremental Backup, Copy, Prepare, and Restore Pipeline

This pipeline automates the process of taking MySQL incremental backups, copying them to a remote server, preparing the backups, and restoring them. It uses Jenkins and integrates commands such as xtrabackup for backup creation, tar for compression, and rsync for restoring the files.

Pipeline Stages

1. Taking Incremental Backup

  • Purpose: Creates an incremental backup for MySQL using xtrabackup based on the previous day’s backup.

  • Prerequisites:

    • The base or previous day’s incremental backup must exist in the directory (/data/backups/mysql/pssb/inc/inc_<lastDate>).

  • Steps:

    1. Checks if the previous backup directory exists.
    2. Creates a new directory for the current backup (/data/backups/mysql/pssb/inc/inc_<currentDate>).
    3. Runs the xtrabackup command to take an incremental backup.
    4. Compresses the backup into a .tar.gz file.

  • Error Codes:

    • 2: Previous backup directory not found.
    • 3: Incremental backup creation failed.

Example Command:

sudo /usr/bin/xtrabackup --backup \
    --target-dir=/data/backups/mysql/pssb/inc/inc_16-12-24 \
    --incremental-basedir=/data/backups/mysql/pssb/inc/inc_15-12-24 \
    -u root -pGr@mSev@k@123

2. Copying Backups to Remote Server

  • Purpose: Copies the compressed backup file to a remote backup server.

  • Prerequisites:

    • Ensure the SSH key is configured (/home/devopsadmin/.ssh/id_rsa).
    • Confirm the remote server is reachable.

  • Steps:

    1. Transfers the backup file using scp with the specified port and identity file.

  • Error Codes: N/A

Example Command:

scp -i /home/devopsadmin/.ssh/id_rsa -P 59222 \
    /data/backups/mysql/pssb/inc/compressed/inc_16-12-24.tar.gz \
    devopsadmin@103.210.73.207:/data/backups/mysql/compressed/inc_16-12-24.tar.gz

3. Incremental Backup Preparation

  • Purpose: Prepares the incremental backup for restoration by applying logs.

  • Prerequisites:

    • Ensure the compressed backup file exists on the remote server.

  • Steps:

    1. Verifies if the backup file exists on the remote server.
    2. Extracts the compressed backup file to a specified directory.
    3. Runs xtrabackup to prepare the incremental backup and apply logs to the base directory.

  • Error Codes:

    • 4: Backup file not found on the remote server.
    • 5: Preparing the incremental backup failed.

Example Command:

sudo tar -xzvf /data/backups/mysql/compressed/inc_16-12-24.tar.gz -C /data/backups/mysql/inc_16-12-24
sudo xtrabackup --prepare --apply-log-only \
    --target-dir=/data/backups/mysql/base_15-12-24 \
    --incremental-dir=/data/backups/mysql/inc_16-12-24

4. Restoring the Backup

  • Purpose: Restores the prepared backup to the MySQL data directory.

  • Prerequisites:

    • Stop the MySQL server before restoring.

  • Steps:

    1. Uses rsync to copy the backup data to the MySQL data directory.
    2. Sets the appropriate file permissions for MySQL.
    3. Starts the MySQL server.
    4. Renames the backup directory with the current date for consistency.

  • Error Codes:

    • 6: Failed to replace files during restoration.

Example Command:

sudo rsync -avrP /data/backups/mysql/base_15-12-24/ /data/db/pxc/mysql/
sudo chmod 750 -R /data/db/pxc/mysql/
sudo chown -R mysql:mysql /data/db/pxc/mysql/
sudo systemctl start mysql@bootstrap

Error Handling

This pipeline includes custom error messages mapped to the following status codes:

Status Code Description
0 MySQL incremental backup successful, database is consistent
1 MySQL backup is inconsistent
2 Base directory files not found or cannot get sequence no
3 Backup creation failed on MySQL server
4 Failed to copy backup to backup server
5 Backup preparation failed
6 Importing backup failed on the backup server

5. Rename the Base Folder

After restoring the incrementally prepared backup, rename the base folder to the current date. This ensures it can be used as the base for the next incremental backup:

mv /data/backups/base_<last_date> /data/backups/base_<current_date>
  • Replace <last_date> with the previous base folder’s date.
  • Replace <current_date> with the current date.

Note: Renaming the folder is essential to use it as the base directory for the next incremental backup.

6. Count Rows on Both Demo Node and Backup Node

To verify consistency between the demo node and the backup node, use the genReport.sh script. This script compares row counts in both databases.

Command to Run the Script:

bash genReport.sh -u root -p Gr@mSev@k@123 -d psdb_sb
  • -u: MySQL username (e.g., root).
  • -p: MySQL password (e.g., Gr@mSev@k@123).
  • -d: Database name (e.g., psdb_sb).

This script will generate a report of row counts, ensuring that the demo node and backup node data are consistent.

MySQL Backup Failure Recovery Process

1. Overview

The backup process failed on February 07, 2025, and as of February 10, 2025, recovery needs to be initiated

2. Steps to Recover from Backup Failure

Step 1: Verify the LSN in the Base Folder

  1. Navigate to the MySQL backup base directory on the backup node: 
    cd /data/backups/mysql/demo/inc/base_07-02-25
  2. Check the xtrabackup_checkpoints file to retrieve the LSN: 
    cat xtrabackup_checkpoints

    root@pssb1bvm006:/data/backups/mysql# cat base_07-02-25/xtrabackup_checkpoints 
 backup_type = log-applied
 from_lsn = 0
 to_lsn = 8239522152
 last_lsn = 8239522152
 flushed_lsn = 8239492299
 redo_memory = 0
 redo_frames = 0
  3. Identify the to_lsn value. This will be used to determine the correct incremental backup.

Step 2 : Identify the Incremental Backup Folder

  1. Identify the folder corresponding to the LSN value retrieved in Step 1.
devopsadmin@pssb1abm003:/data/backups/mysql/pssb/inc$ sudo grep -iR "to_lsn = 8239522152" .
./inc_07-02-25/xtrabackup_info:innodb_to_lsn = 8239522152
./inc_07-02-25/xtrabackup_checkpoints:to_lsn = 8239522152
  1. Rename the identified incremental backup folder: 
    mv /data/backups/mysql/demo/inc/inc_07-01-25 /data/backups/mysql/demo/inc/inc_prev_YYYYMMDD
    example: 
    mv inc_07-02-25/ inc_09-02-25
    Replace YYYYMMDD with the actual date.

Step 3: Rename the Base Folder

  1. Rename the current base folder to mark it as the previous backup: 
    mv /data/backups/mysql/demo/inc/base_07-02-25 /data/backups/mysql/demo/inc/base_prev_YYYYMMDD
    example: 
    mv base_07-02-25/ base_09-02-25/
    Replace YYYYMMDD with the actual date.

Step 4: Run the Backup Build Process

Scylla Backup

This Jenkins pipeline automates the process of backing up, copying, restoring, and verifying data from a Scylla database. The pipeline includes the following stages: Get and Restore Schema, Take Backup, Copy Backup, Restore Backup, Count Rows in Demo and Count Rows in Demo. Below is an explanation of how each stage works, with an example for each.

Pipeline Overview

The pipeline is structured to perform the following tasks:

  1. Generate and Restore Schema: Generate the schema using a script and restore it on a remote Scylla instance.
  2. Backup Scylla Data: Flush the data, take a snapshot of the keyspace, and compress it into a tarball.
  3. Copy Backup to Remote Server: Transfer the backup files to a remote server.
  4. Restore Backup: Stop Scylla, drain the data, remove the existing files, and restore the backup.
  5. Count Rows in Demo: Count rows in a demo keyspace and generate a report.
  6. Count Rows in Backup: Count rows in a backup keyspace and generate a report.

Stage Breakdown

1. Stage: getAndRestoreSchema

Purpose: This stage generates the schema from the database and restores it.

Steps:

  • The genSchema.sh script is executed to generate the schema file (schema.cql).
  • File ownership is adjusted to ensure the devopsadmin user has access.
  • If schema restoration is enabled (restoreSchema is true), the generated schema is copied to the remote server and restored using cqlsh.

Example:

  • The schema for the psds_sb keyspace is generated and then restored on the remote server using the scp command.

2. Stage: takeBackup

Purpose: This stage takes a snapshot of the keyspace and compresses it into a tarball.

Steps:

  • Flush: The nodetool flush command is used to ensure that all memory data is pushed to disk.
  • Snapshot Creation: A snapshot of the keyspace is created using nodetool snapshot.
  • Table Data Extraction: The tables in the psds_sb keyspace are identified and their data directories are located.
  • Compression: The snapshot data for each table is compressed into a .tar.gz file.

Example:

  • A snapshot for the psds_sb keyspace is created and compressed into a file named tableName_snapshot.tar.gz.

3. Stage: Copy Backups

Purpose: This stage copies the backup files to a remote server for storage.

Steps:

  • The backup file (scylla_fullbkp_${currentDate}.tar.gz) is copied to the remote server using scp.
  • If the transfer fails, an error message is displayed.

Example:

  • The final compressed backup file is transferred to the pssb1bvm006 server for storage.

4. Stage: Restore Backups

Purpose: This stage restores the backup data to the Scylla instance.

Steps:

  • The backup file is extracted on the remote server to a specific directory.
  • Scylla is drained and stopped before restoring the backup.
  • Existing table data is removed from the target location, and the new backup data is extracted into the table directories.
  • After restoration, Scylla is restarted.

Example:

  • The scylla_fullbkp_${currentDate}.tar.gz file is extracted, and the corresponding table snapshots are restored on the remote server.

5. Stage: CountRowsInDemo

Purpose: This stage counts the rows in a demo keyspace and generates a report.

Steps:

  • The genReport.sh script is executed to count the rows in the demo keyspace.

Example:

  • The script generates a report on the number of rows in the psds_sb keyspace on the demo server.

5. Stage: CountRowsInBackup

Purpose: This stage counts the rows in a demo keyspace and generates a report.

Steps:

  • The genReport.sh script is executed to count the rows in the demo keyspace.

Example:

  • The script generates a report on the number of rows in the psds_sb keyspace on the demo server.

Error Handling

The pipeline includes error handling to manage various failure scenarios. The status codes and their corresponding error messages are as follows:

Status Code Message
0 Scylla Full Backup backup successful and Database is consistent
1 Scylla backup is inconsistent
2 Error Flushing the keyspace scylla might not in running state
3 Error while Creation of Snapshot scylla on Prod server might not running
4 Failed to Copy backup to Backup server
5 Backup Restoration failed
6 Error finding the table in Backup server, schema may be changed

Each stage checks for potential issues and updates the statusCode variable accordingly. If an error occurs, the pipeline halts and the corresponding error message is displayed.

Example Workflow

  1. Get and Restore Schema: The schema is generated from the source database and restored on the target server.
  2. Take Backup: A snapshot of the psds_sb keyspace is taken and compressed into a tarball.
  3. Copy Backup: The backup tarball is copied to a remote server for storage.
  4. Restore Backup: The backup is restored on a remote Scylla instance by stopping the server, removing old data, and extracting the backup into the correct directories.
  5. Count Rows in Demo: The row count for the demo keyspace is generated and reported.