How to Change MySQL Root Password on Mac

1. Install XAMPP for OSX
2. Confirm the root User Has No Password
3. Change the root User Password Using an SQL ALTER Statement
4. Log in With Your New root User Password

This article teaches you how to change the MySQL root user password on OSX. We’ll use XAMPP so that you can change the password using the MySQL console.

Install XAMPP for OSX

First, download and install XAMPP for OSX from Apache Friends. Once XAMPP is installed, use your terminal to access the XAMPP installation directory.

Afterward, login to the MySQL using the following:

mysql -u root -p

As it stands, the root user has no password. As a result, the previous command will log you into MySQL without a password prompt.

But before we change the password, let’s confirm the root has no password.

Confirm the root User Has No Password

To confirm the root user has no password, switch to the mysql database using the following:

USE mysql;

The mysql database has many tables, but our interest is the user table. The user table has the User and the Password columns.

The latter contains the password for the user. So, use the following to check the password for the user root:

SELECT User, authentication_string from user;

Output (if root has no password):

+------------------+------------------------------------------------------------------------+
| User             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| debian-sys-maint | $A$005$Wv1MO|Uh1gezb+wKL5oU1hvgAp90tnMa9fTMbPNZtGAFSYC6dgziVVPAd0 |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             |                                                                        |
+------------------+------------------------------------------------------------------------+
5 rows in set (0.01 sec)

If the root user has no password, your output should be the same as the one above. Now, let’s change the password.

Change the root User Password Using an SQL ALTER Statement

To change the root user password, you’ll use an SQL ALTER statement to assign a new password. So, the following will change the root password to DelftStack:

ALTER USER root@localhost IDENTIFIED BY 'DelftStack'

Output:

Query OK, 0 rows affected (0.021 sec)

Now, confirm that root has a password:

SELECT User, authentication_string from user;

Output (your password will differ):

+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| root | *D064C3894639CE84CBA931173B3A55263B736A7B |
| root |                                           |
| root |                                           |
| pma  |                                           |
+------+-------------------------------------------+
4 rows in set (0.001 sec)

To ensure the password change takes effect the next time you log in, flush the privileges:

FLUSH PRIVILEGES;

Log in With Your New root User Password

With the root user having a password, you can log in with the password using the following:

mysql -u root -p

Upon executing the previous command, MySQL will ask you to enter your password. You get logged in if you enter the correct password; otherwise, you’ll receive an Access denied message. If that happens, double-check your password and try again.