Optimize MySQL speed on DirectAdmin

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:

• How to convert MySQL to MariaDB on DirectAdmin. .

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:

• How to login to Linux VPS with SSH protocol>

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:

• Check CustomBuild version in use

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:

• Check CustomBuild version in use

After having CustomBuild 2.0, execute following commands:

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:

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

Here are my results:

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.

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

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:

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:

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:

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:

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:

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:

• Summary of instructions for using DirectAdmin.

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

• Hotline 247: 028 888 24768 (Ext 0)
• Ticket/Email: You use the email to register for the service and send it directly to: support@azdigi.com