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:
NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges.
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.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.