Сбросить пароль mysql mac os

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.

Источник

How to reset root user password in MySQL 8.0

Jul 17, 2019 · 4 min read

MySQL root user account allow you to have full access on MySQL database. You need to have superuser(root) access in unix environment or Administrator access on Windows to manipulate with mysql services via cli.

We’ve all been there, but on my Mac Catalina i can’t access root account no matter which source i downloaded from. There are plenty of online Q&A about using “ALTER USER” “SET PASSWORD” & suggestion to config my.cnf file etc or even using mysqladmin command. None of this methods work for my machine.

If you’ve suc c essfully install mysql or preinstalled mysql on your OS check with following command and skip the installation session, just go straight to resetting session. Windows user can follow official documentation.

Installation

During installation, cli won’t prompt(on Mac) you to enter root password or make any others configuration changes. But this is definitely leave your database insecure & access from remote locations. But many linux distro do ask about default root password during installation, either you’ve forgotten, or just typed “enter”. In such case you can jump to Reset with skip grant tables (linux) session.

Ubuntu and Debian

You need to update your OS packages to latest, then install via advance package manager or download from official website.

CentOS, Fedora, and Red Hat Enterprise Linux

You need to update your OS packages to latest, then install via package manger(dnf or yum) or download from official website. You can use either chkconfig or service to check mysql service is running.

MacOS

Install brew package manager & install mysql via brew or download from official website.

If you’d old mysql you can either upgrade with brew or reinstall completely.

WARNING: Be sure to backup your databases before you proceed further. You may use mysqldump for individual database backup. For mac you can backup binary databases from following path although it’s not guarantee to work 100%.

After backing up existing databases with the method you’d chosen, we can now safely remove old sql and install new one (those who will be using brew with prune, it’s obsolete now)

Now we can check our installation by brew service.

Reset root password using mysqladmin(linux)

You can access mysqladmin command and reset the current password. Check the mysql process are running on CentOS, Fedora, Red Hat Enterprise Linux and run as root user, then we can reset with new password.

If this case don’t work anymore in newer mysql please follow next session.

Reset with skip grant tables (linux)

Step1: you can simply stop mysql service & restart with skip grant parameter

Then you can reset your root password just like mac user (follow to Step2). Simply refresh privileges and alter user’s password in our case user is happened to be root.

Reset with skip grant tables (mac)

Step1: you can simply stop mysql service & restart with skip grant parameter

Then we will skip the loading of grant table so as of MySQL 8.0.x, — skip-grant-tables automatically enables — skip-networking, which does not allow remote connections.

Step2: Now you will be in mysql command prompt or just type “mysql”. Linux user can start resume from this command.

Step3: We will use mysql databases(meta database), it’s default database which store engine meta info, help, innodb records, proxy, plugins, master & slave records.

If we don’t flush the privileges mysql cli will prompt “MySQL server is running with the — skip-grant-tables option so it cannot execute this statement”. You won’t find such error after following above step, please remember to replace NEWPASSWORD with your desire password.

Step4: Finally you can restart(linux) or start MySQL and login with given password.

Harden your MySQL server

This will ask you root password & walk you through series of question start with validating a plugin called “validate_password” to strength your passwords & test your password policies. Then you may change your root password, afterwards it’s recommend to remove “anonymous user”, deny access from remote, remove testing databases & refresh all privileges. We are all done here, unless you can’t start the first step in that case you don’t have mysql root user password.

Conclusion

Now you’ve a basic MySQL database on your local machine & secure it. Happy coding.

Источник

Resetting mysql server root password on OS X

First of all, I know there are several threads, but I have tried so many solutions and I cant get anything to work.

I dont have any experience with mysql server and Terminal.

I downloaded mysql server 5.7.19

I open mysql from system setting, click stop server. Then I open the terminal and write

I asks me for my password (I assume this is the same when I start my computer). I get a message that command not found.

UPDATE: When I run the solution below, after opening a new window I get the following errors:

4 Answers 4

Stop the MySQL server.

Restart it with the —skip-grant-tables option.

Open another terminal to connect to the MySQL server using the mysql client.

Tell the server to reload the grant tables so that account-management statements work.

Now reset the password for root user

MySQL 5.7.6 and later:

MySQL 5.7.5 and earlier:

Stop the server and restart it normally

First step is to stop MySQL service.

Then you need to start it in safe mode

secondly: let’s open another shell/terminal window, log in with no password

Because in MySQL 5.7, the password field in mysql.user table is removed, now the field name is ‘authentication_string’.

Now again yu need to start the MySQL server

XXXXX$ mysql -u root -bash: mysql: command not found MacBook-Pro:

XXXXX$ UPDATE mysql.user SET Password=PASSWORD(‘my-new-password’) WHERE User=’root’; -bash: syntax error near unexpected token `(‘ MacBook-Pro:

XXXXX$ FLUSH PRIVILEGES; -bash: FLUSH: command not found MacBook-Pro:

The command is not found because MySQL installation folder ( /usr/local/mysql/ ) is not included in the system variable PATH.

1. You can add to PATH
2. OR you can use full path /usr/local/mysql/bin/mysqld_safe

It took me a while in resolving this, considering most solutions around are for versions lower than MySQL version 5.7

Follow this below and it could help get you sorted as well.

For Safely ensuring process: — Turn off the tick on «Automatically Start MySQL Server on Startup» inside System Preferences of MySQL (spotlight — mysql)

• Open Terminal and type sudo /usr/local/mysql/support-files/mysql.server start -PS: This is for ensuring its in-line, times were that the next processes were breaking on me.
• Now shut the MySQL service: sudo /usr/local/mysql/support-files/mysql.server stop
• Type sudo mysqld_safe —skip-grant-tables This will have now bypassed the security for MySQL — not safe for operations and not a permanent solution to always allow you to use MySQL. Currently, as you would see, its in a process. This will allow us to do following steps. Leave this tab of Terminal OPEN throughout remaining process!!

Now Cmd+N (new terminal window), and in the new terminal: — sudo /usr/local/mysql/support-files/mysql.server start — update user set authentication_string=password(‘jj’) where user=’root’ This on older version would have been as update user set password=PASSWORD(“jj”) where user=’root’; — FLUSH PRIVILEGES; //This is essential (updates disk instead of cache) to ensuring the next time around when you close mysql and get back it stays accessible as you setup. — \q or quit

Close it all down — All terminals, give your computer a restart, and ensure everything is in order (ofcourse this entails — restart — open terminal — mysql -u root -p (enter) — respond with password you gave on steps above). In my answer: jj was the password set

Cool-Stuff for General knowledge of fairly new (this somehow immediately worked for me after saying Password is not a field or something of sorts, on going in this new Terminal at update user set authentication_string=password(‘jj’) where user=’root’ , so if you had the same, go at it in following steps — in >mysql itself where you are..): — use mysql; — show tables; — describe user; and then continue as steps above from the point of update user set authentication_string=password(‘jj’) where user=’root’

Источник

How to recover mysql root password in MacOS

I start my MAMP and try to connect to my MySQL server via localhost or 127.0.0.1 , I tried all the possible password that I think it is correct, but for some reasons it still didn’t like it, so I think I forgot it somehow. I need help.

Try #1

Enter all the possible passwords

MySQL -u root -h localhost -p

Try #2

Enter possible passwords

MySQL -u root -h localhost

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Try #3

ERROR! MySQL server PID file could not be found!

I’m trying to run this

Try #4

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Try #5

Close my MAMP application, and run this

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

I don’t what else to try anymore.

I can’t really set the root password because I can’t even get in.

This is the process of my MySQL

ps aux | grep MySQL

4 Answers 4

This is how you can do it in macOS Sierra, probably you install it via pkg and in the system preferences have an icon for MySQL, something like this:

If is up and running open a terminal and run this command:

That will help you to find the path of mysqld and the current command used to start the server, the output could be something like this:

Next step is to stop MySQL, you can do this by clicking on the System Preferences menu and click on the Stop MySQL Server button, Once is stoped within a terminal you will need to start again the server but with some extra options, something like this:

It is indeed the same command from the output of pgrep -fl mysql but in this case, you just need to add:

After doing that you will notice that the menu from the System Preferences is Green again and MySQL should be up and running, now you can try to login and change password:

Stop and start again MySQL from the System Preference panel and it should be working now with the new password.

Источник

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.

Источник