2

I guess I messed up the user table trying to update the password running something like

UPDATE mysql.user SET authentication_string='root' WHERE User='root';

without flushing privileges. Now I'm basically locked out and I cannot connect to mysql cli even if I try

sudo mysqld_safe --skip-grant-tables &

There is a way I can safely remove all mysql databases including user table?

(MacOS mojave)

This is the content of /usr/local/bin related to mysql

mysql               mysqladmin          mysqlpump
mysql.server            mysqlbinlog         mysqlrouter
mysql_client_test       mysqlcheck          mysqlrouter_plugin_info
mysql_config            mysqld              mysqlshow
mysql_config_editor     mysqld_multi            mysqlslap
mysql_secure_installation   mysqld_safe         mysqltest
mysql_ssl_rsa_setup     mysqldump           mysqlxtest
mysql_tzinfo_to_sql     mysqldumpslow
mysql_upgrade           mysqlimport
2

First backup your /etc/mysql and /var/lib/mysql directories.

Then try to initalize

mysql_install_db

to recreate the mysql schema from scratch.

You can read about it here: https://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

5
  • I do not have both those dirs. And I get command not found launching mysql_install_db. It is a fresh installation I don't have any db I have to save. I don't mind wiping up the whole mysql installation. But I'm not clear what to do other than uninstall using brew Apr 28 '19 at 17:44
  • what whereis mysql says?
    – Comar
    Apr 28 '19 at 17:45
  • which mysql prints /usr/local/bin/mysql (whereis prints nothing) Apr 28 '19 at 17:47
  • Ah, my mistake, I have confused OS. Look for mysql configuration, binaries and files somewhere inside /usr/local, you may try to use locate mysql for this job.
    – Comar
    Apr 28 '19 at 17:50
  • I edited the question with my mysql bins in /usr/local/bin if it helps Apr 28 '19 at 17:52
1

You could simply uninstall mysql and purge configuration files if you don't need the databases or any settings.

The steps would be:

brew uninstall mysql
brew cleanup
sudo rm -rf /usr/local/var/mysql    ## verify within my.cnf

ps. Take this with a grain of salt, as I'm not a MacOS user.

3
  • I would avoid mess up the whole sistem, I will search around a little bit. macOS it's a f nightmare... Apr 28 '19 at 17:59
  • ok even removing /usr/local/var/mysql didn't solved.. Apr 28 '19 at 18:05
  • You might need to remove the binaries too, try this also: sudo rm -rf /usr/local/bin/mysql.
    – Leo
    Apr 29 '19 at 14:53
0

I got the problem, basically almost all the solution you can find around are outdated. With mysql 8.0.15 you have to follow this guide https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

Using the method UPDATE mysql.user SET authentication_string.. will lock you out from your own mysql server (you won't solve even with mysqld_safe --skip-grant-tables).

0

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.