Fixing can’t connect to local MySQL server through socket error in Ubuntu 20.04 while installing MySQL 8

While installing MySQL in Ubuntu 20.04, I ended up with the issue “Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ ” and also “ERROR 1045 (28000): Access denied for user ‘root’@’localhost'”.

Although MySQL 8.0.27 in Ubuntu 20.04 worked without any issue in a VirtualBox VM and LXC container, it failed with the issues mentioned above, in the cloud environment that I am using to host the blogs.

From the error message, it is evident that the root cause of the issue was related to permission for the “root” account of MySQL. So I checked the permission of the folders at the location “/var/run/mysqld/” and “/run/mysqld/“. Everything looked fine, but still had issues when I executed the command “mysql” or “mysql -u root -p“, with or without “sudo” in the terminal.

Installing MySQL Server

Let’s start with installing MySQL. If you have already installed MySQL and struggling to access it, let’s remove it completely.

To uninstall and remove MySQL, execute the below commands in the terminal.

# Uninstall all MySQL packages
sudo apt remove --purge mysql-*

# Remove config file
sudo rm -rf /etc/mysql /var/lib/mysql 

#Package clean up
sudo apt autoremove
sudo apt autoclean

To install MySQL, execute the below commands in the terminal.

sudo apt update
sudo apt upgrade

#Install MySQL server
sudo apt install mysql-server

# You may encounter an error in the below command
sudo mysql_secure_installation

This would install MySQL with all its dependencies. If you are able to pass through “mysql_secure_installation” without any issue, you are all good and you no need to worry about the steps explained below. If you encounter any issue on executing the command “mysql_secure_installation” or “sudo mysql“, then you may have to follow the below steps.

Fixing “Can’t connect to local MySQL”

To fix the MySQL socket issue and access denied error for root@localhost, follow the below steps.

1. Stop the MySQL server by executing the command “sudo service mysql stop“.
2. Create socket location as a placeholder by executing the command “sudo mkdir -p /var/run/mysqld“.
3. Change the permission of the folder by “sudo chown mysql:mysql /var/run/mysqld“.
4. Start the MySQL server with additional attributes by executing the command “/usr/sbin/mysqld --skip-grant-tables --skip-networking &“. Ensure that the command ends with “&“. That terminates the input args list.
5. Now execute the command “sudo mysql” in terminal. If you are not able to connect to the MySQL prompt now, you may have to restart the Ubuntu server and perform the steps again after completely removing MySQL.
6. Execute the below command in MySQL prompt to change the password of MySQL root account.

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword';
FLUSH PRIVILEGES;
EXIT;

After exiting from the MySQL prompt, execute the commands “sudo service mysql stop” and “sudo service mysql start” in the terminal to stop and start the MySQL.

After restarting MySQL, execute the command “sudo mysql -u root -p“, and provide the password that you have set in the above step. If all goes well, by now you would be able to login into the MySQL environment.

To view the list of user accounts associated with the running MySQL server, execute the SQL Query “select User,Host,Plugin from mysql.user;” in MySQL prompt. This would return all user accounts and their associated authentication method.