MySQL Questions

[netstability]

I am attempting to solve a mysql dilemma here and think a VPS could be a possible solution; I just need a bit of clarification on a few things.

1. What I need to do is have a remote mysql server "mirror" one that is local so that I can have web pages that I host retrieve information from it rather than my local machine. The information needs to sync approx every 2min, but this is only an "update" sync so it should be a minimal traffic hit.

2. I am under the impression that creating a "mirror" mirrors the entire mysql server not simply one account. This is why I am looking at a VPS as a possible solution. Am I correct in thinking that an instance of mysql within a VPS would be fully independent from any other VPS on the same machine?

3. Finally, if #2 works, is it possible on a VPS to run more than one instance of mysql so that I can have the "mirror" server as well as one for database driven sites that I host?

Thank you for any information you can provide, I realize this is a somewhat unique request.

[charles]

This is mysql replication. First, please note that you *can* in fact replicate only specific databases, so you shouldn't need to do anything fancy. Second, yes a VPS is completely isolated so the databae is distinct. And yes you could run multiple instances of mysql on the same server if you wanted to, but there is no need.

hth
charles

[netstability]

I probably don't need a VPS just yet, especially if this can be done with individual databases. Would it be possible and would someone be able to assist setting this up on a DA reseller account?

[charles]

Since this requires changes to the database condfiguration file, you cannot do this on a reseller account. If you had a VPS it's not something we can support, but I'm sure a dozen people on these forums could help you set it up.

See

http://dev.mysql.com/doc/refman/4.1/en/binary-log.html

And pay attention to "binlog-do-db". Also since you'd be doing this over the WAN, please use ssl.

http://dev.mysql.com/doc/refman/4.1/en/ssl-options.html

hth
charles