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.


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.

  • 1
    Please answer your own question using the button below! – slhck Oct 20 '11 at 14:09

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
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
|improve this answer|||||
  • Feels great to solve your own problems, eh? +1 !!! – RolandoMySQLDBA Oct 31 '11 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.