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.
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:
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.cnfto 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:
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.
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: