Databases - Revision history

413vhcu1lq0463ob at 10:17, 3 February 2025

2025-02-03T10:17:27Z

← Older revision		Revision as of 10:17, 3 February 2025
Line 1:		Line 1:
	* [[MySQL/MariaDB]]		* [[MySQL/MariaDB]]
			* [[PostgreSQL]]

413vhcu1lq0463ob: Replaced content with "* MySQL/MariaDB"

2025-02-03T10:17:02Z

Replaced content with "* MySQL/MariaDB"

← Older revision		Revision as of 10:17, 3 February 2025
Line 1:		Line 1:
	~~== Stuffs ==~~		* [[MySQL/MariaDB]]

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

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..."

2024-10-06T22:33:39Z

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