DEV Community: KM

MySQL DB backup using Bash script

KM — Sun, 05 Jan 2020 15:53:24 +0000

Backups are important for any data. Taking DB backups for MySQL/MariaDB is a cinch with the following script.

Bash script

Create a file called backup_script.sh. Replace the credentials and path to suit your system.

#!/bin/bash

USER='username'
PASSWORD='password'
DATABASE='ALL_DB'
# Directory should always be mentioned with a trailing slash
DIRECTORY='/path/for/db_backup/'
DOW=$(date +"%A")

# Delete backups which are 7 days old
rm -f $DIRECTORY$DATABASE-$DOW.sql.gz > /dev/null 2>&1

#Backup the DB and Compress it
/usr/bin/mysqldump --user=$USER --password=$PASSWORD --host=$HOST --all-databases --skip-lock-tables > $DIRECTORY$DATABASE-$DOW.sql
/bin/gzip $DIRECTORY$DATABASE-$DOW.sql

The script will create/keep in total 7 backups, with the day of the week as its suffix. Escape $ sign if its used in the PASSWORD variable.

Provide execute permission to the script
$ chmod 755 backup_script.sh
Make sure to verify the backup-file is generated by executing the script:
$ ./backup_script.sh

CRON Job

Add the following line to your crontab. This will automatically execute a backup daily at 0400 hours.
0 4 * * * /bin/sh /path/to/backup_script.sh >> /dev/null 2>&1

SSHBlack - Securing open linux servers

KM — Sun, 05 Jan 2020 15:53:21 +0000

SSHBlack is a must have utility for open servers in the public network(internet). Download the latest versions from the website. The installation steps are as follows:

$ tar -zxf sshblackv281.tar.gz
$ mv sshblack /usr/local/sshblack
$ cd /usr/local/sshblack
# provide excutable permission for the script
$ chmod 755 sshblack.pl
# Create a new chain BLACKLIST
$ iptables -N BLACKLIST

Next we need to update the sshblack.pl. Open your favourite editor and update the variables

# this will run the process in the background
my($DAEMONIZE) = '1';
# The INPUT log file you want to monitor; If Ubuntu OS its '/var/log/auth.log'; if its RedHat based OS '/var/log/secure';
my($LOG) = '/var/log/auth.log';
# Update your static IP which should never be blacklisted, displayed as WWW.XXX.YYY.ZZZ;
my($LOCALNET) = '^(?:127\.0\.0\.1|WWW\.XXX\.YYY\.ZZZ)';

Save the file. ./sshblack.pl will start the script as a background process. The /var/log/sshblacklisting file will log the IP information of clients accessing/attacking the server.

Once the server is attacked more than 5 times(default value of variable $MAXHITS), a block rule is added to iptables with the IP information. This prevents new connections to the server from the attacker, in turn preventing the server from brute force attempts. A sample of IP’s which is blacklisted in my server using the script is listed below:

$ iptables -L
Chain BLACKLIST (1 references)
target     prot opt source destination
DROP       all  --  132.232.54.102       anywhere
DROP       all  --  139.59.84.55         anywhere
DROP       all  --  222.187.232.212      anywhere
DROP       all  --  222.187.225.10       anywhere
DROP       all  --  222.187.238.32       anywhere
DROP       all  --  58.241.250.152       anywhere

If you clear the iptables, make sure to clear the text database which keeps track of the attacked IP address.
echo '' > /var/tmp/ssh-blacklist-pending

emSigner USB DSC token installation and usage on macOS for filing GST

KM — Sun, 05 Jan 2020 15:52:52 +0000

Prerequisites: Chrome browser, ePass DSC USB token. I assume that you are using Google Chrome browser.

Steps

Download and install Java 8 from Java website.
Since Mac OS doesn't support Java 8, download and install Java for OS X 2017–001 from Apple website.
Download and install ePass 2003 (Mac) from emudhra website.
Download emSigner for Mac from GST website.
Open emSigner installation utility and copy the emSigner folder to Desktop.
Open Spotlight (use the keys) – Command+Space.
In Spotlight, enter - terminal. Open the application.
Once the terminal application opens, enter - cd Desktop/emSigner. Press enter key.
Execute the following in the next line - java -jar emsigner_WS_OMM.jar. The following message will be displayed:
Go to MacOS > System Preferences > Java. In the Java Control Panel, open the Security tab.
Click on Edit Site List. Add https://127.0.0.1:1585 in the exceptions list. Save and close the panel.
Restart the machine.
Execute steps (6), (7), (8) and (9) to run the emSigner listner application.
Insert the USB DSC token into the USB slot.
Open Spotlight (use the keys) – Command+Space. Enter EnterSafeUserMgr. It will display the DSC token.
Open this URL in your Chrome browser https://127.0.0.1:1585, Click on Advanced button & again click Add certificate exception.
Now file your GST using the DSC key.

Since the application is installed on your machine, you don't need to execute all the steps to file GST again. For each month's filing, you only need to execute the steps 13 through 17.

Simple Laravel DB backups for MySQL/MariaDB

KM — Mon, 19 Aug 2019 15:13:54 +0000

Databases(DB) persists all critical information related to a web application. Hence DB backups are a necessity. I will post a simple code snippet suitable for Laravel applications to backup your DB to AWS S3,. I assume that you are using EC2 machine for hosting the Laravel application.

Since configuring and setting up a S3 bucket is out of scope for the article, I will outline the steps for reference

Start by creating a S3 bucket(eg: db-backups) which is private.
Enable versioning for the bucket, so that overwrite will still preserve the backups.
Complete creating the bucket and note down ARN(eg: arn:aws:s3:::db-backups/*).
Create an IAM user(eg: s3user) having programatic write access without selecting any existing policies.
Note down the API access/secret keys and the ARN of the user(eg: arn:aws:iam::8XX45XXXX57:user/s3user).

Under the Permissions tab of the IAM user, add the following as an Inline policy.

{
    "Version":"2012-10-17",
    "Statement":[
       {
          "Effect":"Allow",
          "Action":[
             "s3:PutObject"
          ],
          "Resource":[
             "arn:aws:s3:::db-backups/*"
          ]
       }
    ]
}

In the S3 bucket policy, add the following:

    {
       "Version": "2012-10-17",
       "Statement": [
          {
             "Sid": "statement1",
             "Effect": "Allow",
             "Principal": {
                "AWS": "arn:aws:iam::8XX45XXXX57:user/s3user"
             },
             "Action": [
                 "s3:PutObject"
             ],
             "Resource": [
                "arn:aws:s3:::db-backups/*"
             ]
          }
       ]
    }

Make sure to update the user, bucket ARN's. This IAM user will only be able to write to the bucket. No other permissions are granted for security reasons.

Coming back to the main topic, DB backups in Laravel. If you are using RDS as the DB store, EC2 machine won't have the mysqldump command. Hence, please install them using (Ubuntu distros)

sudo apt-get install mysql-client

Once installed, execute the following commands in shell and note the path
which mysqldump (eg: /usr/bin/mysqldump) &
which gzip (eg: /bin/gzip)

Update the .env file variables of the Laravel application

AWS_ACCESS_KEY_ID=XXXXXXX
AWS_SECRET_ACCESS_KEY=XXXXXXX
AWS_DEFAULT_REGION=eu-central-1
DB_AWS_BUCKET=db-backups

MYSQLDUMP_EXE=/usr/bin/mysqldump
GZIP_EXE=/bin/gzip

Read through the Laravel documentation and install packages for S3 access. Create a new disk in config/filesystems.php file, eg:

        's3db' => [
            'driver' => 's3',
            'key' => env('AWS_ACCESS_KEY_ID'),
            'secret' => env('AWS_SECRET_ACCESS_KEY'),
            'region' => env('AWS_DEFAULT_REGION'),
            'bucket' => env('DB_AWS_BUCKET'),
        ],

To utilise Laravel configuration caching, I have created a file called config/env.php, where custom ENV variables are configured. Its similar to config/app.php, except it holds variables which are newly introduced in the application. A sample follows:

<?php 

return [
    'mysqldump_exe' => env('MYSQLDUMP_EXE'),
    'gzip_exe' => env('GZIP_EXE'),
];

I prefer to create custom classes, under the app/Classes directory. Feel free to use yours, but change the namespace of the following file accordingly. Create a file app/Classes/DbBackup.php. The code is self explanatory.

<?php

namespace App\Classes;

use Illuminate\Support\Facades\Storage;

class DbBackup
{
    private $fileName, $tmpFile, $gzipFile;

    /**
     * Initialise the variables
     *
     * @return void
     */
    public function __construct()
    {
        $this->fileName = config('database.connections.mysql.database').'-'.date('Y-m-d').'.sql';
        $this->tmpFile = '/tmp/'.$this->fileName;
        $this->gzipFile = $this->tmpFile.'.gz';
    }

    /**
     * Create the backup
     *
     * @return object this
     */
    protected function createBackup()
    {
        exec(config('env.mysqldump_exe').
            ' --user='.config('database.connections.mysql.username').
            ' --host='.config('database.connections.mysql.host').
            ' --password='.config('database.connections.mysql.password').
            ' --databases '.config('database.connections.mysql.database').
            ' > '.$this->tmpFile);

        exec(config('env.gzip_exe').' '.$this->tmpFile);

        return $this;
    }

    /**
     * Upload to S3
     *
     * @return object this
     */
    protected function uploadToS3()
    {
        Storage::disk('s3db')
            ->put($this->fileName.'.gz', fopen($this->gzipFile, 'r'));

        return $this;
    }

    /**
     * Delete the temp file
     *
     * @return object this
     */
    protected function removeBackup()
    {
        sleep(2);
        unlink($this->gzipFile);
    }

    /**
     * Method which calls all sub functions
     *
     * @return void
     */
    public function init()
    {
        $this->createBackup()
            ->uploadToS3()
            ->removeBackup();
    }

}

Now, we require the DB backups to be run daily, only in Production systems. Hence update the method in app/Console/Kernel.php.

    protected function schedule(Schedule $schedule)
    {
        // this code will create the automatic DB backups.
        $schedule->call('App\Classes\DbBackup@init')
            ->daily()
            ->environments(['production']);
    }

The above code will only run if the Laravel scheduler is configured.

That's it, folks! DB backups will be created & saved to S3 daily at midnight.