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?

womble
  • 98,245
nickjyc
  • 83

2 Answers2

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.

Chad Feller
  • 786
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,

dom
  • 85