Home WebServer PanelDirectAdmin Optimize MySQL speed on DirectAdmin

Optimize MySQL speed on DirectAdmin

by Thạch Phạm
Published: Last Updated on

While using the DirectAdmin control panel, perhaps any of us have also suffered from poor query speed from MySQL. In this article, AZDIGI will show you the simplest way to optimize MySQL speed on DirectAdmin.

I. Overview

When we use the DirectAdmin control panel, most of us choose the default configuration setting, and the default configuration will install MySQL.

If you want to use MariaDB instead of MySQL, you can refer to the following tutorial:

Even if you are using MySQL or MariaDB, you should use the latest version to get the best performance.

In addition to version upgrades, we still have many other ways to optimize MySQL speed on DirectAdmin, and what is that optimal way? So please continue reading this tutorial. But your VPS or server will need at least 2GB of RAM.

II. Optimize MySQL speed on DirectAdmin

To optimize MySQL speed on DirectAdmin, follow these 3 steps.

Step 1: SSH into your DirectAdmin system

To optimize MySQL speed on DirectAdmin, we first need to SSH or access your VPS/server with root privileges. If you do not know how to SSH into your VPS/Server, you can refer to the following tutorial:

After successfully SSH-ing, we continue with step 2 to start the process of backing up the entire database on DirectAdmin with CustomBuild 2.0.

Step 2: Backup the entire database on DirectAdmin with CustomBuild 2.0

First, we need to check if the version of CustomBuild we are using is 2.0 or not. To check the version of CustomBuild you are using, please check out the following short tutorial:

In case your CustomBuild version is lower than 2.0 then you need to upgrade CustomBuild 1.x to 2.0 , you can check out the following tutorial:

After having CustomBuild 2.0, execute following commands:

AZDIGI Tutorial
cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup

Explanation of the above commands:

  • Command 1: Move to custombuild folder.
  • Command 2: Adjust the configuration to enable database backup.
  • Command 3: Launch the database backup process.

If you do not know where the mysql_backup directory contains the databases, you can use the following command:

AZDIGI Tutorial
cat /usr/local/directadmin/custombuild/options.conf | grep mysql_backup_dir

Here are my results:

Screen Shot 2020 07 24 at 4.06.20 PM

So the directory containing the backed-up databases will be /usr/local/directadmin/custombuild/mysql_backups. Depending on the configuration in the /usr/local/directadmin/custombuild/options.conf file, it may be different on your VPS/server.

Or you can also change this path by reusing the configuration options.conf file if you want.

Since I only have one user demo2 with the demo2_test database, when I run the ./build mysql_backup command , the process is speedy, and only 2 databases are generated, including a default database.

Optimize MySQL speed on DirectAdmin

Checking the list of files in this directory, we will see 2 databases similar to the above log.

Optimize MySQL speed on DirectAdmin

In case your mysql_backups directory already exists and you don’t want to be overwritten, you can change the current directory name with the following command:

mv mysql_backups mysql_backups.`date +%F`

After running this command, the directory name mysql_backups will be changed to a new name of the form mysql_backups.YYYY-mm-dd as shown below:

Screen Shot 2020 07 24 at 4.59.45 PM

So the process of backing up the entire database on DirectAdmin has been completed. We continue to step 3 to optimize MySQL speed on DirectAdmin.

Step 3: How to customize MySQL speed optimization on DirectAdmin

After we have backed up all your databases in step 2, we will do this step.

We execute the following two commands, for VPS/Server on 8GB RAM:

AZDIGI Tutorial
cp /etc/my.cnf /etc/my.cnf.old
wget -O /etc/my.cnf http://files.directadmin.com/services/all/mysql/my-huge-5.5.cnf

Explanation of the two commands above:

  • Command 1: This command will backup the MySQL/MariaDB configuration file my.cnf to the new name my.cnf.old. In case we need this file again.
  • Command 2: This command will load a sample configuration file that has been tweaked and optimized for speed for MySQL/MariaDB.

Here you will see there is a default configuration file path which is http://files.directadmin.com/services/all/mysql/my-huge-5.5.cnf, in addition to the default optimized configuration file, we will have two other optimized default configuration files:

  • http://files.directadmin.com/services/all/mysql/my-medium-5.5.cnf
  • http://files.directadmin.com/services/all/mysql/my-large-5.5.cnf

If your RAM is only 2GB, you should use the two commands below:

AZDIGI Tutorial
cp /etc/my.cnf /etc/my.cnf.old
wget -O /etc/my.cnf http://files.directadmin.com/services/all/mysql/my-medium-5.5.cnf

If your VPS has more than 4GB of RAM, use these two commands:

AZDIGI Tutorial
cp /etc/my.cnf /etc/my.cnf.old
wget -O /etc/my.cnf http://files.directadmin.com/services/all/mysql/my-large-5.5.cnf

After you have done 1 of the 3 configurations above depending on the VPS/Server, we will need to restart MySQL/MariaDB with the following command to receive the new configuration:

AZDIGI Tutorial
service mysqld restart

Wait a moment for the MySQL/MariaDB service to restart.

So we have completed the steps to optimize MySQL speed on DirectAdmin. Although there are many other optimization ways, this is the simplest way, even those unfamiliar with the configuration parameters can also be optimized.

III. Summary

Through this article, AZDIGI has shown you the simplest way to optimize MySQL speed on DirectAdmin, and there are 3 configuration options for you to choose from to match the RAM of your VPS/Server.

Hope the article is helpful for you. Wishing you success!

See more useful articles about DirectAdmin at the following link:

If you need assistance, you can contact support in the ways below:

Đánh giá

Tham gia nhóm hỗ trợ Server - Hosting

Tham gia nhóm Hỗ trợ Server - Hosting & WordPress để cùng nhau hỏi đáp và hỗ trợ các vấn đề về WordPress, tối ưu máy chủ/server.

Tham gia ngay

Bài viết cùng chuyên mục

AZDIGI – Không chỉ là đơn vị hàng đầu trong lĩnh vực Web Hosting và Máy chủ, chúng tôi mong muốn mang lại những kiến thức bổ ích nhất và luôn cập nhật thường xuyên cho cộng đồng người đam mê thiết kế website, công nghệ,…

Vui lòng không sao chép nội dung nếu chưa xin phép. Designed and Developed by PenciDesign