MySQL :: MySQL Secure Deployment Guide :: 5 Post Installation Setup

Creating a Safe Directory For Import and Export Operations

MySQL users with the FILE privilege have permission to read and write files on the server host using LOAD DATA INFILE and SELECT ... INTO OUTFILE statements, and the LOAD_FILE() function. By default, a user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. This includes the server data directory containing the files that implement the privilege tables.

To limit the scope of the FILE privilege, create a directory that users with the FILE privilege can safely use for import and export operations. In this deployment, the directory created is named mysql-files and is located under the data directory. In a later step, when server startup options are configured, the secure_file_priv option is set to the mysql-files directory.

$> cd /usr/local/mysql  
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files

Configuring Server Startup Options

Specify options that the MySQL server should use at startup by placing them in a MySQL configuration file. If you do not do this, the server starts with its default settings (see Server Configuration Defaults).

Note

Certain InnoDB options can only be configured before initializing the data directory. Among these options are innodb_data_home_dir, innodb_data_file_path, innodb_log_file_size, innodb_log_group_home_dir, innodb_page_size, and innodb_undo_tablespaces. If you do not want to use default values for these options, set your own values in the MySQL configuration file before initializing the data directory. This deployment uses default InnoDB configuration settings. For more information, see InnoDB Startup Configuration.

To create a MySQL configuration file, issue these commands as root:
```
$> cd /etc
$> touch my.cnf
$> chown root:root my.cnf  
$> chmod 644 my.cnf
```
Note

If there is an existing my.cnf configuration file in the same location that belongs to another MySQL instance, use a different name for your configuration file.
Under a [mysqld] group entry, set the datadir, socket, port, log-error options for the instance. If there are other MySQL installations on the host, ensure that the values for these options are unique to this instance. This deployment uses the default values.
```
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/localhost.localdomain.err
```
Important

The location of the MySQL data directory is critically important to the security of a MySQL installation. In addition to user data, the data directory contains data dictionary and system tables, which store sensitive information about database objects, users, privileges, and so on. Following the principle of least privilege, system user access to the data directory should be as restrictive as possible. The size of the file system where the data directory resides should also be considered. Ensure that the file system can accommodate the anticipated size of your data. The deployment described in this guide places the data directory in the default location (/usr/local/mysql/data), and access to the directory is limited to the mysql operating system user account.
Set the user option to ensure that the server is started as the unprivileged mysql user account. For security reasons, it is important to avoid running the server as the operating system root user.
```
user=mysql
```
If you intend to permit import and export operations, set the secure_file_priv system variable to the path of the mysql-files directory that you created previously. This option limits file import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function, to the specified directory. If you do not intend to permit import or export operations, set secure_file_priv to NULL, which disables import and export operations entirely. NULL is the default setting.
```
secure_file_priv=/usr/local/mysql/mysql-files
```
To avoid potential security issues with the LOCAL version of LOAD DATA, ensure that local_infile is enabled by default.
```
local_infile=OFF
```
For more information, see Security Considerations for LOAD DATA LOCAL.

After completing the steps above, the configuration file should contain these settings, assuming you have not added others:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/localhost.localdomain.err
user=mysql
secure_file_priv=/usr/local/mysql/mysql-files
local_infile=OFF

Initializing the Data Directory

After installing MySQL, you must initialize the data directory, which includes the mysql system database and its tables, including grant tables, server-side help tables, and time zone tables. Initialization also creates the root@localhost superuser account and the InnoDB system tablespace and related data structures required to manage InnoDB tables.

To initialize the data directory:

Change location to the top-level directory of the MySQL installation, create the data directory, and grant ownership to the mysql user.
```
$> cd /usr/local/mysql
$> mkdir data
$> chmod 750 data
$> chown mysql:mysql data
```
Note

Data directory ownership is assigned to the mysql user, but most of the MySQL installation remains owned by root. Other exceptions are the error log file, the mysql-files directory, the pid file, and socket file, to which the mysql user must have write access. Files and resources that the mysql user requires read access to include configuration files (e.g., /etc/my.cnf) and the MySQL binaries (/usr/local/mysql/bin).

Initialize the data directory.

$> cd /usr/local/mysql
$> bin/mysqld --defaults-file=/etc/my.cnf --initialize

Initialization output is printed to the error log (/usr/local/mysql/data/localhost.localdomain.err) and appears similar to the output shown below. The output includes a random initial password for the root@localhost account. The initial password is required later when resetting the root@localhost password.

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use 
--explicit_defaults_for_timestamp server option (see documentation for more details).
[Warning] InnoDB: New log files created, LSN=45790
[Warning] InnoDB: Creating foreign key constraint system tables.
[Warning] No existing UUID has been found, so we assume that this is the first time that this 
server has been started. Generating a new UUID: ee40ce3b-367c-11e7-adf9-080027b8b5f8.
[Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
[Warning] CA certificate ca.pem is self signed.
[Note] A temporary password is generated for root@localhost: jh;kgEza*9&t

Note

Data directory initialization creates time zone tables in the mysql database but does not populate them. To do so, refer to the instructions in MySQL Server Time Zone Support.

For more information about data directory initialization, see Initializing the Data Directory.

Starting the Server using systemd

This section describes how to start the server with systemd and how to enable automatic restart of the MySQL server when the host is rebooted.

systemd provides manual server management using the systemctl command:

systemctl {start|stop|restart|status} mysqld

To configure the MySQL installation to work with systemd:

Add a systemd service unit configuration file with details about the MySQL service. The file is named mysqld.service and is placed in /usr/lib/systemd/system.

$> cd /usr/lib/systemd/system
$> touch mysqld.service
$> chmod 644 mysqld.service

Add this configuration information to the mysqld.service file:

[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/usr/local/mysql/data/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize  
--pid-file=/usr/local/mysql/data/mysqld.pid $MYSQLD_OPTS 

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

Important

The ExecStart start string above is broken across two lines to fit within the page width of this document. Restore the string to a single line after copying the configuration information to a file. Also, the --pid-file setting in the ExecStart start string must match the PIDFile setting that precedes it. The --pid-file option specified in the my.cnf configuration file is ignored by systemd.

Add a configuration file for the systemd tmpfiles feature. The file is named mysql.conf and is placed in /usr/lib/tmpfiles.d.
```
$> cd /usr/lib/tmpfiles.d
$> touch mysql.conf
$> chmod 644 mysql.conf
```
Add this configuration information to the mysql.conf file:
```
d /usr/local/mysql/data 0750 mysql mysql  -
```

Enable the mysqld service to automatically start at reboot.

$> systemctl enable mysqld.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service 
to /usr/lib/systemd/system/mysqld.service.

To ensure the systemd configuration works, start the mysqld service manually using systemctl.
```
$> systemctl start mysqld
```

Check the status of the mysqld service. The output should appear similar to the following, which shows that the mysqld service was started successfully.

$> systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-09-08 09:44:38 ADT; 2h 6min ago
     Docs: man:mysqld(7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2345 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2345 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --d...

Sep 08 09:44:23 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 08 09:44:38 localhost.localdomain systemd[1]: Started MySQL Server.

To verify that systemd automatically starts MySQL when the system is rebooted, restart your system and check the status of the mysqld service again.
```
$> systemctl status mysqld
```

Note

systemd has its own log file which can be accessed using journalctl. To view mysqld-related log messages, use journalctl -u mysqld. Some messages, such as MySQL startup messages, may be printed to the systemd log.

For more information about systemd, see Managing MySQL Server with systemd.

Resetting the MySQL root Account Password

This procedure assumes that the MySQL server is running. You can check server status by issuing:

$> systemctl status mysqld

When the data directory was initialized, a random initial password was generated for the MySQL root account (root@localhost) and marked as expired. Perform these steps to set a new password:

Using the mysql client, connect to the server as root@localhost using the random password that the server generated during the initialization sequence:
```
$> cd /usr/local/mysql 
$> bin/mysql -u root -p
Enter password: (enter the random root password here)
```
After connecting, assign a new root@localhost password. Use a strong password that conforms to the default password policy imposed by the Password Validation Plugin, which is enabled in a later step. (See Chapter 6, Installing the MySQL Password Validation Plugin.)

The password must be at least 8 characters long, contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character. Additionally, the password should not match the user name part of the effective user account for the current session, either forward or in reverse.
```
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
```

Testing the Server

Now that MySQL is installed and initialized, and the MySQL root user password is reset, perform a couple of simple tests to verify that the server works.

Use mysqlshow to verify that you can retrieve information from the server.

$> cd /usr/local/mysql 
$> bin/mysqlshow -u root -p
Enter password: (enter root password here)
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Use mysqladmin to view MySQL server version information.

$> cd /usr/local/mysql 
$> bin/mysqladmin -u root -p version
Enter password: (enter root password here)

The output should be similar to that shown here:

bin/mysqladmin  Ver 8.42 Distrib 5.7.19, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2017, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.19-enterprise-commercial-advanced
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			3 min 21 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 105  Flush tables: 1  
Open tables: 98  Queries per second avg: 0.029

For additional tests, see Testing the Server.

Chapter 5 Post Installation Setup

Creating a Safe Directory For Import and Export Operations

Configuring Server Startup Options

Initializing the Data Directory

Starting the Server using systemd

Resetting the MySQL root Account Password

Testing the Server