Solved

Replicate data from one MySQL DB to another

Posted on 2011-02-22
4
253 Views
Last Modified: 2012-05-11
Hi,

I have two servers, hosted by different companies.  Both running MySQL.  I was wondering if there's a script that could run automatically that would transfer all of the data in the DB from one server over to the other.  Preferable something that ran automatically, like every 4 hours.  The table structure would be the same on both servers.

Thanks
0
Comment
Question by:Rush_2112
  • 2
4 Comments
 
LVL 6

Expert Comment

by:Ferrosti
ID: 34952100
It depends on how you need to handle your data. What is the purpose of this scenario?
Usually one would go for a binary log and apply it to the second server (master/slave setup).
0
 
LVL 78

Expert Comment

by:arnold
ID: 34952205
Do you have direct access to the mysql servers?
I.e. you would have an application/script running locally that will connect to each mysql server and perform the select/if exists update else insert into the other.
You can configure the frequency within your local system as well as add the logic to speedup the process by not retrieving unchanged data unnecessarily.
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 35072410
Both servers are on shared hosting plans, so  I don't know if I have the kind of access you're talking about.  I was hoping there would be a msysql script that could auto-run.
0
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 35072980
In the scenario you describe, you can not without using an external process i.e. on your local computer you setup a script that will access a special PHP page on one host to get the data and then use a special php on the other host into which you will insert the data.

I'd think most hosting firms include access to phpmyadmin that has the ability to export backup the datatabase.  Using phpmyadmin you can then import/restore the database on the other host.

You could as part of access to your php page trigger checks to the other to perform replicative tasks.
i.e. a php request is received. and a response is provided instead of the PHP exiting, it will query the remote site and further process data.  note that this will increase the load on your system.  Note futher, that your hosts may prevent outgoing requests from the web servers which is what is needed from one to be able to access the other.

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sort in mysql based off of query param 4 29
mysql database, schema and table creation 13 78
java mysql insert application 14 41
Not listening to where 1 9
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question