413vhcu1lq0463ob: Created page with "== 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 === Almo..."

2025-02-03T10:17:08Z

Created page with "== 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 === Almo..."

New page

== 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')"

MySQL/MariaDB - Revision history