Introduction

Copying users and permissions from one MySQL or MariaDB server to another is not that simple, there isn't a catch-all solution and you might need different tools depending on your server version.

Some people online would just advice to dump the internal mysql database, which holds all of the users and permissons, among other things.

Overwriting the mysql database is dangerous, especially between different versions of the database server.

I vastly prefer exporting and restoring grant statements.

These will also dump password hashes and thus allow you to recreate the same passwords without getting any of these in cleartext.

MariaDB 10.3+

Not only do you need a server version equal or higher than 10.3, but you need at least the corresponding version of the MariaDB client tools, which include mysqldump.

If you have all of that, dumping the grants is as easy as:

mysqldump --system=users > grants.sql

Of course you can write a more complex command if you need to specify username and password and maybe a remote host:

mysqldump -h <MARIADB_HOST> -u <USER> -p --system=users > grants.sql

Restoring these on another server is as easy as passing these commands to a mysql client:

mysql < grants.sql

Where you can of course also add options to connect using the client (user, password, etc.).

MySQL 5.7+

MySQL Server is no longer the default database server on most Linux distributions but if you're using it, its client package includes a new utility called mysqlpump (it's pump and not dump).

Here's the command to run:

mysqlpump --exclude-databases=% --users

You can specify user, password, etc. to mysqlpump like you'd do mysqldump or the mysql client.

The grants can be restored using the mysql client as explained in the previous section.

MariaDB < 10.3

I use a script for any other database server versions.

The script possibly also works for later versions.

#!/bin/bash

set -e

PASS="REDACTED"
USER="MYSQL_USER"

QUERY="SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) USER_ACCOUNT FROM mysql.user"

MYSQL_USERS=`mysql -u $USER -p$PASS -Bse "$QUERY"`

echo "" > mysql_grants.sql
for usr in $MYSQL_USERS
do
  mysql -u $USER -p$PASS -Bse "SHOW GRANTS FOR $usr" >> mysql_grants.sql
done

grep -v "debian-sys-maint" mysql_grants.sql | \
        grep -v "root" > mysql_grants_no_root.sql

sed -i '/[^;] *$/s/$/;/' mysql_grants.sql
sed -i '/[^;] *$/s/$/;/' mysql_grants_no_root.sql

Where you need to fill in the correct user and password from inside the script.

You could use the read -s command to interactively ask for the password and not echo it to the command line but that's outside of the scope here.

The script lists all of the users then performs a "SHOW GRANTS" query for each of them, outputs the results to a file in current directory called mysql_grants.sql.

I also create a second file called mysql_grants_no_root.sql where I remove any mention of the root or special Debian user "debian-sys-maint" as I usually don't want to restore these (sometimes I do).

The two sed commands at the end add semicolons to every line so we can pipe the resulting sql files to a mysql client.

Older MySQL versions

If the script above doesn't work and your MySQL version is < 5.6, you could try a tool called pt-show-grants from the Percona Toolkit.

The toolkit itself can be downloaded from the Percona website by picking your platform. It then needs to be manually installed.

The pt-show-grants utility outputs grant statements you can ingest to another database by piping it into a mysql client.