Search code examples
mysql

Problem changing mysql root password on mac

a colleague of mine has a MySQL server installed on her Mac. The OSX version is Sonoma 14.5, MySQL is version 8.4.0. We're both novices in what concerns MySQL server.

The problem is, that workbench cannot access the server, claiming the password is wrong. The password has potentially expired or been lost since the setup of the server, so we have to reset it.

And we fail to reset the password, that's the main issue.

We followed this: Setting the MySQL root user password on OS X and this: How to find out the MySQL root password but it wouldn't work. When using a statement with PASSWORD('new password'), it throws a syntax error (see below). When using a statement with ALTER USER, it either says

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

or

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Looking for this error code in google actually gives me info about the --secure-file-priv option, which is a different flag to what I am using.

The most recent procedure that I've tried is the one from here: How to get all privileges back to the root user in MySQL?

  1. I stopped the mysql server.
  2. Then, in the one terminal I executed
sudo cd /usr/local/mysql/bin
sudo ./mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
  1. In another terminal, I did the following
sudo cd /usr/local/mysql/bin
./mysql -u root
mysql> use mysql; update user set authentication_string='' where User='root';
mysql> quit
./mysqladmin shutdown
  1. Then, I restarted the server with sudo ../support-files/mysql.server start which gave me

Starting MySQL . SUCCESS!

  1. I logged in and tried to set the password
./mysql -u root
mysql> SET PASSWORD = PASSWORD('testPassword');

which prompted this message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('testPassword')' at line 1

So, again, the same syntax error as before. In step 5, I also tried with ALTER USER too, but it also gave me the error messages from before. According to this answer Reset MySQL root password using ALTER USER statement after install on Mac one is supposed to use SET PASSWORD, which, however, doesn't seem to work.

What am I doing wrong?

Thank you!

conni

Solution

  • You can maybe try to to drop the root user and try to create a new root user and grant all the privileges.

    DROP USER 'root'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED BY 'new_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

    Let me know if this works or not.