Notes

Reset MySQL root password on Linux

Edit on GitHub

Databases
4 minutes

tl;dr

 1sudo service mysql stop
 2
 3# get rid of can't connect errors
 4# by creating socket file
 5sudo mkdir -p /var/run/mysqld
 6sudo chown mysql:mysql /var/run/mysqld
 7sudo touch /var/run/mysqld/mysqld.sock
 8sudo chmod 777 /var/run/mysqld/mysqld.sock
 9
10# run mysql in safe mode
11sudo mysqld_safe --skip-grant-tables &
12
13# connect
14mysql -u root
1-- MySQL commands
2UPDATE mysql.user SET authentication_string=PASSWORD('1256fhsad12') WHERE user='root';
3UPDATE mysql.user SET plugin="mysql_native_password" WHERE user='root';
4FLUSH PRIVILEGES;
5exit;
1-- MySQL 8 commands
2ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
3-- UPDATE mysql.user SET plugin="mysql_native_password" WHERE user='root';
4FLUSH PRIVILEGES;
5exit;

stop mysql

1sudo service mysql stop

Start MySQL in safe mode

1sudo 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:

1mysql -u root

The mysql database handles the settings for MySQL itself. Update the password for the root user

1--UPDATE user SET PASSWORD=PASSWORD("the new password you want to use") WHERE USER='root';
2
3-- MySQL 5.7 now uses `authentication_string` instead of `password`
4UPDATE mysql.user SET authentication_string=PASSWORD('XXXX') WHERE user = 'root';

Refresh the MySQL user privileges:

1FLUSH PRIVILEGES;

Exit MySQL:

1exit

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

You can also try resetting with the following command

1sudo dpkg-reconfigure mysql-server-*

where * is the version you have installed. You can find out the version with mysql --version

On macOS you can try the following:

1# macOS
2mysqld --skip-grant-tables  # this will run mysql 
3
4mysql -u root
5mysql> FLUSH PRIVILEDGES;
6mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Root user login and PhpMyAdmin

1-- Create a new super user for phpMyAdmin
2CREATE USER 'pmauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'tyuY12HPEBsY04Y12HPEf3Sx';
3GRANT ALL PRIVILEGES ON *.* TO 'pmauser'@'localhost';
4FLUSH PRIVILEGES;

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:

1sudo find / -type s

Check if the socket file exists

1ls -al /var/run/mysqld/

if not, make one and set permissions

1touch /var/run/mysqld/mysqld.sock
2chmod 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

1mkdir -p /var/run/mysqld
2chown 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

1USE mysql;
2SELECT * FROM user;

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

1mysql_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-*)


ERROR 1054 (42S22): Unknown column 'password' in 'field list'

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is authentication_string. The query will become

1update user set authentication_string=password('XXXX') where user='root';

 #1524 - Plugin 'auth_socket' is not loaded

Load the mysql_native_password plugin when resetting the password

1UPDATE mysql.user SET plugin="mysql_native_password" WHERE user='root';