2 days ago I downloaded mysql-server with sudo apt-get install mysql-server
First, I opened the mysql server with mysql -u root -p
and for the password I pressed enter.
I created two users, one with some privileges for a database and the other with full privileges on the same database. After that I read that you need to run mysql_secure_installation
to set up some things so I did and I logged in with the password.
The very next day (yesterday), I tried to log in from both of the new user profiles. I did not have any trouble, but then... I tried to log in as root with the password unsuccessfully. I tried to troubleshoot it and recover it, but with no luck. Whatever I tried to do had some other error. Some examples that what I did:
First try safe mode
I've stopped the server (sudo service mysql stop
) and then I tried to open the server in safe mode step by step from confirmed answers mysqld_safe --skip-grant-tables &
But then I (obviously) got some error messages like error socket issue does not exist
or can't create something
. Now from other confirmed answer (post) I've restarted the mysql-server, I backed up the socket, I stopped once again the mysql-server and I renamed the socket to the original name:
sudo service mysql start
cd /var/run
sudo cp -rp ./mysqld ./mysqld.bak
sudo service mysql stop
sudo mv ./mysqld.bak ./mysqld
But then instead of following the steps of this post, I've returned to the original, so instead of running sudo mysqld_safe --skip-grant-tables --skip-networking &
, I chose to run sudo mysqld_safe --skip-grant-tables &
. But I got access to the server with mysql -u root
. So then I did once more step by step what I needed to do in order to reset the password but I don't remember what exactly... I know that I did something like that with success
update user set authentication_string='new_password' where user='root';
flush privilages;
So, I started the server but once more I couldn't get access (I could still connect with the other two users).
Second try re-install
Then I decided to re-install the mysql-server so that configuration files will remove.So I did the following commands
sudo apt-get remove --purge mysql*
sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql
sudo apt-get dist-upgrade
sudo apt-get install mysql-server
But it was asking once more for root password. I tried to run mysql_secure_installation
BUT it was also asking for root password... I tried to log in with one of the other two users and it was successful.
Third try
I've got very desperate, so I first did the same steps in second try (except of the last command) and I found a confirmed answer (I think, but I am not sure, could even be from other site) with the following command sudo find / -iname 'mysql*' -exec rm -rf {} \;
(I know that I should not do something like that and probably at least I should just rename them) but it only deleted 4 files. Then I installed the server and tried to log in and mysql_secure_installation
with no success but now, I couldn't even log in as one of the other users...
Fourth try
I found a confirmed answer from ubuntu forum and I followed the steps from mysql documentation (mysql doc) but when I tried to run the command mysqld --init-file=/home/me/mysql-init &
I've got once more some errors. I tried to fix the errors as I did in the previous steps, but they did not get fixed.
Finally
At this point I have uninstalled every mysql command from the second try and I took into consideration to re-install Ubuntu (or some other debian distro). I also did try to install mariadb, but I got the same problems.
Edit 1:
I have access to the server with the folloing command sudo mysql -u root
Edit 2 (solution):
Well, according to this post, some systems like Ubuntu, mysql use UNIX auth_socket plugin. I was able to solve my problem and this is the original post for future newbies