Detailed introduction to the sample code of how to change the root password of mysql under Linux

mysql is a database that we often need to use in linux or windows. I believe that every programmer should be familiar with mysql, but sometimes the brain is short-circuited. I suddenly forgot the password of mysql super user root. At this time, I need to change a new password. The following article introduces how to change the root password of mysql under Linux. Let’s take a look.

Preface

It should have been several months since the service was deployed on mysql, because most of the work now is in the terminal, so there are very few Log in, I want to modify something today, and suddenly I found that I had completely forgotten the mysql password. I crawled through the code and finally found the password for the business database, but the root password was still not found, and the permissions could not be changed, so I started to crawl through the pits. I estimate that I will encounter them again in the future, so I will compile and record them. Friends who need it can come and 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 log in directly as root and then reset the password. The following 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 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;

Exitmysql

 mysql > quit

Restart mysql

 sudo service mysql restart

Password login:

 mysql -u root -p

Exception handling

An error message is reported when changing the password, prompting ERROR 1054 (42S22)

When changing the password At that time, many documents on the Internet prompted to enter the command to modify:

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

The result was an error 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

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

After changing the password, login to mysql fails, prompting ERROR 2002 (HY000)

After changing the password After restarting, I couldn't log in when I logged in to mysql. It prompted

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

search and later feedback was because I installed multiple versions of mysql. Execute the following in sequence. command can be solved.

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

Log in to mysql

 mysql -u root -p

Summary

The above is the detailed content of Detailed introduction to the sample code of how to change the root password of mysql under Linux. For more information, please follow other related articles on the PHP Chinese website!