Replication of a remote MySQL database to local MySQL database

Question

I'm the developer of my employers corporate private website, a HTML5/PHP/CSS powered site for company reports etc. The site, hosted on a shared hosting package, connects to a remote read-only MySQL DB server for authentication and report data queries. This remote DB is mainly used by a separate windows exe.

The remote DB goes down frequently which of course causes the private website to bomb out on a MySQL connection error. I've thought about replicating the remote DB to the site's local hosted MySQL DB and using the local DB as the site's primary and only DB connection.

Is there a way of synchronising the remote DB to the site's shared hosting DB in a simple and pain free way? Like every hour it updates or something?

Or is there a function where the site's hosting package MySQL DB functionality can cache the remote DB data and use that as a fallback.

Thanks in advance.

• Rodrigo Gonzalez: my replication should be enough for you Read dev.mysql.com/doc/refman/5.6/en/replication.html
• Michael - sqlbot: Replication seems unlikely to be a possibility in this case, depending on what exactly OP means by "shared hosting."
• user44632: @Rodrigo - Thanks for the guide I'll look at that now. @ Michael - The shared hosting is a namecheap pro shared linux hosting account. [link] namecheap.com/hosting/shared.aspx [/link]

Answer 1

Check here (http://drcharlesbell.blogspot.cl/2011/06/comparing-databases-with-mysqldbcompare.html)

It explain how to Compare-Copy a DB to another DB, my case is the same as yours.

Depending on your access level you can:

• Use a external client as middle man (to make de compare-copy)
• Script in PHP to check new lines in the original DB and copy them
• Use one of the many tools of MySQL to copy
  • MySQLDBCompare
  • MySQLDiff
  • MySQLDBCopy

Answer 2

MySQL replication can be helpful. Your remote DB (lets call it A) and Future DB on local site (lets call it B) can be configured with Master-Slave replication.

"A" will be master and "B" will be slave (Read Only)

How Master Slave replication works? Basically Whatever changes happens to the master ("A") will be logged into binary logs and further it will be transferred to the Slave server ("B") in asycronous manner. Further the slave will apply those transactions/changes to itself.

Note : You cannot use Slave .i.e. "Server B" for writes.

Let me know if you need detailed steps to configure Master-Slave replication.

Hope this helps.

• Rutvij Kothari: Hi @V_K thanks for your reply. I tried to set up master-slave replication. I have a remote database and local database. Setup is up and running but data on a slave isn't syncing with the master database. Do you know any good source or any idea how can I solve that?
• Vaibhav: can you please share details. Data sync speed is the issue? or data sync is not working at all?. Also share the steps you have performed to setup Master-Slave and below command output. show master status \G ---(On master) show slave status \G ---- (On slave)
• Rutvij Kothari: Hey @V_K thanks for your comment. I was having a different type of problem. Which I solved eventually. Thanks for your input. :)
• Vaibhav: happy to know @RutvijKothari