CODEWITHSUNDEEP
mysqlreplicationshared-hosting
TheBlackBenzKid
TheBlackBenzKid

Reputation: 27105

How to clone mySQL continuously .. instantly on shared hosting

I have a MySQL install on a shared server and have access through phpMyAdmin. I want to make a continuous, real time clone of that database to a cloud mySQL database (we have created an Nginx-ready MySQL server specially for this database) I want to create a real time clone of the old one, then update code to point to the new database...

Upvotes: 2

Views: 6500

Answers (2)

halfer
halfer

Reputation: 20487

I think you will have difficulty doing real-time replication of a MySQL in a shared server environment. Since you appear to be moving db servers, I would be inclined to do a hot copy of your data, and install that on the new db server. At the same time as taking that copy, you should switch on query logging on your application.

Your switch over would then consist of running logged queries against the new database (faster than they were logged!) and finally, at a point that all logged queries have been run, switching the configuration of the app so that the new db is used.

Edit: the problem with a hot copy is that data is being written to the db at the same time as it is being copied. That means that the 'last updated' time will be different for each table. On that basis, is it possible in your application to set up a 'last_updated' column for each row? If so you will be able to tell for each table which logged queries still need to be copied.

Upvotes: 1

daxroc
daxroc

Reputation: 316

What you're looking for is replication. It has far to many options to cover here in a single post.

http://dev.mysql.com/doc/refman/5.5/en/replication.html

If your going to do replication over the internet you'll want to secure it.Your host might allow a virtual local area network So this doesn't use up your bandwidth resources.

A great set of tools from percona you should look at are maatkit

https://launchpad.net/percona-toolkit

Documentation and usage examples http://www.maatkit.org/doc/

It's good for other tasks but it also allows you to replicate a live database quickly.

When your working with live databases make sure your backups are upto date.

Upvotes: 1

Related Questions