When migrating MySQL databases to a new server, most administrators focus on transferring the database schemas and data while overlooking user accounts and permissions. This oversight can lead to broken applications when the new server goes live. Unlike database objects which can be easily exported through tools like phpMyAdmin or mysqldump, user privileges require special handling.

The most reliable method is to query MySQL's system tables directly. Here's the SQL query I use to generate CREATE USER statements:

SELECT CONCAT('CREATE USER \'', user, '\'@\'', host, 
              '\' IDENTIFIED BY \'', 
              IF(authentication_string='', 'RANDOM_PASSWORD', authentication_string), 
              '\';') 
FROM mysql.user 
WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema');

For modern MySQL versions (5.7.6+), use this enhanced version that handles authentication plugins:

SELECT CONCAT('CREATE USER \'', user, '\'@\'', host, '\'',
    IF(plugin='mysql_native_password', 
       CONCAT(' IDENTIFIED WITH mysql_native_password BY \'', 
              IF(authentication_string='', 'RANDOM_PASSWORD', authentication_string), '\''),
    IF(plugin='caching_sha2_password',
       CONCAT(' IDENTIFIED WITH caching_sha2_password BY \'', 
              IF(authentication_string='', 'RANDOM_PASSWORD', authentication_string), '\''),
    IF(plugin='sha256_password',
       CONCAT(' IDENTIFIED WITH sha256_password BY \'', 
              IF(authentication_string='', 'RANDOM_PASSWORD', authentication_string), '\''),
    '')))) AS create_user_stmt
FROM mysql.user
WHERE user NOT LIKE 'mysql.%' AND user NOT LIKE '%root%';

After creating the users, you'll need to grant their privileges. This script generates all necessary GRANT statements:

SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') 
FROM mysql.user 
WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema');

You can execute these statements directly on the new server or pipe them to a file:

mysql -u root -p -e "SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') 
FROM mysql.user 
WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema');" | 
mysql -u root -p --skip-column-names | 
mysql -u root -p

For simpler cases, mysqldump can help:

mysqldump --routines --no-data --no-create-info --users \
--host=old_server --user=admin --password \
--ignore-table=mysql.user mysql > users_and_grants.sql

Then import to the new server:

mysql -u root -p < users_and_grants.sql

After migration, verify the privileges match by comparing outputs from both servers:

mysql -u root -p -e "SELECT user, host, authentication_string, 
plugin FROM mysql.user ORDER BY user, host;" > users.txt
mysql -u root -p -e "SELECT * FROM mysql.db ORDER BY user, host;" > db_privs.txt
mysql -u root -p -e "SELECT * FROM mysql.tables_priv ORDER BY user, host;" > table_privs.txt

For large deployments, consider Percona's pt-show-grants tool:

pt-show-grants --host=old_server --user=admin --password \
--ignore='mysql.%','root' > grants.sql
mysql -u root -p < grants.sql

When migrating MySQL databases between servers, table data gets most of the attention - but user privileges often fall through the cracks. The mysqldump utility handles database contents beautifully, but leaves authentication and authorization data behind. Here's how to bridge that gap.

MySQL stores privilege information in the mysql system database. A complete dump includes all user accounts, passwords (hashed), and permissions:

mysqldump --all-databases --routines --triggers --events --users --password=[password] > full_backup.sql

Alternatively, target just the privilege system:

mysqldump mysql user db tables_priv columns_priv procs_priv proxies_priv > mysql_privileges.sql

For more surgical control, generate GRANT statements for all users:

mysql -B -N -e "SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') 
FROM mysql.user WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema')" | 
mysql | sed 's/$GRANT .*$/\1;/;s/^$Grants for .*$/-- \1 --/;/--/{x;p;x;}' > all_grants.sql

This produces executable SQL like:

-- Grants for app_user@localhost --
GRANT USAGE ON *.* TO app_user@localhost;
GRANT SELECT, INSERT, UPDATE ON app_db.* TO app_user@localhost;

When copying users between MySQL 5.7 and 8.0+, handle authentication differences:

# For MySQL 5.7 → 8.0 migration:
SELECT CONCAT('ALTER USER \'', user, '\'@\'', host, '\' IDENTIFIED WITH mysql_native_password 
BY \'', authentication_string, '\';') FROM mysql.user WHERE plugin = 'mysql_native_password';

• Compare SELECT * FROM mysql.user outputs
• Test application connections with each user type
• Verify procedure/function permissions with SHOW CREATE PROCEDURE
• Check replication privileges if applicable