We can set up MySQL to be run on an external server when we have a large database that would require management and monitoring of its own. The best way to do so is to host MySQL on a different server. Then we can connect our application to our Remote MySQL database which is pretty simple.
This shouldn’t be needed too often, as usual, you can just move all those user accounts to another server to comfort and maintain the load. But in the case of a large database using up the whole server, you don’t have many choices.
Set up DA to Use a Remote MySQL Server
1) Firstly, install MySQL on the remote server. DirectAdmin is not required on this server, as it just used to host our MySQL Databases and nothing else.
2) The next move is to create/setup up a user that a DirectAdmin can connect to on the remote MySQL server. Normally the default user for DirectAdmin is da_admin, so the connection with the same name is better but not required.
The username and password you have created for the new user must be set in the /usr/local/directadmin/conf/mysql.conf file for DirectAdmin to use. On a side note, if the remote server is on a LAN IP, and the connecting IP to that remote server will not be the same as your server IP, but rather some other IPs. Instead of server IP, you can add an access host by default in the /usr/local/directadmin/conf/mysql.conf file.
Add the following line to the file mentioned in the previous paragraph:
access_host=x.x.x.x
Where x.x.x.x is the IP or host will be the IP address of your remote database server.
Please note that the above given method is only for grants da_admin on the “localhost”, meaning you have to already be on that remote MySQL server to use the account. You will need to add another access host or IP to allow the DA server to connect to it. Normally, you just run the GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost command again, but change localhost to the IP of your DA server. Just like this.
GRANT ALL PRIVILEGES ON *.* TO [email protected] WITH GRANT OPTION; FLUSH PRIVILEGES;
We don’t need the “identified by pass” again in this case, because the password is already from adding it the first time.
3) In this instance, the remote MySQL database will be ready and found to accept the DA to use it. You can test this by logging to your DA via SSH and then type:
mysql -uda_admin -p --host=x.x.x.x
Where x.x.x.x is the IP of your MySQL server. If you are not able to connect, then you must need to check your settings again, also make sure that the MySQL port 3306 is open on the remote box.
4) Then tell DirectAdmin to use the remote server.
Edit the configuration file: /usr/local/directadmin/conf/mysql.conf
add the line: host=x.x.x.x
where x.x.x.x is the IP of your remote server. Make sure there is a newline character at the end of the line. (Eg, press enter)
5) Enter into DA and check the MySQL section of your user level to see if you get any kind of errors related to this. If not, then you can try for adding a database to make sure it works.
6) The only authorize task would then be to fix up phpMyAdmin also connect to the remote host. To do so, Edit /var/www/html/phpMyAdmin/config.inc.php.
Then find the following line in the configuration file:
$cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname or IP address
Change it to the following.
$cfg['Servers'][$i]['host'] = 'x.x.x.x'; // MySQL hostname or IP address
Where x.x.x.x is the IP of your MySQL server.
7) For new MySQL user databases, you will need to tell the DirectAdmin about the new IP, so they have that IP assigned into their access hosts.