My problem started off with me not being able to log in as root any more on my mysql install. I was attempting to run mysql without passwords turned on... but whenever I ran the command
# mysqld_safe --skip-grant-tables &I would never get the prompt back. I was trying to follow these instructions to recover the password.
The screen just looks like this:
root@jj-SFF-PC:/usr/bin# mysqld_safe --skip-grant-tables 120816 11:40:53 mysqld_safe Logging to syslog. 120816 11:40:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysqland I don't get a prompt to start typing the SQL commands to reset the password.
When I kill it by pressing CTRL + C, I get the following message:
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!If I retry the command and leave it long enough, I do get the following series of messages:
root@jj-SFF-PC:/run/mysqld# 120816 13:15:02 mysqld_safe Logging to syslog. 120816 13:15:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120816 13:16:42 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [1]+ Done mysqld_safe --skip-grant-tables root@jj-SFF-PC:/run/mysqld#But then if I try to log in as root by doing:
# mysql -u rootI get the following error message:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)I checked and /var/run/mysqld/mysqld.sock file doesn't not exist. The folder does, but not the file.
Also, I don't know if this helps or not, but I ran find / -name mysqld and it came up with:
/var/run/mysqld - folder /usr/sbin/mysqld - file /run/mysqld - folderI'm new to Linux and MySQL, so I don't know if this is normal or not. But I'm including this info just in case it helps.
I finally decided to uninstall and reinstall mysql.
apt-get remove mysql-server apt-get remove mysql-client apt-get remove mysql-common apt-get remove phpmyadminAfter reinstalling all packages again in the same order as above, during the phpmyadmin install, I got the same error:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)So I tried again to uninstall/reinstall. This time, after I uninstalled the packages, I also manually renamed all mysql files and directories to mysql.bad in their respective locations.
/var/lib/mysql /var/lib/mysql/mysql /var/log/mysql /usr/lib/perl5/DBD/mysql /usr/lib/perl5/auto/DBD/mysql /usr/lib/mysql /usr/bin/mysql /usr/share/mysql /usr/share/dbconfig-common/internal/mysql /etc/init.d/mysql /etc/apparmor.d/abstractions/mysql /etc/mysqlThen I tried to reinstall mysql-server and mysql-client again. But I've noticed that it doesn't prompt me for a password. Isn't it supposed to ask for an admin password?
After sleepless nights trying to find a solution to this problem, I have finally come to one. Thus, I’ve decided to share it and I know I’ll need it again in the future (It’s not the first time I install MySQL in a recently formatted Windows 10 computer and face this problem).
Note that I’m running Debian in a Linux Subsystem for Windows 10 and I suppose it might work with any subsystem.
Cleaning the Backyard
First of all, let’s start from scratch, you want to make sure you don’t have any remaining MySQL garbage in your system:
sudo apt-get remove --purge mysql*After that, check if everything is clean, run the following code, and hope nothing shows up
dpkg -l | grep mysqlIf that doesn’t do the trick, like in my case I still had some installed…
Try to purge them individually, like so (php is just an innocent amidst the sinful, so leave it be) :
sudo apt-get remove --purge mysql-apt-configAnd now, clean everything else
sudo rm -rf /etc/mysql /var/lib/mysqlsudo apt-get autoremove
sudo apt-get autoclean
Installing Mysql-server
Now, we can start from scratch and install it. First, we have to download our configuration file using (be sure to get the latest file, mine was 8.15–1. you can find them here //repo.mysql.com):
wget //repo.mysql.com/mysql-apt-config_0.8.15-1_all.debOnce it is done, install it using:
sudo dpkg -i mysql-apt-config_0.8.15-1_all.debIt will prompt you to choose a MySQL version, take mysql-5.7. I was taken by the greed of choosing 8.0, but it gave me more problems than I had wished for, then humbly get 5.7:
Now, we can properly install mysql-server:
sudo apt update && sudo apt install mysql-serverYou’ll need to enter a password…make sure you’ll remember it ;)
Secure Mysql Installation
Now, we want to change some default settings that might not be so secure, then run:
sudo mysql_secure_installationEnter root password (I’ve told you to remember it) and…
Here’s when my nightmares started and I stumbled with:
Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
I noticed then that by default this service is not started, we have to start it, but using sudo service mysql start or sudo service mysqld start won’t start it, neither will sudo systemctl start mysql. They will just give you a bunch of problems, you should:
sudo /etc/init.d/mysql startNow, we can again run:
sudo mysql_secure_installationThe settings I’ve used were:
Would you like to setup VALIDATE PASSWORD plugin? yPlease enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Change the password for root?: n
Remove anonymous users? y
Disallow root login remotely? n
Remove test database and access to it? y
Reload privilege tables now? y
Making Sure Everything is Set
Now, before trying to connect to mysql, make sure we have a root has an authentication string. Enter MySQL Monitor:
mysql -u root -pAnd then enter:
Make sure you see something this
Now, if the authentication string is empty, we have to set up a new password for root. Still, in MySQL Monitor, run:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';And then reload grant tables:
mysql> FLUSH PRIVILEGES;Now, check if our root user has an authentication_string:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;Using MySQL Workbench from Windows 10
I like connecting to the database from Windows itself, and once I have installed workbench in Windows (//dev.mysql.com/downloads/workbench/), it opens up without any connections.
Now we have to create a new connection, by clicking in the add button:
Add a name to the connection and leave the other settings as they are:
Now you can simply connect to the database in your Linux Subsystem from Windows.