How to modify the root password of mysql under Linux

Preface

The service has been deployed on MySQL for several months. Because my work is basically done in the terminal, I rarely log in. Today, I wanted to modify something, and suddenly I found that I had completely forgotten the MySQL password. I finally found the password of the business database in the code, but the root password was still not found, and the permissions could not be changed. So I started my journey of climbing pits. I guess I will encounter them again in the future, so I will organize and record them. Friends in need, come and have a look.

System Parameters

server

 $ cat /proc/version
 Linux version 4.8.3-x86_64-linode76 (maker@build) (gcc version 4.7.2 (Debian 4.7.2-5) ) #1 SMP Thu Oct 20 19:05:39 EDT 2016
 $ lsb_release -a
 No LSB modules are available.
 Distributor ID: Ubuntu
 Description: Ubuntu 16.04.1 LTS
 Release: 16.04
 Codename: xenial

mysql

 mysql> show variables like "%version%";
 +-------------------------+-------------------------+
 | Variable_name | Value |
 +-------------------------+-------------------------+
 | innodb_version | 5.7.16 |
 | protocol_version | 10 |
 | slave_type_conversions | |
 | tls_version | TLSv1,TLSv1.1 |
 | version | 5.7.16-0ubuntu0.16.04.1 |
 | version_comment | (Ubuntu) |
 | version_compile_machine | x86_64 |
 | version_compile_os | Linux |
 +-------------------------+-------------------------+

Solution

Start mysql in safe mode, you can log in directly as root, and then reset the password. Here are the specific steps

Stop the running MySQL service:

 sudo service mysql stop

Start mysql in safe mode:

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

Log in directly as root, no password required:

 mysql -u root

Reset Password:

 mysql> use mysql;
 mysql> update user set authentication_string=password('password') where user='root';
 mysql> flush privileges;

Exit mysql

 mysql > quit

Restart mysql

 sudo service mysql restart

Password login:

 mysql -u root -p

Exception handling

Password modification error message, ERROR 1054 (42S22)

When changing the password, many documents on the Internet prompt you to enter the following command to change it:

 mysql> update user set password=PASSWORD("password") where User='root';

As a result, an error occurs when using this command:

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

The reason is that starting from MySQL 5.7, the password field has been replaced by authentication_string. You can use the following command to modify it

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

After changing the password, logging into MySQL fails, and ERROR 2002 (HY000) is displayed

After changing the password and restarting, I couldn't log in to MySQL, and it said

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

After searching, I found out that it was because I had installed multiple versions of MySQL. The problem can be solved by executing the following commands in sequence.

View the running mysql

 ps -A | grep mysql

Kill the running mysql

 sudo pkill mysql

View the running mysqld

 ps -A|grep mysqld

Kill the running mysqld

 sudo pkill mysqld

Restart mysql

 service mysql restart

Login to mysql

 mysql -u root -p

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.