When migrating MySQL servers, simply copying the mysql.user table isn't always sufficient. MySQL stores user privileges across multiple tables in the mysql database:

mysql.user (user accounts and global privileges)
mysql.db (database-level privileges)
mysql.tables_priv (table-level privileges)
mysql.columns_priv (column-level privileges)
mysql.procs_priv (stored procedure privileges)

Use mysqldump to properly export all privilege-related tables:

mysqldump --all-databases --routines --no-data --lock-all-tables \
--host=old-server --user=admin --password > mysql_privileges.sql

This command exports:

• All database structures without data (--no-data)
• Stored procedures (--routines)
• User privileges across all tables

If you only need user accounts and passwords:

mysqldump mysql user --host=old-server --user=admin --password > mysql_users.sql

For complete privilege migration:

mysqldump mysql user db tables_priv columns_priv procs_priv \
--host=old-server --user=admin --password > full_privileges.sql

After transferring the dump file to the new server:

mysql --host=new-server --user=root --password < mysql_privileges.sql

Then flush privileges to apply changes:

mysqladmin --host=new-server --user=root --password flush-privileges

Check user privileges on the new server:

SELECT * FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';

• MySQL versions must be compatible (5.7 to 5.7, not 5.7 to 8.0)
• Password hashing algorithms may differ between versions
• Test the migration in a staging environment first

For regular migrations, consider this bash script:

#!/bin/bash
# MySQL Privilege Migration Script

OLD_HOST="old.mysql.server"
NEW_HOST="new.mysql.server"
MYSQL_USER="admin"
MYSQL_PASS="securepassword"
DUMP_FILE="/tmp/mysql_privileges_$(date +%Y%m%d).sql"

# Export privileges
mysqldump --host=$OLD_HOST --user=$MYSQL_USER --password=$MYSQL_PASS \
--all-databases --routines --no-data --lock-all-tables > $DUMP_FILE

# Import to new server
mysql --host=$NEW_HOST --user=$MYSQL_USER --password=$MYSQL_PASS < $DUMP_FILE

# Flush privileges
mysqladmin --host=$NEW_HOST --user=$MYSQL_USER --password=$MYSQL_PASS flush-privileges

echo "Migration completed. Verify with: SHOW GRANTS FOR 'user'@'host'"

---

When migrating MySQL servers, simply copying the mysql.user table isn't sufficient due to:

• Password hash variations between MySQL versions
• Potential privilege table structure changes
• System-specific metadata conflicts

Method 1: Using mysqldump for System Tables

mysqldump --all-databases --routines --triggers --no-create-info --no-data \
--ignore-table=mysql.user --ignore-table=mysql.db --ignore-table=mysql.tables_priv \
--ignore-table=mysql.columns_priv --ignore-table=mysql.procs_priv \
--ignore-table=mysql.proxies_priv > privileges.sql

Method 2: SHOW GRANTS Command

Generate privilege 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/$/;/' > all_grants.sql

Method 3: pt-show-grants (Percona Toolkit)

pt-show-grants --host oldserver --user admin --password > grants.sql
mysql --host newserver --user admin --password < grants.sql

For MySQL 5.7+ to 8.0+ migrations:

# On old server (5.7):
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

# Then use SHOW GRANTS or pt-show-grants to export

After migration, verify consistency:

mysql -e "SELECT user,host,authentication_string FROM mysql.user"
mysql -e "SHOW GRANTS FOR 'appuser'@'%'"

• Missing FLUSH PRIVILEGES after import
• Version-specific authentication plugins
• Hostname resolution differences
• Missing global privileges (REQUIRE SSL, etc.)

Example bash script for complete migration:

#!/bin/bash
OLD_HOST="db1.example.com"
NEW_HOST="db2.example.com"
CREDS="-u root -p${MYSQL_PWD}"

# Export grants
mysql ${CREDS} -h ${OLD_HOST} -N -e \
"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') \
FROM mysql.user WHERE user NOT LIKE 'mysql.%'" \
| mysql ${CREDS} -h ${OLD_HOST} \
| sed 's/$/;/' \
| grep -v "Grants for" > mysql_grants.sql

# Import to new server
mysql ${CREDS} -h ${NEW_HOST} < mysql_grants.sql
mysql ${CREDS} -h ${NEW_HOST} -e "FLUSH PRIVILEGES"

echo "Migration completed. Verify privileges on new server."