How to change MySQL root user password by terminal

I know some other administrator who has forgotten the password of the root of MySQL, this can be a real inconvenience, right?

Imagine that you need to create a new database, do anything and you can't because you don't remember the password of the administrator (root) of the MySQL server, a real problem.

Here I will show you how to access the MySQL server through the terminal WITHOUT setting the root password, so that once inside you can change the root password.

Related article:
Check tables of a MySQL database and repair corrupt

The first thing will be to stop the mysql service:

The following two commands must be executed with administrative privileges, either by setting sudo at the beginning of the command or by executing them directly as root

service mysql stop

This stopped the service, now we are going to start it but in a different way, a way that will not ask us for a password later:

mysqld_safe --skip-grant-tables &

Ready, now let's access the MySQL terminal:

mysql -u root

They will see that it did not ask for a password, they will see that they have already entered the MySQL console or terminal and can do whatever they want, let's proceed to change the MySQL root password.

First we will enter the MySQL database as such:

use mysql;

Then, let's change the password:

update user set password=PASSWORD("ElNuevoPassword") where user='root';

Now let's refresh the privileges:

flush privileges;

And finally we go out:

quit;

Ready, we have changed the password of the MySQL root user, now we are going to stop the service and start it as it should be:

service mysql stop

service mysql start

End

This is all, they have regained control of their own MySQL server


The content of the article adheres to our principles of editorial ethics. To report an error click here!.

15 comments, leave yours

Leave a Comment

Your email address will not be published.

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.

  1.   firecold said

    Very good tip, thanks

  2.   Chaparral said

    Excellent, great!

  3.   FIXOCONN said

    to wait for it to happen to me to try it, nothing good tips

  4.   CrisXuX said

    Excellent

  5.   Gustavo Londono L. said

    very good article, a hug !!

  6.   Ricardo said

    Very useful, you just got me out of a tight spot. Thank you.

  7.   pepper said

    Very useful, you got me out of a tight spot, thanks!

  8.   Joseph said

    This solution has worked for me many times, but now I have a newly installed mysql engine and I tried to set the password, however it tells me that the "password" field does not exist, check the structure and the field does not really exist. Any idea how to fix it?

  9.   Ignatius farre said

    Thank you, you have saved me by being able to regain control of my mysql ...

  10.   David said

    I worked out of ten thousand thanks.

  11.   Javierfdez said

    Well, you have solved my problem. Thank you!

  12.   freval said

    The last 4 steps make a difference thank you very much

  13.   FuzzJS said

    Thanks it worked very well for me however I had an error after the first command with the following message:

    mysqld_safe Directory '/ var / run / mysqld' for UNIX socket file don't exist

    Creating the directory solved the problem and I was able to complete the change of password, I share the commands in case it happens to someone.

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

  14.   ANNE JULIA said

    Too good

  15.   Giuseppe said

    Thank you very much for the article.
    It helped me to recover the test databases of a raspberry pi in which I have installed a LAMP server for a long time.