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