Shulou(Shulou.com)06/01 Report--

Two methods of resetting password for single instance of MySQL

In our work and study, we sometimes forget the password of the database. Here are the steps to reset the password of a single instance of MySQL.

Description:

(1) [root@mysql1 ~] # cat / etc/redhat-release

CentOS release 6.7 (Final)

(2) [root@mysql1] # mysql-version

Mysql Ver 14.14 Distrib 5.7.13, for Linux (i686) using EditLine wrapper

The main steps are as follows:

Stop MySQL first

[root@mysql1 ~] # / etc/init.d/mysqld stop

/ etc/init.d/mysqld: line 46: / usr/local/mysql: is a directory

/ etc/init.d/mysqld: line 47: / usr/local/mysql/data: is a directory

Shutting down MySQL. SUCCESS!

View the status of MySQL:

[root@mysql1 ~] # / etc/init.d/mysqld status

/ etc/init.d/mysqld: line 46: / usr/local/mysql: is a directory

/ etc/init.d/mysqld: line 47: / usr/local/mysql/data: is a directory

ERROR! MySQL is not running

View the progress of MySQL:

[root@mysql1 ~] # ps aux | grep mysql | grep-v grep

Use-- skip-grant-tables to enable MySQL to ignore login authorization verification

[root@mysql1] # mysqld_safe-- skip-grant-tables-- user=mysql &

[1] 6559

[root@mysql1] # 2017-07-30T14:23:38.600285Z mysqld_safe Logging to'/ usr/local/mysql/data/mysqld.err'.

2017-07-30T14:23:38.640326Z mysqld_safe Starting mysqld daemon with databases from / usr/local/mysql/data

No password required to log in to MySQL

[root@mysql1 ~] # mysql

Reset root password

Description: newly installed MySQL5.7, login prompt password error, installation did not change the password, later through password-free login to change the password, enter update mysql.user set password=password ('wtf123') where user='root' and host='localhost' prompt ERROR 1054 (42S22): Unknown column' password' in 'field list', is originally under the mysql5.7 database does not have the password field, password field changed to authentication_string.

Mysql > update mysql.user set authentication_string=password ('wtf123') where user='root'and host='localhost'

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

Refresh: mysql > flush privileges

Exit: mysql > quit

Description: set password=password ('wtf1234') cannot be used

Restart the service and log in again

[root@mysql1 ~] # / etc/init.d/mysqld restart

[root@mysql1] # mysql-uroot-pwtf123

Description: view database password command:

Mysql > select user,host,authentication_string from mysql.user

Extension: reset the mysql password by modifying the / etc/my.cnf configuration file

1. Open the configuration file for mysql, and command: vim / etc/my.cnf. Add a new line of skip-grant-tables in the configuration file, and the result is as follows:

two。 Save and exit!

3. Restart mysqld, command: service mysqld restart

4. No password required to log in to MySQL

[root@mysql1 ~] # mysql

Mysql > update mysql.user set authentication_string=password ('123456') where user='root'and host='localhost'

Flush privileges; # Refresh permissions

Exit: quit

5. Restore my.cnf and restart after exiting. The command is as follows:

Vim / etc/my.cnf # Open the mysql configuration file and precede the skip-grant-tables with #

/ etc/init.d/mysqld restart # restart mysql

Log in to the mysql database with the new password with the following command:

# mysql-uroot-p123456 can log in to the database normally!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.