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

When you rebuild a database server, you often have the databases but not the user accounts that connect to them. This guide shows how to turn a folder of per-user backup files into one SQL script that recreates every MySQL or MariaDB user with its original password hash.

1. Prepare the backup files

Collect the user backup files in one folder, for example:

• Folder: /root/database_user/
• File names: [email protected]

The script expects each file to contain lines in this form. Check one of your files with cat first, and adjust the script if your format is different.

name: appuser@localhost
password: *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO 'appuser'@'localhost';

• name: the user and the host it connects from.
• password: the stored password hash, not the plain password.
• GRANT lines: the privileges to restore. If your files have none, you will add grants by hand in step 5.

Keep the folder readable by root only (chmod 700 /root/database_user), because password hashes can be attacked offline.

2. Create the restore script

#!/bin/bash
set -euo pipefail

# Folder with the *.mysql.user backup files
USER_BACKUP_PATH="/root/database_user"

# SQL file the script will write
OUTPUT_SQL="/root/database_user_restore.sql"

{
  echo "-- MySQL/MariaDB user restore script"
  echo "SET SQL_LOG_BIN=0;"
} > "$OUTPUT_SQL"

shopt -s nullglob
for userfile in "$USER_BACKUP_PATH"/*.mysql.user; do
  echo "Processing: $userfile"

  # "name: user@host" -> user and host
  NAME=$(grep -oP '^name:\s*\K\S+' "$userfile" || true)
  USERNAME="${NAME%@*}"
  HOSTNAME="${NAME#*@}"

  # "password: <hash>"
  PASSWORD_HASH=$(grep -oP '^password:\s*\K\S+' "$userfile" || true)

  if [[ -z "$NAME" || "$NAME" != *@* || -z "$PASSWORD_HASH" ]]; then
    echo "  Skipping: could not read name or password in $userfile"
    continue
  fi

  # Only 41-character hashes starting with * (mysql_native_password) can be restored this way
  if [[ ! "$PASSWORD_HASH" =~ ^\*[0-9A-Fa-f]{40}$ ]]; then
    echo "  Skipping $USERNAME@$HOSTNAME: hash is not mysql_native_password; reset this password by hand"
    continue
  fi

  {
    echo "DROP USER IF EXISTS '$USERNAME'@'$HOSTNAME';"
    echo "CREATE USER '$USERNAME'@'$HOSTNAME' IDENTIFIED WITH mysql_native_password AS '$PASSWORD_HASH';"
    grep -E '^GRANT ' "$userfile" || echo "-- No GRANT lines found for '$USERNAME'@'$HOSTNAME'; add grants by hand"
    echo ""
  } >> "$OUTPUT_SQL"

  echo "  Added: $USERNAME@$HOSTNAME"
done

echo "FLUSH PRIVILEGES;" >> "$OUTPUT_SQL"
chmod 600 "$OUTPUT_SQL"
echo "Restore script written to $OUTPUT_SQL"

What the script does:

• It keeps each user's original host. An earlier version of this guide changed every host other than localhost to %, which silently allowed those users to connect from anywhere.
• It copies the GRANT lines from each file instead of granting ALL PRIVILEGES ON *.* WITH GRANT OPTION, which would have made every restored user a full administrator.
• SET SQL_LOG_BIN=0 keeps the changes out of the binary log, so they are not copied to replicas. Remove that line if you want replicas to receive the users too.

3. Run the script

/root/restore_mysql_users.sh

It processes every .mysql.user file in the folder and writes /root/database_user_restore.sql. Read its messages for any users it skipped.

4. Review the generated SQL

less /root/database_user_restore.sql

For each user you should see a DROP USER IF EXISTS line, a CREATE USER line with the hash, and that user's GRANT lines. The file ends with FLUSH PRIVILEGES. Look for any -- No GRANT lines found comments and decide what each of those users needs.

5. Load the users into the server

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

Enter the MySQL root password when asked. On MariaDB the mariadb client works the same way. If a user had no grants in the backup, give them access to their own database only, for example:

GRANT ALL PRIVILEGES ON `appdb`.* TO 'appuser'@'localhost';

6. Check the restored users

SELECT user, host, plugin FROM mysql.user ORDER BY user;
SHOW GRANTS FOR 'appuser'@'localhost';

Then test a real login as one of the restored users, for example mysql -u appuser -p appdb. Delete the backup folder and the generated SQL file, or move them somewhere safe, once you have finished.

7. Version notes

You can't turn a mysql_native_password hash into a caching_sha2_password one by changing the plugin name in the SQL. To move a user to the newer plugin, set a new password with ALTER USER 'appuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new-password';.

If you are planning a server of your own for your databases, see VPS plans compared.

Frequently asked questions

If you get stuck restoring users on your server, open a support ticket or use our 24/7 live chat.