Migrate mysql users to another server

Question

I have created a mysqldump --all-databases and transferred all my databases to the new server. It didn't work as debian-sys-maintusers password didn't match. So I change the password of this user. After that I restart my server and got this error.

ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

I dont know how many more errors will come after this. So I thought create dump with only databases which are associated with my applications (mysqldump --databases).

Now how do migrate the users? Is there any standard way?

More Information:

New Server version: 5.1.63-0+squeeze1 (Debian)
Old Server version: 5.0.51a-24+lenny5 (Debian)

Answer 1

You probably need to run mysql_upgrade, since your MySql versions are different.

As a general rule, however, do not copy the mysql system schema from one server to another. As a consequence, and as far as I know, there is no "standard" way of copying users and user privileges from one server to another.

If you really want/need to do it, try the following:

$> mysql --silent --skip-column-names -e"show grants for user@host"

The above outputs GRANT statements that you can feed straight away into your target server to create the user and give the same authorisations.

However, if your target server is empty, you could just move the whole data folder from your old server to the new server, and then run the standard upgrade procedure from 5.0 to 5.1 on the new server.