Home > Database > Mysql Tutorial > Detailed introduction to the sample code of how to change the root password of mysql under Linux

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

黄舟
Release: 2017-03-16 13:23:44
Original
1255 people have browsed it

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
Copy after login

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   |
 +-------------------------+-------------------------+
Copy after login

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
Copy after login

Start mysql in safe mode:

 sudo mysqld_safe --skip-grant-tables --skip-networking &
Copy after login

Log in directly with root without a password:

 mysql -u root
Copy after login

Reset password:

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

Exitmysql

 mysql > quit
Copy after login

Restart mysql

 sudo service mysql restart
Copy after login

Password login:

 mysql -u root -p
Copy after login
Copy after login

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';
Copy after login

The result was an error when using this command:

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

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';
Copy after login

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)
Copy after login

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
Copy after login

kill the running mysql

 sudo pkill mysql
Copy after login

View the running mysqld

 ps -A|grep mysqld
Copy after login

kill the running mysqld

 sudo pkill mysqld
Copy after login

Restart mysql

 service mysql restart
Copy after login

Log in to mysql

 mysql -u root -p
Copy after login
Copy after login

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!

Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template