2.10.1 Postinstallation Procedures for Unix-like Systems Problems Running mysql_install_db

After installing MySQL on a Unix-like system, you must initialize the data directory (including the tables in the mysql system database), start the server, and make sure that the server works satisfactorily.

On a Unix-like system, the grant tables in the mysql database are set up by the mysql_install_db program. For some installation methods, this program is run for you automatically if an existing mysql database cannot be found:

For other platforms and installation types, including installation from generic binary and source distributions, you must initialize the data directory yourself.

The following procedure describes how to initialize the data directory (if that has not previously been done) and start the server. It also suggests some commands that you can use to test whether the server is accessible and working properly.

In the examples shown here, the server runs under the user ID of the mysql 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. For information about creating the account, see Creating a mysql System User and Group, in Section 2.2, “Installing MySQL on Unix/Linux Using Generic Binaries”.

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

    shell> cd BASEDIR

    BASEDIR is the installation directory for your MySQL instance. It is likely to be something like /usr/local/mysql or /usr/local. The following steps assume that you have changed location to this directory.

    You will find several files and subdirectories in the BASEDIR directory. The most important for installation purposes are the bin and scripts subdirectories, which contain the server as well as client and utility programs.

    To make it more convenient to invoke programs installed in the bin directory under the installation directory, you can add that directory to your PATH environment variable setting. That enables your shell (comand interpreter) to find MySQL programs properly, so that you can run a program by typing only its name, not its entire path name. See Section 4.2.10, “Setting Environment Variables”.

  2. If necessary, ensure that the distribution contents are accessible to mysql. If you installed the distribution as mysql, no further action is required. If you installed the distribution as root, its contents will be owned by root. This is suitable for most of the installation, with the exception that the data directory must be owned by mysql. To accomplish this, run the following commands as root in the installation directory. For some distribution types, the data directory might be named var rather than data; adjust the second command accordingly.

    shell> chown -R root .
    shell> chown -R mysql data
    shell> chgrp -R mysql .
  3. If necessary, initialize the data directory, including the mysql database containing the initial MySQL grant tables that determine how users are permitted to connect to the server. You must do this if you used a distribution type for which the installation procedure does not initialize the data directory for you.

    shell> scripts/mysql_install_db --user=mysql

    Typically, data directory initialization need be done only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, the command that initializes the data directory does not overwrite any existing privilege tables, so it should be safe to run in any circumstances.

    It might be necessary to specify other options such as --basedir or --datadir if mysql_install_db does not identify the correct locations for the installation directory or data directory. For example:

    shell> scripts/mysql_install_db --user=mysql \
             --basedir=/opt/mysql/mysql \

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

    The mysql_install_db command creates the server's data directory with mysql as the owner. Under the data directory, it creates directories for the mysql database that holds the grant tables and the test database that you can use to test MySQL. The program also creates privilege table entries for the initial account or accounts. The accounts have no passwords initially. Section 2.10.2, “Securing the Initial MySQL Accounts”, describes the initial privileges. Briefly, these privileges permit the MySQL root user to do anything, and permit anybody to create or use databases with a name of test or starting with test_. See Section 6.2, “The MySQL Access Privilege System”, for a complete listing and description of the grant tables.

    If you do not want to have the test database, you can remove it after starting the server, using the instructions in Section 2.10.2, “Securing the Initial MySQL Accounts”.

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

  4. If the plugin directory (the directory named by the plugin_dir system variable) is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE. This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

  5. If you installed MySQL using a source distribution, you may want to optionally copy one of the provided configuration files from the support-files directory into your /etc directory. There are different sample configuration files for different use cases, server types, and CPU and RAM configurations. If you want to use one of these standard files, you should copy it to /etc/my.cnf, or /etc/mysql/my.cnf and edit and check the configuration before starting your MySQL server for the first time.

    If you do not copy one of the standard configuration files, the MySQL server will be started with the default settings.

    If you want MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the mysql.server script itself, and in Section 2.10.3, “Starting and Stopping MySQL Automatically”.

  6. Start the MySQL server:

    shell> bin/mysqld_safe --user=mysql &

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

    For further instructions for running MySQL as an unprivileged user, see Section 6.1.5, “How to Run MySQL as a Normal User”.

    If the command fails immediately and prints mysqld ended, look for information in the error log (which by default is the host_name.err file in the data directory).

    If the server is unable to access the data directory it starts or read the grant tables in the mysql database, it writes a message to its error log. Such problems can occur if you neglected to create the grant tables by initializing the data directory before proceeding to this step, or if you ran the command that initializes the data directory without the --user option. Remove the data directory and run the command with the --user option as described previously.

    If you have other problems starting the server, see Section 2.10.4, “Troubleshooting Problems Starting the MySQL Server”. For more information about mysqld_safe, see Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.

  7. 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> bin/mysqladmin version
    shell> bin/mysqladmin variables

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

    shell> bin/mysqladmin version
    mysqladmin  Ver 14.12 Distrib 5.5.44, for pc-linux-gnu on i686
    Server version          5.5.44
    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 --help option.

  8. Verify that you can shut down the server:

    shell> bin/mysqladmin -u root shutdown
  9. Verify that you can start the server again. Do this by using mysqld_safe or by invoking mysqld directly. For example:

    shell> bin/mysqld_safe --user=mysql &

    If mysqld_safe fails, see Section 2.10.4, “Troubleshooting Problems Starting the MySQL Server”.

  10. 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> bin/mysqlshow
    |     Databases      |
    | information_schema |
    | mysql              |
    | test               |
    shell> bin/mysqlshow mysql
    Database: mysql
    |          Tables           |
    | columns_priv              |
    | db                        |
    | event                     |
    | func                      |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | host                      |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | servers                   |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    shell> bin/mysql -e "SELECT Host,Db,User FROM db" mysql
    | host | db     | user |
    | %    | test   |      |
    | %    | test_% |      |
  11. At this point, you should have the server running. However, none of the initial MySQL accounts have a password, and the server permits permissive access to test databases. To tighten security, follow the instructions in Section 2.10.2, “Securing the Initial MySQL Accounts”.

The MySQL 5.5 installation procedure creates time zone tables in the mysql database but does not populate them. To do so, use the instructions in Section 10.6, “MySQL Server Time Zone Support”.

You can set up new accounts using the bin/mysql_setpermission script if you install the DBI and DBD::mysql Perl modules. See Section 4.6.13, “mysql_setpermission — Interactively Set Permissions in Grant Tables”. For Perl module installation instructions, see Section 2.13, “Perl Installation Notes”.

If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the bin/mysqlaccess script at approximately line 18. Search for a line that looks like this:

$MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.