stop mysql

sudo service mysql stop

Start MySQL in safe mode

sudo mysqld_safe --skip-grant-tables

You may need to type Enter twice.

# mysqld_safe --skip-grant-tables
2017-07-25T07:39:45.596828Z mysqld_safe Logging to syslog.
2017-07-25T07:39:45.600267Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-07-25T07:39:45.621303Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

If you see the above (and don’t get the prompt back) it worked, just open a new termianl and log in. To avoid losing the prompt, you can run the previous command in the background by adding a & at the end, like: sudo mysqld_safe --skip-grant-tables &

Log into MySQL as root:

mysql -u root

Change to the mysql database, which handles the settings for MySQL itself:

USE mysql;

Update the password for the root user:

UPDATE user SET PASSWORD=PASSWORD("the new password you want to use") WHERE USER='root';

Refresh the MySQL user privileges:

FLUSH PRIVILEGES;

Exit MySQL:

exit

If this doesn’t work, you can try force the application to quit by pressing CTRL-C on your keyboard.

Troubleshooting

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@localhost:~# 2017-08-16T06:46:15.453699Z mysqld_safe Logging to syslog.
2017-08-16T06:46:15.458042Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-08-16T06:46:15.461782Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

To find all socket files on your system run:

sudo find / -type s

Check if the socket file exists

ls -al /var/run/mysqld/

if not, make one and set permissions

touch /var/run/mysqld/mysqld.sock
chmod 777 /var/run/mysqld/mysqld.sock

if you get while trying to start MySQL in safe mode

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

then make the directory

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

and re-run the command to start MySQL in safe mode.


If you get the following error while setting the password

ERROR 1146 (42S02): Table 'mysql.USER' doesn't exist

See if you’re not mixing title cases. It is mysql.user, not mysql.USER. The table names are case sensitive, don’t mess with them. (You can find out with the describe mysql.user; command)

If alphabet case is not the issue, your database may be corrupt. See if mysql.user exits

USE mysql;
SELECT * FROM user;

If these are missing you can try recreating the tables by running

mysql_install_db

(I got this error because i had set a 100 character long generated password while installing MySQL, and it wasn’t valid. The installation proceeded without giving an error. But checking the mysql.user table showed *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE. Since mine was a fresh installation, i just removed and reinstalled MySQL apt-get remove -y mysql-* && apt-get purge -y mysql-*)