Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a LAMP server currently running. It's been falling over a lot because there is too much stress on the DB. I'd like to split the DB out of there and run it on a separate machine. I have about 100 websites currently running on the server already.

Here's my plan: 1. setup a new server, install mysql on the server 2. on the webserver, open the port 3306 to be an iptunnel to 3306 on the new server

Is this advisable or would it be better to go through and manually change the database server in the scripts to the other one?

If I were to do that, presumably I'd have to adjust all the users in the database to allow access from the webserver now right?

Thoughts?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

You've missed the important bit: transfer all the data across. Doing this without downtime is difficult (though not impossible, especially if you're willing to wade into the murky world that is MySQL multi-master replication), if you're willing to survive some downtime you can either do it in one big hit, or database by database (which if you've got lots of separate databases can be a good way to test your migration plan with a small, low-value site rather than blowing everything up in a giant mess).

As far as naming goes, the answer is service CNAMEs -- a CNAME 'db' or 'mysql' or something, that points to your database server.

If you're doing a one-big-cutover (or you only have one database), setup this CNAME now and point to 127.0.0.1, and start changing all your scripts to use the CNAME. Search-n-replace or whatever is needed (also take the opportunity to centralise this sort of configuration if -- as it sounds like -- you've hard-coded your DB info in lots of separate scripts, in case you have to do something like this again in the future), but without actually moving any data to the new server. Once everything's running through the CNAME, then you can start moving things around, and it's a lot easier to change the CNAME at the end of the maintenance rather than having to change a thousand scripts while in a maintenance window.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.