.::: Script to backup All user & specific MySQL MariaDB & MarinaDB :::.

A. Backup Specification User MariaDB MySQL

1. capture user
select @@hostname,user,host from mysql.user where user like 'teguh%';
show grants for 'teguh'@'%';
show grants for 'teguh'@'localhost';

MariaDB [(none)]> select @@hostname,user,host from mysql.user where user like 'teguh%';
+------------+-------+-----------+
| @@hostname | User  | Host      |
+------------+-------+-----------+
| teguhth    | teguh | %         |
| teguhth    | teguh | localhost |
+------------+-------+-----------+
2 rows in set (0.006 sec)
 
MariaDB [(none)]> show grants for 'teguh'@'%';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for teguh@%                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `teguh`@`%` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

MariaDB [(none)]> show grants for 'teguh'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for teguh@localhost                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `teguh`@`localhost` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>
 

2. create scritp backup user

[root@teguhth user]# cat backup_user.sh
#!/bin/bash

# Set your MariaDB credentials
DB_USER="teguh"
DB_PASSWORD="teguhteguh"
DB_HOST="localhost"
DB_PORT="3306"

# Set the output directory
OUTPUT_DIR="/data/user"

# Create a timestamp for the output file
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Function to execute SQL queries and append results to a file
execute_query_and_append() {
    QUERY=$1
    OUTPUT_FILE=$2

    # Execute the query, append results to the output file, and add a semicolon
    echo -n "$(mysql -u$DB_USER -p$DB_PASSWORD -h $DB_HOST -P $DB_PORT -e "$QUERY;")" >> "$OUTPUT_FILE"
#    echo ";" >> "$OUTPUT_FILE"

echo "" >> "$OUTPUT_FILE"

}

# Get a list of users and hosts from the provided sample output
#USERS_AND_HOSTS=(
   #             "teguh %"
      #           "teguh localhost"
       #          )

USERS_AND_HOSTS=('teguh %' 'teguh localhost')

# Iterate over users and collect results
for USER_AND_HOST in "${USERS_AND_HOSTS[@]}"; do
    # Extract user and host
    USER=$(echo $USER_AND_HOST | awk '{print $1}')
    HOST=$(echo $USER_AND_HOST | awk '{print $2}')

    # Set the output file
    OUTPUT_FILE="$OUTPUT_DIR/$TIMESTAMP-user_info_${USER}_${HOST}.sql"

    # Show create user
    CREATE_USER_QUERY="SHOW CREATE USER '$USER'@'$HOST';"
  execute_query_and_append "$CREATE_USER_QUERY" "$OUTPUT_FILE"

# Show grants for user
    GRANTS_QUERY="SHOW GRANTS FOR '$USER'@'$HOST';"
    execute_query_and_append "$GRANTS_QUERY" "$OUTPUT_FILE"
 

 # Add semicolon at the end of each line in the file
    sed -i 's/$/;/g' "$OUTPUT_FILE"

   
# Add '---' before lines matching "CREATE USER for" & "grants for USER for"
sed -i '/CREATE USER for/s/^/---/' "$OUTPUT_FILE"
sed -i '/Grants for/s/^/---/' "$OUTPUT_FILE"
# Display the modified file
cat "$OUTPUT_FILE"

done

echo "Script completed successfully!"
[root@teguhth user]#

3. run backup user

sh backup_user.sh

[root@teguhth user]# ls
backup_user.sh  old  test
[root@teguhth user]# sh backup_user.sh
---CREATE USER for teguh@%
CREATE USER `teguh`@`%` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F';
---Grants for teguh@%
GRANT ALL PRIVILEGES ON *.* TO `teguh`@`%` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION;
---CREATE USER for teguh@localhost
CREATE USER `teguh`@`localhost` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F';
---Grants for teguh@localhost
GRANT ALL PRIVILEGES ON *.* TO `teguh`@`localhost` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION;
Script completed successfully! at 20231211_164938
[root@teguhth user]#
 

4. check result
[root@teguhth user]# ls
20231211_164938-user_info_teguh_localhost.sql  20231211_164938-user_info_teguh_%.sql  backup_user.sh  old  test
[root@teguhth user]#
[root@teguhth user]# cat 20231211_164938-user_info_teguh_%.sql
---CREATE USER for teguh@%
CREATE USER `teguh`@`%` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F';
---Grants for teguh@%
GRANT ALL PRIVILEGES ON *.* TO `teguh`@`%` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION;
[root@teguhth user]#
[root@teguhth user]# cat 20231211_164938-user_info_teguh_localhost.sql
---CREATE USER for teguh@localhost
CREATE USER `teguh`@`localhost` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F';
---Grants for teguh@localhost
GRANT ALL PRIVILEGES ON *.* TO `teguh`@`localhost` IDENTIFIED BY PASSWORD '*EB39BF97E50561CC0C8AA79DC4F49D572679286F' WITH GRANT OPTION;
[root@teguhth user]#

 

B. Backup All User Mariadb

1. Create script ro backup all user

[root@teguhth script]# cat backup_user_all_maria.sh
#!/bin/bash

# Set your MariaDB credentials
DB_USER="teguh"
DB_PASSWORD="teguhteguh"
DB_HOST="localhost"
DB_PORT="3306"

# Set the output directory
OUTPUT_DIR="/data/user"

# Create a timestamp for the output file
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Function to execute SQL queries and append results to a file
execute_query_and_append() {
    QUERY=$1
    OUTPUT_FILE=$2

    # Execute the query, append results to the output file, and add a semicolon
    echo -n "$(mysql -u$DB_USER -p$DB_PASSWORD -h $DB_HOST -P $DB_PORT -e "$QUERY;")" >> "$OUTPUT_FILE"
    echo "" >> "$OUTPUT_FILE"
}

# Get a list of users
USERS_QUERY="SELECT CONCAT(User, '@', Host) FROM mysql.user;"
#USERS_QUERY="SELECT CONCAT(User, '@', Host) FROM mysql.user WHERE User != 'root';"
USERS_AND_HOSTS=($(mysql -u$DB_USER -p$DB_PASSWORD -h $DB_HOST -P $DB_PORT -N -e "$USERS_QUERY"))

# Iterate over users and collect results
for USER_AND_HOST in "${USERS_AND_HOSTS[@]}"; do
    # Extract user and host
    USER=$(echo $USER_AND_HOST | cut -d'@' -f1)
    HOST=$(echo $USER_AND_HOST | cut -d'@' -f2)

    # Set the output file
    OUTPUT_FILE="$OUTPUT_DIR/$TIMESTAMP-user_info_${USER}_${HOST}.sql"

    # Show create user
    CREATE_USER_QUERY="SHOW CREATE USER '$USER'@'$HOST';"
    execute_query_and_append "$CREATE_USER_QUERY" "$OUTPUT_FILE"

    # Show grants for user
    GRANTS_QUERY="SHOW GRANTS FOR '$USER'@'$HOST';"
    execute_query_and_append "$GRANTS_QUERY" "$OUTPUT_FILE"

    # Add semicolon at the end of each line in the file
    sed -i 's/$/;/g' "$OUTPUT_FILE"

    # Add '---' before lines matching "CREATE USER for" & "grants for USER for"
    sed -i '/CREATE USER for/s/^/---/' "$OUTPUT_FILE"
    sed -i '/Grants for/s/^/---/' "$OUTPUT_FILE"

    # Display the modified file
    cat "$OUTPUT_FILE"
done

echo "Script completed successfully!"

[root@teguhth script]#

2. test run backup

[root@teguhth script]# sh backup_user_all_maria.sh

 

3. result