2.9 Postinstallation Setup and Testing

This section discusses tasks that you should perform after installing MySQL:

When you are ready to create additional user accounts, you can find information on the MySQL access control system and account management in *note access-control::.

 File: manual.info.tmp, Node: data-directory-initialization, Next: starting-server, Prev: postinstallation, Up: postinstallation

2.9.1 Initializing the Data Directory

After MySQL is installed, the data directory must be initialized, including the tables in the 'mysql' system database:

This section describes how to initialize the data directory manually for MySQL installation methods for which data directory initialization is not automatic. For some suggested commands that enable testing whether the server is accessible and working properly, see *note testing-server::.

Data Directory Initialization Overview

In the examples shown here, the server is intended to run under the user ID of the 'mysql' login account. Either create the account if it does not exist (see *note binary-installation-createsysuser::), or substitute the name of a different existing login account that you plan to use for running the server.

  1. Change location to the top-level directory of your MySQL installation, which is typically '/usr/local/mysql' (adjust the path name for your system as necessary):

      cd /usr/local/mysql

    Within this directory are several files and subdirectories, including the 'bin' subdirectory that contains the server as well as client and utility programs.

  2. The 'secure_file_priv' system variable limits import and export operations to a specific directory. Create a directory whose location can be specified as the value of that variable:

      mkdir mysql-files

    Grant directory user and group ownership to the 'mysql' user and 'mysql' group, and set the directory permissions appropriately:

      chown mysql:mysql mysql-files
  chmod 750 mysql-files

  3. Use the server to 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. For example:

      bin/mysqld --initialize --user=mysql

    For important information about the command, especially regarding command options you might use, see note data-directory-initialization-procedure::. For details about how the server performs initialization, see note data-directory-initialization-server-actions::.

    Typically, data directory initialization need be done only after you first install MySQL. (For upgrades to an existing installation, perform the upgrade procedure instead; see *note upgrading::.) However, the command that initializes the data directory does not overwrite any existing 'mysql' database tables, so it is safe to run in any circumstances.

    Note:

    Initialization of the data directory might fail if required system libraries are missing. For example, you might see an error like this:

      bin/mysqld: error while loading shared libraries:
  libnuma.so.1: cannot open shared object file:
  No such file or directory

    If this happens, you must install the missing libraries manually or with your system's package manager. Then retry the data directory initialization command.

  4. If you want to deploy the server with automatic support for secure connections, use the *note 'mysql_ssl_rsa_setup': mysql-ssl-rsa-setup. utility to create default SSL and RSA files:

      bin/mysql_ssl_rsa_setup

    For more information, see *note mysql-ssl-rsa-setup::.

  5. In the absence of any option files, the server starts with its default settings. (See note server-configuration-defaults::.) To explicitly specify options that the MySQL server should use at startup, put them in an option file such as '/etc/my.cnf' or '/etc/mysql/my.cnf'. (See note option-files::.) For example, you can use an option file to set the 'secure_file_priv' system variable.

  6. To arrange for MySQL to start without manual intervention at system boot time, see *note automatic-start::.

  7. Data directory initialization creates time zone tables in the 'mysql' database but does not populate them. To do so, use the instructions in *note time-zone-support::.

Data Directory Initialization Procedure

Change location to the top-level directory of your MySQL installation, which is typically '/usr/local/mysql' (adjust the path name for your system as necessary):

 cd /usr/local/mysql

To initialize the data directory, invoke *note 'mysqld': mysqld. with the '--initialize' or '--initialize-insecure' option, depending on whether you want the server to generate a random initial password for the ''root'@'localhost'' account, or to create that account with no password:

For instructions on assigning a new ''root'@'localhost'' password, see *note data-directory-initialization-password-assignment::.

Note:

The server writes any messages (including any initial password) to its standard error output. This may be redirected to the error log, so look there if you do not see the messages on your screen. For information about the error log, including where it is located, see *note error-log::.

On Windows, use the '--console' option to direct messages to the console.

On Unix and Unix-like systems, it is important for the database directories and files to be 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, start *note 'mysqld': mysqld. from the system 'root' account and include the '--user' option as shown here:

 bin/mysqld --initialize --user=mysql
 bin/mysqld --initialize-insecure --user=mysql

Alternatively, execute *note 'mysqld': mysqld. while logged in as 'mysql', in which case you can omit the '--user' option from the command.

On Windows, use one of these commands:

 bin\mysqld --initialize --console
 bin\mysqld --initialize-insecure --console

Note:

Data directory initialization might fail if required system libraries are missing. For example, you might see an error like this:

 bin/mysqld: error while loading shared libraries:
 libnuma.so.1: cannot open shared object file:
 No such file or directory

If this happens, you must install the missing libraries manually or with your system's package manager. Then retry the data directory initialization command.

It might be necessary to specify other options such as '--basedir' or '--datadir' if *note 'mysqld': mysqld. cannot identify the correct locations for the installation directory or data directory. For example (enter the command on a single line):

 bin/mysqld --initialize --user=mysql
   --basedir=/opt/mysql/mysql
   --datadir=/opt/mysql/mysql/data

Alternatively, put the relevant option settings in an option file and pass the name of that file to *note 'mysqld': mysqld. For Unix and Unix-like systems, suppose that the option file name is '/opt/mysql/mysql/etc/my.cnf'. Put these lines in the file:

 [mysqld]
 basedir=/opt/mysql/mysql
 datadir=/opt/mysql/mysql/data

Then invoke *note 'mysqld': mysqld. as follows (enter the command on a single line, with the '--defaults-file' option first):

 bin/mysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf
   --initialize --user=mysql

On Windows, suppose that 'C:.ini' contains these lines:

 [mysqld]
 basedir=C:\\Program Files\\MySQL\\MySQL Server 5.7
 datadir=D:\\MySQLdata

Then invoke *note 'mysqld': mysqld. as follows (again, you should enter the command on a single line, with the '--defaults-file' option first):

 bin\mysqld --defaults-file=C:\my.ini
    --initialize --console

Important:

When initializing the data directory, you should not specify any options other than those used for setting directory locations such as '--basedir' or '--datadir', and the '--user' option if needed. Options to be employed by the MySQL server during normal use can be set when restarting it following initialization. See the description of the '--initialize' option for further information.

Server Actions During Data Directory Initialization

Note:

The data directory initialization sequence performed by the server does not substitute for the actions performed by note 'mysql_secure_installation': mysql-secure-installation. and note 'mysql_ssl_rsa_setup': mysql-ssl-rsa-setup. See note mysql-secure-installation::, and note mysql-ssl-rsa-setup::.

When invoked with the '--initialize' or '--initialize-insecure' option, *note 'mysqld': mysqld. performs the following actions during the data directory initialization sequence:

  1. The server checks for the existence of the data directory as follows:

    * If no data directory exists, the server creates it.

* If the data directory exists but is not empty (that is, it
  contains files or subdirectories), the server exits after
  producing an error message:

       [ERROR] --initialize specified but the data directory exists. Aborting.

  In this case, remove or rename the data directory and try
  again.

  As of MySQL 5.7.11, an existing data directory is permitted to
  be nonempty if every entry either has a name that begins with
  a period ('.') or is named using an '--ignore-db-dir' option.

  *Note*:

  Avoid the use of the '--ignore-db-dir' option, which has been
  deprecated since MySQL 5.7.16.

  2. Within the data directory, the server creates the 'mysql' system database and its tables, including the grant tables, time zone tables, and server-side help tables. See *note system-schema::.

  3. The server initializes the system tablespace and related data structures needed to manage *note 'InnoDB': innodb-storage-engine. tables.

    Note:

    After note 'mysqld': mysqld. sets up the 'InnoDB' system tablespace, certain changes to tablespace characteristics require setting up a whole new instance. Qualifying changes include the file name of the first file in the system tablespace and the number of undo logs. If you do not want to use the default values, make sure that the settings for the 'innodb_data_file_path' and 'innodb_log_file_size' configuration parameters are in place in the MySQL configuration file before running note 'mysqld': mysqld. Also make sure to specify as necessary other parameters that affect the creation and location of 'InnoDB' files, such as 'innodb_data_home_dir' and 'innodb_log_group_home_dir'.

    If those options are in your configuration file but that file is not in a location that MySQL reads by default, specify the file location using the '--defaults-extra-file' option when you run *note 'mysqld': mysqld.

  4. The server creates a ''root'@'localhost'' superuser account and other reserved accounts (see *note reserved-accounts::). Some reserved accounts are locked and cannot be used by clients, but ''root'@'localhost'' is intended for administrative use and you should assign it a password.

    Server actions with respect to a password for the ''root'@'localhost'' account depend on how you invoke it:

    * With '--initialize' but not '--initialize-insecure', the
  server generates a random password, marks it as expired, and
  writes a message displaying the password:

       [Warning] A temporary password is generated for root@localhost:
       iTag*AfrH5ej

* With '--initialize-insecure', (either with or without
  '--initialize' because '--initialize-insecure' implies
  '--initialize'), the server does not generate a password or
  mark it expired, and writes a warning message:

       [Warning] root@localhost is created with an empty password ! Please
       consider switching off the --initialize-insecure option.

    For instructions on assigning a new ''root'@'localhost'' password, see *note data-directory-initialization-password-assignment::.

  5. The server populates the server-side help tables used for the note 'HELP': help. statement (see note help::). The server does not populate the time zone tables. To do so manually, see *note time-zone-support::.

  6. If the 'init_file' system variable was given to name a file of SQL statements, the server executes the statements in the file. This option enables you to perform custom bootstrapping sequences.

    When the server operates in bootstrap mode, some functionality is unavailable that limits the statements permitted in the file. These include statements that relate to account management (such as note 'CREATE USER': create-user. or note 'GRANT': grant.), replication, and global transaction identifiers.

  7. The server exits.

Post-Initialization root Password Assignment

After you initialize the data directory by starting the server with '--initialize' or '--initialize-insecure', start the server normally (that is, without either of those options) and assign the ''root'@'localhost'' account a new password:

  1. Start the server. For instructions, see *note starting-server::.

  2. Connect to the server:

    * If you used '--initialize' but not '--initialize-insecure' to
  initialize the data directory, connect to the server as
  'root':

       mysql -u root -p

  Then, at the password prompt, enter the random password that
  the server generated during the initialization sequence:

       Enter password: (ENTER THE RANDOM ROOT PASSWORD HERE)

  Look in the server error log if you do not know this password.

* If you used '--initialize-insecure' to initialize the data
  directory, connect to the server as 'root' without a password:

       mysql -u root --skip-password

  3. After connecting, use an *note 'ALTER USER': alter-user. statement to assign a new 'root' password:

      ALTER USER 'root'@'localhost' IDENTIFIED BY 'ROOT-PASSWORD';

See also *note default-privileges::.

Note:

Attempts to connect to the host '127.0.0.1' normally resolve to the 'localhost' account. However, this fails if the server is run with 'skip_name_resolve' enabled. If you plan to do that, make sure that an account exists that can accept a connection. For example, to be able to connect as 'root' using '--host=127.0.0.1' or '--host=::1', create these accounts:

 CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'ROOT-PASSWORD';
 CREATE USER 'root'@'::1' IDENTIFIED BY 'ROOT-PASSWORD';

It is possible to put those statements in a file to be executed using the 'init_file' system variable, as discussed in *note data-directory-initialization-server-actions::.

 File: manual.info.tmp, Node: starting-server, Next: testing-server, Prev: data-directory-initialization, Up: postinstallation

2.9.2 Starting the Server

This section describes how start the server on Unix and Unix-like systems. (For Windows, see note windows-server-first-start::.) For some suggested commands that you can use to test whether the server is accessible and working properly, see note testing-server::.

Start the MySQL server like this if your installation includes *note 'mysqld_safe': mysqld-safe.:

 $> bin/mysqld_safe --user=mysql &

Note:

For Linux systems on which MySQL is installed using RPM packages, server startup and shutdown is managed using systemd rather than note 'mysqld_safe': mysqld-safe, and note 'mysqld_safe': mysqld-safe. is not installed. See *note using-systemd::.

Start the server like this if your installation includes systemd support:

 $> systemctl start mysqld

Substitute the appropriate service name if it differs from 'mysqld' (for example, 'mysql' on SLES systems).

It is important that the MySQL server be run using an unprivileged (non-'root') login account. To ensure this, run *note 'mysqld_safe': mysqld-safe. as 'root' and 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 *note changing-mysql-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.

If you have other problems starting the server, see note starting-server-troubleshooting::. For more information about note 'mysqld_safe': mysqld-safe, see note mysqld-safe::. For more information about systemd support, see note using-systemd::.

 File: manual.info.tmp, Node: starting-server-troubleshooting, Prev: starting-server, Up: starting-server

2.9.2.1 Troubleshooting Problems Starting the MySQL Server ..........................................................

This section provides troubleshooting suggestions for problems starting the server. For additional suggestions for Windows systems, see *note windows-troubleshooting::.

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

 File: manual.info.tmp, Node: testing-server, Next: default-privileges, Prev: starting-server, Up: postinstallation

2.9.3 Testing the Server

After the data directory is initialized and you have started the server, perform some simple tests to make sure that it works satisfactorily. This section assumes that your current location is the MySQL installation directory and that it has a 'bin' subdirectory containing the MySQL programs used here. If that is not true, adjust the command path names accordingly.

Alternatively, add the 'bin' directory to your 'PATH' environment variable setting. That enables your shell (command interpreter) to find MySQL programs properly, so that you can run a program by typing only its name, not its path name. See *note setting-environment-variables::.

Use *note 'mysqladmin': 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:

 $> bin/mysqladmin version
 $> bin/mysqladmin variables

If you cannot connect to the server, specify a '-u root' option to connect as 'root'. If you have assigned a password for the 'root' account already, you'll also need to specify '-p' on the command line and enter the password when prompted. For example:

 $> bin/mysqladmin -u root -p version
 Enter password: (ENTER ROOT PASSWORD HERE)

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

 $> bin/mysqladmin version
 mysqladmin  Ver 14.12 Distrib 5.7.44, for pc-linux-gnu on i686
 ...

 Server version          5.7.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 *note 'mysqladmin': mysqladmin, invoke it with the '--help' option.

Verify that you can shut down the server (include a '-p' option if the 'root' account has a password already):

 $> bin/mysqladmin -u root shutdown

Verify that you can start the server again. Do this by using note 'mysqld_safe': mysqld-safe. or by invoking note 'mysqld': mysqld. directly. For example:

 $> bin/mysqld_safe --user=mysql &

If note 'mysqld_safe': mysqld-safe. fails, see note starting-server-troubleshooting::.

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

Use *note 'mysqlshow': mysqlshow. to see what databases exist:

 $> bin/mysqlshow
 +--------------------+
 |     Databases      |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+

The list of installed databases may vary, but always includes at least 'mysql' and 'information_schema'.

If you specify a database name, *note 'mysqlshow': mysqlshow. displays a list of the tables within the database:

 $> bin/mysqlshow mysql
 Database: mysql
 +---------------------------+
 |          Tables           |
 +---------------------------+
 | columns_priv              |
 | db                        |
 | engine_cost               |
 | event                     |
 | func                      |
 | general_log               |
 | gtid_executed             |
 | help_category             |
 | help_keyword              |
 | help_relation             |
 | help_topic                |
 | innodb_index_stats        |
 | innodb_table_stats        |
 | ndb_binlog_index          |
 | plugin                    |
 | proc                      |
 | procs_priv                |
 | proxies_priv              |
 | server_cost               |
 | servers                   |
 | slave_master_info         |
 | slave_relay_log_info      |
 | slave_worker_info         |
 | slow_log                  |
 | tables_priv               |
 | time_zone                 |
 | time_zone_leap_second     |
 | time_zone_name            |
 | time_zone_transition      |
 | time_zone_transition_type |
 | user                      |
 +---------------------------+

Use the *note 'mysql': mysql. program to select information from a table in the 'mysql' database:

 $> bin/mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql
 +------+-----------+-----------------------+
 | User | Host      | plugin                |
 +------+-----------+-----------------------+
 | root | localhost | mysql_native_password |
 +------+-----------+-----------------------+

At this point, your server is running and you can access it. To tighten security if you have not yet assigned a password to the initial account, follow the instructions in *note default-privileges::.

For more information about note 'mysql': mysql, note 'mysqladmin': mysqladmin, and note 'mysqlshow': mysqlshow, see note mysql::, note mysqladmin::, and note mysqlshow::.

 File: manual.info.tmp, Node: default-privileges, Next: automatic-start, Prev: testing-server, Up: postinstallation

2.9.4 Securing the Initial MySQL Account

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 *note data-directory-initialization::.

This section describes how to assign a password to the initial 'root' account created during the MySQL installation procedure, if you have not already done so.

Note:

Alternative means for performing the process described in this section:

A password may already be assigned to the initial account under these circumstances:

The 'mysql.user' grant table defines the initial MySQL user account and its access privileges. Installation of MySQL creates only a ''root'@'localhost'' superuser account that has all privileges and can do anything. If the 'root' account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as 'root' without a password and be granted all privileges.

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 *note proxy-users::.

To assign a password for the initial MySQL 'root' account, use the following procedure. Replace ROOT-PASSWORD in the examples with the password that you want to use.

Start the server if it is not running. For instructions, see *note starting-server::.

The initial 'root' account may or may not have a password. Choose whichever of the following procedures applies:

After assigning the 'root' account a password, you must supply that password whenever you connect to the server using the account. For example, to connect to the server using the *note 'mysql': mysql. client, use this command:

 $> mysql -u root -p
 Enter password: (ENTER ROOT PASSWORD HERE)

To shut down the server with *note 'mysqladmin': mysqladmin, use this command:

 $> mysqladmin -u root -p shutdown
 Enter password: (ENTER ROOT PASSWORD HERE)

Note:

For additional information about setting passwords, see note assigning-passwords::. If you forget your 'root' password after setting it, see note resetting-permissions::.

To set up additional accounts, see *note creating-accounts::.

 File: manual.info.tmp, Node: automatic-start, Prev: default-privileges, Up: postinstallation

2.9.5 Starting and Stopping MySQL Automatically

This section discusses methods for starting and stopping the MySQL server.

Generally, you start the *note 'mysqld': mysqld. server in one of these ways:

systemd, the note 'mysqld_safe': mysqld-safe. and note 'mysql.server': mysql-server. scripts, Solaris SMF, and the macOS Startup Item (or MySQL Preference Pane) can be used to start the server manually, or automatically at system startup time. systemd, *note 'mysql.server': mysql-server, and the Startup Item also can be used to stop the server.

The following table shows which option groups the server and startup scripts read from option files.

MySQL Startup Scripts and Supported Server Option Groups

Script Option Groups

*note 'mysqld': mysqld.'[mysqld]', '[server]', '[mysqld-MAJOR_VERSION]'

*note 'mysqld_safe': mysqld-safe.'[mysqld]', '[server]', '[mysqld_safe]'

*note 'mysql.server': mysql-server.'[mysqld]', '[mysql.server]', '[server]'

'[mysqld-MAJOR_VERSION]' means that groups with names like '[mysqld-5.6]' and '[mysqld-5.7]' are read by servers having versions 5.6.x, 5.7.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, note 'mysql.server': mysql-server. also reads the '[mysql_server]' group and note 'mysqld_safe': mysqld-safe. also reads the '[safe_mysqld]' group. To be current, you should update your option files to use the '[mysql.server]' and '[mysqld_safe]' groups instead.

For more information on MySQL configuration files and their structure and contents, see *note option-files::.

 File: manual.info.tmp, Node: upgrading, Next: downgrading, Prev: postinstallation, Up: installing