Skip to content

Instantly share code, notes, and snippets.

Last active January 1, 2020 20:32
Show Gist options
  • Save jlavelle-uk/b61a7be76976939aec16799f9eea746f to your computer and use it in GitHub Desktop.
Save jlavelle-uk/b61a7be76976939aec16799f9eea746f to your computer and use it in GitHub Desktop.
Fresh MYSQL install, Access denied for user 'root'@'localhost'

Fresh MYSQL install, Access denied for user 'root'@'localhost'

So how to fix it?

Lets go through the install process first:

install a complete LAMP Stack first then install phpmyadmin (note the ^ is important)

sudo apt-get install lamp_stack^

Enable Apache2 to start on boot

sudo systemctl enable apache2

Start Apache2 if it isn't started

sudo systemctl start apache2

Add yourself the the www-data Group and take ownership of the /var/www directory

sudo usermod -a -G www-data $USER

sudo chown -R $USER:www-data /var/www/

Install phpmyadmin

sudo apt-get install phpmyadmin javascript-common

Set root password for MySQL change NEWPASSWORD to your choice of password - This gives the error

mysqladmin -u root password NEWPASSWORD

What's happening?

If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username. If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again.

Right, let's fix it!

You can't fix it if you can't login. So to login we need to find an administrator password. Luckily there is one!

Locate and copy the Debian System Maintenance password in this file

sudo xed /etc/mysql/debian.cnf

Now enter the following in terminal - you will be prompted for a password. Enter the password you just copied from /etc/mysql/debian.cnf above

mysql -u debian-sys-maint -p

Bingo, you're in with full Administrator rights!

At the mysql> prompts enter the following commands pressing enter after each one - change NEWPASSWORD to a password of your choice

use mysql;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';



That's it! Fixed. To test it:

Login with the following command, you'll be prompted for your newly created password

mysql -u root -p

To exit mysql> enter quit;

Now secure MySQL

sudo /usr/bin/mysql_secure_installation

I hope you found that useful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment