Mac os mysql change root password

MacOS сбросить root пароль для MySQL

Я установил mysql-сервер на свою macOS Mojave, скачав .dmg пакет с официального сайта MySQL.

При установке mysql, установщик не запрашивал никакого root пароля, но в конце выдал сообщение, что пароль для root — dufQqAt00Z, и сообщение сохранить этот пароль.

Я сохранил себе этот пароль (возможно, не скопировав его полностью?).

Далее при подключении к mysql, к моему удивлению, ни этот пароль не пустой пароль для root — не подходили для подключения к MySQL:

Поэтому, следующие шаги подробно описывают как изменить root пароль в MySQL на маке.

1. Остановить mysql-процесс. Два варианта как остановить mysql в MAC OS:

• Пишем в spotlight-search «mysql», открывается панелька mysql, где нажимаем остановить процесс.
  
• Находим pid процесса и убиваем mysql, например так: sudo pkill mysql.

2. Запускаем мускуль в режиме safe с опцией «skip-grant-tables»:

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

Вот как это выглядит, при необходимости вводим sudo пароль операционной системы:

Мы видим сообщение что демон стартанул. Теперь мы будем иметь возможность подключаться к MySQL без ввода пароля (см. пункт 3), так как мы запустили mysql с опцией skip-grant-tables.

3. Подключаемся к mysql, указывая любое имя пользователя и пароль или не указывая вовсе (ведь мы отключили проверку прав в пункте 2), после чего задаем новый пароль пользователю root:

Конечно, вместо строки new-psw, указывайте желаемый пароль.

4. На этом всё, теперь останавливайте mysql, запущенный в safe mode и запукайте в обычном режиме. Подключайтесь к MySQL снова, указывая новый пароль:

Если вы увидите ошибку:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

То задайте новый пароль командой set password :

Теперь мы знаем, как сбросить root пароль на macOS.

Источник

zubaer-ahammed / Reset MySQL Root Password in Mac OS.md

Reset mysql root password in Mac OS:

First Stop MySQL:

1. Go to: ‘System Preferences’ >> ‘MySQL’ and stop MySQL

1. sudo /usr/local/mysql/support-files/mysql.server start
2. sudo /usr/local/mysql/support-files/mysql.server stop
3. sudo /usr/local/mysql/support-files/mysql.server status

Process to Reset MySQL Root Pass in Mac:

Make sure you have Stopped MySQL first (above).

Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables

In a new window connect to the database, set a new password and flush the permissions & quit: mysql -u root

For MySQL older than MySQL 5.7 use:

For MySQL 5.7+ use:

Now flush privileges:

Restart MySQL server.

This comment has been minimized.

Copy link Quote reply

OlatunjiYso commented Mar 21, 2019 •

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

This comment has been minimized.

Copy link Quote reply

classykim925 commented May 17, 2019

Hi, I use 8.0.15 and wanted to reset password since i forgot it. here’s a question.
There’s no reaction if i click «stop mysql server» button and it saids «command not found» when i put «sudo /usr/local/mysql/support-files/mysql.server stop».
Do you have any idea how to stop mysql?

This comment has been minimized.

Copy link Quote reply

wasswa-derick commented May 31, 2019

@classykim925
To reference the similar link MySQL 8.0.15 + , the password() function is not available. Use the command below.

Kindly use UPDATE mysql.user SET authentication_string=’password’ WHERE User=’root’;

This comment has been minimized.

Copy link Quote reply

Kristiyan96 commented May 31, 2019

On Mac, list the running processes with brew services list , and use the name of the process to stop it using brew services stop mysql@5.7 (in your case it might not be mysql@5.7).

This comment has been minimized.

Copy link Quote reply

mohammad-masud commented Aug 18, 2019

This comment has been minimized.

Copy link Quote reply

danieldare commented Aug 19, 2019

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

Worked for me. Thanks

This comment has been minimized.

Copy link Quote reply

sulaim9n commented Sep 11, 2019

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

it shows me that the password is locked .?

This comment has been minimized.

Copy link Quote reply

onrush1995 commented Nov 12, 2019

MacOSX MySql root Password Reset master solution
Step by step guide-
$ brew services stop mysql
$ pkill mysqld
$ rm -rf /usr/local/var/mysql/ # NOTE: this will delete your existing database.
$ brew postinstall mysql
$ brew services restart mysql
$ mysql -u root

This comment has been minimized.

Copy link Quote reply

onrush1995 commented Nov 12, 2019

Hi, I use 8.0.15 and wanted to reset password since i forgot it. here’s a question.
There’s no reaction if i click «stop mysql server» button and it saids «command not found» when i put «sudo /usr/local/mysql/support-files/mysql.server stop».
Do you have any idea how to stop mysql?

You can follow my guide & let me know if it works.

This comment has been minimized.

Copy link Quote reply

bchewy commented Nov 20, 2019

This comment has been minimized.

Copy link Quote reply

neoVincent commented Dec 3, 2019

On 8.0.15 (maybe already before that version) the PASSWORD() function does not work, as mentioned in the comments below. You have to use

UPDATE mysql.user SET authentication_string=’password’ WHERE User=’root’;

This comment has been minimized.

Copy link Quote reply

fquiroz commented Dec 6, 2019 •

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

Worked for me. Thanks

Worked for me too , mysql version 5.7.26

This comment has been minimized.

Copy link Quote reply

ridvansumset commented Jan 16, 2020

@OlatunjiYso has got it right. thanks!

This comment has been minimized.

Copy link Quote reply

nacm commented Feb 8, 2020 •

This has worked for me

This comment has been minimized.

Copy link Quote reply

vigneshveeran-ad commented Feb 8, 2020

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

worked for me thanks

This comment has been minimized.

Copy link Quote reply

wentfar commented Feb 11, 2020

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

works for me, thx!

This comment has been minimized.

Copy link Quote reply

sailei1 commented Feb 17, 2020

This comment has been minimized.

Copy link Quote reply

conan25216 commented Feb 28, 2020 •

It doesn’t work for me..
when I run «mysqld_safe —skip-grant-tables»..

then I go «mysql -uroot»

I’ve just installed the mariadb with «brew install mariadb» 3mintues ago
I went through the installation process carefully to check out password tips it may give me
But I didn’t see anything related anywhere.

Can anyone help me? Thanks a lot
My mariadb version is mysql Ver 15.1 Distrib 10.4.11-MariaDB, for osx10.15 (x86_64) using readline 5.1

and then i reinstall mariadb

where is the password?

This comment has been minimized.

Copy link Quote reply

nichocases commented Apr 12, 2020

Well I don’t know if this help everyone but this work for me, I have «mysql-workbench-community-8.0.19» and «mysql-5.7.29-macos10.14»

• [ check in ‘System Preferences’ >> ‘MySQL’ that is «Running»]

Open terminal and type:

• [ sudo /usr/local/mysql/bin/mysqld_safe —skip-grant-tables]

Open a new terminal tab

• [ sudo /usr/local/mysql/bin/mysql -u root ]

In the same terminal tab
UPDATE mysql.user SET authentication_string=PASSWORD(«rootpass») WHERE User=’root’;

After this
FLUSH PRIVILEGES;

This comment has been minimized.

Copy link Quote reply

ericrasch commented Apr 30, 2020

MacOSX MySql root Password Reset master solution
Step by step guide-
$ brew services stop mysql
$ pkill mysqld
$ rm -rf /usr/local/var/mysql/ # NOTE: this will delete your existing database.
$ brew postinstall mysql
$ brew services restart mysql
$ mysql -u root

This worked for me. Thanks!!

This comment has been minimized.

Copy link Quote reply

onrush1995 commented Apr 30, 2020

This comment has been minimized.

Copy link Quote reply

Amina74 commented May 14, 2020

Well I don’t know if this help everyone but this work for me, I have «mysql-workbench-community-8.0.19» and «mysql-5.7.29-macos10.14»

• [ check in ‘System Preferences’ >> ‘MySQL’ that is «Running»]

Open terminal and type:

• [ sudo /usr/local/mysql/bin/mysqld_safe —skip-grant-tables]

Open a new terminal tab

• [ sudo /usr/local/mysql/bin/mysql -u root ]

In the same terminal tab
UPDATE mysql.user SET authentication_string=PASSWORD(«rootpass») WHERE User=’root’;

After this
FLUSH PRIVILEGES;

I used this procedure then I get this error, 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. How do I fix this

This comment has been minimized.

Copy link Quote reply

nichocases commented May 25, 2020

did you create some tables with the same name before get the error? @Amina74

This comment has been minimized.

Copy link Quote reply

luislmoron commented May 31, 2020

MacOSX MySql root Password Reset master solution
Step by step guide-
$ brew services stop mysql
$ pkill mysqld
$ rm -rf /usr/local/var/mysql/ # NOTE: this will delete your existing database.
$ brew postinstall mysql
$ brew services restart mysql
$ mysql -u root

Oh my god, thanks broth.

This comment has been minimized.

Copy link Quote reply

carolineloppi commented Jul 4, 2020

2. ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

Thank you so much. Worked for me on Mac!

This comment has been minimized.

Copy link Quote reply

nanu89 commented Jul 16, 2020

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

Worked for me. Thanks

This comment has been minimized.

Copy link Quote reply

zhongdongy commented Aug 10, 2020

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

1. Make sure you have Stopped MySQL first (above).
2. Run the server in safe mode with privilege bypass: sudo mysqld_safe —skip-grant-tables
   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
   FLUSH PRIVILEGES;
   exit;
   Then
   mysql -u root
   ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘yourpasswd’;

Worked for me. Thanks

Worked for me too , mysql version 5.7.26

For my case, password() still worked on mysqld version 5.7.31. Haven’t tested the new method yet.

Источник