How to change the root password of mysql in Linux: mysqlroot

How to change the root password of mysql in Linux: mysqlroot

Preface

It may have been several months since the service was deployed in mysql. Because the current work is basically on the terminal, it is very rare to log on. Today, I want to modify something, suddenly I forgot my 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, as a result, I began my journey to climb the trap. I guess I will encounter it later. I will record it. Let's take 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 directly log on as the root user and reset the password. The procedure is as follows:

The MySQL service stops running:

 sudo service mysql stop

Start mysql in safe mode:

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

Log on directly with root without a password:

 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 Logon:

 mysql -u root -p

Exception Handling

An ERROR is reported when the password is changed. The ERROR 1054 (42S22) is displayed)

When changing the password, many documents on the Internet are prompted to enter the command to modify:

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

The following error is returned when you use this command:

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

The reason is that, starting from mysql 5.7, the password field is replaced with authentication_string. You can use the following command to modify

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

An ERROR occurred while logging on to mysql after changing the password. ERROR 2002 (HY000) is displayed)

After the password is changed and restarted, you cannot log on to mysql.

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

After searching, the feedback is obtained because I have installed Multiple mysql versions and can solve the problem by executing the following commands in sequence.

View the running mysql

 ps -A|grep mysql

Kill mysql running

 sudo pkill mysql

View the running mysqld

 ps -A|grep mysqld

Kill the mysqld

 sudo pkill mysqld

Restart mysql

 service mysql restart

Log on to mysql

 mysql -u root -p

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.