I believe I've deleted MySQL root user, how do I recreate it?

I'm running ISPConfig on CentOS and I don't know how, but I don't seem to have a root mysql user.

I can stop mysqld with

/etc/init.d/mysqld stop

Start it again and skip grant tables

mysqld_safe --skip_grant_tables &
mysql> SELECT * FROM sql.user

I see 4 users I had previously created but no root user. I tried putting this in a file /root/rootreset.sql.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'my_pass' WITH GRANT OPTION;

And starting with that file

mysqld_safe --init-file=/root/rootreset.sql &

[1] 30039
[root@algrhckdj ~]# Starting mysqld daemon with databases from /var/lib/mysql/
STOPPING server from pid file /var/run/mysqld/mysqld.pid
111020 04:06:34  mysqld ended
[1]+  Done                    mysqld_safe --init-file=/root/rootreset.sql

It looks like the sql was executed, I don't see an error, but the service doesn't seem to be running, I can't connect to it without this error.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

I tried running the sql from the console after starting with --skip-grant-table but got this error.

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

This seems like a lot of info but I've been trying all the different suggestions online and can't get anywhere. What should I be doing?

Help me superuser, you're my only hope.

Solution 1:

I followed these instructions and my root user is now listed in the user table and I can login with it. What seemed to do it was performing these steps as the mysql user rather than root.

root# /etc/init.d/mysqld stop
root# su mysql
mysql$ pwd
/home/mysql
mysql$ echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'my_pass' WITH GRANT OPTION;"> mysql-init
mysql$ mysqld_safe --init-file=/home/mysql/mysql-init &
mysql$ rm mysql-init
mysql$ mysql -uroot -p   # woot, works