Search Our Database

Introduction

To enhance the performance, security, and scalability of your hosting environment, you may separate the MySQL database server from DirectAdmin onto a dedicated server. MySQL is a widely used open-source relational database management system, especially for web applications and services. This approach allows for better resource allocation, improved database performance, and easier maintenance of both the application and database layers. By isolating the database, we can achieve faster response times, reduce the load on the main server, and optimize the overall system architecture.

This document outlines the steps and considerations involved in splitting the database into a standalone server.

Steps to configure a remote MySQL server in DirectAdmin

1. Connect to your web server via SSH

Begin by connecting to your web server using SSH as the root user.

2. Update the MySQL configuration

Edit the MySQL configuration file located at /usr/local/directadmin/conf/mysql.conf and add the IP address of the remote MySQL server as the host:

user=da_admin 
host=xx.xx.xx.xx 
passwd=H&*(h@123

Replace xx.xx.xx.xx with the IP address of the remote MySQL server.

3. Ensure MySQL is installed on the remote server

SSH into the remote MySQL server and run the following command to check if MySQL is installed:

mysqld -V

If MySQL is not installed, execute the following commands to download and install it:

mkdir mysql 
cd mysql 
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb
sudo apt update
sudo apt install mysql-server

4. Log in to MySQL on the remote server

Log in to MySQL by running the following command:

mysql -uroot -p

5. Grant permissions to the web server

Grant full privileges to the da_admin user on the remote MySQL server by executing:

GRANT ALL PRIVILEGES ON *.* TO da_admin@xxx.xxx.xxx.xxx IDENTIFIED BY 'H&*(h@123' WITH GRANT OPTION; 
FLUSH PRIVILEGES; 
quit

Replace xxx.xxx.xxx.xxx with the IP address of your web server.

6. Test connectivity

To ensure the web server can access the remote MySQL server, run this command on your web server:

mysql -uda_admin -p --host=xxx.xxx.xxx.xxx

7. Test database management in DirectAdmin

In DirectAdmin, verify that the web server can create and manage databases using the MySQL Management interface.

8. Configure phpMyAdmin for remote access

To allow phpMyAdmin to access the remote MySQL server, edit the /var/www/html/phpMyAdmin/config.inc.php file. Find the following line:

$cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname or IP address

Change it to:

$cfg['Servers'][$i]['host'] = 'xx.xx.xx.xx'; // MySQL hostname or IP address

Conclusion

By following these steps, you can successfully configure a remote MySQL server for use with DirectAdmin. This setup ensures efficient database management across servers and enables integration with phpMyAdmin. Make sure to follow security best practices when granting remote access to your database.

Article posted on 23 February 2020 by Louis