reset root (overarching) password for mysql

mysqld --skip-grant-tables

or

sudo mysqld_safe --skip-grant-tables

then

mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

if this complains about permissions on the mac, try 

sudo chown -R _mysql:staff /usr/local/mysql-5.7.22-macos10.13-x86_64/
sudo chmod -R 777 /usr/local/mysql-5.7.22-macos10.13-x86_64/data/

then

 /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

or

mysqld --skip-grant-tables

if that doesn't work then try an init file:

mysqld --init-file=/var/lib/mysql/reset.txt  --user=mysql


inside that reset.txt file put the "alter user" statement above.

if that doesn't work then you have to really reset everything: warning, this WILL delete all your databases unless you back them up first. The commands below contain a backup.

mysqldump -u root -p"myrootpassword" mydatabase > /root/mydatabase.sql

# make sure we move existing dbs out the way so they are safe

mv /var/lib/mysql /var/lib/mysql_original

mkdir /var/lib/mysql

chown mysql:mysql /var/lib/mysql

chmod 750 /var/lib/mysql

# and now the coup de grace

mysqld --initialize


You can now re-launch mysqld and it will let you reset your password (you then have to reimport all your databases however, which is a big of a nightmare if you skipped the mysqldump command above)



Comments

Popular posts from this blog

what to do if you crashed your database OR it says there are no tables (but there are!)

Permanently save login password for mysql client