MySQL DBA Tips Corner      Return to the MySQL DBA Tips Corner.

Setting Up the Initial MySQL Privileges

by Jeff Hunter, Sr. Database Administrator

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The scripts/mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges:

• The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host.

  NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges.

• An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user.

• Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist.

Since the installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify a password using the PASSWORD() function):

  % mysql -u root mysql
  mysql> SET PASSWORD FOR root@localhost=PASSWORD('manager');
  Query OK, 0 rows affected (0.02 sec)

  mysql> exit

Now check that the new password for root works:

  % mysql -u root -pmanager mysql

Instead of using the PASSWORD() function, you can also directly manipulate the privilege tables:

  % mysql -u root mysql
  mysql> UPDATE user SET password=PASSWORD('manager')
      -> WHERE user='root';
  mysql> FLUSH PRIVILEGES;

Yet another way to set the password is by using the mysqladmin command:

  % mysqladmin -u root password manager

Only users with write/update access to the mysql database can change the password for other users. All normal users (not anonymous ones) can only change their own password with either of the preceding commands or with SET PASSWORD=PASSWORD('new password').

Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root

You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

If you want the initial privileges to be different than those described, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all *.frm, *.MYI, and *.MYD files in the directory contained the mysql database. (This is the directory named mysql under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.