Streamlined Bulk MySQL/MariaDB User Recovery: Your Complete Backup File Manual Print

Restoring MySQL users from backup files can be a daunting task, but with the right approach, it can be automated and streamlined. This guide will walk you through the process of restoring MySQL users from backup files. Whether you are handling a few users or thousands, this guide will help you simplify the process.

🔧 Step 1: Preparing the Backup Files

Before diving into restoring MySQL users, ensure that your backup files are ready. These files typically contain user credentials and other necessary details, such as the username, password hash, and privileges.

Example Folder Structure:

• **Backup folder path**: `/home/example_user/database_user/`
  - **File names**: `username@localhost.mysql.user`

Each backup file should contain the following:

• Username: The name of the MySQL user.
• Password: The hashed password for the MySQL user.
• Hostname: The host from which the user connects.
• Privileges: The privileges granted to the user.

📝 Step 2: Creating the MySQL User Restore Script

To restore all the users from your backup files, you can create a script that:

1. Loops through all backup files.
2. Extracts the username, password hash, and privileges.
3. Generates a MySQL restore script (.sql) containing commands to drop, create, and grant privileges to the users.

🖥 Script to Restore MySQL Users

Follow these steps to create and run the restore script:

1. Log in to your MySQL server via SSH as a user with sufficient privileges: 

   ssh root@your_server_ip
   

2. Navigate to the /root/ directory (or any directory of your choice): 

   cd /root/
   ​

3. Create the script file: Use a text editor like nano to create a new script file: 

   nano restore_mysql_users.sh
   ​

4. Paste the Script: Copy and paste the following script into the file:

   #!/bin/bash
   
   # Define the path to the database user backup files
   USER_BACKUP_PATH="/home/example_user/database_user"
   
   # Define the output file that will contain the SQL restore commands
   OUTPUT_SQL="/home/example_user/database_user_restore.sql"
   
   # Start the SQL restore script by writing an initial header
   echo "-- MySQL User Restoration Script" > "$OUTPUT_SQL"
   echo "SET SQL_LOG_BIN=0;" >> "$OUTPUT_SQL"
   # The above commands create a new SQL file (or overwrite an existing one) and disable binary logging for the restoration.
   
   # Loop through each MySQL user backup file in the specified directory.
   for userfile in "$USER_BACKUP_PATH"/*.mysql.user; do
       echo "🔍 Processing: $userfile"
       
       # Extract the username from the backup file:
       # This line uses grep with a Perl regex (-oP) to capture text that follows "name:" up until the '@' character.
       USERNAME=$(grep -oP '^name:\s*\K[^@]+' "$userfile")
       
       # Extract the hostname from the backup file:
       # It again looks for the value after "name:" and then uses cut to split at '@' and take the second part.
       HOSTNAME=$(grep -oP '^name:\s*\K[^ ]+' "$userfile" | cut -d'@' -f2)
       
       # Extract the password hash:
       # This command looks for the line starting with "password:" and extracts the hash that follows.
       PASSWORD_HASH=$(grep -oP '^password:\s*\K\S+' "$userfile")
   
       # Validate that the USERNAME, PASSWORD_HASH, and HOSTNAME were successfully extracted.
       if [[ -z "$USERNAME" || -z "$PASSWORD_HASH" || -z "$HOSTNAME" ]]; then
           echo "⚠️ Skipping: Unable to parse $userfile"
           continue
       fi
   
       # Adjust the hostname value:
       # If the extracted hostname is "localhost", use it directly; otherwise, replace it with "%" to allow connections from any host.
       if [[ "$HOSTNAME" == "localhost" ]]; then
           HOSTNAME="localhost"
       else
           HOSTNAME="%"
       fi
   
       # Append SQL commands to the output file to restore the user.
       # 1. DROP USER IF EXISTS: Remove any existing user with the same username@hostname.
       # 2. CREATE USER: Recreate the user with the provided password hash.
       #    The "IDENTIFIED WITH mysql_native_password AS" clause tells MySQL to use the specified hash.
       # 3. GRANT ALL PRIVILEGES: Grant the user full privileges on all databases.
       # 4. FLUSH PRIVILEGES: Reload the privilege tables to apply the changes immediately.
       echo "DROP USER IF EXISTS '$USERNAME'@'$HOSTNAME';" >> "$OUTPUT_SQL"
       echo "CREATE USER '$USERNAME'@'$HOSTNAME' IDENTIFIED WITH mysql_native_password AS '$PASSWORD_HASH';" >> "$OUTPUT_SQL"
       echo "GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'$HOSTNAME' WITH GRANT OPTION;" >> "$OUTPUT_SQL"
       echo "FLUSH PRIVILEGES;" >> "$OUTPUT_SQL"
       echo "" >> "$OUTPUT_SQL"
   
       echo "✅ Converted user: $USERNAME@$HOSTNAME"
   done
   
   # Final message to indicate the restoration script has been generated.
   echo "🎉 MySQL user restoration script generated: $OUTPUT_SQL"
   ​

5. Save and Exit: After pasting the script, press Ctrl + X, then Y to save, and Enter to exit.

🛠 Step 3: Making the Script Executable

Once the script is saved, run the following command to make it executable:

/root/restore_mysql_users.sh

What Happens Next:

• The script will process each .mysql.user file from the backup folder.
• It will generate a SQL file (database_user_restore.sql) in the /home/example_user/ directory.

✅ Step 5: Verifying the Generated SQL Script

Once the script finishes, ensure the SQL file (database_user_restore.sql) was created successfully. You can check the contents of the SQL file with:

cat /home/example_user/database_user_restore.sql

The generated file should include:

• DROP USER commands.
• CREATE USER commands with password hashes.
• GRANT commands to assign privileges.
• FLUSH PRIVILEGES to apply the changes.

🔄 Step 6: Running the SQL Script to Restore Users

After reviewing the SQL file, you can restore the users by running the SQL script in MySQL:

mysql -u root -p < /home/example_user/database_user_restore.sql

You will be prompted to enter the MySQL root password. This command will restore all the users and their privileges.

🔍 Step 7: Verifying the Restored Users

To confirm the users have been successfully restored, run the following SQL query:

SELECT user, host FROM mysql.user;

This will show all the users that have been restored in your MySQL instance.

🎯 Conclusion

In this guide, we've provided a step-by-step process to restore MySQL users from backup files. By using a simple script, you can automate the restoration of MySQL users, making it much easier to manage large numbers of users.

Further Enhancements:

• Modify the GRANT statements to limit user access to specific databases instead of granting all privileges on *.*.
• For MySQL 8.0 and later, adjust the authentication plugin if necessary by updating IDENTIFIED WITH mysql_native_password to IDENTIFIED WITH caching_sha2_password.
• Test the SQL file in a staging environment before applying it in production.

Let’s Recap:

• Backup Files: Ensure you have user backup files with proper structure.
• Script: Automate the restoration process with a bash script.
• SQL File: The script generates a restore SQL file that can be executed in MySQL.
• Restoration: Execute the SQL file to restore the users and privileges.