Главная » Linux

Восстановление пароля mysql linux

На чтение 8 мин Просмотров 2 Опубликовано
Содержание
  1. How to reset mysql root password?
  2. 8 Answers 8
  3. PART 1: remove the old MYSQL :
  4. PART 2: Install MYSQL

How to reset mysql root password?

I have a little problem with my phpmyadmin, in fact I accidentally delete multiple user accounts. Since it is impossible to connect without the error:

# 1045 - Access denied for user 'root' @ 'localhost' (using password: NO) 
UPDATE mysql.user SET Password = PASSWORD ('') WHERE User = 'root'; FLUSH PRIVILEGES;

does not work, or I didn’t understood how it worked. I’m on FreeBSD 8.1, my version of PhpMyadmin is 2.11. Thank you in advance for your answers.

8 Answers 8

sudo stop mysql sudo mysqld --skip-grant-tables --skip-networking mysql mysql> update mysql.user set password = password('your_new_password') where user = 'root'; mysql> flush privileges; mysql> exit; sudo mysqladmin shutdown sudo start mysql

please don’t post the exact same answer multiple times. If the question is a duplicate, flag it as such.

missing from the above snippet — though explained in the offsite summary. 1) After running the first sudo mysqld. , switch to a different terminal window. 2) Prompt for the the new password when running mysqladmin shutdown: sudo mysqladmin shutdown -u root -p

For mysql 5.7.16 or later I found this (from mysql.com) to be the working solution. Below are some points which are different compared to previous older versions

1) I used the below command to run mysql in safe mode as per some other references which actually worked fine for me (mysql 5.7.16 ubuntu 16.04).

mysqld_safe --skip-grant-tables --skip-networking

2) The below update stmt is NOT working for later version (ie. 5.7 and above)

-- NOT Working for 5.7 and later UPDATE mysql.user SET Password = PASSWORD ('') WHERE User = 'root'; FLUSH PRIVILEGES;

INSTEAD use below for 5.7.6 and later

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

OR user below for 5.7.5 or earlier versions.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

I am Using MySQL Server 8 and this is how I solved this problem.

create a file and name it accordingly. I’ve named it as reset.txt. put below content in the file but change MyNewPass. This will be your new SQL password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Stop my SQL server (Go to services and search MYSQL and right-click on it and stop )

Читайте также:  Linux find distribution version

Now open a cmd in your bin folder of My SQLserver directory. in my case it’s

C:\Program Files\MySQL\MySQL Server 8.0\bin

execute the following command.

mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file mt24">
)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this answer" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="answer" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="2" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f4.0%2f" data-se-share-sheet-license-name="CC BY-SA 4.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this answer
answered Feb 10, 2020 at 11:54
3
  • I tried this solution but i'm still getting an error saying I cannot connect to the database.
    – wolfblitza
    Jun 3, 2020 at 1:29
  • @wolfblitza when you try mysqld command did you try it in an admin cmd. if you did not please do it and let me know if you are getting the same error.
    – Ravinda Lakshan
    Jun 3, 2020 at 7:17
  • Yes I ran it in admin
    – wolfblitza
    Jun 4, 2020 at 0:24
Add a comment|
0

Please follow the instruction from the below link to reset your root password.You have to do it from outside mysql.

Resetting MySql Password

Forgetting your MySQL password can be a real headache for you.Here are some easy steps that you can follow to recover MySql password under Windows Stop MySql

You have to stop MySql service before you proceed.In Windows environment, go to 'Task Manager' and Under 'Service' tab find MySQL and stop it. Write Sql to change your password

All you need to do is to create a text file and put the below two lines into that. UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; Save it under C:\ drive and give it a name 'mysql-init.txt' Time to restart MySQL by your own.

Now it's time to restart your MySQl which you stopped in before but this time from command line. C:> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt Finishing up!

Now you can log in with your new password. When you finish remove the file that you created in the previous stage.

Also there is a link (http://kaziprogrammingblog.osinweb.com/article/showarticle/Resetting-MySql-Password) where I have explained the same thing.

Hope this will help. )

)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this answer" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="answer" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="2" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f3.0%2f" data-se-share-sheet-license-name="CC BY-SA 3.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this answer
)">edited Feb 16, 2014 at 7:04
answered Nov 4, 2013 at 11:39
Add a comment|
0

Answer for XAMPP on Windows:

  • Edit C:\xampp\mysql\bin\my.ini and insert skip-grant-tables below [mysqld]
  • Restart MySQL from Control Panel interface
  • In the phpMyAdmin window, select SQL tab from the top panel. This will open the SQL tab where we can run the SQL queries. Now type the following query in the text area and click Go

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; FLUSH PRIVILEGES;

  • Remove the skip-grant-tables in the my.ini file
  • Restart MySQL from Control Panel interface

DONE!

Be awared that mysql root user password does not have to be the same as password for phpmyadmin.

)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this answer" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="answer" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="2" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f3.0%2f" data-se-share-sheet-license-name="CC BY-SA 3.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this answer
answered May 9, 2017 at 19:11
Add a comment|
0

here is what I did and it worked:
After you install (rpm installation), do a vi /etc/my.cnf.
This will give you a path where your datadir is set. For me it was datadir=/var/lib/mysql.
Add a line there user=root, and remove all the content inside the datadir path: rm -rf /var/lib/mysql/*.
Now hit the command: mysqld --initialize. A temporary password is generated at a location. For this:
grep "A temporary password" /var/log/*.
You will get a line that says:
/var/log/mysqld.log:2018-05-01T15:13:47.937449Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: &uosjoGfi9:K. So for me &uosjoGfi9:K was my temporary password.
Now do a mysql -u root -p. Paste your temporary password from what you got from above.
You will be in your mysql cli mode. Now do:
mysql> use mysql;
You will be asked to reset your password: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Run your ALTER command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPswd';
And you are done.

)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this answer" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="answer" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="2" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f3.0%2f" data-se-share-sheet-license-name="CC BY-SA 3.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this answer
answered May 1, 2018 at 14:41
Add a comment|
0

in my case its a frustrating Windows Server installation (iis/php/mysql) so this is what i did:

PART 1: remove the old MYSQL :

NOTE: if you already have data you should back it up!

Step 1 Uninstall MySQL from Control Panel. (you should know how to do this)

Step 2 Run Command Prompt as Admini and execute the following commands to stop and remove MySQL service.

Net stop MySQL Sc delete MySQL

Step 3 Delete these folders:

C:\Program Files\MySQL C:\Program Files (x86)\MySQL C:\ProgramData\MySQL 
C:\Users\[User-Name]\AppData\Roaming\MySQL

PART 2: Install MYSQL

Download installer from https://dev.mysql.com/downloads/installer/ and install -MySQL Server 8.0.23

open cmd as admin and go to c:\Program Files\MySQL\MySQL Server 8.0\bin\ run

mysqld --initialize --console mysqld --install

now start the service (type services.msc in run panel)

mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Источник

Оцените статью
© 2023 Posetke
Adblock
detector