I just tried to install serendipity. So I added a new user to the database. Than it happened, instead adding a new user I deleted all existing users including 'root'. So I had a big problem, without root you're not able to login again. All the hints in the web for flushing the root user password didn't help because I even didn't have a root user anymore.

So here is the way throughout the problem: At first you have to start the mysqld_safe daemon with the option --skip-grant-tables

mephisto ~ # mysqld_safe --pid-file=/root/mysqld.pid --skip-grant-tables --user=root

Now it should be possible to connect to your mysql database on localhost as root.

mephisto ~ # mysql mysql
mysql>

The problem is now that mysqld is running with --skip-grant-tables and so it is not possible to add users in the "normal" way. We have to insert the user root into the user table manually by the following command:

mysql> INSERT INTO user
     -> VALUES('localhost','root',PASSWORD('my_pass'),
     -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
     -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
     -> 'Y','Y','','','','',0,0,0,0);

Now you have to stop the mysqld daemon

kill -s 9 `cat /root/mysqld.pid`

and restart it in the normal way (e.g. /etc/init.d/mysql start on gentoo). You should now be able to login as root again with your previously set password.

BUT the much better way is: "Leave the user root in the user table!"