Databases

Stuffs

Creating and deleting databases on MariaDB

CREATE DATABASE database_name;

Creating and deleting users on MariaDB

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

DROP USER 'username'@'%';

Granting privileges on databases to users on MariaDB

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

FLUSH PRIVILEGES

At the end, always flush privileges.

FLUSH PRIVILEGES;

How to make backups of MariaDB

Almost the same as Linux file backups, but using the MariaDB dump file as the source. If you want a different retention policy, you can simply change the days in the script. I recommend using root to run this script so as not to show the username and password in the script, but if necessary uncomment and fill in the blanks.

#!/bin/bash

DATABASE=
# USERNAME AND PASSWORD NOT NEEDED IF YOU'RE RUNNING WITH ROOT.
#SERVER=
#USERNAME=
#PASSWORD=
NAME=
OUTPUT_DIR=
WEEKLY_BACKUP_RETENTION=30 #DAYS
MONTHLY_BACKUP_RETENTION=365 #DAYS
YEARLY_BACKUP_RETENTION=3650 #DAYS

mkdir -p $OUTPUT_DIR/
mkdir -p $OUTPUT_DIR/daily
mkdir -p $OUTPUT_DIR/weekly
mkdir -p $OUTPUT_DIR/monthly
mkdir -p $OUTPUT_DIR/yearly

echo "STARTING $NAME BACKUP $(date +'%Y-%m-%d-%H-%M-%S')"

export WEEK_DAY=`/bin/date +%u`

# delete the old file

echo "DELETING $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz IF EXISTS FILE"
rm -f $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz
echo "DELETING $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql IF EXISTS FILE"
rm -f $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql

export DAY=`/bin/date +%d`
export MONTH_DAY=`/bin/date +%d-%m`

#IF USING USERNAME AND PASSWORD
#/usr/bin/mysqldump -u "$USERNAME" -p"$PASSWORD" -h "$SERVER" "$DATABASE" --no-tablespaces > $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql
/usr/bin/mysqldump $DATABASE --no-tablespaces > $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql

echo "GENERATING $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz FILE"
tar czf $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql

echo "DELETING $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql FILE"
rm -f $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.sql

DATE=`/bin/date +%Y-%m-%d`

#####################
##   WEEK BACKUP   ##
#####################
if [ "$WEEK_DAY" == "0" ]; then
echo "DOING WEEK BACKUP OF $NAME"
cp $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz $OUTPUT_DIR/weekly/$NAME-$DATE.tar.gz
fi

#####################
##  MONTH BACKUP   ##
#####################
if [ "$DAY" == "01" ]; then
echo "DOING MONTH BACKUP OF $NAME"
cp $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz $OUTPUT_DIR/monthly/$NAME-$DATE.tar.gz
fi


#####################
##  YEAR BACKUP    ##
#####################
if [ "$MONTH_DAY" == "01-01" ]; then
echo "DOING YEAR BACKUP OF $NAME"
cp $OUTPUT_DIR/daily/$NAME-$WEEK_DAY.tar.gz $OUTPUT_DIR/yearly/$NAME-$DATE.tar.gz
fi

#####################
## RETENTION POLICY##
#####################

find $OUTPUT_DIR/weekly -type f -mtime +$WEEKLY_BACKUP_RETENTION | xargs rm -f

find $OUTPUT_DIR/monthly -type f -mtime +$MONTHLY_BACKUP_RETENTION | xargs rm -f

find $OUTPUT_DIR/yearly -type f -mtime +$YEARLY_BACKUP_RETENTION | xargs rm -f


echo "FINISHING $NAME BACKUP $(date +'%Y-%m-%d-%H-%M-%S')"