Assigning root Account Passwords
The root
account passwords can be set several ways. The following discussion demonstrates three methods:
-
Use the
SET PASSWORD
statement -
Use the
UPDATE
statement -
Use the mysqladmin command-line client program
To assign passwords using SET PASSWORD
, connect to the server as root
and issue a SET PASSWORD
statement for each root
account listed in the mysql.user
table. Be sure to encrypt the password using the PASSWORD()
function.
For Windows, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'::1' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'%' = PASSWORD('
newpwd
');
The last statement is unnecessary if the mysql.user
table has no root
account with a host value of %
.
For Unix, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'::1' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'
host_name
' = PASSWORD('newpwd
');
You can also use a single statement that assigns a password to all root
accounts by using UPDATE
to modify the mysql.user
table directly. This method works on any platform:
shell>mysql -u root
mysql>UPDATE mysql.user SET Password = PASSWORD('
->newpwd
')WHERE User = 'root';
mysql>FLUSH PRIVILEGES;
The FLUSH
statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.
To assign passwords to the root
accounts using mysqladmin, execute the following commands:
shell>mysqladmin -u root password "
shell>newpwd
"mysqladmin -u root -h
host_name
password "newpwd
"
Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.
The mysqladmin method of setting the root
account passwords does not work for the 'root'@'127.0.0.1'
or 'root'@'::1'
account. Use the SET PASSWORD
method shown earlier.
After the root
passwords have been set, you must supply the appropriate password whenever you connect as root
to the server. For example, to shut down the server with mysqladmin, use this command:
shell>mysqladmin -u root -p shutdown
Enter password:(enter root password here)