8

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?

1

2 Answers 2

9

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.

1
  • 1
    You could also service mysqld restart --skip-grant-tables --skip-networking. Then, after fixing root, service mysql restart. Nonetheless, +1 !!! Aug 27, 2011 at 22:21
1

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,

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.