07 October 2021
If you have never assigned a root password for MySQL, the server does not require a password at all for connecting as root. However, this is insecure. It’s highly recommended that you set a password.
To set up a root password for the first time, there are two ways to go about it:
Use the mysql_secure_installation command. This command will ask for both the old and new MySQL root password and will also conduct some other security settings, including disabling the test database.
Here is how:
Launch Terminal and type the command: mysql_secure_installation
Answer the questions as shown below:
Change the root password? [Y/n] <-- y
New password: <-- Enter a new MySQL root password
Re-enter new password: <-- Repeat the MySQL root password
Remove anonymous users? [Y/n] <-- y
Disallow root login remotely? [Y/n] <-- y
Remove test database and access to it? [Y/n] <-- y
Reload privilege tables now? [Y/n] <-- y
P.S. The most common reason for this error is an empty password. You have to specify the root password while connecting to the Mysql database:
mysqladmin -u root -pROOT-PASSWORD.
2. Use the mysqladmin command at the shell prompt as follows:
Open Terminal and type the following command: mysqladmin -u root password newpass
Note that If you see the following:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
You need to follow the instructions below on how to recover your MySQL password.
By the way, the word 'password' in the above example is part of the command, so don’t replace it with your password. Enter the new password in the 'newpass' section.
Okay, it’s been a while since you have installed MySQL on your Mac and set the root user password. You can easily forget the MySQL root password after some time. That’s your case, right? No worries, if you have forgotten the MySQL root password, can’t remember or want to break in, you can easily reset your MySQL database password from the command line as long as you know the root user password of the box you are on.
Resetting the MySQL password is not difficult, yet it varies from the version you are using.
So, this is how to reset the MySQL password on a Mac:
Stop the MySQL Server. To do this, you need to go to System Preferences and choose MySQL. Then, select Stop MySQL Server.
Tip: By the way, you can also use Terminal to stop the MySQL Server. You need to type in the Terminal the following command:
service mysql stop
You will get the following output correspondingly:
or Stopping MySQL database server: mysqld.
2. Start the server in safe mode with privilege bypass: In the Terminal, type the following command:
sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables
You will see the following:
Connect to the MySQL server using the MySQL client: in a new terminal window and type the following command (ensure you type it line by line):
mysql -u root
The output is the following:
4. Set a new MySQL root user password:
The next command depends on your MySQL version
For MySQL 5.7.5 and earlier
MySQL 5.7.6 and newer
5. Stop the MySQL server: use this command to stop MySQL:
Finally, start the MySQL server again and test it:
MySQL can be used without a password, but for security reasons, it’s better to set the root password. Don’t worry, if you happen to forget that password, it’s not that difficult to recover MySQL root password on your Mac. Simply use this guide.