Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

While trying to get remote MySQL working, I somehow managed to lock myself out with the root account.

Normally, I would stop the MySQL service and restart it using /etc/init.d/mysql --init-file, but when I try to do that, it says the script has been converted to an upstart job and to use the "service mysql" command. Unfortunately, as far as I know, the service command doesn't support the --init-file option.

Is there any other way to reset the host for the root user?

share|improve this question
1  
This is a duplicate of serverfault.com/questions/210677/reseting-mysql-root-password –  mailq Aug 27 '11 at 21:12

2 Answers 2

up vote 9 down vote accepted

Stop MySQL:

service mysqld stop

then start MySQL like this

mysqld_safe --skip-grant-tables --skip-networking

At this point you should be able to connect to mysql (from the local machine) as root.

share|improve this answer
1  
You could also service mysqld restart --skip-grant-tables --skip-networking. Then, after fixing root, service mysql restart. Nonetheless, +1 !!! –  RolandoMySQLDBA Aug 27 '11 at 22:21
    
Thank you! That did it. –  nickjyc Aug 27 '11 at 22:32

Ultimately, that option wil be passed to mysqld_safe; the documentation describes this in detail. You may be able to discover how upstart is invoking MySQL, and then start it manually in similar way with your added --init-file option. Make sure you start it up as the right user, however!

If you're using Debian or Ubuntu, you will probably find that there is a root-equivalent debian-sys-maint user which you may be able to use temporarily; you'll find the automatically generated password for that in /etc/mysql/debian.cnf. There may be alternatives on other systems, too? This option would probably be easier,

share|improve this answer

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.