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';
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;
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';