Category: Tech-Notes

Source: https://stackoverflow.com/questions/41984956/cant-reset-root-password-with-skip-grant-tables-on-ubuntu-16

The mysql.sock is deleted when the mysql service is stopped and mysqld_safe can’t create it. A solution is to back up the sock folder and restore before starting mysqld_safe

Start server

$ sudo service mysql start

Go to sock folder

$ cd /var/run

Back up the sock

$ sudo cp -rp ./mysqld ./mysqld.bak

Stop server

$ sudo service mysql stop

Restore the sock

$ sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell

mysql -u root

Change password

FLUSH PRIVILEGES;

SET PASSWORD FOR root@'localhost' = PASSWORD('my_new_password');

Source: https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04?

I just installed MySQL and/or mariadb on Ubuntu and ran mysql_secure_installation and changed the root password. Now when I try to login to mysql using the root account while logged in Ubuntu as normal user account I get access denied.

First, connect in sudo mysql

sudo mysql -u root
Check your accounts present in your db

SELECT User,Host FROM mysql.user;
+——————+———–+
| User | Host |
+——————+———–+
| admin | localhost |
| debian-sys-maint | localhost |
| magento_user | localhost |
| mysql.sys | localhost |
| root | localhost |
Delete current root@localhost account

mysql> DROP USER ‘root’@’localhost’;
Query OK, 0 rows affected (0,00 sec)
Recreate your user

mysql> CREATE USER ‘root’@’%’ IDENTIFIED BY ”;
Query OK, 0 rows affected (0,00 sec)
Give permissions to your user (don’t forget to flush privileges)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Exit MySQL and try to reconnect without sudo.