2.10. Post-Installation Setup and Testing

MySQL 5.0

2.10. Post-Installation Setup and Testing

After installing MySQL, there are some issues that you should address. For example, on Unix, you should initialize the data directory and create the MySQL grant tables. On all platforms, an important security concern is that the initial accounts in the grant tables have no passwords. You should assign passwords to prevent unauthorized access to the MySQL server. Optionally, you can create time zone tables to enable recognition of named time zones.

The following sections include post-installation procedures that are specific to Windows systems and to Unix systems. Another section, Section 2.10.2.3, “Starting and Troubleshooting the MySQL Server”, applies to all platforms; it describes what to do if you have trouble getting the server to start. Section 2.10.3, “Securing the Initial MySQL Accounts”, also applies to all platforms. You should follow its instructions to make sure that you have properly protected your MySQL accounts by assigning passwords to them.

When you are ready to create additional user accounts, you can find information on the MySQL access control system and account management in Section 5.8, “The MySQL Access Privilege System”, and Section 5.9, “MySQL User Account Management”.

2.10.1. Windows Post-Installation Procedures

On Windows, the data directory and the grant tables do not have to be created. MySQL Windows distributions include the grant tables with a set of preinitialized accounts in the database under the data directory. It is unnecessary to run the mysql_install_db script that is used on Unix. Regarding passwords, if you installed MySQL using the Windows Installation Wizard, you may have already assigned passwords to the accounts. (See Section 2.3.3, “Using the MySQL Installation Wizard”.) Otherwise, use the password-assignment procedure given in Section 2.10.3, “Securing the Initial MySQL Accounts”.

Before setting up passwords, you might want to try running some client programs to make sure that you can connect to the server and that it is operating properly. Make sure that the server is running (see Section 2.3.9, “Starting the Server for the First Time”), and then issue the following commands to verify that you can retrieve information from the server. The output should be similar to what is shown here:

C:\> 
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

C:\> 
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

C:\> 
+------+-------+------+
| host | db    | user |
+------+-------+------+
| %    | test% |      |
+------+-------+------+

If you are running a version of Windows that supports services and you want the MySQL server to run automatically when Windows starts, see Section 2.3.11, “Starting MySQL as a Windows Service”.

2.10.2. Unix Post-Installation Procedures

After installing MySQL on Unix, you need to initialize the grant tables, start the server, and make sure that the server works satisfactorily. You may also wish to arrange for the server to be started and stopped automatically when your system starts and stops. You should also assign passwords to the accounts in the grant tables.

On Unix, the grant tables are set up by the mysql_install_db program. For some installation methods, this program is run for you automatically:

  • If you install MySQL on Linux using RPM distributions, the server RPM runs mysql_install_db.

  • If you install MySQL on Mac OS X using a PKG distribution, the installer runs mysql_install_db.

Otherwise, you will need to run mysql_install_db yourself.

The following procedure describes how to initialize the grant tables (if that has not previously been done) and then start the server. It also suggests some commands that you can use to test whether the server is accessible and working properly. For information about starting and stopping the server automatically, see Section 2.10.2.2, “Starting and Stopping MySQL Automatically”.

After you complete the procedure and have the server running, you should assign passwords to the accounts created by mysql_install_db. Instructions for doing so are given in Section 2.10.3, “Securing the Initial MySQL Accounts”.

In the examples shown here, the server runs under the user ID of the login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server.

  1. Change location into the top-level directory of your MySQL installation, represented here by :

    shell> 
    

    is likely to be something like or . The following steps assume that you are located in this directory.

  2. If necessary, run the mysql_install_db program to set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. You'll need to do this if you used a distribution type for which the installation procedure doesn't run the program for you.

    Typically, mysql_install_db needs to be run only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, mysql_install_db does not overwrite any existing privilege tables, so it should be safe to run in any circumstances.

    To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the or directory:

    shell> 
    shell> 
    

    The mysql_install_db script creates the server's data directory. Under the data directory, it creates directories for the database that holds all database privileges and the database that you can use to test MySQL. The script also creates privilege table entries for and anonymous-user accounts. The accounts have no passwords initially. A description of their initial privileges is given in Section 2.10.3, “Securing the Initial MySQL Accounts”. Briefly, these privileges allow the MySQL user to do anything, and allow anybody to create or use databases with a name of or starting with .

    It is important to make sure that the database directories and files are owned by the login account so that the server has read and write access to them when you run it later. To ensure this, the option should be used as shown if you run mysql_install_db as . Otherwise, you should execute the script while logged in as , in which case you can omit the option from the command.

    mysql_install_db creates several tables in the database, including , , , , , , and others. See Section 5.8, “The MySQL Access Privilege System”, for a complete listing and description of these tables.

    If you don't want to have the database, you can remove it with mysqladmin -u root drop test after starting the server.

    If you have trouble with mysql_install_db at this point, see Section 2.10.2.1, “Problems Running mysql_install_db.

  3. Start the MySQL server:

    shell> 
    

    It is important that the MySQL server be run using an unprivileged (non-) login account. To ensure this, the option should be used as shown if you run as system . Otherwise, you should execute the script while logged in to the system as , in which case you can omit the option from the command.

    Further instructions for running MySQL as an unprivileged user are given in Section 5.7.5, “How to Run MySQL as a Normal User”.

    If you neglected to create the grant tables before proceeding to this step, the following message appears in the error log file when you start the server:

    mysqld: Can't find file: 'host.frm'
    

    If you have other problems starting the server, see Section 2.10.2.3, “Starting and Troubleshooting the MySQL Server”.

  4. Use mysqladmin to verify that the server is running. The following commands provide simple tests to check whether the server is up and responding to connections:

    shell> 
    shell> 
    

    The output from mysqladmin version varies slightly depending on your platform and version of MySQL, but should be similar to that shown here:

    shell> 
    mysqladmin  Ver 14.12 Distrib 5.0.25, for pc-linux-gnu on i686
    Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license
    
    Server version          5.0.25-Max
    Protocol version        10
    Connection              Localhost via UNIX socket
    UNIX socket             /var/lib/mysql/mysql.sock
    Uptime:                 14 days 5 hours 5 min 21 sec
    
    Threads: 1  Questions: 366  Slow queries: 0
    Opens: 0  Flush tables: 1  Open tables: 19
    Queries per second avg: 0.000
    

    To see what else you can do with mysqladmin, invoke it with the option.

  5. Verify that you can shut down the server:

    shell> 
    
  6. Verify that you can start the server again. Do this by using mysqld_safe or by invoking mysqld directly. For example:

    shell> 
    

    If mysqld_safe fails, see Section 2.10.2.3, “Starting and Troubleshooting the MySQL Server”.

  7. Run some simple tests to verify that you can retrieve information from the server. The output should be similar to what is shown here:

    shell> 
    +-----------+
    | Databases |
    +-----------+
    | mysql     |
    | test      |
    +-----------+
    
    shell> 
    Database: mysql
    +---------------------------+
    |          Tables           |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | func                      |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | host                      |
    | proc                      |
    | procs_priv                |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    
    shell> 
    +------+--------+------+
    | host | db     | user |
    +------+--------+------+
    | %    | test   |      |
    | %    | test_% |      |
    +------+--------+------+
    
  8. There is a benchmark suite in the directory (under the MySQL installation directory) that you can use to compare how MySQL performs on different platforms. The benchmark suite is written in Perl. It requires the Perl DBI module that provides a database-independent interface to the various databases, and some other additional Perl modules:

    DBI
    DBD::mysql
    Data::Dumper
    Data::ShowTable
    

    These modules can be obtained from CPAN (http://www.cpan.org/). See also Section 2.14.1, “Installing Perl on Unix”.

    The directory contains the results from many runs against different databases and platforms. To run all tests, execute these commands:

    shell> 
    shell> 
    

    If you don't have the directory, you probably installed MySQL using RPM files other than the source RPM. (The source RPM includes the benchmark directory.) In this case, you must first install the benchmark suite before you can use it. There are separate benchmark RPM files named -i386.rpm that contain benchmark code and data.

    If you have a source distribution, there are also tests in its subdirectory that you can run. For example, to run , execute this command from the top-level directory of your source distribution:

    shell> 
    

    The expected result of the test can be found in the file.

  9. At this point, you should have the server running. However, none of the initial MySQL accounts have a password, so you should assign passwords using the instructions found in Section 2.10.3, “Securing the Initial MySQL Accounts”.

The MySQL 5.0 installation procedure creates time zone tables in the database. However, you must populate the tables manually using the instructions in Section 5.11.8, “MySQL Server Time Zone Support”.

2.10.2.1. Problems Running mysql_install_db

The purpose of the mysql_install_db script is to generate new MySQL privilege tables. It does not overwrite existing MySQL privilege tables, and it does not affect any other data.

If you want to re-create your privilege tables, first stop the mysqld server if it's running. Then rename the directory under the data directory to save it, and then run mysql_install_db. Suppose that your current directory is the MySQL installation directory and that mysql_install_db is located in the directory and the data directory is named . To rename the database and re-run mysql_install_db, use these commands.

shell> 
shell> 

When you run mysql_install_db, you might encounter the following problems:

  • mysql_install_db fails to install the grant tables

    You may find that mysql_install_db fails to install the grant tables and terminates after displaying the following messages:

    Starting mysqld daemon with databases from XXXXXX
    mysqld ended
    

    In this case, you should examine the error log file very carefully. The log should be located in the directory named by the error message and should indicate why mysqld didn't start. If you do not understand what happened, include the log when you post a bug report. See Section 1.8, “How to Report Bugs or Problems”.

  • There is a mysqld process running

    This indicates that the server is running, in which case the grant tables have probably been created already. If so, there is no need to run mysql_install_db at all because it needs to be run only once (when you install MySQL the first time).

  • Installing a second mysqld server does not work when one server is running

    This can happen when you have an existing MySQL installation, but want to put a new installation in a different location. For example, you might have a production installation, but you want to create a second installation for testing purposes. Generally the problem that occurs when you try to run a second server is that it tries to use a network interface that is in use by the first server. In this case, you should see one of the following error messages:

    Can't start server: Bind on TCP/IP port:
    Address already in use
    Can't start server: Bind on unix socket...
    

    For instructions on setting up multiple servers, see Section 5.13, “Running Multiple MySQL Servers on the Same Machine”.

  • You do not have write access to the directory

    If you do not have write access to create temporary files or a Unix socket file in the default location (the directory), an error occurs when you run mysql_install_db or the mysqld server.

    You can specify different locations for the temporary directory and Unix socket file by executing these commands prior to starting mysql_install_db or mysqld, where is the full pathname to some directory for which you have write permission:

    shell> /
    shell> /mysql.sock
    shell> 
    

    Then you should be able to run mysql_install_db and start the server with these commands:

    shell> 
    shell> 
    

    If mysql_install_db is located in the directory, modify the first command to .

    See Section A.4.5, “How to Protect or Change the MySQL Unix Socket File”, and Appendix F, Environment Variables.

There are some alternatives to running the mysql_install_db script provided in the MySQL distribution:

  • If you want the initial privileges to be different from the standard defaults, you can modify mysql_install_db before you run it. However, it is preferable to use and to change the privileges after the grant tables have been set up. In other words, you can run mysql_install_db, and then use to connect to the server as the MySQL user so that you can issue the necessary and statements.

    If you want to install MySQL on several machines with the same privileges, you can put the and statements in a file and execute the file as a script using after running mysql_install_db. For example:

    shell> 
    shell> 
    

    By doing this, you can avoid having to issue the statements manually on each machine.

  • It is possible to re-create the grant tables completely after they have previously been created. You might want to do this if you're just learning how to use and and have made so many modifications after running mysql_install_db that you want to wipe out the tables and start over.

    To re-create the grant tables, remove all the , , and files in the database directory. Then run the mysql_install_db script again.

  • You can start mysqld manually using the option and add the privilege information yourself using mysql:

    shell> 
    shell> 
    

    From mysql, manually execute the SQL commands contained in mysql_install_db. Make sure that you run mysqladmin flush-privileges or mysqladmin reload afterward to tell the server to reload the grant tables.

    Note that by not using mysql_install_db, you not only have to populate the grant tables manually, you also have to create them first.

2.10.2.2. Starting and Stopping MySQL Automatically

Generally, you start the mysqld server in one of these ways:

  • By invoking mysqld directly. This works on any platform.

  • By running the MySQL server as a Windows service. This can be done on versions of Windows that support services (such as NT, 2000, XP, and 2003). The service can be set to start the server automatically when Windows starts, or as a manual service that you start on request. For instructions, see Section 2.3.11, “Starting MySQL as a Windows Service”.

  • By invoking mysqld_safe, which tries to determine the proper options for mysqld and then runs it with those options. This script is used on Unix and Unix-like systems. See Section 5.4.1, “mysqld_safe — MySQL Server Startup Script”.

  • By invoking mysql.server. This script is used primarily at system startup and shutdown on systems that use System V-style run directories, where it usually is installed under the name . The mysql.server script starts the server by invoking mysqld_safe. See Section 5.4.2, “mysql.server — MySQL Server Startup Script”.

  • On Mac OS X, you can install a separate MySQL Startup Item package to enable the automatic startup of MySQL on system startup. The Startup Item starts the server by invoking mysql.server. See Section 2.5, “Installing MySQL on Mac OS X”, for details.

The mysqld_safe and mysql.server scripts and the Mac OS X Startup Item can be used to start the server manually, or automatically at system startup time. mysql.server and the Startup Item also can be used to stop the server.

To start or stop the server manually using the mysql.server script, invoke it with or arguments:

shell> 
shell> 

Before mysql.server starts the server, it changes location to the MySQL installation directory, and then invokes mysqld_safe. If you want the server to run as some specific user, add an appropriate option to the group of the option file, as shown later in this section. (It is possible that you will need to edit mysql.server if you've installed a binary distribution of MySQL in a non-standard location. Modify it to into the proper directory before it runs mysqld_safe. If you do this, your modified version of mysql.server may be overwritten if you upgrade MySQL in the future, so you should make a copy of your edited version that you can reinstall.)

mysql.server stop stops the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown.

To start and stop MySQL automatically on your server, you need to add start and stop commands to the appropriate places in your files.

If you use the Linux server RPM package (.rpm), the mysql.server script is installed in the directory with the name . You need not install it manually. See Section 2.4, “Installing MySQL on Linux”, for more information on the Linux RPM packages.

Some vendors provide RPM packages that install a startup script under a different name such as mysqld.

If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. The script can be found in the directory under the MySQL installation directory or in a MySQL source tree.

To install mysql.server manually, copy it to the directory with the name mysql, and then make it executable. Do this by changing location into the appropriate directory where mysql.server is located and executing these commands:

shell> 
shell> 

Older Red Hat systems use the directory rather than . Adjust the preceding commands accordingly. Alternatively, first create as a symbolic link that points to :

shell> 
shell> 

After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use :

shell> 

On some Linux systems, the following command also seems to be necessary to fully enable the mysql script:

shell> 

On FreeBSD, startup scripts generally should go in . The manual page states that scripts in this directory are executed only if their basename matches the shell filename pattern. Any other files or directories present within the directory are silently ignored. In other words, on FreeBSD, you should install the script as to enable automatic startup.

As an alternative to the preceding setup, some operating systems also use or to start additional services on startup. To start up MySQL using this method, you could append a command like the one following to the appropriate startup file:

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'

For other systems, consult your operating system documentation to see how to install startup scripts.

You can add options for mysql.server in a global file. A typical file might look like this:

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql

[mysql.server]
basedir=/usr/local/mysql

The mysql.server script understands the following options: , , and . If specified, they must be placed in an option file, not on the command line. mysql.server understands only and as command-line arguments.

The following table shows which option groups the server and each startup script read from option files:

Script Option Groups
mysqld , , ]
mysqld_safe , ,
mysql.server , ,

] means that groups with names like and are read by servers having versions 4.1.x, 5.0.x, and so forth. This feature can be used to specify options that can be read only by servers within a given release series.

For backward compatibility, mysql.server also reads the group and mysqld_safe also reads the group. However, you should update your option files to use the and groups instead when using MySQL 5.0.

See Section 4.3.2, “Using Option Files”.

2.10.2.3. Starting and Troubleshooting the MySQL Server

This section provides troubleshooting suggestions for problems starting the server on Unix. If you are using Windows, see Section 2.3.13, “Troubleshooting a MySQL Installation Under Windows”.

If you have problems starting the server, here are some things to try:

  • Check the error log to see why the server does not start.

  • Specify any special options needed by the storage engines you are using.

  • Make sure that the server knows where to find the data directory.

  • Make sure that the server can access the data directory. The ownership and permissions of the data directory and its contents must be set such that the server can read and modify them.

  • Verify that the network interfaces the server wants to use are available.

Some storage engines have options that control their behavior. You can create a file and specify startup options for the engines that you plan to use. If you are going to use storage engines that support transactional tables (, , ), be sure that you have them configured the way you want before starting the server:

Storage engines will use default option values if you specify none, but it is recommended that you review the available options and specify explicit values for those for which the defaults are not appropriate for your installation.

When the mysqld server starts, it changes location to the data directory. This is where it expects to find databases and where it expects to write log files. The server also writes the pid (process ID) file in the data directory.

The data directory location is hardwired in when the server is compiled. This is where the server looks for the data directory by default. If the data directory is located somewhere else on your system, the server will not work properly. You can determine what the default path settings are by invoking mysqld with the and options.

If the default locations don't match the MySQL installation layout on your system, you can override them by specifying options to mysqld or mysqld_safe on the command line or in an option file.

To specify the location of the data directory explicitly, use the option. However, normally you can tell mysqld the location of the base directory under which MySQL is installed and it looks for the data directory there. You can do this with the option.

To check the effect of specifying path options, invoke mysqld with those options followed by the and options. For example, if you change location into the directory where mysqld is installed and then run the following command, it shows the effect of starting the server with a base directory of :

shell> 

You can specify other options such as as well, but and must be the last options.

Once you determine the path settings you want, start the server without and .

If mysqld is currently running, you can find out what path settings it is using by executing this command:

shell> 

Or:

shell>  variables

is the name of the MySQL server host.

If you get (which means ) when starting mysqld, this means that the privileges of the data directory or its contents do not allow the server access. In this case, you change the permissions for the involved files and directories so that the server has the right to use them. You can also start the server as , but this raises security issues and should be avoided.

On Unix, change location into the data directory and check the ownership of the data directory and its contents to make sure the server has access. For example, if the data directory is , use this command:

shell> 

If the data directory or its files or subdirectories are not owned by the login account that you use for running the server, change their ownership to that account. If the account is named , use these commands:

shell> 
shell> 

If the server fails to start up correctly, check the error log. Log files are located in the data directory (typically on Windows, for a Unix binary distribution, and for a Unix source distribution). Look in the data directory for files with names of the form .err and .log, where is the name of your server host. Then examine the last few lines of these files. On Unix, you can use to display them:

shell> .err
shell> .log

The error log should contain information that indicates why the server couldn't start. For example, you might see something like this in the log:

000729 14:50:10  bdb:  Recovery function for LSN 1 27595 failed
000729 14:50:10  bdb:  warning: ./test/t1.db: No such file or directory
000729 14:50:10  Can't init databases

This means that you did not start mysqld with the option and Berkeley DB found something wrong with its own log files when it tried to recover your databases. To be able to continue, you should move the old Berkeley DB log files from the database directory to some other place, where you can later examine them. The log files are named in sequence beginning with , where the number increases over time.

If you are running mysqld with table support and mysqld dumps core at startup, this could be due to problems with the recovery log. In this case, you can try starting mysqld with . If that helps, you should remove all log files from the data directory and try starting mysqld again without the option.

If either of the following errors occur, it means that some other program (perhaps another mysqld server) is using the TCP/IP port or Unix socket file that mysqld is trying to use:

Can't start server: Bind on TCP/IP port: Address already in use
Can't start server: Bind on unix socket...

Use ps to determine whether you have another mysqld server running. If so, shut down the server before starting mysqld again. (If another server is running, and you really want to run multiple servers, you can find information about how to do so in Section 5.13, “Running Multiple MySQL Servers on the Same Machine”.)

If no other server is running, try to execute the command . (The default MySQL port number is 3306.) Then press Enter a couple of times. If you don't get an error message like , some other program is using the TCP/IP port that mysqld is trying to use. You'll need to track down what program this is and disable it, or else tell mysqld to listen to a different port with the option. In this case, you'll also need to specify the port number for client programs when connecting to the server via TCP/IP.

Another reason the port might be inaccessible is that you have a firewall running that blocks connections to it. If so, modify the firewall settings to allow access to the port.

If the server starts but you can't connect to it, you should make sure that you have an entry in that looks like this:

127.0.0.1       localhost

This problem occurs only on systems that do not have a working thread library and for which MySQL must be configured to use MIT-pthreads.

If you cannot get mysqld to start, you can try to make a trace file to find the problem by using the option. See Section E.1.2, “Creating Trace Files”.

2.10.3. Securing the Initial MySQL Accounts

Part of the MySQL installation process is to set up the database that contains the grant tables:

  • Windows distributions contain preinitialized grant tables that are installed automatically.

  • On Unix, the grant tables are populated by the mysql_install_db program. Some installation methods run this program for you. Others require that you execute it manually. For details, see Section 2.10.2, “Unix Post-Installation Procedures”.

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

  • Accounts with the username are created. These are superuser accounts that can do anything. The initial account passwords are empty, so anyone can connect to the MySQL server as without a password — and be granted all privileges.

    • On Windows, one account is created; this account allows connecting from the local host only. The Windows installer will optionally create an account allowing for connections from any host only if the user selects the Enable root access from remote machines option during installation.

    • On Unix, both accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of for one of the accounts, or the actual hostname or IP number for the other.

  • Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

    • On Windows, one anonymous account is for connections from the local host. It has all privileges, just like the accounts. The other is for connections from any host and has all privileges for the database and for other databases with names that start with .

    • On Unix, both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of for one of the accounts, or the actual hostname or IP number for the other. These accounts have all privileges for the database and for other databases with names that start with .

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:

  • If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.

  • You should assign a password to each MySQL account.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the accounts. Replace “” in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all.

You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Anonymous Account Password Assignment

To assign passwords to the anonymous accounts, connect to the server as and then use either or . In either case, be sure to encrypt the password using the function.

To use on Windows, do this:

shell> 
mysql> ');
mysql> ');

To use on Unix, do this:

shell> 
mysql> ');
mysql> ' = PASSWORD('');

In the second statement, replace with the name of the server host. This is the name that is specified in the column of the non- record for in the table. If you don't know what hostname this is, issue the following statement before using :

mysql> 

Look for the record that has in the column and something other than in the column. Then use that value in the second statement.

The other way to assign passwords to the anonymous accounts is by using to modify the table directly. Connect to the server as and issue an statement that assigns a value to the column of the appropriate table records. The procedure is the same for Windows and Unix. The following statement assigns a password to both anonymous accounts at once:

shell> 
mysql> ')
    ->     
mysql> 

After you update the passwords in the table directly using , you must tell the server to re-read the grant tables with . Otherwise, the change goes unnoticed until you restart the server.

Anonymous Account Removal

If you prefer to remove the anonymous accounts instead, do so as follows:

shell> 
mysql> 
mysql> 

The statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as , do this instead:

shell> 
mysql> 
mysql> 

That account allows anonymous access but has full privileges, so removing it improves security.

Account Password Assignment

You can assign passwords to the accounts in several ways. The following discussion demonstrates three methods:

  • Use the statement

  • Use the mysqladmin command-line client program

  • Use the statement

To assign passwords using , connect to the server as and issue two statements. Be sure to encrypt the password using the function.

For Windows, do this:

shell> 
mysql> ');
mysql> ');

For Unix, do this:

shell> 
mysql> ');
mysql> ' = PASSWORD('');

In the second statement, replace with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords.

To assign passwords to the accounts using mysqladmin, execute the following commands:

shell> "
shell>  password ""

These commands apply both to Windows and to Unix. In the second command, replace with the name of the server host. The double quotes 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.

You can also use to modify the table directly. The following statement assigns a password to both accounts at once:

shell> 
mysql> ')
    ->     
mysql> 

The statement applies both to Windows and to Unix.

After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command:

shell> 
Enter password: 

Note: If you forget your password after setting it up, Section A.4.1, “How to Reset the Root Password”, covers the procedure for resetting it.

To set up additional accounts, you can use the statement. For instructions, see Section 5.9.2, “Adding New User Accounts to MySQL”.