When migrating MySQL servers, database transfers via mysqldump are straightforward, but user privileges require special handling. The key challenge lies in merging privileges from the old server's mysql database (5.0.67) into the new server (5.0.51a) without disrupting existing accounts.

First, create a privilege dump from the source server that excludes system accounts:

mysqldump -u root -p --skip-lock-tables \
--no-create-info --no-data --extended-insert=FALSE \
mysql user db tables_priv columns_priv procs_priv \
> mysql_privileges.sql

For merging privileges without duplicates, use this script to filter existing users:

#!/bin/bash
# Filter existing users from privilege dump
MYSQL_USER=root
MYSQL_PASS=yourpassword
TEMP_FILE=filtered_privileges.sql

# Extract existing usernames
mysql -u $MYSQL_USER -p$MYSQL_PASS -N -e \
"SELECT CONCAT('\'', user, '\'@\'', host, '\'') FROM mysql.user" > existing_users.txt

# Filter privilege dump
grep -v -f existing_users.txt mysql_privileges.sql > $TEMP_FILE

# Import filtered privileges
mysql -u $MYSQL_USER -p$MYSQL_PASS mysql < $TEMP_FILE

For precise control, generate individual GRANT statements from the source server:

mysql -u root -p -N -e \
"SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') \
FROM mysql.user WHERE user NOT IN ('root','mysql.sys','debian-sys-maint')" \
| mysql -u root -p --skip-column-names \
| sed 's/$/;/' > grants.sql

This creates executable SQL that can be reviewed and selectively run on the target server.

After import, verify consistency with:

-- Compare user lists
SELECT user, host FROM mysql.user ORDER BY user, host;

-- Check specific privileges
SHOW GRANTS FOR 'username'@'host';

For password hashes in MySQL 5.0, ensure compatibility:

-- Update password format if needed
UPDATE mysql.user SET password = OLD_PASSWORD('plaintext') 
WHERE user = 'username' AND host = 'host';

When migrating MySQL servers, database administrators often focus on transferring databases using mysqldump, but user privileges stored in the mysql database require special handling. The key challenge is preserving existing privileges on the destination server while importing new ones.

The mysql database contains several critical tables for privilege management:

user       # Global privileges
db         # Database-level privileges
tables_priv # Table-level privileges
columns_priv # Column-level privileges
procs_priv  # Stored procedure privileges

To export privileges without risking database conflicts:

mysqldump --no-data --skip-lock-tables mysql user db tables_priv columns_priv procs_priv > mysql_privileges.sql

This creates a SQL dump containing only the privilege tables' structure and data.

Rather than importing the entire mysql database, use these SQL commands to merge specific users:

# First, identify conflicting users
SELECT User, Host FROM mysql.user WHERE User IN 
(SELECT User FROM temp_import.user);

# Then selectively import non-conflicting users
INSERT IGNORE INTO mysql.user 
SELECT * FROM temp_import.user 
WHERE (User, Host) NOT IN 
(SELECT User, Host FROM mysql.user);

For a controlled merge process:

# Step 1: Create temporary database for import
CREATE DATABASE temp_privileges;
USE temp_privileges;

# Step 2: Import the privilege dump
SOURCE mysql_privileges.sql;

# Step 3: Transfer specific user's privileges
INSERT INTO mysql.db 
SELECT * FROM temp_privileges.db 
WHERE User = 'target_user';

# Step 4: Clean up
DROP DATABASE temp_privileges;
FLUSH PRIVILEGES;

Between MySQL 5.0.67 and 5.0.51, watch for:

• Column structure changes in privilege tables
• Deprecated privilege types
• Password hash algorithm differences

Use this compatibility check:

SHOW CREATE TABLE mysql.user;
# Compare output between source and destination

For multiple users, consider this bash script:

#!/bin/bash
# Export privileges
mysqldump --no-data --skip-lock-tables mysql user db > privileges.sql

# Filter and import
mysql -e "CREATE DATABASE temp_priv; USE temp_priv; SOURCE privileges.sql;"
mysql -e "INSERT IGNORE INTO mysql.user SELECT * FROM temp_priv.user;"
mysql -e "INSERT IGNORE INTO mysql.db SELECT * FROM temp_priv.db;"
mysql -e "DROP DATABASE temp_priv; FLUSH PRIVILEGES;"

After migration, verify with:

SHOW GRANTS FOR 'username'@'host';
SELECT * FROM mysql.db WHERE Db = 'database_name';