How to Tune and Optimize Performance of MySQL 8.0 on a CentOS 7 Server

In this article we will discuss how to Tune and Optimize your MySQL server. We are using two scripts for completing our objective.

Prerequisites

  1. MySQL installed CentOS 7 server

Using Mysqltuner

Mysqltuner is a high-performance MySQL tuning Perl script that gives a snapshot of the MySQL server’s health status and gives specific recommendations for improved, increased performance, stability, and efficiency. Now we will discuss the setup and managing of mysqltuner script

Installing Mysqltuner

yum install -y epel-release
yum install -y mysqltuner

Optimizing with Mysqltuner

Run the following command to optimize your MySQL

mysqltuner

You will be asked to enter your MySQL administrative username and password

After running the command mysqltuner will recommend changing some configuration to optimize your MySQL server.

You can change the configuration file /etc/my.cnf according to your recommendation. Then restart the MySQL service.

systemctl restart mysqld

Run the mysqltuner command again check whether your MySQL server has optimized

Using tuning-primer

Tuning Primer is a shell script that obtains information from MySQL server internals and makes recommendations on adjustments of the server variables. We will discuss the Initial setup of the script below.

yum install bc -y
git clone https://github.com/RootService/tuning-primer.git
cd tuning-primer
chmod +x tuning-primer.sh

If you are outside the tuning-primer directory move to the directory, and run the script

./ tuning-prime.sh

After running the script they will recommend changing some of the configuration files. Give priority to the recommendation in red and yellow color.

Note: After making changes to the configuration file always remember to restart the MySQL service

systemctl restart mysqld

Related Articles