XFileSharing Pro - How to host database on seperate server from Web Server?

Message
Author
RockDiz
Posts: 135
Joined: May 05, 2011 4:47 pm

How to host database on seperate server from Web Server?

#1 Postby RockDiz » Dec 04, 2012 8:44 pm

Hello,

My webserver is getting alot of CPU load with mysql.
I was wondering, how can i host mysql on a seperate server?
index_dl.cgi is already using a lot, so i'd like to put mysql on a seperate dedicated server.

chrda
Posts: 296
Joined: Sep 14, 2009 7:16 pm

#2 Postby chrda » Dec 04, 2012 10:06 pm

Install MySQL on a new server
export database dump, import database dump on new mysql server
change mysql host in XFS config to ip for the new mysql server.

seems its time for you to hire a part time server administrator.

trinsic
Posts: 149
Joined: Dec 21, 2009 9:24 am

#3 Postby trinsic » Dec 04, 2012 10:24 pm

Setting up MySQL on another host is rather easy.

Basically install it like you would on any other machine except for one thing. And that is account privileges require a different setting, by default only localhost can access MySQL so when creating a new user for MySQL make sure that the user can be accessed from another server.

This is a good tutorial on it:
http://www.cyberciti.biz/tips/how-do-i- ... erver.html

You need to bind MySQL to your databases IP address rather than localhost.
Disable skip-networking by commenting it (#)
Grant access to a MySQL user along with the IP of your main server
Or you can use an asterisk (*) which means any IP

You may not need to do Step 7, go to Step 8 and see if you can connect from your main server to the database server.

RockDiz
Posts: 135
Joined: May 05, 2011 4:47 pm

#4 Postby RockDiz » Dec 05, 2012 7:28 am

Both are very helpful replies, thanks a lot guys! :D

Also, would the server specs of the Database Server effect the speed of my Main Web Server? for example, when I navigate in between pages and etc I have some lag (unless my computer is just messing with me)

What specs should I concentrate on when looking into building a server for mysql?
SSD, RAM, GHZ?

trinsic
Posts: 149
Joined: Dec 21, 2009 9:24 am

#5 Postby trinsic » Dec 05, 2012 9:47 am

You should use Mysqltuner first to see if you can optimize what you currently have.

But for a separate system I'd try and get as much of the database buffered (cached) in the RAM as possible so have more RAM than the size of your database.

Then CPU and HDD, or even SSDs; low latency and high speed which are perfect for this. CPU speed is more important than the number of cores.

nyan
Posts: 163
Joined: Oct 28, 2010 8:01 pm

#6 Postby nyan » Dec 05, 2012 1:01 pm

Make sure you have a private lan on a second network card between the two servers too, sql traffic can easily grow a few 100 mbit/s
but first of all, run mysqltuner on your current system... or post your current my.cnf

randy
Posts: 321
Joined: Mar 13, 2012 7:00 pm

#7 Postby randy » Dec 11, 2012 6:21 am

ask sibsoft. they will do it for you if you pay them :)