Part of the MySQL installation process is to set up the mysql database that contains the grant tables:

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace “newpwd” in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all.

You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Anonymous Account Password Assignment

To assign passwords to the anonymous accounts, connect to the server as root and then use either SET PASSWORD or UPDATE. In either case, be sure to encrypt the password using the PASSWORD() function.

To use SET PASSWORD on Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non-localhost record for root in the user table. If you don't know what host name this is, issue the following statement before using SET PASSWORD:

mysql> SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement.

Anonymous Account Removal

If you prefer to remove the anonymous accounts instead, do so as follows:

shell> mysql -u root
mysql> DROP USER '';

The DROP statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root, do this instead:

shell> mysql -u root
mysql> DROP USER ''@'localhost';

That account allows anonymous access but has full privileges, so removing it improves security.

root Account Password Assignment

You can assign passwords to the root accounts in several ways. The following discussion demonstrates three methods:

To assign passwords using SET PASSWORD, connect to the server as root and issue SET PASSWORD statements. 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('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same host name that you used when you assigned the anonymous account passwords.

If the user table contains an account with User and Host values of 'root' and '127.0.0.1', use an additional SET PASSWORD statement to set that account's password:

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes 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 set the password for the 'root'@'127.0.0.1' account. To do so, use SET PASSWORD as shown earlier.

You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to all root accounts:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The UPDATE statement applies both to Windows and to Unix.

After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command:

shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

To set up additional accounts, you can use the GRANT statement. For instructions, see Section 4.2, “Adding User Accounts”.