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

How to recover MySQL database password

Stop Mysql Server

Skip authorization table Start MySQL server

Reset root password (update user table record)

Restart MySQL server in normal manner

Password Recovery Examples

Example 1: Reset MySQL administrative password

First stop the MySQL server that is already running

[root@host50 ~]# systemctl stop mysqld[root@host50 ~]# systemctl status mysqld● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Tue 2019-07-02 03:54:56 CST; 6s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 1426 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 1083 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1430 (code=exited, status=0/SUCCESS)Jul 02 03:31:22 host50 systemd[1]: Starting MySQL Server... Jul 02 03:31:36 host50 systemd[1]: Started MySQL Server.Jul 02 03:54:55 host50 systemd[1]: Stopping MySQL Server... Jul 02 03:54:56 host50 systemd[1]: Stopped MySQL Server.

Skip authorization table Start MySQL server (Configuration--skip-grant-tables option) See last line of documentation

[root@host50 ~]# vim /etc/my.cnf[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksecure_file_priv="/myload"# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0default-storage-engine=innodblog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidskip_grant_tables=1

After reconnecting mysql, reset the login password of root user by modifying the records in user table in mysql library.

[root@host50 ~]# systemctl restart mysqld[root@host50 ~]# mysql -urootmysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456')-> WHERE user='root' AND host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 1mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)mysql> exit

Note: By executing "FLUSH PRIVILEGES;" you can make the authorization table take effect immediately. For MySQL service running normally, you can also change the password in the above way without restarting the service. In this example, because it is to recover the password, it is best to restart the MySQL service program, so the above "FLUSH PRIVILEGES;" operation can be skipped.

Restart Mysql service as normal, verify new password (comment skip_grant_tables option)

[root@host50 ~]# vim /etc/my.cnf[mysqld]#skip_grant_tables=1... [root@host50 ~]# systemctl restart mysqld[root@host50 ~]# mysql -urootERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@host50 ~]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Example 2: Reset Mysql administrative user password (known password)

Method 1: Using mysqladmin management tool, you need to verify the old password

[root@host50 ~]# mysqladmin -u root -p password 'qaz123edc' Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Method 2: After logging in to mysql as root, use the set password command to set it (validate_password_policy=0 must be configured first)

mysql> set password for root@localhost=password('123456');Query OK, 0 rows affected, 1 warning (0.00 sec)

Method 3: After logging in mysql as root, use grant authorization tool to set

mysql> grant all on *.* to root@localhost identified by '123456';Query OK, 0 rows affected, 1 warning (0.00 sec)

Method 4: After logging in MySQL as root, use update to update the corresponding table record

mysql> update mysql.user set authentication_string=password('123456') -> where user='root' and host='localhost';Query OK, 0 rows affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 1

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.