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
  • 95,029
  • 29
  • 173
  • 228
nickjyc
  • 83
  • 1
  • 5
  • 1
    This is a duplicate of http://serverfault.com/questions/210677/reseting-mysql-root-password – mailq Aug 27 '11 at 21:12

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
  • 776
  • 5
  • 6
  • 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
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
  • 55
  • 8