7725
7764
Part of the MySQL installation process is to set up the mysql
7726
7765
database that contains the grant tables:
7728
* Windows distributions contain preinitialized grant tables that
7729
are installed automatically.
7731
* On Unix, the grant tables are populated by the
7732
mysql_install_db program. Some installation methods run this
7733
program for you. Others require that you execute it manually.
7734
For details, see Section 2.13.1, "Unix Post-Installation
7737
The grant tables define the initial MySQL user accounts and their
7738
access privileges. These accounts are set up as follows:
7740
* Accounts with the user name root are created. These are
7741
superuser accounts that can do anything. The initial root
7742
account passwords are empty, so anyone can connect to the
7743
MySQL server as root --- without a password --- and be granted
7746
+ On Windows, one root account is created; this account
7747
allows connecting from the local host only. The Windows
7748
installer will optionally create an account allowing for
7749
connections from any host only if the user selects the
7750
Enable root access from remote machines option during
7753
+ On Unix, both root accounts are for connections from the
7754
local host. Connections must be made from the local host
7755
by specifying a host name of localhost for one of the
7756
accounts, or the actual host name or IP number for the
7759
* Two anonymous-user accounts are created, each with an empty
7767
* Windows distributions contain preinitialized grant tables.
7769
* On Unix, the mysql_install_db program populates the grant
7770
tables. Some installation methods run this program for you.
7771
Others require that you execute it manually. For details, see
7772
Section 2.13.1, "Unix Post-Installation Procedures."
7774
The mysql.user grant table defines the initial MySQL user accounts
7775
and their access privileges:
7777
* Some accounts have the user name root. These are superuser
7778
accounts that have all privileges and can do anything. The
7779
initial root account passwords are empty, so anyone can
7780
connect to the MySQL server as root without a password and be
7781
granted all privileges.
7783
+ On Windows, root accounts are created that allow
7784
connections from the local host only. Connections can be
7785
made by specifying a host name of localhost or 127.0.0.1.
7786
If the user selects the Enable root access from remote
7787
machines option during installation, the Windows
7788
installer creates another root account that allows
7789
connections from any host.
7791
+ On Unix, each root account allows connections from the
7792
local host. Connections can be made by specifying a host
7793
name of localhost, 127.0.0.1, or the actual host name or
7795
An attempt to connect to the host 127.0.0.1 normally resolves
7796
to the localhost account. However, this fails if the server is
7797
run with the --skip-name-resolve option, so the 127.0.0.1
7798
account is useful in that case.
7800
* Some accounts are for anonymous users. These have an empty
7760
7801
user name. The anonymous accounts have no password, so anyone
7761
7802
can use them to connect to the MySQL server.
7763
+ On Windows, one anonymous account is for connections from
7764
the local host. It has no global privileges. (Before
7765
MySQL 5.1.16, it has all global privileges, just like the
7766
root accounts.) The other is for connections from any
7767
host and has all privileges for the test database and for
7768
other databases with names that start with test.
7770
+ On Unix, both anonymous accounts are for connections from
7771
the local host. Connections must be made from the local
7772
host by specifying a host name of localhost for one of
7773
the accounts, or the actual host name or IP number for
7774
the other. These accounts have all privileges for the
7775
test database and for other databases with names that
7778
As noted, none of the initial accounts have passwords. This means
7779
that your MySQL installation is unprotected until you do something
7804
+ On Windows, there is one anonymous account that allows
7805
connections from the local host. Connections can be made
7806
by specifying a host name of localhost. The account has
7807
no global privileges. (Before MySQL 5.1.16, it has all
7808
global privileges, just like the root accounts.)
7810
+ On Unix, each anonymous account allows connections from
7811
the local host. Connections can be made by specifying a
7812
host name of localhost for one of the accounts, or the
7813
actual host name or IP number for the other.
7815
To display which accounts exist in the mysql.user table and check
7816
whether their passwords are empty, use the following statement:
7817
mysql> SELECT User, Host, Password FROM mysql.user;
7818
+------+--------------------+----------+
7819
| User | Host | Password |
7820
+------+--------------------+----------+
7821
| root | localhost | |
7822
| root | myhost.example.com | |
7823
| root | 127.0.0.1 | |
7825
| | myhost.example.com | |
7826
+------+--------------------+----------+
7828
This output indicates that there are several root and
7829
anonymous-user accounts, none of which have passwords. The output
7830
might differ on your system, but the presence of accounts with
7831
empty passwords means that your MySQL installation is unprotected
7832
until you do something about it:
7834
* You should assign a password to each MySQL root account.
7782
7836
* If you want to prevent clients from connecting as anonymous
7783
7837
users without a password, you should either assign a password
7784
7838
to each anonymous account or else remove the accounts.
7786
* You should assign a password to each MySQL root account.
7840
In addition, the mysql.db table contains rows that allow all
7841
accounts access to the test database and other databases with
7842
names that start with test_. This is true even for accounts that
7843
otherwise have no special privileges such as the default anonymous
7844
accounts. This is convenient for testing, but administrators may
7845
want database access restricted only to accounts that have
7846
permissions granted explicitly for that purpose, particularly on
7847
production servers. Such administrators should remove these
7848
mysql.db table rows.
7788
7850
The following instructions describe how to set up passwords for
7789
the initial MySQL accounts, first for the anonymous accounts and
7790
then for the root accounts. Replace "newpwd" in the examples with
7791
the actual password that you want to use. The instructions also
7792
cover how to remove the anonymous accounts, should you prefer not
7793
to allow anonymous access at all.
7795
You might want to defer setting the passwords until later, so that
7796
you don't need to specify them while you perform additional setup
7851
the initial MySQL accounts, first for for the root accounts, then
7852
for the anonymous accounts. The instructions also cover how to
7853
remove the anonymous accounts, should you prefer not to allow
7854
anonymous access at all, and describe how to remove permissive
7855
access to test databases. Replace newpwd in the examples with the
7856
password that you want to use. Replace host_name with the name of
7857
the server host. You can determine this name from the output of
7858
the preceding SELECT statement. For the output shown, host_name is
7863
If you forget your root password after setting it, see Section
7864
B.5.4.1, "How to Reset the Root Password."
7866
You might want to defer setting the passwords until later, to
7867
avoid the need to specify them while you perform additional setup
7797
7868
or testing. However, be sure to set them before using your
7798
7869
installation for production purposes.
7800
Anonymous Account Password Assignment
7802
To assign passwords to the anonymous accounts, connect to the
7803
server as root and then use either SET PASSWORD or UPDATE. In
7804
either case, be sure to encrypt the password using the PASSWORD()
7807
To use SET PASSWORD on Windows, do this:
7808
shell> mysql -u root
7809
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
7810
mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');
7812
To use SET PASSWORD on Unix, do this:
7813
shell> mysql -u root
7814
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
7815
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');
7817
In the second SET PASSWORD statement, replace host_name with the
7818
name of the server host. This is the name that is specified in the
7819
Host column of the non-localhost record for root in the user
7820
table. If you don't know what host name this is, issue the
7821
following statement before using SET PASSWORD:
7822
mysql> SELECT Host, User FROM mysql.user;
7824
Look for the record that has root in the User column and something
7825
other than localhost in the Host column. Then use that Host value
7826
in the second SET PASSWORD statement.
7828
Anonymous Account Removal
7830
If you prefer to remove the anonymous accounts instead, do so as
7832
shell> mysql -u root
7833
mysql> DROP USER '';
7835
The DROP statement applies both to Windows and to Unix. On
7836
Windows, if you want to remove only the anonymous account that has
7837
the same privileges as root, do this instead:
7838
shell> mysql -u root
7839
mysql> DROP USER ''@'localhost';
7841
That account allows anonymous access but has full privileges, so
7842
removing it improves security.
7844
root Account Password Assignment
7846
You can assign passwords to the root accounts in several ways. The
7847
following discussion demonstrates three methods:
7871
To set up additional accounts, see Section 5.5.2, "Adding User
7874
Assigning root Account Passwords
7876
The root account passwords can be set several ways. The following
7877
discussion demonstrates three methods:
7849
7879
* Use the SET PASSWORD statement
7881
* Use the UPDATE statement
7851
7883
* Use the mysqladmin command-line client program
7853
* Use the UPDATE statement
7855
7885
To assign passwords using SET PASSWORD, connect to the server as
7856
root and issue SET PASSWORD statements. Be sure to encrypt the
7857
password using the PASSWORD() function.
7886
root and issue a SET PASSWORD statement for each root account
7887
listed in the mysql.user table. Be sure to encrypt the password
7888
using the PASSWORD() function.
7859
7890
For Windows, do this:
7860
7891
shell> mysql -u root
7861
7892
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
7893
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
7862
7894
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');
7896
The last statement is unnecessary if the mysql.user table has no
7897
root account with a host value of %.
7864
7899
For Unix, do this:
7865
7900
shell> mysql -u root
7866
7901
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
7902
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
7867
7903
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');
7869
In the second SET PASSWORD statement, replace host_name with the
7870
name of the server host. This is the same host name that you used
7871
when you assigned the anonymous account passwords.
7905
You can also use a single statement that assigns a password to all
7906
root accounts by using UPDATE to modify the mysql.user table
7907
directly. This method works on any platform:
7908
shell> mysql -u root
7909
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
7910
-> WHERE User = 'root';
7911
mysql> FLUSH PRIVILEGES;
7873
If the user table contains an account with User and Host values of
7874
'root' and '127.0.0.1', use an additional SET PASSWORD statement
7875
to set that account's password:
7876
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
7913
The FLUSH statement causes the server to reread the grant tables.
7914
Without it, the password change remains unnoticed by the server
7915
until you restart it.
7878
7917
To assign passwords to the root accounts using mysqladmin, execute
7879
7918
the following commands:
7880
7919
shell> mysqladmin -u root password "newpwd"
7881
7920
shell> mysqladmin -u root -h host_name password "newpwd"
7883
These commands apply both to Windows and to Unix. In the second
7884
command, replace host_name with the name of the server host. The
7885
double quotes around the password are not always necessary, but
7922
Those commands apply both to Windows and to Unix. The double
7923
quotation marks around the password are not always necessary, but
7886
7924
you should use them if the password contains spaces or other
7887
7925
characters that are special to your command interpreter.
7889
7927
The mysqladmin method of setting the root account passwords does
7890
not set the password for the 'root'@'127.0.0.1' account. To do so,
7891
use SET PASSWORD as shown earlier.
7893
You can also use UPDATE to modify the user table directly. The
7894
following UPDATE statement assigns a password to all root
7896
shell> mysql -u root
7897
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
7898
-> WHERE User = 'root';
7899
mysql> FLUSH PRIVILEGES;
7901
The UPDATE statement applies both to Windows and to Unix.
7903
After the passwords have been set, you must supply the appropriate
7904
password whenever you connect to the server. For example, if you
7905
want to use mysqladmin to shut down the server, you can do so
7928
not work for the 'root'@'127.0.0.1' account. Use the SET PASSWORD
7929
method shown earlier.
7931
After the root passwords have been set, you must supply the
7932
appropriate password whenever you connect as root to the server.
7933
For example, to shut down the server with mysqladmin, use this
7907
7935
shell> mysqladmin -u root -p shutdown
7908
7936
Enter password: (enter root password here)
7912
If you forget your root password after setting it up, Section
7913
B.5.4.1, "How to Reset the Root Password," covers the procedure
7916
To set up additional accounts, you can use the GRANT statement.
7917
For instructions, see Section 5.5.2, "Adding User Accounts."
7938
Assigning Anonymous Account Passwords
7940
The mysql commands in the following instructions include a -p
7941
option based on the assumption that you have set the root account
7942
passwords using the preceding instructions and must specify that
7943
password when connecting to the server.
7945
To assign passwords to the anonymous accounts, connect to the
7946
server as root, then use either SET PASSWORD or UPDATE. Be sure to
7947
encrypt the password using the PASSWORD() function.
7949
To use SET PASSWORD on Windows, do this:
7950
shell> mysql -u root -p
7951
Enter password: (enter root password here)
7952
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
7954
To use SET PASSWORD on Unix, do this:
7955
shell> mysql -u root -p
7956
Enter password: (enter root password here)
7957
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
7958
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');
7960
To set the anonymous-user account passwords with a single UPDATE
7961
statement, do this (on any platform):
7962
shell> mysql -u root -p
7963
Enter password: (enter root password here)
7964
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
7966
mysql> FLUSH PRIVILEGES;
7968
The FLUSH statement causes the server to reread the grant tables.
7969
Without it, the password change remains unnoticed by the server
7970
until you restart it.
7972
Removing Anonymous Accounts
7974
If you prefer to remove any anonymous accounts rather than
7975
assigning them passwords, do so as follows on Windows:
7976
shell> mysql -u root -p
7977
Enter password: (enter root password here)
7978
mysql> DROP USER ''@'localhost';
7980
On Unix, remove the anonymous accounts like this:
7981
shell> mysql -u root -p
7982
Enter password: (enter root password here)
7983
mysql> DROP USER ''@'localhost';
7984
mysql> DROP USER ''@'host_name';
7986
Securing Test Databases
7988
By default, the mysql.db table contains rows that allow access by
7989
any user to the test database and other databases with names that
7990
start with test_. (These rows have an empty User column value,
7991
which for access-checking purposes matches any user name.) This
7992
means that such databases can be used even by accounts that
7993
otherwise possess no privileges. If you want to remove any-user
7994
access to test databases, do so as follows:
7995
shell> mysql -u root -p
7996
Enter password: (enter root password here)
7997
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
7998
mysql> FLUSH PRIVILEGES;
8000
The FLUSH statement causes the server to reread the grant tables.
8001
Without it, the privilege change remains unnoticed by the server
8002
until you restart it.
8004
With the preceding change, only users who have global database
8005
privileges or privileges granted explicitly for the test database
8006
can use it. However, if you do not want the database to exist at
8008
mysql> DROP DATABASE test;
7919
8010
2.14. Environment Variables
added
removed
INSTALL-SOURCE
