MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The MySQL installation process involves initializing the data
directory, including the grant tables in the
mysql
system database that define MySQL
accounts. For details, see
Section 2.10.1, “Initializing the Data Directory”.
This section describes how to assign passwords to the initial accounts created during the MySQL installation procedure, if you have not already done so.
The mysql.user
grant table defines the initial
MySQL user accounts and their access privileges:
Some accounts have the user name root
.
These are superuser accounts that have all privileges and can
do anything. If these root
accounts have
empty passwords, anyone can connect to the MySQL server as
root
without a
password and be granted all privileges.
On Windows, root
accounts are created
that permit connections from the local host only.
Connections can be made by specifying the host name
localhost
, the IP address
127.0.0.1
, or the IPv6 address
::1
. If the user selects the
Enable root access from remote
machines option during installation, the
Windows installer creates another root
account that permits connections from any host.
On Unix, each root
account permits
connections from the local host. Connections can be made
by specifying the host name localhost
,
the IP address 127.0.0.1
, the IPv6
address ::1
, or the actual host name or
IP address.
An attempt to connect to the host 127.0.0.1
normally resolves to the localhost
account.
However, this fails if the server is run with
skip_name_resolve
enabled, so
the 127.0.0.1
account is useful in that
case. The ::1
account is used for IPv6
connections.
If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.
On Windows, there is one anonymous account that permits
connections from the local host. Connections can be made
by specifying a host name of localhost
.
On Unix, each anonymous account permits connections from
the local host. Connections can be made by specifying a
host name of localhost
for one of the
accounts, or the actual host name or IP address for the
other.
The 'root'@'localhost'
account also has
a row in the mysql.proxies_priv
table
that enables granting the
PROXY
privilege for
''@''
, that is, for all users and all
hosts. This enables root
to set up
proxy users, as well as to delegate to other accounts the
authority to set up proxy users. See
Section 6.2.12, “Proxy Users”.
To display which accounts exist in the
mysql.user
system table and check whether their
passwords are empty, use the following statement:
mysql> SELECT User, Host, Password FROM mysql.user;
+------+--------------------+----------+
| User | Host | Password |
+------+--------------------+----------+
| root | localhost | |
| root | myhost.example.com | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | myhost.example.com | |
+------+--------------------+----------+
This output indicates that there are several
root
and anonymous-user accounts, none of which
have passwords. The output might differ on your system, but the
presence of accounts with empty passwords means that your MySQL
installation is unprotected until you do something about it:
Assign a password to each MySQL root
account that does not have one.
To prevent clients from connecting as anonymous users without a password, either assign a password to each anonymous account or remove the accounts.
In addition, the mysql.db
table contains rows
that permit all accounts to access the test
database and other databases with names that start with
test_
. This is true even for accounts that
otherwise have no special privileges such as the default anonymous
accounts. This is convenient for testing but inadvisable on
production servers. Administrators who want database access
restricted only to accounts that have permissions granted
explicitly for that purpose should remove these
mysql.db
table rows.
The following instructions describe how to set up passwords for
the initial MySQL accounts, first for the root
accounts, then for the anonymous accounts. The instructions also
cover how to remove anonymous accounts, should you prefer not to
permit anonymous access at all, and describe how to remove
permissive access to test databases. Replace
new_password
in the examples with the
password that you want to use. Replace
host_name
with the name of the server
host. You can determine this name from the output of the preceding
SELECT
statement. For the output
shown, host_name
is
myhost.example.com
.
You need not remove anonymous entries in the
mysql.proxies_priv
table, which are used to
support proxy users. See Section 6.2.12, “Proxy Users”.
For additional information about setting passwords, see
Section 6.2.9, “Assigning Account Passwords”. If you forget your
root
password after setting it, see
Section B.3.3.2, “How to Reset the Root Password”.
To set up additional accounts, see Section 6.2.7, “Adding Accounts, Assigning Privileges, and Dropping Accounts”.
You might want to defer setting the passwords until later, to avoid the need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.
Alternative means for performing the process described in this section:
On Windows, you can perform the process during installation with MySQL Installer (see Section 2.3.3, “MySQL Installer for Windows”).
On all platforms, the MySQL distribution includes mysql_secure_installation, a command-line utility that automates much of the process of securing a MySQL installation.
On all platforms, MySQL Workbench is available and offers the ability to manage user accounts (see Chapter 25, MySQL Workbench ).
A root
account password can be set several
ways. The following discussion demonstrates three methods:
Use the SET PASSWORD
statement
Use the UPDATE
statement
Use the mysqladmin command-line client program
To assign passwords using SET
PASSWORD
, connect to the server as
root
and issue a SET
PASSWORD
statement for each root
account listed in the mysql.user
system
table.
For Windows, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'::1' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'%' = PASSWORD('
new_password
');
The last statement is unnecessary if the
mysql.user
table has no
root
account with a host value of
%
.
For Unix, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'::1' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR 'root'@'
host_name
' = PASSWORD('new_password
');
You can also use a single statement that assigns a password to
all root
accounts by using
UPDATE
to modify the
mysql.user
table directly. This method works
on any platform:
shell>mysql -u root
mysql>UPDATE mysql.user SET Password = PASSWORD('
->new_password
')WHERE User = 'root';
mysql>FLUSH PRIVILEGES;
The FLUSH
statement causes the
server to reread the grant tables. Without it, the password
change remains unnoticed by the server until you restart it.
To assign passwords to the root
accounts
using mysqladmin, execute the following
commands:
shell>mysqladmin -u root password "
shell>new_password
"mysqladmin -u root -h
host_name
password "new_password
"
Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.
The mysqladmin method of setting the
root
account passwords does not work for the
'root'@'127.0.0.1'
or
'root'@'::1'
account. Use the
SET PASSWORD
method shown
earlier.
After the root
passwords have been set, you
must supply the appropriate password whenever you connect as
root
to the server. For example, to shut down
the server with mysqladmin, use this command:
shell>mysqladmin -u root -p shutdown
Enter password:(enter root password here)
The mysql commands in the following
instructions include a -p
option based on the
assumption that you have assigned the root
account passwords using the preceding instructions and must
specify that password when connecting to the server.
To assign passwords to the anonymous accounts, connect to the
server as root
, then use either
SET PASSWORD
or
UPDATE
.
To use SET PASSWORD
on Windows,
do this:
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>SET PASSWORD FOR ''@'localhost' = PASSWORD('
new_password
');
To use SET PASSWORD
on Unix, do
this:
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>SET PASSWORD FOR ''@'localhost' = PASSWORD('
mysql>new_password
');SET PASSWORD FOR ''@'
host_name
' = PASSWORD('new_password
');
To set the anonymous-user account passwords with a single
UPDATE
statement, do this (on any
platform):
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>UPDATE mysql.user SET Password = PASSWORD('
->new_password
')WHERE User = '';
mysql>FLUSH PRIVILEGES;
The FLUSH
statement causes the
server to reread the grant tables. Without it, the password
change remains unnoticed by the server until you restart it.
If you prefer to remove any anonymous accounts rather than assigning them passwords, do so as follows on Windows:
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>DROP USER ''@'localhost';
On Unix, remove the anonymous accounts like this:
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>DROP USER ''@'localhost';
mysql>DROP USER ''@'
host_name
';
By default, the mysql.db
table contains rows
that permit access by any user to the test
database and other databases with names that start with
test_
. (These rows have an empty
User
column value, which for access-checking
purposes matches any user name.) This means that such databases
can be used even by accounts that otherwise possess no
privileges. If you want to remove any-user access to test
databases, do so as follows:
shell>mysql -u root -p
Enter password:(enter root password here)
mysql>DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql>FLUSH PRIVILEGES;
The FLUSH
statement causes the
server to reread the grant tables. Without it, the privilege
change remains unnoticed by the server until you restart it.
With the preceding change, only users who have global database
privileges or privileges granted explicitly for the
test
database can use it. However, if you
prefer that the database not exist at all, drop it:
mysql> DROP DATABASE test;