Automating MySQL Dumps: Jenkins Through a Bastion to Local Servers

Database backups that depend on someone remembering are not backups. Here the MySQL server sits in a private network that only a bastion host can reach, and the copies must end up on servers in the office. Jenkins already has a scheduler, credentials store and history, so it can run the whole job.

Versions in use on this date. Jenkins 2.426.1 LTS was released on 15 November 2023 and needs Java 17 (Java 11 support ends in 2024). MySQL 8.0 and Ubuntu 22.04.

The path of the data

flowchart TB
  J[Jenkins agent] ==>|SSH ProxyJump| B[Bastion host<br/>public IP, locked down]
  B ==>|SSH| M[MySQL server<br/>private subnet]
  M -->|mysqldump piped through gzip| J
  J -->|rsync over SSH| L1[Local backup server 1]
  J -->|rsync over SSH| L2[Local backup server 2]
  S[Jenkins credentials] -.-> J

The dump streams from MySQL through the SSH connection straight to the agent, so nothing is written to the database server’s disk (which may already be short of space) and the bastion never stores data. The agent then pushes the file to the local servers.

Prepare the access

On the MySQL server create a dedicated backup user with only what mysqldump needs:

CREATE USER 'backup'@'localhost' IDENTIFIED BY '<long random password>';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS ON *.* TO 'backup'@'localhost';

Keep the password in a file on the MySQL server (/etc/mysql/backup.cnf, owner root, mode 600) so it never appears in a process list or in the Jenkins log:

[client]
user=backup
password=<long random password>

Create one SSH key pair for Jenkins, authorise it on the bastion, the MySQL server and the local servers, and store the private key in Jenkins as an SSH Username with private key credential (backup-ssh). Restrict it on the servers with command= or from= options in authorized_keys where you can.

The pipeline

Install the SSH Agent plugin. Then:

pipeline {
  agent { label 'backup' }
  triggers { cron('H 1 * * *') }
  options { timeout(time: 2, unit: 'HOURS'); buildDiscarder(logRotator(numToKeepStr: '30')) }
  environment {
    BASTION = 'ops@bastion.example.com'
    DBHOST  = 'ops@10.0.5.20'
    STAMP   = sh(returnStdout: true, script: 'date +%F-%H%M').trim()
  }
  stages {
    stage('Dump') {
      steps {
        sshagent(credentials: ['backup-ssh']) {
          sh '''
            set -o pipefail
            mkdir -p dumps
            ssh -o StrictHostKeyChecking=yes -J "$BASTION" "$DBHOST" \
              "mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
               --single-transaction --routines --triggers --events --all-databases" \
              | gzip -9 > dumps/all-$STAMP.sql.gz
          '''
        }
      }
    }
    stage('Verify') {
      steps {
        sh '''
          gzip -t dumps/all-$STAMP.sql.gz
          test $(stat -c %s dumps/all-$STAMP.sql.gz) -gt 1024
          zcat dumps/all-$STAMP.sql.gz | tail -n 1 | grep -q "Dump completed"
          sha256sum dumps/all-$STAMP.sql.gz > dumps/all-$STAMP.sql.gz.sha256
        '''
      }
    }
    stage('Copy') {
      steps {
        sshagent(credentials: ['backup-ssh']) {
          sh '''
            for H in backup1.office.lan backup2.office.lan; do
              rsync -e "ssh -o StrictHostKeyChecking=yes" -a dumps/all-$STAMP.sql.gz* backup@$H:/srv/mysql-dumps/
              ssh backup@$H "find /srv/mysql-dumps -name 'all-*.sql.gz*' -mtime +14 -delete"
            done
          '''
        }
      }
    }
  }
  post {
    always  { sh 'rm -rf dumps' }
    failure { mail to: 'devops@example.com', subject: "MySQL backup FAILED #${env.BUILD_NUMBER}", body: "${env.BUILD_URL}" }
  }
}

Why it is written this way:

• set -o pipefail makes the stage fail if mysqldump fails, not only if gzip fails. Without it a broken dump produces a small “successful” file.
• --single-transaction gives a consistent snapshot of InnoDB tables without locking them. Use it only when tables are InnoDB.
• -J (ProxyJump) sends the connection through the bastion without storing the key there. StrictHostKeyChecking=yes needs the host keys in known_hosts on the agent; add them once with ssh-keyscan after checking the fingerprints.
• The verify stage catches truncated and empty files, and the trailing “Dump completed” line only exists if the dump finished.
• Two local servers means one failure does not lose the day’s backup. The -mtime +14 clean-up keeps fourteen days on each.

Test the restore, not just the backup

Once a month, restore the newest file into a scratch MySQL (a container is enough) and run a few row counts:

zcat all-2023-11-21-0100.sql.gz | docker exec -i scratch-mysql mysql -uroot -p"$ROOTPW"

A backup that was never restored is only a hope.

Notes

Run the job on an agent, not the controller. Put the schedule outside business hours; H spreads jobs so they do not all start on the minute. Encrypt dumps if the local servers are shared (gpg --symmetric or age), because a dump contains every table, including password hashes.