How to Backup MySQL Users and Privileges (September 2026)

If you have ever run mysqldump --all-databases, copied the resulting file to a fresh server, and then watched your application crash with “Access denied for user ‘app’@’localhost'”, you already know the hard truth: a standard MySQL dump does not include user accounts or their privileges. Both live in the built-in mysql system database, and most backup tools skip that schema on purpose.

This guide walks you through how to back up MySQL users and privileges cleanly, restore them on the same or a different server, automate the job with cron, and keep the resulting dump file safe. You will get four working methods, a side-by-side comparison table, and a few safeguards I wish someone had told me before my first server migration.

Quick Answer: Where MySQL Stores Users and How to Dump Them

MySQL stores every user account, password hash, and privilege grant inside the mysql system database, in the grant tables (mainly mysql.user, mysql.db, and mysql.tables_priv). To back them up, run one of four tools that emit portable CREATE USER and GRANT statements: mysqldump --system=users on MariaDB 10.3+, mysqlpump --users on MySQL 5.7+, Percona’s pt-show-grants on any version, or a short bash loop over SHOW GRANTS FOR as a final fallback.

Where MySQL Stores Users, Passwords, and Privileges

Every MySQL or MariaDB server ships with a built-in schema named mysql. Inside it are the grant tables, and those tables are the single source of truth for who is allowed to connect, from which host, with what password, and with what privileges.

The three tables that matter most for user backups are:

• mysql.user – global privileges and the password hash for every account.
• mysql.db – database-level privileges (for example, SELECT on sales.*).
• mysql.tables_priv and mysql.columns_priv – finer-grained table and column grants.

Because these tables are MyISAM on most default installations (even when the data you care about sits in InnoDB), they can be dumped and restored with the usual tools. The trick is choosing the right tool, because each one handles the mysql system schema differently.

Why You Should Not Dump the mysql System Database Directly

The instinctive move is to run mysqldump mysql and call it a day. On every server I have touched, that approach has caused one of two failures: either the restore overwrites the destination server’s grant tables and breaks replication, or the password hashes no longer match between major versions and every account locks out. The grant tables store internal metadata, default roles, and time-zone tables that differ between MySQL 5.7, 8.0, and MariaDB.

DBA Stack Exchange moderators repeat this point often: dump CREATE USER plus GRANT statements instead of raw rows. That way the destination server builds accounts using its own grant tables and authentication plugins, which is what you want during a version upgrade or cross-server migration.

Method 1: MariaDB 10.3+ With mysqldump –system=users

If your server reports Server version: 10.3.x or higher in SELECT VERSION();, MariaDB’s enhanced mysqldump can dump users and grants without touching application data. The --system=users flag tells it to emit only the system schemas mysql needs to recreate accounts.

The command looks like this:

mysqldump --system=users --no-create-db --no-create-info 
  --skip-comments --skip-lock-tables 
  -u backup -p"$MYSQL_PWD" mysql > /backup/grants-$(date +%F).sql

The output is a clean .sql file full of CREATE USER 'app'@'10.0.%' IDENTIFIED BY ... and matching GRANT ... statements. To restore, pipe the file back into the mysql client:

mysql -u root -p < /backup/grants-2026-09-11.sql

After the import, run FLUSH PRIVILEGES; only if the destination server did not pick up the changes automatically (it usually does).

Method 2: MySQL 5.7+ and 8.0 With mysqlpump –users

Oracle’s mysqlpump (shipped with MySQL 5.7 and still available in 8.0 as a separate utility) has a dedicated --users flag that does exactly what MariaDB’s --system=users does, plus a few extras for selective dumps.

A typical grants-only backup looks like:

mysqlpump --users --exclude-databases=% 
  --skip-comments --add-drop-user 
  -u backup -p"$MYSQL_PWD" > /backup/grants-$(date +%F).sql

The --exclude-databases=% pattern is intentional: it skips every user database while still dumping the user accounts from the mysql schema. If you only need a few accounts, swap in --include-users='app%,reporter%' instead.

To restore, run the standard client in batch mode:

mysql -u root -p < /backup/grants-2026-09-11.sql

One thing to keep in mind: mysqlpump is not available on MariaDB and was deprecated in MySQL 8.4. If you are starting a fresh project on the latest MySQL 8.4 release, jump to Method 3 with pt-show-grants or the bash fallback.

Method 3: Percona pt-show-grants (Works on Any Version)

When you cannot rely on a specific version flag, Percona’s pt-show-grants is the tool I reach for. It is part of Percona Toolkit, runs against any MySQL or MariaDB version from 5.1 onward, and produces normalized SHOW GRANTS output for every account on the server.

Install it on Debian or Ubuntu with:

sudo apt-get install percona-toolkit

On RHEL, AlmaLinux, or Rocky Linux, use:

sudo yum install percona-toolkit

The dump command is a single line:

pt-show-grants --host=127.0.0.1 --user=backup --password="$MYSQL_PWD" 
  > /backup/grants-$(date +%F).sql

The output is one CREATE USER plus GRANT USAGE ON *.* block per account, followed by every privilege granted to that account. One Reddit user summed it up perfectly: “pt-show-grants saved me – dumped everything in one line, restored in seconds.” I have hit the same button dozens of times.

To restore, pipe the file into the client:

mysql -u root -p < /backup/grants-2026-09-11.sql

Method 4: Bash Script Fallback Using SHOW GRANTS

If you cannot install Percona Toolkit and the other tools are not available, a short bash loop around SHOW GRANTS FOR gets the job done on any version. This is the only method that needs zero extra software beyond the mysql client itself.

Save this as backup-grants.sh:

#!/usr/bin/env bash
set -euo pipefail

OUT="/backup/grants-$(date +%F).sql"
USER="backup"
PASS="${MYSQL_PWD:-}"

# Pull every user@host pair, normalize, then dump grants for each.
mysql -N -B -u "$USER" -p"$PASS" -e 
  "SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user WHERE user<>''" 
  | while read -r account; do
      echo "-- Grants for ${account}"
      mysql -N -B -u "$USER" -p"$PASS" 
        -e "SHOW GRANTS FOR ${account};"
      echo ""
    done > "$OUT"

chmod 600 "$OUT"
echo "Wrote $OUT"

Run it with:

chmod +x backup-grants.sh
./backup-grants.sh

The script iterates over every non-empty account in mysql.user and emits a SHOW GRANTS FOR 'user'@'host'; line for each. The result is a .sql file you can replay on any destination with the same mysql -u root -p < grants.sql pattern as the other methods.

To restore a single user from this file (or any other dump), use grep to slice it first:

grep -A 50 "Grants for 'app'@'10.0.%'" /backup/grants-2026-09-11.sql 
  | mysql -u root -p

Method Comparison: Which Tool Should You Use?

No single tool wins everywhere. The table below matches each method to the version it supports, the kind of output it produces, and the situation where I have seen it work best.

Tool	Supported versions	Output format	Best for
mysqldump –system=users	MariaDB 10.3+ only	CREATE USER + GRANT statements	MariaDB servers on a current LTS line
mysqlpump –users	MySQL 5.7 and 8.0 (not 8.4+)	CREATE USER + GRANT statements, with –include-users support	Selective user backups on classic MySQL releases
pt-show-grants	Any MySQL or MariaDB 5.1+	Normalized SHOW GRANTS output	Heterogeneous fleets and version-agnostic scripts
Bash + SHOW GRANTS loop	Any version with the mysql client	Plain SHOW GRANTS lines per user	Air-gapped boxes with no extra packages allowed

Quick rule of thumb: pick pt-show-grants if you manage more than one server, pick mysqldump --system=users if everything you run is MariaDB, and pick mysqlpump --users if you still live on MySQL 5.7 or 8.0. The bash script is your safety net when nothing else fits.

Best Practices: Backup User Privileges and Dump File Security

The user that runs these dump commands does not need full root. Following the principle of least privilege, create a dedicated backup account with the minimum GRANTs required for the tool you picked. The split between InnoDB and MyISAM matters because mysqldump behaves differently with each storage engine.

InnoDB-only Servers (Most Modern Setups)

For InnoDB tables you can use --single-transaction, which takes a brief lock and runs the dump inside a single transaction. That requires:

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.*
  TO 'backup'@'localhost' IDENTIFIED BY 'strong-password';
FLUSH PRIVILEGES;

Servers With MyISAM Tables (Older or Mixed Fleets)

If you still rely on MyISAM, you need an additional lock and the --lock-all-tables flag. Fromdual’s older MySQL guide recommends LOCK TABLES ON *.*, which the GRANT above already covers, plus the optional EVENT and TRIGGER privileges if you back those objects up:

GRANT EVENT, TRIGGER, SHOW VIEW ON *.*
  TO 'backup'@'localhost';
FLUSH PRIVILEGES;

For Percona’s pt-show-grants and the bash script, you only need read access to the grant tables:

GRANT SELECT ON mysql.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Securing the Dump File

Every grants dump contains password hashes in plaintext SQL. That is fine in transit over an SSH tunnel, but it is dangerous sitting on disk. I lock down my dumps with three rules:

• Restrict permissions – run chmod 600 grants-*.sql right after writing the file, or do it inside the script as shown in Method 4.
• Encrypt at rest – if the backup host has LUKS or an equivalent, that is enough. For off-host copies, pipe the file through gpg -c or openssl enc -aes-256-gcm before shipping it.
• Version the filename – the $(date +%F) suffix in every command above keeps one backup per day, so a corrupt file never wipes out your only good copy.

Automating the Grants Backup and Verifying the Dump

The fastest way to forget a backup is to leave it manual. Pair your grants dump with the regular mysqldump of application data in a single cron entry, then verify the file before trusting it.

A Cron-Ready Backup Script

Save this as /usr/local/sbin/mysql-grants-backup.sh:

#!/usr/bin/env bash
set -euo pipefail

OUT_DIR="/backup/mysql"
DATE="$(date +%F)"
GRANTS="${OUT_DIR}/grants-${DATE}.sql"
DATA="${OUT_DIR}/all-databases-${DATE}.sql.gz"

mkdir -p "$OUT_DIR"

# 1. Dump users and privileges with pt-show-grants (or your tool of choice).
pt-show-grants --user=backup --password="${MYSQL_PWD}" > "$GRANTS"
chmod 600 "$GRANTS"

# 2. Dump application data with mysqldump, in parallel.
mysqldump --single-transaction --routines --triggers 
  --user=backup --password="${MYSQL_PWD}" --all-databases 
  | gzip > "$DATA"
chmod 600 "$DATA"

echo "Grants: $GRANTS"
echo "Data:   $DATA"

Schedule it daily at 02:30 by adding this line to /etc/cron.d/mysql-backup:

30 2 * * * root /usr/local/sbin/mysql-grants-backup.sh >> /var/log/mysql-backup.log 2>&1

Store MYSQL_PWD in a root-only /root/.my.cnf file rather than in the script itself. The --login-path option in newer MySQL clients is even cleaner if you have it available.

Verifying the Dump Before You Trust It

Before you ship a dump file to cold storage, run a syntax check against it. Nothing is worse than restoring a corrupted .sql file during an outage. For pt-show-grants output, the fastest check is a dry parse:

mysql --execute="SET sql_mode='ANSI'; SOURCE /backup/grants-2026-09-11.sql;" 
  --user=root --password 2>&1 | tee /tmp/grants-verify.log

If that command finishes without errors, every statement in the file is at least syntactically valid against the current server. For application data dumps, gzip -t all-databases-*.sql.gz plus a count of CREATE TABLE lines gives you a quick sanity check.

Restoring a Single User From the Dump

Sometimes you do not want a full restore – you only need one account. Slice the file with grep and pipe it back in:

awk '/-- Grants for '''app'''@/ {flag=1} flag {print} /^$/ && flag {flag=0; exit}' 
  /backup/grants-2026-09-11.sql | mysql -u root -p

This pulls every grant line that belongs to the app@ account and replays it on the destination, leaving every other account untouched.

Frequently Asked Questions

Wrapping Up: Your MySQL User Backups

Backing up MySQL users and privileges is one of those tasks that takes five minutes once it is automated and ruins your week when it is not. Pick the method that matches your server version, store the .sql file with chmod 600, pair the grants dump with your regular mysqldump cron entry, and verify the file before you trust it.

Start with one method today – pt-show-grants if you want a tool that works everywhere, or mysqldump --system=users if your fleet is pure MariaDB 10.3+. Run it against a staging server, restore it into a fresh instance, and confirm that every account comes back with the right grants. Once that round trip works, schedule the script in cron and stop worrying about losing user accounts on the next migration.