6.10 Setting up the initial MySQL privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. 4.7.1 Quick installation overview. 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 and be treated as the anonymous user.
• Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist.

Note: The default privileges are different for Win32. 4.12.4 Running MySQL on Win32.

Since your 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 the password using the PASSWORD() function):

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

You can also use the SET PASSWORD statement:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

Another way to set the password is by using the mysqladmin command:

shell> mysqladmin -u root 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 reread the grant tables (with FLUSH PRIVILEGES), since 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, but be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

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

To recreate the grant tables completely, remove all the `*.frm', `*.ISM' and `*.ISD' files in the directory containing 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.

NOTE: For MySQL versions older than 3.22.10, you should NOT delete the `*.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.