Please keep in mind that mysql.user is different between major releases.
select count(1) from information_schema.columns
where table_schema='mysql' and table_name='user';
This query brings back
- 37 for MySQL 5.0
- 39 for MySQL 5.1
- 42 for MySQL 5.5.
- You cannot run this query in MySQL 4.x, but run
desc mysql.user and you will see 31 columns.
There are actually two ways to safely port users from one database to another regardless of the version of MySQL.
OPTION 1 : Use mk-show-grants (Soon to be called pt-show-grants from Percona Toolkit)
This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.
OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)
mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
The resulting MySQLUserGrants.sql from either option can simply be executed and the grant tables are properly populated.