Hi Matthias,
You grant tables have the correct schema.
To reset the test user's password, you can do this:
1. Restart the MySQL Server using the
--skip-grant-tables option
2. Log in as root (
mysql -uroot -- no -p option needed here)
3. Run the following statement:
UPDATE mysql.user SET Password = PASSWORD('cheese') WHERE User = 'test' AND Host = 'localhost';
Of course, you can use whatever password you like instead of "cheese". ;)
4. Restart the MySQL Server without the --skip-grant-tables option.
Your test@localhost user should now be able to log in.
You can use this technique to reset root and other MySQL user passwords as well.
Do NOT leave MySQL running with --skip-grant-tables any longer than aboslutely necessary. This is tremendously insecure, since it allows anyone to log in as any MySQL user without a password.
Also, you should always avoid operating directly on the grant tables, except in a very few, very special cases like this one. Use of GRANT, SET PASSWORD, CREATE USER, etc. is the proper (and much safer) way to handle user administration.
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle