Remote MySQL Server?

W

willfe

New member
Joined
Feb 17, 2004
Messages
3
I've spent some time searching through older posts on the forums here, but the only relevant thread I can find, at http://www.directadmin.com/forum/showthread.php?threadid=299&highlight=remote+mysql, is dated July 31 2003, so I'm hoping that perhaps a newer answer is available. :)

Is it yet possible to use a remote MySQL server for accounts created on/managed by a DirectAdmin installation? We run a dedicated database server for many of our accounts on a cPanel box and we'd love to do the same on the DirectAdmin box; the performance increase for hosted accounts is dramatic once MySQL is offloaded.

According to the thread I referenced above, the answer was "no" back in July; my fingers are crossed that maybe that's changed now?

If it has not, I'm aware of alternatives, including just manually moving databases that run lots of "long queries" or take lots of hits per hour over to our dedicated DB server, but the downsides to this include the loss of DirectAdmin control of the database(s) for the affected user, and less accurate (and more difficult to manage) disk usage monitoring and limiting.

I don't mind developing something that can help DirectAdmin manage a remote MySQL database (like an API to get or set quota information, wrap phpMyAdmin so it knows to hit a remote server, etc.) but could use some pointers as to where to start. Does anyone have any pointers or thoughts about this kind of thing?

Thanks in advance!
 
Hello,

Can you use a remote mysql server? yes.

Does DA setup remote mysql servers, no. What I could do, is allow the addition of a "host" variable in the /usr/local/directadmin/conf/mysql.conf file, where a person could add the host they'd want to connect to setup databases remotely.

John
 
Yeah, I know I can set up the remote server manually. That's what's being done at present for a couple of larger databases just to stop MySQL and Apache from fighting each other too much for CPU time.

The option you describe sounds helpful, though; currently the above is all manual, and we lose the ability to count databases against user quotas when they're remote.

Adding that configuration option would be immensely helpful. It doesn't solve the quota problem but does mean users could directly manage their databases regardless of the DB's physical location. That'd be very helpful. As an aside, being able to set this option's default value would be a godsend as well; that way every account's default DB host could be set to the remote one so we can further reduce load on the web server.

There may be no real "graceful" way to include remote databases in a local user's quota; I'm going to look into using a sparse file that can really use just 1 byte of space (or whatever) but claim to be X bytes in size (to match the size of the remote database files). I'm hoping the kernel can be fooled into counting the claimed size and not the actual blocks used, but that may not work. Assuming that works, obviously it'd need to be updated frequently by a monitoring process (but that's fairly trivial to do) and it'd need chown'ing to the account owner, but those are reasonable. If I get that working I'll post about it here -- I imagine others might find that useful :)

Thanks so much for the quick reply! DirectAdmin is a great product and it's supported by some very impressive staff. Great job, folks!
 
Bump -- Are the developers considering this? What are the possible solutions to adhering to quotas on a remote MySQL server?
 
Hello,

The "host=your.host.com" feature has already been added and should be out with this next release (either today or tomorrow). I have done a bit more research and implementing mysql disk quotas through the connection is entirely possible and I can add that for the next release after this one :)

John
 
DirectAdmin Support said:
and implementing mysql disk quotas through the connection is entirely possible and I can add that for the next release after this one :)
Click to expand...
Yay! So, is Remote DNS coming thereafter? :D ;)
 
Outstanding! That's great news John, thanks a lot! I look forward to the next release :)

This will easily double the number of accounts the server can handle without increasing its current load or hurting performance.
 
Has there been any progress on this kind of option?

We have a couple of servers that have extremely busy MySQL DBs, so putting them somewhere else with minimum pain would be ideal.
 
Hello,

The external mysql option has been around for quite a while now.

Edit /usr/local/directadmin/conf/mysql.conf and set
host=your.otherserver.com

Make sure you have a "da_admin" account setup on the other server with correct permissions. This guide will help you set it up:
http://help.directadmin.com/item.php?id=45 .. Note that the "da_admin@localhost" will likely need to be replaced with "da_admin@your.otherserver.com".

Disk usage for mysql is done through mysql, not from files, thus the disk usage will work correctly. Also, for the 1.23.4 release (coming today/tomorrow) all of the database backup/restore tools will also support the feature (add -h option to the mysql commands).

John
 
great job

I am going to try this in next week.

Thanks for informations

Regards,
Piotr Kloc
 
question about remote mysql

I have runned remote mysql.
By creating a database ,my users havent access to this database because default access host is "localhost" right?
my database server ip is 10.0.0.2.
is any chance to add automacilly this ip to access hosts when user is creating db ?


Regards
Greg
 
Did you try this?

Edit /usr/local/directadmin/conf/mysql.conf and set
host=your.otherserver.com
Click to expand...

or using an IP address instead of hostname? Or are you asking for something different.
 
solution

i meant that when i add a database defauult access hosts is set to : localhost + my ip address , no 10.0.0.2
ive solved it creating script - database_create_post.sh in custom directory .this script adds that ip wile creating a db.

Regards
Greg
 
is there a complete full howto for using a remote SQL server ? or should i use the above mentioned stuff?
 
You must log in or register to reply here.
Back
Top