How does Ubuntu reset the root password of MySQL

Time:2021-10-11

The root password is required to create a new user, a new database and set access control in MySQL. If you forget the password, you can only reset the password. However, I still can’t reset the password for the results found on the Internet. I can only find the document myself and record the results here for future reference!

Scheme I: provision of official documents (recommended)

  1. Kill MySQL related processes
    If the MySQL server is running, stop it. Locate the. PID file that contains the server process ID. The exact location and name of this file depends on your distribution, host name, and configuration. The common locations are ` / var / lib / MySQL / `, ` / var / run / mysqld / ` and ` / usr / local / MySQL / data / `.
    If you can't, just 'PS - aux|grep mysqld' and 'kill' the corresponding process

    By sending a message tomysqldThe process sends akillSignal to end the MySQL process(Note: Nokill -9

    Note: the commands after kill are in backquotes, not single quotes
    #The following command is an example command and cannot be executed successfully
    kill `cat /mysql-data-directory/host_name.pid`

My file location is this

kill cat /run/mysqld/mysqld.pid

2. Create a file containing the command to modify the user password. (* * suppose the file is ` / home / Ubuntu / MySQL init ` *)
```bash
#Run this command in version 5.7.6 and later
echo 'ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';' > mysql-init
#Run this command before 5.7.6
echo 'SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');' > mysql-init
  1. Ensure that MySQL has read permission to the file
    #If unsure, change to '777'
    chmod 777 mysql-init

Or directly transfer the permissions of the file to MySQL users and groups

chown mysql:mysql mysql-init

4. Start MySQL with the following command
```bash
mysqld --init-file=/home/ubuntu/mysql-init &
  1. After successful startup, delete the file just created
    rm -f mysql-init
  2. Now restart normally and you can log in with the root password you just created

    If you still cannot reset the password, please modify the contents of the file created above to the following contents and repeat steps 3-5

    UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
    FLUSH PRIVILEGES;

Scheme 2: safe mode reset method (the most common search results on the Internet)

Idea: start MySQL in safe mode, so you can log in as root without a password, and then reset the password.

  1. Stop MySQL service:

    sudo service mysql stop  

    The above commands apply to Ubuntu and Debian. Used under CentOS, Fedora and RHELmysqldreplacemysql

  2. Start MySQL in safe mode:

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

    Note that we added--skip-networking, avoid remote password free login to MySQL.

  3. Log in with root without password:

    mysql -u root
  4. Reset password:

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

    Note that a semicolon is required after the command.

  5. After the reset, we exit and start the MySQL service:

    mysql> quit

    Quit does not require a semicolon.

  6. Restart service:

    sudo service mysql restart
    #If you cannot restart, you may need to manually kill the MySQL related processes and restart

    If it is not Debian series, but CentOS, Fedora and RHEL, you need to usemysqldreplacemysql

  7. You can now try logging in with your new password:

    MySQL - U root - pnewpassword # note that there must be no space between - P and password.
    #Or
    MySQL - U root - P # enter the password after entering

    among-uCan also work withrootConnected together, such asmysql -uroot -p

be careful

Since MySQL 5.7.6, the 'password' field has been deleted and 'authentication' has been used instead_ The 'string' field stores the password

The following problem may be encountered during modification

[email protected]:~$ sudo mysqld_safe --skip-grant-tables --skip-networking 
2019-06-28T01:27:24.687004Z mysqld_safe Logging to syslog.
2019-06-28T01:27:24.690188Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-06-28T01:27:24.693132Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

resolvent

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

Scheme 3: it is mainly applicable to Debian, Ubuntu and other Debian derivative systems

On Ubuntu and Debian systems, there is adebian-sys-maintUser, the user is responsible for the operation of some system scripts on MySQL under Debian system. So we can change the root password through this user. The user’s password can be/etc/mysql/debian.cnfFound below:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = PASSWORD
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

After logging in to MySQL with this user, you can also change the password:

sudo mysql -u debian-sys-maint -p

This work adoptsCC agreement, reprint must indicate the author and the link to this article