I am new to MySQL and I am having trouble installing it for a few days now. I was running MySQL and these are the things that I did.
mysql -u root -p
and entered my root password, then I created a new user using
mysql>GRANT ALL PRIVILEGES ON *.* TO 'ananyapam'@'localhost' IDENTIFIED BY 'password';
mysql>\q
Entered using the new user
mysql -u ananyapam -p
(Succesful!) Then tried deleting the new user ananyapam using
DELETE FROM mysql.user WHERE user = 'ananyapam';
Now when I listed my users and there was no user ananyapam. However, now when I try to create the user ananyapam again using
mysql>GRANT ALL PRIVILEGES ON *.* TO 'ananyapam'@'localhost' IDENTIFIED BY 'password';
it returns an error message. Not understanding much after a while of searching, I thought of removing and then reinstalling it using the commands below:
sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean
I then tried to reinstall MySQL using this
sudo apt install mysql-server
sudo mysql_secure_installation
it returns error
ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
This link might be useful for someone:Access denied for user 'debian-sys-maint' - installing mysql?
Edit: @Melebius suggested the other answer but after
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *477880DFF7642ABC170F60991A43E7FFB8B000E4 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
it returns an error message
mysql> CREATE USER 'debian-sys-maint'@'localhost' IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'debian-sys-maint'@'localhost'
Any help is appreciated.
GRANT ALL PRIVILEGES
…” This is not how a user is created. “I thought of removing and then reinstalling it using the commands below” What was the output of those commands? – Melebius Sep 09 '20 at 13:08