Step 1 \u2014 Identifying the Database Version
\nMost modern Linux distributions ship with either MySQL or MariaDB, a popular drop-in replacement which is fully compatible with MySQL. Depending on the database used and its version, you\u2019ll need to use different commands to recover the root password.
\nYou can check your version with the following command:<\/p>\n\n\n\n
mysql –version
\nCopy
\nYou\u2019ll see some output like this with MySQL:<\/p>\n\n\n\n
MySQL output
\nmysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
\nOr output like this for MariaDB:<\/p>\n\n\n\n
MariaDB output
\nmysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
\nMake note of which database and which version you\u2019re running, as you\u2019ll use them later. Next, you need to stop the database so you can access it manually.
\nStep 2 \u2014 Stopping the Database Server
\nTo change the root password, you have to shut down the database server beforehand.
\nYou can do that for MySQL with:<\/p>\n\n\n\n
sudosystemctl stop mysql
\nAnd for MariaDB wtih:<\/p>\n\n\n\n
sudosystemctl stop mariadb<\/p>\n\n\n\n
After the database server is stopped, you\u2019ll access it manually to reset the root password.
\nStep 3 \u2014 Restarting the Database Server Without Permission Checking
\nIf you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.
\nTo do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.
\nStart the database without loading the grant tables or enabling networking:<\/p>\n\n\n\n
sudomysqld_safe –skip-grant-tables –skip-networking &<\/p>\n\n\n\n
The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.
\nNow you can connect to the database as the root user, which should not ask for a password.<\/p>\n\n\n\n
mysql -uroot
\nCopy
\nYou\u2019ll immediately see a database shell prompt instead.
\nMySQL prompt
\nType ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.
\nmysql>
\nMariaDB prompt
\nType ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.
\nMariaDB [(none)]>
\nNow that you have root access, you can change the root password.
\nStep 4 \u2014 Changing the Root Password
\nOne simple way to change the root password for modern versions of MySQL is using the ALTER USER command. However, this command won\u2019t work right now because the grant tables aren\u2019t loaded.
\nLet\u2019s tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.<\/p>\n\n\n\n
FLUSH PRIVILEGES;\u975e\u5e38\u5173\u952e\uff0c\u5fc5\u987b\u5148\u6267\u884c\u8fd9\u4e2a\uff0c\u7136\u540e\u6267\u884c\u540e\u9762\u7684\u8bed\u53e5\u624d\u6709\u6548<\/em><\/strong> ALTER USER’root’@’localhost’IDENTIFIED BY ‘new_password’; SET PASSWORD FOR ‘root’@’localhost’=PASSWORD(‘new_password’); UPDATE mysql.user SET authentication_string =PASSWORD(‘new_password’)WHERE User =’root’AND Host =’localhost’; Output sudokill sudokill sudosystemctl start mysql sudosystemctl start mariadb mysql -uroot -p Step 1 \u2014 Identifying the Database Version Most modern L … <\/p>\n
Now we can actually change the root password.
For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command.<\/p>\n\n\n\n
\nCopy
\nFor MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:<\/p>\n\n\n\n
\nCopy
\nMake sure to replace new_password with your new password of choice.
\nNote: If the ALTER USER command doesn\u2019t work, it\u2019s usually indicative of a bigger problem. However, you can try UPDATE \u2026 SET to reset the root password instead.<\/p>\n\n\n\n
\nCopy
\nRemember to reload the grant tables after this.
\nIn either case, you should see confirmation that the command has been successfully executed.<\/p>\n\n\n\n
\nQuery OK, 0 rows affected (0.00 sec)
\nThe password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.
\nStep 5 \u2014 Restart the Database Server Normally
\nFirst, stop the instance of the database server that you started manually in Step 3. This command searches for the PID, or process ID, of MySQL or MariaDB process and sends SIGTERM to tell it to exit smoothly after performing clean-up operations. You can learn more in this Linux process management tutorial.
\nFor MySQL, use:<\/p>\n\n\n\ncat\/var\/run\/mysqld\/mysqld.pid<\/code>
\nCopy
\nFor MariaDB, use:<\/p>\n\n\n\n\/var\/run\/mariadb\/mariadb.pid<\/code>
\nCopy
\nThen, restart the service using systemctl.
\nFor MySQL, use:<\/p>\n\n\n\n
\nCopy
\nFor MariaDB, use:<\/p>\n\n\n\n
\nCopy
\nNow you can confirm that the new password has been applied correctly by running:<\/p>\n\n\n\n
\nCopy
\nThe command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.
\nConclusion
\nYou now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.<\/p>\n","protected":false},"excerpt":{"rendered":"