If you host a large database that can't be split up, and your server doesnt have the resources to handle it, you can setup mysql to be run on an external server. Â This shouldn't be needed too often, as usually, you can just move entire user accounts to another server to ease the load. Â But in the case of one large database using up the whole server, then you dont' have much choice.
Note that this guide does not transfer any databases over. Â It's generally a good idea to do this before you add users. Â Also, mysql will continue to run on your local DA.. so existing databases and scripts should continue to function, but users will not be able to control them through DA.
The first step is to install mysql onto the remote server. Â DA is not require on this server, as it is just used for mysql and nothing else. Â I won't go into the detail on how to do this (use google).
The next step is to setup a user that DA can connect to on the remote mysql server. Â The default DA uses is da_admin, so staying with that name is a good idea, but not required.
You can use this guide to setup the da_admin user on your server: help.directadmin.com/item.php?id=45. Â The username and password you setup have to be set in the /usr/local/directadmin/conf/mysql.conf file for DA to use.
On a related note, if the remote server is on a LAN IP, and the connecting IP to that remote server will not be your server IP, but rather some other IP such as a 192.168.x.x type of IP then you can use this guide to add an access host by default instead of the server IP: directadmin.com/features.php?id=818.
Note that the above guide will only grants da_admin on the "localhost", meaning you have to already be on that remote mysql server to use the account. Â We'll need to add another acccess hsot (ip) to allow the DA server to connect to it. Â Basically, you just run the "GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost" command again, but you change localhost to the IP of your DA server,
GRANT ALL PRIVILEGES ON *.* TO da_admin@1.2.3.4 WITH GRANT OPTION;
FLUSH PRIVILEGES;
At this point.. the remote mysql database should be setup and ready to accept DA to use it. Â Test it out by logging into your DA machine via ssh, and type:
mysql -uda_admin -p --host=4.3.2.1
where 4.3.2.1 is the IP of your mysql server. Â If you can't connect, go back over your settings again.. also make sure that port 3306 is open on the remote box.Now.. the easier part is to tell DA to use the remote server. Â Edit:
/usr/local/directadmin/conf/mysql.conf
add the line:
host=4.3.2.1
where 4.3.2.1 is the IP of your remote server. Make sure there is a newline character at the end of the line.. (Eg, press enter)
That should be it. Â Go into DA and check the mysql section of your user level to see if you get any errors. Â If not, try adding a database to make sure it works.
The only cleanup task would then be to fix up phpMyAdminto also connect to the remote host.
Edit /var/www/html/phpMyAdmin/config.inc.php
Find this line:
$cfg['Servers'][$i]['host'] Â Â Â Â Â = 'localhost'; // MySQL hostname or IP address
and change it to$cfg['Servers'][$i]['host'] Â Â Â Â Â = '4.3.2.1'; // MySQL hostname or IP address
where 4.3.2.1 is the IP of your mysql server.