Category Archives: mysql

Access denied for user ‘root’@’localhost’

In 5.6, you have to do it:

 sudo mysql -u root
 use mysql; 
 mysql> update user set plugin='' where User='root';

or

 mysql> update user set password=PASSWORD("SET_YOUR_PASSWORD") where user='root';
 mysql> flush privileges; 

In 5.7, you have to do it:

 sudo mysql -u root 
 use mysql;
 mysql> update user set plugin='mysql_native_password' where User='root';
 mysql> update user set password=PASSWORD("SET_YOUR_PASSWORD") where user='root';
 mysql> flush privileges;

Finally, restart the instance/daemon without the –skip-grant-tables option.

sudo /etc/init.d/mysql restart

You should now be able to connect with your new password.

mysql -u root -p

Enter password: my_password

UNINSTALL OR COMPLETELY REMOVE MYSQL FROM UBUNTU 18.04

Sometimes Mysql does not act as we expected for example after successful installation you cannot access MySQL as a root user. So for that kind of situation, we can remove and reinstall Mysql with some small changes on the root user and its privileges.

       sudo apt-get remove --purge mysql* 
       sudo apt-get purge mysql* 
       sudo apt-get autoclean 
       sudo apt-get remove dbconfig-mysql 
       sudo apt-get dist-upgrade 

    Now mysql is completly remove from your system So let’s Install mysql

       sudo apt-get install mysql-server 
       sudo mysql_secure_installation 

    If you face an issue to connect MySQL a root user then do the following steps below

       sudo mysql -u root -p 
       show databases; 
      result should be like this
      +——————–+
      | Database |
      +——————–+
      | information_schema |
      | mysql |
      | performance_schema |
      | sys |
      +——————–+
       use mysql; 
       UPDATE user SET plugin='mysql_native_password' WHERE User='root'; 
       UPDATE mysql.user set authentication_string=PASSWORD('rootpasswordgiven') where user='root'; 
       FLUSH PRIVILEGES; 
       QUIT