Remote mysql database server setup [tunneling] question

[mudmanc4]

Last time I had to do this I used [bind-address=wha.te.ver.ip] with a specific port , granting users, flushing and dealing with iptables. Done.

This time I'de like to create a tunnel between the two , and using the external server solely as sql .

Iv'e got everything installed, rsync set up locally and remote [for when I bork something lol ] sql 5.3 x86_64 centos 5.5 running only basic services needed for access and the sql server.

The httpd server is running plesk which has a remote sql server ability, but it only has very basic settings, such as administrator and password duh ? So this will be done with , and without plesk support , which bothers me not , and is one good reason the secondary server is running no GUI , including myphpadmin or anything else unnecessary like it.

So this will be done with plesk support , to allow the creation of databases on the remote machine from the users panel. As locally the sql server will be un installed completely.

Iv'e already set up password-less ssh logins for myself locally as well as the backup server via rsync. Iv'e read quite a bit about tunneling sql, but before I begin I'de like to hear from anyone with experience , or any suggestions.

Thanks for any replies.

[TriRan]

Document what you do because I'm very interested in this infact I already have mysql hosted remotely but it is not tunneled id like to set one up aswell

[mudmanc4]

I always make a new page when I learn something new. I start it as I go add to it then wrap it in css and go. So thats not an issue.

It shouldn't be too difficult in any sense , but making sure there's nothing open , * not listening on any other port then the specified non standard, creating a key set between the application server and the database server while allowing users to create a database through the panel, or via ssh is whats going to make me think.

I'll make a simple database , maybe with wordpress or something , a wiki or who knows and test with this. Then I'll have one of you guys portscan and probe the hell out of it from every direction , even give out the credentials.without the keys it should be impossible.

I'm thinking of a way to do a mac address auth as well. No ? But if it's too complex **, it'll slow the data transmission down too much.

edit : *actually listening on no ports at all

** Keep alives ?

And all the databases are run as different users with different passwords

[dlewis23]

The httpd server is running plesk which has a remote sql server ability, but it only has very basic settings, such as administrator and password duh ? So this will be done with , and without plesk support , which bothers me not , and is one good reason the secondary server is running no GUI , including myphpadmin or anything else unnecessary like it.

If you go rid of plesk and used cPanel it would make this so much easier. cPanel has remote MySQL options built in that make it super easy.

[mudmanc4]

If you go rid of plesk and used cPanel it would make this so much easier. cPanel has remote MySQL options built in that make it super easy.

Yea my thoughts are chuck all GUI application access, it just makes things more difficult.

The plan Iv'e had on that server is $300 /annually 1000GB /month 512 MB /memory , 100 domains, unlimited databases, 30GB disk blah blah , so I can't go wrong really. But Iv'e well overgrown that and have drawn attention. Hence the secondary server with the same resources less a bit , no panel ( unless i install one ) which i will not.

So plesk is the only way with that pricing, I could chuck it , but really I'd have to wipe the VPS and start playing outside of what it's set up for. Which I did at one point, but went back because at that time i could not administer dns zones for separate domains ect.

And plesk has that ability , but i want to do more then it can handle within the application itself.

[mudmanc4]

At this point Iv'e been working with several variables to benchmark overall system performance for mysql. Yes , I started from a basic point of usability, resource allocation and performance compared to the last several years of logs.

Ive boiled it down to using debian (squeeze) for the database server. Although the latest stable mysql-server is 5.1 , I don't foresee any issues. At least at this point.

Any thoughts ?

[mudmanc4]

I'm at the point of {way too far in depth} concerning RSA / DSA ssh keys.

Since DSA is faster at generation , but slower validating, and RSA is really no longer the industry standard, other then USB cards ect. I did say industry standards , not what people are still using lol

There's quite a bit of variable in any way this is done, considering this is for database security, speed as well as security are concerns. As well as staying , as much as possible PCI compliant.

[TriRan]

I'm at the point of {way too far in depth} concerning RSA / DSA ssh keys.

Since DSA is faster at generation , but slower validating, and RSA is really no longer the industry standard, other then USB cards ect. I did say industry standards , not what people are still using lol

There's quite a bit of variable in any way this is done, considering this is for database security, speed as well as security are concerns. As well as staying , as much as possible PCI compliant.

in my current setup i run my DB server on quite a bit more hardware then that

i'm currently running 4 servers

2 load balanced round robin web servers one hosts httpd/ftpd/dns the other one is rsync'd running only httpd both running quad core 3.5GHz cpu's with 2 GB of ram

1 Mysql, Mail, DNS server with a quad core 2.4GHz cpu 2 GB of ram but much higher guaranteed IO then the httpd

and 1 windows server running IIS7.5 to host a webservice for a project being hosted under mod_mono on the linux servers its a quad core 2.4GHz with 1GB of ram

as far as performance the current setup has handled about 1000 concurrent users but has about 6000 concurrent user capacity

i'd really like to tunnel the SQL but it will bite if it hinders the performance i'm currently getting

[mudmanc4]

6000 concurrent connections on 2GB ram running apache ? You meant users online right ? Even 1000 concurrent accessing an NGIX server would be pushing it way over the top , unless of course it's 90% static material ?

If you figure apache will need 10 MB ram / concurrent request and you have 1000 instances that in itself is way off the charts at around 10 GB.

I know my comment is literal , and unlikely qualified for your setup , there's not much room for application with that math. What am I missing ?

edit: I had to go redo the math in a different light. Roughly 10KB per request at 1000 concurrent connections, thats 9.7 MB per second ( thats CONCURRENT, or persistent database connections. If set up this way. Am I wrong ?

[TriRan]

Indeed the mysql server can't support that I was more referring to my httpd servers I'm running nginx reverse proxy to Apache worker I've tested up to 3k concurrent with only one Web server running and mysql only used 14 threads at 129 connections I don't use keep alive on Apache since it is handling only dynamic content I think the reason I'm able to hit that during the tests is because it's fake traffic hitting cached material same for the mysql connections its mostly hitting cache I recently added the 2nd httpd server in the round robin setup and haven't really had time to test it so my numbers weren't exactly fact just guessing I'm sure my mysql server will be the bottleneck but that can be sorted by replication to a read only mysql server so that one handles reads the other does writes

[mudmanc4]

I had to set up a massive growing [cannot name it] forum with dual databases a couple years ago, really made a difference. They have last time i checked over 90k members with nearly 6 million posts, at around 10k active members, and at any given time of the day roughly 2-3 thousand members on at a time. Thats the only thing that saved the place at the time. Not sure what there doing now I have nothing to do with it.

As far as this project, I'm experimenting with using sockets 'SOCKS' or chrooting the sql server, which I'm finding is no trivial task in VPS considering it needs to find all it's resources within the jail. So just creating it's own directory and cramming it in there is not going to work as securely as setting it up within it's own volume will.

[dlewis23]

which I'm finding is no trivial task in VPS considering it needs to find all it's resources within the jail.

Are you using a OpenVZ powered VPS?

[TriRan]

I had to set up a massive growing [cannot name it] forum with dual databases a couple years ago, really made a difference. They have last time i checked over 90k members with nearly 6 million posts, at around 10k active members, and at any given time of the day roughly 2-3 thousand members on at a time. Thats the only thing that saved the place at the time. Not sure what there doing now I have nothing to do with it.

As far as this project, I'm experimenting with using sockets 'SOCKS' or chrooting the sql server, which I'm finding is no trivial task in VPS considering it needs to find all it's resources within the jail. So just creating it's own directory and cramming it in there is not going to work as securely as setting it up within it's own volume will.

you might be able to get your VPS provider to make a seperate partition for you so you can do something like that

and yeah if any of my sites ever get that big i will have no problem adding more servers i'm close to just getting my own dedicated right now but i just don't have the justification so until then i will just keep throwing more VPS's at it the american way right?

during my last big night of clients the servers didn't really break a sweat with all the concurrent users it had mysql was less then 2% cpu used running at about 8MB/s, apache was using very little cpu but about 1GB of ram nginx was using very little ram but about 30% cpu

my mysql server is the only server i currently have on a gigabit port besides the windows server but the windows server isn't even live yet the project is still under heavy development

[mudmanc4]

Are you using a OpenVZ powered VPS?

Virtuozzo , which is the paid version correct me ?

Mark I'm waiting for a reply from support at the host on just that partitioning question.