Photo by Samuel Zeller on Unsplash

How to reset root user password in MySQL 8.0

Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Yes, fresh install.

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 successfully 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.

mysql -V
mysql Ver 8.0.xx for OS on x86_64 (YOUR OS)

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.

sudo apt update
sudo apt install mysql-server
systemctl status mysql

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.

sudo yum update
sudo yum install mysql-server mysql
chkconfig --list mysql  (or) service mysql status

MacOS

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

brew update
brew install mysql 

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

brew outdated
brew upgrade mysql

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%.

/usr/local/var/mysql/

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)

brew uninstall mysql 
brew cleanup 
brew update
brew install mysql 

Now we can check our installation by brew service.

brew services list 
Name    Status  User Plist
mysql   started user /Users/user/Library/LaunchAgents/hom....

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.

service mysql status
sudo mysqladmin -u root password NEWPASSWORD
service mysql restart

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

sudo service mysql stop
sudo service mysql start --skip-grant-tables & 
mysql -uroot
mysql>

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

brew services stop mysql

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.

mysqld_safe --skip-grant-tables &

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

$ mysql
Welcome to the MySQL monitor etc .....
......
mysql>
mysql> USE mysql;
Database changed

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.

mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';

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.

brew services restart mysql // Mac
service mysql restart // Linux
mysql -u root -p
Enter password: *******

Harden your MySQL server

sudo mysql_secure_installation

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.