Setting the MySQL root user password on OS X
MysqlMacosPasswordsMysql Problem Overview
I just installed MySQL on Mac OS X. The next step was setting the root user password, so I did this next:
-
Launch the terminal app to access the Unix command line.
-
Under the Unix prompt I executed these commands:
cd /usr/local/mysql/bin ./mysqladmin -u root password 'password'
But, when I execute the command
./mysql -u root
, this is the answer:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 224
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
I can get into the mysql
command line without any password!
Why is this?
Mysql Solutions
Solution 1 - Mysql
Try the command FLUSH PRIVILEGES
when you log into the MySQL terminal. If that doesn't work, try the following set of commands while in the MySQL terminal
mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD("NEWPASSWORD") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
Change out NEWPASSWORD with whatever password you want. Should be all set!
Update: As of MySQL 5.7, the password
field has been renamed authentication_string
. When changing the password, use the following query to change the password. All other commands remain the same:
mysql> UPDATE user SET authentication_string=PASSWORD("NEWPASSWORD") WHERE User='root';
for MySQL 8.0+ Don't use
mysql> UPDATE mysql.user SET authentication_string='password' WHERE User='root';
as it overwrites the authentication_string
, which is supposed to be a hash and not plain text, instead use:
mysql> `ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';`
Solution 2 - Mysql
If you don't remember the password you set for root and need to reset it, follow these steps:
-
Stop the mysqld server, this varies per install
-
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:
UPDATE mysql.user SET Password=PASSWORD('your-password') WHERE User='root';
For MySQL 5.7+ use:
USE mysql;
UPDATE mysql.user SET authentication_string=PASSWORD("your-password") WHERE User='root';
Refresh and quit:
FLUSH PRIVILEGES;
\q
-
Stop the safe mode server and start your regular server back. The new password should work now. It worked like a charm for me :)
Note
Run
UPDATE mysql.user SET authentication_string=null WHERE User='root';
if you don't want to set a password for root user. Or if PASSWORD()
function doesn't work for you.
Solution 3 - Mysql
Once you've installed MySQL, you'll need to establish the "root" password. If you don't establish a root password, then, well, there is no root password, and you don't need a password to log in.
So, that being said, you need to establish a root password.
Using terminal enter the following:
Installation: Set root user password:
/usr/local/mysql/bin/mysqladmin -u root password NEW_PASSWORD_HERE
If you've made a mistake, or need to change the root password use the following:
Change root password:
cd /usr/local/mysql/bin/
./mysql -u root -p
> Enter password: [type old password invisibly]
use mysql;
update user set password=PASSWORD("NEW_PASSWORD_HERE") where User='root';
flush privileges;
quit
Solution 4 - Mysql
The instructions provided in the mysql website is so clear, than the above mentioned
$ sudo /usr/local/mysql/support-files/mysql.server stop
$ sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
/usr/local/mysql/bin/mysql
- mysql> FLUSH PRIVILEGES;
- mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
- mysql>
exit
or Ctrl + z $ sudo /usr/local/mysql/support-files/mysql.server stop
$ sudo /usr/local/mysql/support-files/mysql.server start
/usr/local/mysql/support-files/mysql -u root -p
- Enter the new password i.e MyNewPass
Reference: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
Solution 5 - Mysql
- Stop the mysqld server.
- Mac OS X: System Preferences → MySQL → Stop MySQL Server
- Linux (From Terminal):
sudo systemctl stop mysqld.service
-
Start the server in safe mode with privilege bypass
- From Terminal:
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
- From Terminal:
-
In a new terminal window:
sudo /usr/local/mysql/bin/mysql -u root
-
This will open the MySQL command-line client. From here enter:
-
UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';
-
FLUSH PRIVILEGES;
-
quit
-
-
Stop the mysqld server again and restart it in normal mode.
- Mac OS X (From Terminal):
sudo /usr/local/mysql/support-files/mysql.server restart
- Linux Terminal:
sudo systemctl restart mysqld
- Mac OS X (From Terminal):
Solution 6 - Mysql
For the new MySQL 5.7, for some reason the binary commands of MySQL aren't attached to the shell, and you have to do:
-
Restart the Mac after the installation.
-
Start MySQL:
System Preferences → MySQL → Start button
-
Go to MySQL install folder in the terminal:
cd /usr/local/mysql/bin/
-
Access to MySQL:
./mysql -u root -p
And enter the initial password given to the installation.
-
In the MySQL client, change the password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';
Solution 7 - Mysql
In the terminal, write mysql -u root -p
and hit Return.
Enter the current MySQL password that you must have noted down.
And set the password:
SET PASSWORD = PASSWORD('new_password');
Please refer to this documentation here for more details.
Solution 8 - Mysql
If you have forgot the MySQL root password, can’t remember or want to break in….. you can reset the MySQL database password from the command line in either Linux or OS X as long as you know the root user password of the box you are on:
(1) Stop MySQL
sudo /usr/local/mysql/support-files/mysql.server stop
(2) Start it in safe mode:
sudo mysqld_safe --skip-grant-tables
(3) This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:
mysql -u root
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
In the UPDATE command above just replace the 'password' with your own new password, make sure to keep the quotation marks
(4) Save and quite
FLUSH PRIVILEGES;
\q
(5) Start MySQL
sudo /usr/local/mysql/support-files/mysql.server start
Solution 9 - Mysql
I solved this by:
- Shutting down my MySQL server:
mysql.server stop
- Running MySQL in safe mode:
mysqld_safe --skip-grant-tables
- In another terminal, login with
mysql -u root
- In the same terminal, run
UPDATE mysql.user SET authentication_string=null WHERE User='root';
, thenFLUSH PRIVILEGES;
and then exit withexit;
- Stop the safe mode server with
mysql.server stop
and then start the normal one;mysql.server start
Now you can set your new password with
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
Solution 10 - Mysql
None of the previous comments solved the issue on my Mac.
I used the commands below and it worked.
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
Solution 11 - Mysql
When I installed OS X v10.10 (Yosemite), I got a problem with MySQL. I tried lot of methods, but none worked. I actually found a quite easy way. Try this out.
-
First log in to a terminal from super user (su) privileges.
sudo su
-
Stop MySQL
sudo /usr/local/mysql/support-files/mysql.server stop
-
Start in safe mode:
sudo mysqld_safe --skip-grant-tables
-
Open another terminal, log in as su privileges, and then, log in to the MySQL client (
mysql
) without a passwordmysql -u root
-
Change the password
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
-
Flush privileges
FLUSH PRIVILEGES;
-
You are done now.
Solution 12 - Mysql
The methods mentioned in existing answers don't work for MySQL 5.7.6 or later. According the MySQL documentation, this is the recommended way.
B.5.3.2.3 Resetting the Root Password: Generic Instructions
MySQL 5.7.6 and later:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Reference: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
Solution 13 - Mysql
If you can't remember your password, @radtek's answer worked for me except in my case I had set up MySQL using brew which meant that steps 1 and 2 of his answer had to be changed to:
-
/usr/local/bin/mysql.server stop
-
/usr/local/bin/mysqld_safe --skip-grant-tables
Note: the lack of sudo
.
Solution 14 - Mysql
This is what exactly worked for me:
-
Make sure no other MySQL process is running. To check this do the following:
-
From the terminal, run this command:
lsof -i:3306
If any PID is returned, kill it using
kill -9 PID
-
Go to System Preferences → MySQL → check if any MySQL instances are running, stop them.
-
-
Start MySQL with the command:
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
-
The password for every user is stored in the mysql.user table under columns User and authentication_string respectively. We can update the table as:
UPDATE mysql.user SET authentication_string='your_password' where User='root'
Solution 15 - Mysql
I think this should work:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURNEWPASSWORD'
(Note that you should probably replace root with your username if it isn't root.)
Solution 16 - Mysql
Stopping MySQL Server
sudo /usr/local/mysql/support-files/mysql.server stop
Starting MySQL in safe mode
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
Changing the root password
/usr/local/mysql/bin/mysql -u root
use mysql;
UPDATE user SET authentication_string=PASSWORD('NEW_PASSWORD') WHERE user='root';
FLUSH PRIVILEGES;
exit
Testing
Run /usr/local/mysql/bin/mysql -u root
Now enter the new password to start using MySQL.
Solution 17 - Mysql
macOS v10.14 (Mojave) and later with 5.7.26 installed from the Mac OS X DMG installer.
When attempting to use the UPDATE command posted by other users, it results in the following error:
> ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Copy the password that was presented to you by the installer, open a terminal, and do the following:
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURPASSWORDHERE';
Solution 18 - Mysql
Try this in a terminal:
/usr/local/bin/mysql_secure_installation
Solution 19 - Mysql
This workaround works on my laptop!
Mac with macOS v10.14.5 (Mojave).
MySQL 8.0.17 was installed with Homebrew.
-
I run the following command to locate the path of MySQL
brew info mysql
-
Once the path is known, I run this:
/usr/local/Cellar/mysql/8.0.17/bin/mysqld_safe --skip-grant-table
-
In another terminal I run:
mysql -u root
-
Inside that terminal, I changed the root password using:
update mysql.user set authentication_string='NewPassword' where user='root';
-
and to finish I run:
FLUSH PRIVILEGES;
And voilà, the password was reset.
Solution 20 - Mysql
To reference 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';
Solution 21 - Mysql
You can manually turn-off MySQL on Mac, by clicking on Apple menu and open System Preferences. Choose the “MySQL” preference panel, and then click on the “Stop MySQL Server” button to stop MySQL Server on Mac.
After you stop your MySQL, you'll need to follow these steps.
-
You'll need to start MySQL in skip-grant-tables mode
sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
-
In your terminal itself, enter this command to flush existing privileges
/usr/local/mysql/bin/mysql mysql> FLUSH PRIVILEGES;
-
Now you need to alter the user password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; mysql> exit
Then you can go to Apple menu and open System Preferences. Choose the “MySQL” preference panel, then click on the “Stop MySQL Server” button to stop MySQL Server on Mac.
Finally you can again go to Apple menu and open System Preferences. Choose the “MySQL” preference panel, then click on the “Start MySQL Server” button to start MySQL Server on Mac.
Solution 22 - Mysql
If you forgot your password or want to change it to your MySQL:
-
Start your terminal and enter:
sudo su
-
Enter the password for you system
-
Stop your MySQL server:
sudo /usr/local/mysql/support-files/mysql.server stop
-
Leave this window open, run second terminal window and enter here:
mysql -u root
-
And change your password for MySQL:
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
where "new_password" - your new password. You don't need old password for MySQL.
-
Flush, quit and check your new password:
FLUSH PRIVILEGES;
-
Close all windows and check your new password for MySQL.
Solution 23 - Mysql
Much has changed for MySQL 8. I've found the following modification of the MySQL 8.0 "How to Reset the Root Password" documentation works with Mac OS X.
Create a temporary file, $HOME/mysql.root.txt
, with the SQL to update the root password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<new-password>';
This uses mysql_native_password
to avoid the Authentication plugin 'caching_sha2_password' cannot be loaded error, which I get if I omit the option.
Stop the server, start with an --init-file
option to set the root password, and then restart the server:
mysql.server stop
mysql.server start --init-file=$HOME/mysql.root.txt
mysql.server stop
mysql.server start
Solution 24 - Mysql
For MySQL 8
- Shutdown MySQL server
- Go to System Preferences -> MySQL
- Click
Stop MySQL Server
button
-
Open two terminal [command-line] windows
-
In the first terminal window run the following:
mysqld_safe --skip-grant-tables
- In the second terminal window do the following:
4.1. Login to MySQL
mysql -u root
4.2. Run the following in the MySQL prompt:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';
4.3. Exit MySQL
exit;
- Go back to the first terminal window and shutdown
mysqld_safe
5.1. Press CTRL + Z
5.2. Run the following command
mysqladmin -u root -p shutdown
5.3. Enter the new password you set in 4.2.
when prompted.
- Start MySQL Server [see
1.
]
Solution 25 - Mysql
mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string='yourpasswd' WHERE User='root';
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
I somehow need to do this every time my MacBook restarts.