Post-Installation steps for Percona Server for MySQL 5.7¶

After you have installed Percona Server for MySQL 5.7, you may need to do the following:

Task	Description
Initialize the data directory	The source distribution or generic binary distribution installation does not automatically initialize the data directory
Update the root password	The CentOS/RedHat installations set up a temporary root password.
Securing the Installation	The mysql_secure_installation script improves the security of the installation.
Checking the server status	Verify the server returns information
Configuring the Server to Start at Startup	Common method to start the server automatically
Testing the server	Verify the server installation
Populating the time zone tables	The time zone tables are created but are not populated.
Exclude Buffer Pool Pages from core files	Reduce the size of the core files by excluding the buffer pool

Initializing the Data Directory¶

If you install the server using either the source distribution or generic binary distribution files, the data directory is not initialized, and you must run the initialization process after installation.

Run mysqld with the –initialize option or the initialize-insecure option.

Executing mysqld with either option does the following:

Verifies the existence of the data directory
Initializes the system tablespace and related structures
Creates system tables including grant tables, time zone tables, and server-side help tables
Creates root@localhost

You should run the following steps with the mysql login.

Navigate to the MySQL directory. The example uses the default location.
```
$ cd /usr/local/mysql
```
Create a directory for the MySQL files. The secure_file_priv uses the directory path as a value.
```
$ mkdir mydata
```
The mysql user account should have the drwxr-x--- permissions. Four sections define the permissions; file or directory, User, Group, and Others.

The first character designates if the permissions are for a file or directory. The first character is d for a directory.

The rest of the sections are specified in three-character sets.

Permission User Group Other

Read Yes Yes No

Write Yes No No

Execute Yes Yes No
Run the command to initialize the data directory.
```
$ bin/mysqld --initialize
```
Updating the root password¶

The RedHat and derivative distributions set up a temporary password when MySQL is installed. To reset the password, you must start MySQL with the --skip-grant-tables option and update the user table.

The initial password is located using the following command:

$ grep 'temporary password' /var/log/mysqld.log

Follow this procedure to reset the root password:

Stop MySQL.
```
$ sudo systemctl stop mysqld
```
Set --skip-grant-tables as an environment option. This method lets you specify the option without modifying configuration files.
```
$ sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables 
--skip-networking"
```
Restart MySQL to make the option change effective.
```
$ sudo systemctl restart mysqld
```
Access MySQL as root.
```
$ mysql -u root
```

Change the root password.

mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY ('NewPassword');
mysql> FLUSH PRIVILEGES;
mysql> exit

Stop MySQL
```
$ sudo systemctl stop mysqld
```

Reset the environment options.

$ sudo systemctl unset-environment MYSQLD_OPTS

Start MySQL
```
$ sudo systemctl start mysqld
```
Log in to MySQL using the root password.
```
$ mysql -u root -p
```

Note

if you have trouble logging in after following the steps, repeat the procedure but, instead of using the ALTER USER statement, modify the user table.

mysql> UPDATE mysql.user SET authentication_string=PASSWORD
('NewPassword'), password_expired='N' 
WHERE User='root' AND Host='localhost';
mysql>FLUSH PRIVILEGES;

Securing the Installation¶

The mysql_secure_installation script improves the security of the installation.

Running the script does the following:

Changes the root password
Disallows remote login for root accounts
Removes anonymous users
Removes the test database
Reloads the privilege tables

The following statement runs the script:

$ mysql_secure_installation

Checking the server status¶

After a generic binary installation, the server starts. The following command checks the server status:

$ sudo service mysql status

Access the server with the following command:

$ mysql -u root -p

Configuring the Server to Start at Startup¶

You can manage the server with systemd. If you have installed the server from a generic binary distribution on an operating system that uses systemd, you can manually configure systemd support.

The following commands start, check the status, and stop the server:

$ sudo systemctl start mysql
$ sudo systemctl status mysql
$ sudo systemctl stop mysql

Enabling the server to start at startup, run the following:

$ sudo systemctl enable mysql

Testing the Server¶

After you have initialized the data directory, and the server is started, you can run tests on the server.

This section assumes you have used the default installation settings. If you have modified the installation, navigate to the installation location. You can also add the location by Setting the Environment Variables.

You can use the mysqladmin client to access the server.

If you have issues connecting to the server, you should use the root user and the root account password.

$ sudo mysqladmin -u root -p version

The output should be similar to the following:

Enter password:

mysql Ver 8.0.19-10 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '10', Revision 'f446c04')
...
Server version      8.0.19-10
Protocol version    10
Connection          Localhost via UNIX socket
UNIX socket         /var/run/mysqld/mysqld.sock
Uptime:             4 hours 58 min 10 section

Threads:    2 Questions:    16 Slow queries: 0 Opens: 139 Flush tables: 3
Open tables: 59  Queries per second avg: 0.0000

Use mysqlshow to display database and table information.

$ sudo mysqlshow -u root -p

The output should be similar to the following:

Enter password:

+---------------------+
|      Databases      |
+=====================+
| information_schema  |
+---------------------+
| mysql               |
+---------------------+
| performance_schema  |
+---------------------+
| sys                 |
+---------------------+

Populating the time zone tables¶

The time zone system tables are the following:

time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type

If you install the server using either the source distribution or generic binary distribution files, the installation creates the time zone tables, but the tables are not populated.

The mysql_tzinfo_to_sql program populates the tables from the zoneinfo directory data available in Linux.

A common method to populate the tables is to add the zoneinfo directory path to mysql_tzinfo_to_sql and then send the output into mysql.

The example assumes you are running the command with the root account. You must use an account with the privileges able to modify MySQL system tables.

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p rootpassword

Excluding Buffer Pool Pages from Core files¶

Implemented in Percona Server for MySQL 5.7.33-36, you can use the innodb_buffer_pool_in__core_file to reduce the size of the core file.

Buffer pools can produce large core files because the buffer pool is located in main memory. If the main memory is dumped to a core file, the buffer pool increases the size of the dump.

Having a large core file can have the following issues:

Requires more time to write
Consume disk space
Reading the file

To exclude the buffer pool, run the following command at startup or use a SET statement:

mysqld> SET GLOBAL innodb_buffer_pool_in__core_file=OFF;

Last update: 2022-09-27

Permission	User	Group	Other
Read	Yes	Yes	No
Write	Yes	No	No
Execute	Yes	Yes	No