Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
Please answer your own question using the button below! –  slhck Oct 20 '11 at 14:09
add comment

1 Answer

up vote 1 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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