OurSync Project - Linux Bash

OurSync Project

This project's goal is to make personal server management quick and easy for the everyday user. At the moment, this project is very far from finished. My goal for this project is to have one install script to setup everything for the end user with ease. At the moment, the focus has been on functionality. The setup of this project is very simple. You have the oursync database, remoteSysMaint.sh file, rsync.sh file, and the rs.config file. What these files do are below:

rs.config file:

The rs.config file is meant for the configurations. Any line in this file can be changed if desired. The recommended way to use this file is to fill in the "Database Information" section and then fill out the rest of the configuration within the database tables (Table names located under '#Table Information').

#Database Information
dun='<databaseUserName>'
dpw='<databasePassword>'
dip='<databaseHostName>'
dn='oursync'

#Date Information
dt=$(date +%s)

#Secret Key
secret_key=$(cat ${__dir}/secret.key)

#Table Information
generalConfig='generalConfig'
backupConfig='backupConfig'
processLogs='processLogs'
rmFileConfig='rmFileConfig'
sysMaintConfig='sysMaintConfig'

#Error Log Location
ERR_OUTPUT_ALL=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='ERR_OUTPUT_ALL'")_$$
ERR_OUTPUT=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='ERR_OUTPUT'")_$$

#smtp Information
mailFrom=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='mailFrom'")
mailRcpt=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='mailRcpt'")
smtpsUser=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='smtpsUser'")
smtpsPass=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='smtpsPass'")
msgSubject=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='msgSubject'")
msgMain=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='msgMain'")
msgError=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='msgError'")
smtpsURL=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='smtpsURL'")
smtpsPort=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='smtpsPort'")
smtpsProtocol=$(mysql --host=$dip --user=$dun --password=$dpw -BNe "SELECT value FROM $dn.$generalConfig WHERE name='smtpsProtocol'")

#Folder Information
backupLocationsID=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT idBC FROM $dn.$backupConfig WHERE useBackup = 'Y'")
rmFilesLocationID=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT idRFC FROM $dn.$rmFileConfig WHERE useLocation = 'Y'")
systemMaintID=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT smID FROM $dn.$sysMaintConfig WHERE sysMainUse = 'Y'")

rsync.sh file:

The rsysc.sh file is meant for the backup portion of this project. The simplest way to utilize this is by entering in the source, destination, description, and any rsync parameters into the backupConfig table. The code below will cycle through them until they are all completed. Another functionality of this file to remove files that are x number of days old. In order to use this functionality, the location, description, and days need to be filled out within the rmFileConfig table. Just as the backupConfig table, this table will also run any number of locations within. Everything that is done within this file is logged to the processLogs table with the status, start date, end date, commends, and ERROR messages. Also, if there is the SMTP information set up within the generalConfig table, an email will be sent to the mailRcpt user. The messages that are sent can also be customized within this table.

#!/bin/bash

__dir="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source ${__dir}/rs.config

function FileCheck()
{
    if [[ ! -d "$1" ]];
        then
            echo "The $1 file does not exist. The $1$2 file will mount now."
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, end, ERROR, server) VALUES ('rsync - $1$2 folder', 'FAIL', now(), now(), '$1 was not mounted. $1$2 mounted.', 'rsyncServer')"
            sudo mount $1$2
        else
            echo "The $1$2 file exists. Moving on."
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, end, server) VALUES ('rsync - $1$2 folder', 'PASS', now(), now(), 'rsyncServer')"
    fi
}

function RsyncProcess()
{
    mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, tmp, server) VALUES ('rsync - $4', 'start', now(), 'rsync$dt', 'rsyncServer')"
    rsync -$3 --exclude=.* $1/* $2/ 2> "$ERR_OUTPUT"
    ERR=$(sed "s/'/_/g" $ERR_OUTPUT)

    if [ $? -eq 0 ]
        then
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET end = now(), tmp = '', status = 'finished', ERROR = '$ERR' WHERE tmp = 'rsync$dt'"
        else
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET end = now(), tmp = '', status = 'FAIL', ERROR = '$ERR' WHERE tmp = 'rsync$dt'"
    fi

    echo $ERR>>$ERR_OUTPUT_ALL

    sleep 1
    rm -f $ERR_OUTPUT
}

function RsyncPerm()
{
    chmod -R 777 $1
    if [ $? -eq 0 ];
        then
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, end, server) VALUES ('Permissions - $1', 'finished', now(), now(), 'rsyncServer')"
        else
            mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, end, server) VALUES ('Permissions - $1', 'FAIL', now(), now(), 'rsyncServer')"
    fi
}

function FileRemove()
{
    mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, tmp, server) VALUES ('Remove Old Files - $2', 'start', now(), 'rsync$dt', 'rsyncServer')"
    ERR_OUTPUT="/tmp/rsync_err_$$"
    fpath=$1*
    find $fpath -type f -mtime +30 -exec ls -ltrd {} \; >> $ERR_OUTPUT
    find $fpath -type f -mtime +30 -exec rm -rf {} \;
    count=$(cat /tmp/file.out | wc -l)
    ERR=$(sed "s/'/_/g" $ERR_OUTPUT)
    mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET end = now(), tmp = '', status = 'finished', comments = '$ERR' WHERE tmp = 'rsync$dt'"
    rm -f $ERR_OUTPUT
}

#FileCheck <folder to check>

sleep 1

mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, tmp, server) VALUES ('rsync', 'start', now(), 'srsync$dt', 'rsyncServer')"

for bkLID in $backupLocationsID
    do
        source=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT source FROM $dn.$backupConfig WHERE idBC=$bkLID")
        destination=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT destination FROM $dn.$backupConfig WHERE idBC=$bkLID")
        parameters=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT parameters FROM $dn.$backupConfig WHERE idBC=$bkLID")
        backupDescription=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT description FROM $dn.$backupConfig WHERE idBC=$bkLID")
        RsyncProcess $source $destination $parameters "$backupDescription"
        RsyncPerm $destination
    done

for rmFileLID in $rmFilesLocationID
    do
        removeLocation=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT location FROM $dn.$rmFileConfig WHERE idRFC=$rmFileLID")
        removeDescription=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT description FROM $dn.$rmFileConfig WHERE idRFC=$rmFileLID")
        FileRemove $removeLocation "$removeDescription"
    done

mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET end = now(), tmp = '', status = 'finished' WHERE tmp = 'srsync$dt'"
edt=$(date +'%m/%d/%Y %H:%M:%S')
ERRall=$(sed -e '/^$/d' $ERR_OUTPUT_ALL)

curl --url $smtpsProtocol'://'$smtpsURL:$smtpsPort --ssl-reqd \
    --mail-from $mailFrom \
    --mail-rcpt $mailRcpt \
    --user $smtpsUser':'$smtpsPass \
    -T <(echo -e "From: $mailFrom\nTo: $mailRcpt\nSubject: $msgSubject $edt\n\n$msgMain $edt.\n\n$msgError\n$ERRall")

rm -f $ERR_OUTPUT_ALL

remoteSysMaint.sh file:

The remoteSysMaint.sh file is meant for remote upgrading servers. I am the type of person that has many Virtual Machines and Docker Containers. Spending time updating everything manually is not something I enjoy doing. Because of this, I added this functionality to the OurSync project. In the past, I had an upgrade script on every server and utilized cron to automate the upgrades. I was faced with the problem of when I wanted to change a script or even stop a server for updating for a period of time, it took way to long. It would take me about an hour to update all the scripts, run tests, and then verify the results. With the remote upgrade capabilities, I am able to turn on and off upgrades within the config by utilizing the sysMainUse column. (Y for yes and N for no.) There is no limit to the number of servers that can be automatically upgraded. Everything is logged in the processLogs table.

At the moment, this is specifically meant to update Linux servers that use apt. To use this, the sysMaintConfig table needs an ipAddress (IP address or hostname), sysUser, sysPassword, and the sysName. I have this set up to restart the server every Thursday. In the future, I am going to add a configuration so that the date can be updated with a simple config change.

#!/bin/bash

__dir="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

source ${__dir}/rs.config

function SystemMaint()

{

    mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET tmp = '' WHERE tmp = 'sSM$dt'"

    mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, tmp, server) VALUES ('systemMaintenance', 'start', now(), 'sSM$dt', '$4')"

    sshpass -p $3 ssh -o StrictHostKeyChecking=no $2@$1 "echo $3 | sudo -S apt-get update 2>/dev/null"

    sshpass -p $3 ssh -o StrictHostKeyChecking=no $2@$1 "echo $3 | sudo -S apt-get -y upgrade 2>/dev/null"

    sshpass -p $3 ssh -o StrictHostKeyChecking=no $2@$1 "echo $3 | sudo -S apt-get -y autoremove 2>/dev/null"

    sshpass -p $3 ssh -o StrictHostKeyChecking=no $2@$1 "echo $3 | sudo -S apt-get -y autoclean 2>/dev/null"

    mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE $processLogs SET end = now(), tmp = '', status = 'finished' WHERE tmp = 'sSM$dt'"

    if [ $(LC_TIME=C date +%A) = "Thursday" ]; then

        mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO $processLogs (name, status, start, end, server) VALUES ('systemMaintenance-restart', 'restart', now(), now(), '$4')"

        sshpass -p $3 ssh -o StrickHostKeyChecking=no $2@$1 "echo $3 | sudo -S reboot 2>/dev/null"

    fi

}

for sysMainID in $systemMaintID

    do

        sysMainIP=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT ipAddress FROM $dn.$sysMaintConfig WHERE smID=$sysMainID")

        sysMainUser=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT sysUser FROM $dn.$sysMaintConfig WHERE smID=$sysMainID")

        sysMainPass=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT sysPassword FROM $dn.$sysMaintConfig WHERE smID=$sysMainID")

        sysMainName=$(mysql --host=$dip --user=$dun --password=$dpw -BNr -e "SELECT sysName FROM $dn.$sysMaintConfig WHERE smID=$sysMainID")

        SystemMaint "$sysMainIP" "$sysMainUser" "$sysMainPass" "$sysMainName"

    done

 

MySQL Code to Create Database:

These SQL queries are the basics to get the database up and running. The names can be adjusted to the users liking.

CREATE DATABASE `oursync` /*!40100 DEFAULT CHARACTER SET utf8 */;

 

CREATE TABLE `backupConfig` (

    `idBC` int(20) NOT NULL AUTO_INCREMENT,

    `source` varchar(255) NOT NULL,

    `destination` varchar(255) NOT NULL,

    `description` varchar(255) DEFAULT NULL,

    `dateAdded` timestamp(6) NULL DEFAULT current_timestamp(6),

    `dateUpdated` timestamp(6) NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

    `parameters` varchar(45) NOT NULL,

    `useBackup` varchar(1) DEFAULT 'Y',

    PRIMARY KEY (`idBC`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

 

CREATE TABLE `generalConfig` (

    `idC` int(20) NOT NULL AUTO_INCREMENT,

    `name` varchar(145) NOT NULL,

    `value` varchar(200) NOT NULL,

    `description` varchar(255) DEFAULT NULL,

    `dateAdded` timestamp(6) NULL DEFAULT current_timestamp(6),

    `dateUpdated` timestamp(6) NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

    PRIMARY KEY (`idC`),

    UNIQUE KEY `name_UNIQUE` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

 

CREATE TABLE `processLogs` (

    `id` int(11) NOT NULL AUTO_INCREMENT,

    `name` varchar(255) DEFAULT NULL,

    `updated` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

    `comments` longtext DEFAULT NULL,

    `status` varchar(255) NOT NULL,

    `tmp` varchar(255) DEFAULT NULL,

    `start` datetime DEFAULT NULL,

    `end` datetime DEFAULT NULL,

    `server` varchar(255) DEFAULT NULL,

    `ERROR` longtext DEFAULT NULL,

    UNIQUE KEY `id_UNIQUE` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8;

 

CREATE TABLE `rmFileConfig` (

    `idRFC` int(11) NOT NULL AUTO_INCREMENT,

    `location` varchar(200) DEFAULT NULL,

    `description` varchar(200) DEFAULT NULL,

    `dateAdded` timestamp(6) NULL DEFAULT current_timestamp(6),

    `dateUpdated` timestamp(6) NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

    `parameters` varchar(45) DEFAULT NULL,

    `useLocation` varchar(1) DEFAULT 'Y',

    PRIMARY KEY (`idRFC`),

    UNIQUE KEY `idRFC_UNIQUE` (`idRFC`),

    UNIQUE KEY `location_UNIQUE` (`location`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;