A.4.2 Как переустановить забытый пароль пользователя root

Если для MySQL пароль пользователя root никогда не устанавливался, то для соединения с сервером в качестве пользователя root пароль не потребуется. Рекомендуется всегда устанавливать пароль для каждого пользователя (see section 4.2.2 Как обезопасить MySQL от хакеров).

Если вы забыли установленный для root пароль, то новый пароль можно задать при помощи следующей процедуры:

1. Остановите сервер mysqld; для этого нужно послать kill (но не kill -9) серверу mysqld. Номер процесса хранится в файле `.pid', обычно расположенном в каталоге баз данных MySQL:

   shell> kill `cat /mysql-data-directory/hostname.pid`
   

   Чтобы выполнить эту команду, необходимо быть либо Unix-пользователем root, либо пользователем, под которым работает mysqld.
2. Перезапустите mysqld с опцией --skip-grant-tables.
3. Установите новый пароль с помощью команды mysqladmin password:

   shell> mysqladmin -u root password 'mynewpassword'
   

4. Теперь можно либо просто остановить mysqld и заново запустить его обычным способом, либо просто загрузить таблицы привилегий посредством:

   shell> mysqladmin -h hostname flush-privileges
   

5. После этого можно будет соединяться, пользуясь новым паролем.

Есть и другой способ установки нового пароля - с помощью клиента mysql:

Остановите mysqld и перезапустите его с опцией --skip-grant-tables, как было описано ранее.
1. Соединитесь с сервером mysqld посредством:

   shell> mysql -u root mysql
   

2. Выполните следующие команды из клиента mysql:

   mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
       -> WHERE User='root';
   mysql> FLUSH PRIVILEGES;
   

3. После этого можно будет соединяться по новому паролю.
4. Остановите mysqld и запустите его заново, как обычно.

User Comments

I tried to use the mysqladmin of changing the root
password and it did not work. I kept getting
errors about not having permissions to access the
database.

Using the mysql client method of changing the root
password worked.

same problem here, using redhat 7.3 and rpm
installation. any suggestions?

I ran into the same trouble on RH7.3 attempting to
use the mysqladmin means. I was able to
successfully change mine using the msql client
alternative means.

same problem on a cobalt raq, the mysql client way
worked

The directions here are not adequate to 4.04-beta.

After upgrading, 4.040-beta worked fine through
several reboots. But on the last reboot it somehow
lost the root password. Trying to use these
instructions to reset the root password results in:

# mysql/bin/mysqld --skip-grant-tables
Fatal error: Please read "Security" section of the
manual to find out how to run mysqld as root!
021009 14:26:29 Aborting

And that's after figuring out that it can't even
show the error message unless you try to invoke
mysqld from /usr/local rather than, say,
/usr/local/msyql. First off, corrupting the root
password is a bad thing, even for a beta. Second,
the recovery instructions in the manual should be
complete, not sketchy. Third, having error
messages that depend on your being in a
nonstandard place in the path even to see them,
rather than an error message about not finding the
error messages ... should this beta be an alpha?

Okay, so I then start mysqld with --user="mysql",
which seems to work, but:

mysqladmin -u root password 'somepass'
mysqladmin: unable to change password; error: 'You
must have privileges to update tables in the mysql
database to be able to change passwords for others'

So root doesn't have privileges to update tables,
and root is an "other"?

The "Alternate method" above does successfully
correct the root password corruption (however that
happened!).

Nope. Not fixed. Reboot and it's broken again.

Okay, mysqld_safe seems to start it in a broken
state, re the root password. Also, the mysql init
script (Red Hat 6.0) starts it in a broken state.
But going to /usr/local and invoking
mysql/bin/mysqld --user="mysql" & seems to work
okay - unless the failures I'm seeing reflect
something random. Having mysql insist on being in
/usr/local when starting is nasty (hmm, maybe
there's some flag to have it find it's error files
- but it shouldn't be a fatal error not to find them).

mysqld Ver 3.23.40, the following command line
succesfully starts the daemon without the grant tables..

mysqld --skip-grant-tables --user="mysql"

However, using mysqladmin to change the root
password failed. Using the mysql client method to
manually add the password worked.

Environment RH7.2 MySQL 4.04 ran into the
same problems as described above concerning
restarting mysqld with --skip-grant-tables.
For me this was the solution: mysqld --skip-
grant-tables user="root"
Only then I was able to log on to the client as
root.
Greets, Stefan

Any help for a Windows user with this issue?

Mysql 4.0.4 Redhat 7.3
I had a similar error, and none of these fixes worked,
I had to run the safe demon in order to get in to
update my password. I wont forget it again!!!

safe_mysqld --skip-grant-tables &

mysql

use mysql;

update user set password = password('.......') where
user = 'root' and host='localhost';

I found that the second technique listed, using mysql
client, worked when the first one (using mysqladmin)
failed with the 'You must have privileges to update
tables in the mysql database to be able to change
passwords for others'. (FreeBSD 4.7-RELEASE,
mysql 3.23.52).

For me the database directory was not good, so I suggest doing sonthing like : mysqld --skip-grant-tables --user=root --datadir=/var/lib/mysql/

Indeed my default datadir was /var/, and it was useless trying to connect to the database 'mysql' where I had to edit the passwords...

So I suggest using the solution with mysql client, so that you can fix problems due to wrong default settings.
May this comment helps you!

How to Reset a Forgotten Root Password on Win2000 ???

Delete mysql dir under mysql/data.
Install the same Mysql version on other PC
Copy the fresh mysql dir.
Restart win.

let me show the way of reset root password;
based on windows 2000 pro.

at first, stop mysql service
open cmd console,
move to c:/mysql/bin
type below...

c:\mysql\bin>start mysqld -Sg (enter :))

c:\mysql\bin>



it's done... root password has been cleared :)

good job.

It seems that you can also change the root password by starting the 'mysqld ... --skip-grant-tables ...' parallel to the running one. Just override the values from '/etc/my.cnf' with command line options like:
--port=42000
--socket=/tmp/foobar.sock
--pid-file=/tmp/foobar.pid
and then connect to it, do your changes and kill it.
It worked for me ;0)

Im using OSX I have MySQL installed but I cant set the password.


when I try
kill `cat /mysql-data-directory/hostname.pid`

cat: /mysql-data-directory/hostname.pid: No such file or directory

/usr/local/bin/mysqladmin -u root password jahova

I get

./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@localhost' (Using password: NO)'

Any ideas. I really need to get this setup.

Thanks in advance all you gurus.

I tried to change root password but when I try to start mysqld whit --skip-grant-tables I receive this useless msg:"Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
030218 13:36:00 Aborting " .But it work fine with :
/usr/libexec/mysqld -Sg --user="mysql".And only alternate method whit mysql client do the job.Thanks fly to other for posting comment

On Mac OS X, you can install MySQL following any of these guidelines:

Good starting points:

www.macosxhints.com/article.php?story=20010402202522315
www.entropy.ch

Once the ./mysql_install_db script is run, and once the MySQL has been started with the command safe_mysqld & it is safe to assume your MySQL is actually running, but what next? The privileges problem needs to be addressed.

Follow the guidelines on macosxhints, particularly, where it says this, which I repeat somewhat similarly:

Type the following as 'root':

> mysql

Then, inside MySQL, type after the m> prompt, and include the '' quotes around the password:

m> GRANT ALL PRIVILEGES ON *.* TO yourusername@localhost IDENTIFIED BY 'whateverpasswordyouwant';
m> quit

From there, I suggest you continue setting up MySQL using pHpMyAdmin, if you have pHp set up and running. It is better than MacSQL, a Mac OS X program, if you want to remotely administrate this via another computer on the same network.

pHpMyAdmin is a very neat utility that helps you solve privileges problems in an efficient way.

Before you start it, enter the correct settings into the 'config.inc.php' file! That's the secret. From there, you can set/grant/delete all privileges with the on-screen gui which is a bit more intuitive than using command line commands.

Starting points to look for the software mentioned:

pHp package for Mac OS X - www.entropy.ch
pHpMyadmin- www.sourceforge.net
MacSQL- www.versiontracker.com

Wolf Schweitzer, www.swisswuff.ch

Add your own comment.