Periodic Database Backup to Gitlab Private Repository Using Flysystem and Cronjob

Lately, I have worked on a number of website applications that have touched on the issue of data distribution. Some of my clients ask to make regular backups on a separate server and some ask my team to save it for data backup.

One of my team is worried that if the data is stored only on certain servers, the risk of data loss may occur even though the server provider provides backup services every day, but that too must go through some of their business procedures, whereas we need it faster.

So I am thinking of creating a Cronjob that utilizes File I / O to distribute data to the Private Repository on Gitlab, in that repository I certainly can invite several teams to manage and monitor the changes.

[sounds stupid]

Ok, I need the Flysystem Library to do CRUD transactions with the Gitlab API.

I will name every file that is created in the Repository Folder in accordance with the unique identity of the existing data, so that we can easily identify it again when it will make changes to the data.

What do you think, friend?

Requirements:

1. Flysystem Library by Frank de Jonge
2. Flysystem Gitlab Adapter by Roy Voetman
3. Mysqldump PHP by ifsnop
4. Gitlab Access Token

1. Install flysystem using composer:

composer require league/flysystem

2. Install Gitlab Adapter

composer require royvoetman/flysystem-gitlab-storage

3. Install Mysqldump PHP

composer require ifsnop/mysqldump-php

3. Create Gitlab Access Token

Visit Gitlab Access Token page and create your access token:

Once the requirements are met, it's time to code to perform a backup with a cronjob.

But before we start coding, it's a good idea to first understand how the story goes.

Mysqldump process dump database to disk

$db_host = 'YOUR_DB_HOST';
$db_user = 'YOUR_DB_USER';
$db_pass = 'YOUR_DB_PASS';
$db_name = 'YOUR_DB_NAME';

// Create database name
$database_name = 'backup-on-YOUR-DB-NAME-'.date('d-m-Y').'.sql';
// Define path for dump on disk
$app_path = 'YOUT_PATH';

if ( file_exists($app_path . $database_name) ) {
    $database_name = date('His').'-'.$database_name;
    // Dump database
    $dump = new IMysqldump\Mysqldump('mysql:host='. $db_host .';dbname='. $db_name .'', $db_user, $db_pass);
    $dump->start( $app_path . $database_name );
} else {
    // Dump database
    $dump = new IMysqldump\Mysqldump('mysql:host='. $db_host .';dbname='. $db_name .'', $db_user, $db_pass);
    $dump->start( $app_path . $database_name );
}

Read database has been backup from disk

$file_content = file_get_contents( $app_path . $database_name );

Write code to upload database from disk to Gitlab Private Repository.

Here I create a folder as a group for the database that is backed up by month and year as a folder in the Gitlab Private Repository.

$dir_backup = 'db-'.strtolower(date('F-Y')); // Result like: db-december-2021

And write code flysystem code

// Gitlab Flysystem Config
$personal_access_token    = "GITLAB_ACCESS_TOKEN";
$project_id               = "PROJECT_ID";
$branch                   = "BRANCH_NAME";
$cloud_url                = "https://gitlab.com"; // <-- default or use your gitlab instance url

// Create a GitLab Client to talk with the API
$client = new Client( $personal_access_token , $project_id, $branch, $cloud_url);
// Create the Adapter that implements Flysystems AdapterInterface
$adapter = new GitlabAdapter($client);
// Use FileSystem
$filesystem = new Filesystem($adapter);


// Check if directory backup is exists on repository
if ( $filesystem->has($dir_backup) ) {
    // If exists then write database inside
    $filesystem->write($dir_backup.'/'.$database_name, $file_content);
} else {
    // If not exists then create directory
    $filesystem->createDir($dir_backup);
    // then write database inside
    $filesystem->write($dir_backup.'/'.$database_name, $file_content);
}

Now after upload process is done, you can unlink or remove database file from disk.

unlink($app_path . $database_name);

Bonus 🥳

If you want to get notification after backup process is done from your smartphone or your desktop, you can use this awesome free tools ntfy.sh Made with ❤️ by Philipp C. Heckel. Don't forget to say thanks for Philipp C. Heckel

file_get_contents('https://ntfy.sh/<YOUR_NTFY_TOPIC>', false, stream_context_create([
  'http' => [
      'method' => 'POST',
      'header' =>
          "Content-Type: text/plain\r\n" .
          "Title: Daily Database Backup!\r\n" .
          "Priority: default\r\n" .
          "Tags: bell,tada,partying_face",
      'content' => 'Backup successful 😀'
  ]
]));

Please read the documentaion for more information.

Now, where the complete code!? Aaarrgh...

<?php

$db_host = 'YOUR_DB_HOST';
$db_user = 'YOUR_DB_USER';
$db_pass = 'YOUR_DB_PASS';
$db_name = 'YOUR_DB_NAME';

// Create database name
$database_name = 'backup-on-YOUR-DB-NAME-'.date('d-m-Y').'.sql';
// Define path for dump on disk
$app_path = 'YOUT_PATH';

if ( file_exists($app_path . $database_name) ) {
  $database_name = date('His').'-'.$database_name;
  // Dump database
  $dump = new IMysqldump\Mysqldump('mysql:host='. $db_host .';dbname='. $db_name .'', $db_user, $db_pass);
  $dump->start( $app_path . $database_name );
} else {
  // Dump database
  $dump = new IMysqldump\Mysqldump('mysql:host='. $db_host .';dbname='. $db_name .'', $db_user, $db_pass);
  $dump->start( $app_path . $database_name );
}

$file_content = file_get_contents( $app_path . $database_name );

$dir_backup = 'db-'.strtolower(date('F-Y')); // Result like: db-december-2021

// Gitlab Flysystem Config
$personal_access_token    = "GITLAB_ACCESS_TOKEN";
$project_id               = "PROJECT_ID";
$branch                   = "BRANCH_NAME";
$cloud_url                = "https://gitlab.com"; // <-- default or use your gitlab instance url

// Create a GitLab Client to talk with the API
$client = new Client( $personal_access_token , $project_id, $branch, $cloud_url);
// Create the Adapter that implements Flysystems AdapterInterface
$adapter = new GitlabAdapter($client);
// Use FileSystem
$filesystem = new Filesystem($adapter);


// Check if directory backup is exists on repository
if ( $filesystem->has($dir_backup) ) {
    // If exists then write database inside
    $filesystem->write($dir_backup.'/'.$database_name, $file_content);
} else {
    // If not exists then create directory
    $filesystem->createDir($dir_backup);
    // then write database inside
    $filesystem->write($dir_backup.'/'.$database_name, $file_content);
}

file_get_contents('https://ntfy.sh/<YOUR_NTFY_TOPIC>', false, stream_context_create([
  'http' => [
      'method' => 'POST',
      'header' =>
          "Content-Type: text/plain\r\n" .
          "Title: Daily Database Backup!\r\n" .
          "Priority: default\r\n" .
          "Tags: bell,tada,partying_face",
      'content' => 'Backup successful 😀'
  ]
]));

Don't forget to create a cronjob that points to the script that was created to do the backup!

Have nice day 😷 and stay safe!