5 replies to this topic

[cancer10]

Hi People,

I am not too sure if this is the correct section to post this. (Mods pls move this to the appropriate section if not)

This is a common scenario when you need to move your live website which has a good amount of traffic all the time to a new server. How do you manage to move the db WITHOUT locking the db or putting up a "site under maintenance" page and loosing any data?

Whats the best approach assuming the db server is on the same web server ?

Thanks in advance

[Vaielab]

Easiest way would be to do it at night, when you have almost no traffics.
Even big website have downtime sometime.

But if you really can't take your website down for a while, I would suggest sql replication.
Your actual database would be the master, replicate everything on your new database on a other server (slave) until they are syncro.
Once they are syncro, in your code (php, c#, java...) you update to write in your new database, make the slave master, and disable the old (current) database
Copy all your file on the new server, and change the dns.
But this can only be done if you have a dedicated server... I never heard of shared hosting server who allow sql replication

If you tell me what is your database (oracle, mysql, mssql...) I might give you a look with more infos.

Don't forget, if you are changing the server, but not the domain, the dns may take up to 12hours to update itself, so even if you change the dns, some people may still go to the old server, this is why I suggest to update your code even on the old server.
And of course, if you allow your user to upload data to the harddrive (not in the database, but harddrive, like a picture or zip file...) you will still have to transfer them manually after you transfer the server... and if their dns didn't update, they will still upload on the old server, so you will have to be carefull with this, and will probably get some 404 hits.

And this is why it is much more easy to simply close the website for a cople of hours

ps. in your code you can test the ip of the server and know if you are on the old server or new server, and maybe disable upload based on this infos or something

[cancer10]

Hi

Thanks for the reply.

My db server is MySQL 5.x

Do I have to write a SP for the SQL replication to happen?

[bbqroast]

Perhaps copy everything onto a temporary server and then power it up and switch it with the current server? A couple of seconds of downtime at the start and end of the maintenance proceedings, you could even use a consumer PC (desktop/laptop) if you must.

How much traffic exactly? I am lucky because where I live most hits occur at night so things like this are seamless...

[cancer10]

Like 2000 visits a day.

[Vaielab]

cancer10 said:

Hi

Thanks for the reply.

My db server is MySQL 5.x

Do I have to write a SP for the SQL replication to happen?

No store procedure, it's in the database config, that why shared hosting don't allow you to do so.
Here the doc to implement replication MySQL :: MySQL 5.0 Reference Manual :: 15 Replication
But test it somewhere else before, or you might get some error, and your website will go down