Lets go through the install process:
#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 to 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
mysqladmin -u root password NEWPASSWORD
BANG – !! This gives the error !!
What’s happening?
If you install 5.7 and don’t provide a password for the root user, MySQL will use the auth_socket plugin. That plugin 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';
FLUSH PRIVILEGES;
quit;
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.
This post is also available as a “Gist”. You can find the gist here: https://gist.github.com/jlavelle-uk/b61a7be76976939aec16799f9eea746f