It can happen quickly that you have forgotten the root password or access password for your MySQL or MariaDB server. For example, you can no longer get in via phpMyAdmin, but you absolutely have to change something. This is very annoying. We help you to solve the problem.
With a few short commands it is possible to reset the root password for the MySQL server.
First you need to establish an SSH connection with root command line to your server. Now you should check if you might have access to MySQL server via command line.
If you did not receive an authentication error, you can continue here. You have then logged in via the „Socket on your Linux server„. With newer MySQL server versions (and also MariaDB) this is set up by default during the installation.
Start MySQL without rights table
In order for the password to be reset, MySQL must be started in a certain mode. In this mode the rights check is completely disabled. This mode should only be switched on for the short time in which the password is reset. Because otherwise everyone can access all databases. Without username and password.
First you have to stop the current MySQL server. Enter
systemctl stop mysql
Now the MySQL server should be stopped.
To start the server in unsafe mode, the following command is now necessary:
mysqld_safe --skip-grant-tables &
You should now have output that looks similar to the following:
root@mysql-server:~# 200629 14:07:28 mysqld_safe Logging to syslog.
200629 14:07:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
It is now relatively easy to reset the password of a particular user. One command is enough and the password is changed.
update mysql.user set authentication_string=password('neues-passwort') where user = 'root'; flush privileges; quit
This sets the password of all „root“ users (localhost, remote, IP-bound) on the server to „new-password„.
Now the insecure MySQL server must be stopped, the normal server must be started.
pkill -f mysql
systemctl start mysql
Test new access data
Now test if you can get into the database with the new password. You can check this either via phpMyAdmin or via the command line. Enter for this:
mysql -uroot -p
When you are asked for the password, enter the new password you just set. You should now be able to log in. Happy Selecting!