4.3.4 Setting Up the Initial MySQL Privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. See section 2.3.1 Quick Installation Overview. The mysql_install_db script starts up the mysqld server, then initialises 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.

Note: the default privileges are different for Windows. See section 2.6.1.3 Running MySQL on Windows.

Because 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> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Replace 'new_password' with the password that you want to use.

If you know what you are doing, you can also directly manipulate the privilege tables:

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

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

shell> mysqladmin -u root password new_password

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 above 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.

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 from those just described above, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' 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 Version 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.

User Comments

That's so important. Check the files
into /var/lib/mysql and make sure the owner is
mysql. Do that with ls -al. If they are root then
change it by typing chown mysql *
That would might solve your problem if you are
trying to log in as root and you can't get it.

"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."
You can also see how to add a user here:
http://www.mysql.com/doc/A/d/Adding_users.html

Beware, when recreating the default security
settings by running mysql_install_db, check the
owner of the newly created files to make sure
that they are owned by the mysql user otherwise
you won't be able to restart the mysql service.

mysql_setpermission.pl won't recreate user
settings on Win32.

Just to drop a note for beginners:
"mysqladmin -u root password
new_password"
this sets a new password for
root@LOCALHOST,
but not for the root@HOST.
By default the pass for root@HOST is blank so
just typing the command above is not sufficient.
For example your mysql server is completely open in
your local windows LAN....
so to correct the problem type:
"mysqladmin -u root -h my_host password
new_password"

Regards,
BIVOL
mail: bivolATinboxDOTru

I would suggest the following for "inital db lockdown
commands":

//login as root and use the mysql db
mysql -uroot mysql

//then issue these
UPDATE user SET Password=PASSWORD
('root_passwd') where user='root';
UPDATE user SET Password=PASSWORD
('guest_passwd') where user='';
FLUSH PRIVILEGES;

That should change the passwords for the users root
and for the "anonymous" users for all listed hosts.

I find it a bit of an odd construct that a user can
have a password that is host dependent. (If MySql
supported RSA key based authenticaiton then that
would make some sense, but I have yet to see that
in the documentation.) [I guess this "current
feature" has some value to someone.]

I first had the problem that the install script only inserts localhost and the local host's machine name as allowed hosts for the root user. Beacuse of this you could not connect as root from another machine. You can change this by changing the allowed host for root to '%' like this:

UPDATE user SET host = '%'
WHERE user ='root' AND host = 'your.machine.com';
FLUSH PRIVILEGES;

what happens if you set a password for root but forgot it. Do as Unix user Root, how do I go about fixing this?

fixed it. I just deleted the tables and ran mysql_install Db

Hi Anonymous user,
How could you do this? you lose all the users and their privileges when deleting the mysql tables.

mySql has provided a way to do this, check
"A.4.2 How to Reset a Forgotten Root Password" in the manual. I think its the right approach.

Mail back if I am wrong..........!

l have windows 98 and my problem is when l want to inter mysql l got this message how can l fix it :
ERROR 1044: Access denied for user: '@127.0.0.1' to database 'boards'

Add your own comment.