Home / server / Questions / 289312
Accepted
Jason
Asked: 2011-07-12 20:32:43 +0800 CST

Split existing LAMP to have a seperate webserver and database

  • 772

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?

migration mysql database

1 Answers

  1. Best Answer

    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.

    • 2