Sometimes I want a MariaDB slave to have the same user accounts and passwords as the master, but I do not want it to inherit the same permissions.

This is common when the slave is used for reporting, internal access, standby, or a limited failover role. The accounts may need to match, but the grants should remain different.

In that case, the goal is simple: sync only the users and password hashes, and leave the slave’s privileges alone.

MariaDB Is Slightly Different from Old MySQL Habits

In modern MariaDB, user accounts, passwords, and global privileges are stored in mysql.global_priv. The older mysql.user still exists, but it is now a view that references mysql.global_priv. (MariaDB)

That detail matters because many older MySQL-oriented guides still treat mysql.user as the main storage table. In MariaDB 10.4 and newer, that is no longer the real source of truth. MariaDB’s own documentation states that account data is now stored in mysql.global_priv, while mysql.user remains for compatibility. (MariaDB)

So for this use case, the main object to keep in mind is:

mysql.global_priv
Code language: CSS (css)

What You Should Not Sync

If the slave must keep different permissions, do not copy the privilege tables that define access rules beyond the account itself.

Examples include

mysql.db
mysql.tables_priv
mysql.columns_priv
mysql.procs_priv
mysql.proxies_priv
mysql.roles_mapping
Code language: CSS (css)

MariaDB documents mysql.db as database-level privileges and mysql.tables_priv as table-level privileges, while GRANT stores global privileges in mysql.global_priv and database privileges in mysql.db. (MariaDB)

If you copy those tables, you are no longer syncing only users and passwords. You are also cloning authorization behavior, which is exactly what you said you do not want.

The Better Method

The cleaner method is to generate SQL statements from the master and run them on the slave, that means.

• use the master as the source of account information
• generate CREATE USER statements if accounts do not exist yet
• generate ALTER USER statements if accounts already exist
• keep password hashes intact
• do not import the other grant tables

This approach is safer than copying system tables directly, especially when master and slave may not be perfectly identical in role or version.

MariaDB documentation also recommends using account management statements such as CREATE USER and GRANT instead of directly updating privilege tables. (MariaDB)

Step 1, Check the Accounts on the Master

Use this query to list the accounts from the actual privilege storage layer:

SELECT Host, User
FROM mysql.global_priv
ORDER BY User, Host;
Code language: CSS (css)

That confirms which accounts exist on the master. mysql.global_priv is the real table that stores users, account properties, and global privileges in modern MariaDB. (MariaDB)

Step 2: Read the Authentication Data

For practical export work, mysql.user can still be useful because it is exposed in a familiar format as a compatibility view. MariaDB says this view exists specifically so tools that inspect mysql.user can continue to work. (MariaDB)

A practical query is:

SELECT User, Host, plugin, authentication_string
FROM mysql.user
ORDER BY User, Host;
Code language: CSS (css)

This gives you the account, host, authentication plugin, and password-related value where applicable. (MariaDB)

Step 3, Generate CREATE USER Statements

If the users do not exist yet on the slave, generate SQL on the master like this.

SELECT CONCAT(
  'CREATE USER IF NOT EXISTS ''', User, '''@''', Host,
  ''' IDENTIFIED BY PASSWORD ''', authentication_string, ''';'
) AS stmt
FROM mysql.user
WHERE User <> ''
  AND authentication_string <> ''
ORDER BY User, Host;
Code language: PHP (php)

MariaDB documents CREATE USER and supports using password hashes with IDENTIFIED BY PASSWORD. It also notes that creating an account updates the mysql.user view and the underlying mysql.global_priv table. (MariaDB)

Save the output, review it, and run it on the slave.

Step 4, Generate ALTER USER Statements

If the accounts already exist on the slave, it is usually better to update only the passwords

SELECT CONCAT(
  'ALTER USER ''', User, '''@''', Host,
  ''' IDENTIFIED BY PASSWORD ''', authentication_string, ''';'
) AS stmt
FROM mysql.user
WHERE User <> ''
  AND authentication_string <> ''
ORDER BY User, Host;
Code language: PHP (php)

Use this when you want to keep the existing account structure on the slave and only refresh the password hashes. MariaDB documents ALTER USER for modifying authentication settings and passwords. (MariaDB)

Important Caveat, Not Every Account Stores a Password Hash

This part is easy to overlook.

MariaDB documents that some authentication plugins, including unix_socket, named_pipe, gssapi, and pam, do not store passwords in mysql.global_priv the way password-based accounts do. For those accounts, there may be no usable password hash to export with this method. (MariaDB)

So this method is best for normal password-based users. If an account authenticates through socket or external auth, you may need to recreate it with the same plugin instead of trying to sync a password hash.

Why This Works Well

This method gives you exactly the level of sync you want.

Do sync

• account names
• host definitions
• password hashes
• authentication settings where relevant

Do not sync

• database grants
• table grants
• column grants
• routine grants
• role mappings

That keeps the slave aligned at the login level, but independent at the authorization level. For a reporting slave or restricted standby server, that is usually the correct design. (MariaDB)

My Practical Recommendation

If your requirement is to “Make the slave use the same MariaDB users and passwords as the master, but keep different permissions on the slave,”

Then this is the path I would use.

1. Query the master for users and authentication data.
2. Generate CREATE USER statements for accounts that do not exist on the slave.
3. Generate ALTER USER statements for accounts that already exist.
4. Apply only those statements on the slave.
5. Do not dump or restore the other privilege tables.

It is focused, easy to review, and much safer than cloning the full mysql privilege setup.

Final Thought

This is one of those cases where MariaDB looks familiar but behaves differently enough that older MySQL habits can be misleading.

If you only need accounts and passwords, do not copy more than necessary. Use MariaDB’s account statements, keep the slave’s grants separate, and make the synchronization as narrow as possible.

That keeps the setup cleaner and avoids accidentally changing access rules on the replica.

Useful Queries Recap

List accounts from the real MariaDB privilege store

SELECT Host, User
FROM mysql.global_priv
ORDER BY User, Host;
Code language: CSS (css)

Read account authentication data through the compatibility view

SELECT User, Host, plugin, authentication_string
FROM mysql.user
ORDER BY User, Host;
Code language: CSS (css)

Generate CREATE USER statements

SELECT CONCAT(
  'CREATE USER IF NOT EXISTS ''', User, '''@''', Host,
  ''' IDENTIFIED BY PASSWORD ''', authentication_string, ''';'
) AS stmt
FROM mysql.user
WHERE User <> ''
  AND authentication_string <> ''
ORDER BY User, Host;
Code language: PHP (php)

Generate ALTER USER statements

SELECT CONCAT(
  'ALTER USER ''', User, '''@''', Host,
  ''' IDENTIFIED BY PASSWORD ''', authentication_string, ''';'
) AS stmt
FROM mysql.user
WHERE User <> ''
  AND authentication_string <> ''
ORDER BY User, Host;
Code language: PHP (php)

Source Notes

MariaDB documents that mysql.global_priv stores user accounts and global privileges, and that mysql.user remains as a compatibility view in modern versions.

It also documents CREATE USER, ALTER USER, and the cases where some authentication plugins do not store password hashes in the same way. (MariaDB)