.::: Script Backup User MariaDB Include Privileges :::.

 

1. Script backup All user mariadb
 

2. script backup custom user mariadb
 

3. script script_backup_alluser_marinadb.sh

[root@teguhth-all backupuser]# pwd
/data/backupuser
[root@teguhth-all backupuser]# cat script_backup_alluser_marinadb.sh
#!/bin/bash

ipsvr=$(hostname -I | awk '{print $1}' | sed "s/ //g");

userx="admin"
passx="xxx"

#MYSQLX="mysql -h$ipsvr -u$userx -p$passx -N -B"
MYSQLX="mariadb -h$ipsvr -u$userx -p$passx -N -B"

OUTFILE="backup_users_marinadb_${ipsvr}_$(date +%Y%m%d_%H%M%S).sql"

echo "-- Backup User dan Privileges" > "$OUTFILE"
echo "-- Generated : $(date)" >> "$OUTFILE"
echo "" >> "$OUTFILE"

# Ambil semua user
$MYSQLX -e "SELECT CONCAT(User,'|',Host) FROM mysql.user ORDER BY User,Host;" | while IFS='|' read USER HOST
do
    echo "-- ====================================================" >> "$OUTFILE"
    echo "-- USER : '$USER'@'$HOST'" >> "$OUTFILE"
    echo "-- ====================================================" >> "$OUTFILE"
    echo "" >> "$OUTFILE"

    ########################################
    # SHOW CREATE USER
    ########################################

    CREATE_USER=$($MYSQLX -e "SHOW CREATE USER '$USER'@'$HOST';" | cut -f2)

    if [ ! -z "$CREATE_USER" ]; then
        echo "$CREATE_USER;" >> "$OUTFILE"
    fi

    echo "" >> "$OUTFILE"

    ########################################
    # SHOW GRANTS
    ########################################

    $MYSQLX -e "SHOW GRANTS FOR '$USER'@'$HOST';" | while read GRANT
    do
        echo "${GRANT};" >> "$OUTFILE"
    done

    echo "" >> "$OUTFILE"

done

echo "" >> "$OUTFILE"
echo "-- Copyright by           : Teguh Triharto" >> "$OUTFILE"
echo "-- Website                : https://www.linkedin.com/in/teguhth" >> "$OUTFILE"
echo "" >> "$OUTFILE"

echo "Backup selesai."
echo "File : $OUTFILE"
[root@teguhth-all backupuser]#

4. script script_backup_custom_marinadb.sh

[root@teguhth-all backupuser]# pwd
/data/backupuser
[root@teguhth-all backupuser]# cat script_backup_custom_marinadb.sh
#!/bin/bash

ipsvr=$(hostname -I | awk '{print $1}' | sed "s/ //g");

userx="admin"
passx="xxx"

#MYSQLX="mysql -h$ipsvr -u$userx -p$passx -N -B"
MYSQLX="mariadb -h$ipsvr -u$userx -p$passx -N -B"

OUTFILE="custom_backup_users_marinadb_${ipsvr}_$(date +%Y%m%d_%H%M%S).sql"

echo "-- Backup User dan Privileges" > "$OUTFILE"
echo "-- Generated : $(date)" >> "$OUTFILE"
echo "" >> "$OUTFILE"

usercust="$1"

if [ -z "$usercust" ]; then
    echo "Usage:"
    echo "  $0 all"
    echo "  $0 admin"
    echo "  $0 admin,aisyah"
    exit 1
fi

if [ "$usercust" = "all" ]; then

    echo "Backup semua user..."

    SQL="SELECT CONCAT(User,'|',Host)
         FROM mysql.user
         ORDER BY User,Host;"

else

    echo "Backup user : $usercust"

    usercust=$(echo "$usercust" | sed "s/,/','/g")

    SQL="SELECT CONCAT(User,'|',Host)
         FROM mysql.user
         WHERE User IN ('$usercust')
         ORDER BY User,Host;"

fi

$MYSQLX -e "$SQL" | while IFS='|' read USER HOST
do

    echo "-- ====================================================" >> "$OUTFILE"
    echo "-- USER : '$USER'@'$HOST'" >> "$OUTFILE"
    echo "-- ====================================================" >> "$OUTFILE"
    echo "" >> "$OUTFILE"

    ########################################
    # SHOW CREATE USER
    ########################################

    CREATE_USER=$($MYSQLX -e "SHOW CREATE USER '$USER'@'$HOST';" | cut -f2-)

    if [ -n "$CREATE_USER" ]; then
        echo "$CREATE_USER;" >> "$OUTFILE"
    fi

    echo "" >> "$OUTFILE"

    ########################################
    # SHOW GRANTS
    ########################################

    $MYSQLX -e "SHOW GRANTS FOR '$USER'@'$HOST';" | while read GRANT
    do
        echo "$GRANT;" >> "$OUTFILE"
    done

    echo "" >> "$OUTFILE"

done
echo "" >> "$OUTFILE"
echo "-- Copyright by           : Teguh Triharto" >> "$OUTFILE"
echo "-- Website                : https://www.linkedin.com/in/teguhth" >> "$OUTFILE"
echo "" >> "$OUTFILE"

echo "Backup selesai."
echo "File : $OUTFILE"

[root@teguhth-all backupuser]#