WampServer

HI

I am wondering if there is an easy way to syncronise or update Mysql on local machine ( with Wamp) with the live DB hosted on live server.

if someone can help will be great or point me in the right direction.

I am new to this, currently I download a copy of DB from life server ( using export ) , then go to phpmyadmin in Wamp on my local machine, Drop the whole DB and recreate it with the new one... It must be a better way of doing this

Freifer

Short answer...
No it can't be done. as is.

Long Answer:
No it cant be done, but there are some "solutions"

1) Install a cluster. Un less you install a DB cluster between your PC and your Server
Pros: This will keep your DB up to date instantly.
Cons: this is not an easy configuration trick, and requieres a very hight knowledge.
MySQL Cluster info: [www.mysql.com]

2) Make a pseudo cluster, in your app make a log of all sql altering transactions (INSERTS, UPDATES) on any table, and from your Localhost read that log and apply it locally every X time.
Pros: This can emulate the cluster functionality
Cons: This could cause some "data sync" crashes if you modify the same info in localhost and livehost.

3) Download your DB file (as you are doing right now)
Pros: You just need to download your file.
Cons: This solution becomes inefficent when the DB becomes bigger in size. But you can Even corrupt your DB files, if PHP take more that 30 secs to download it (or any time especified in you PNP.INI -> Maxexecutiontime). You can even corrupt your production server DB, and lost your data, so be carefull.

4) Connect to liveserver directly. This is maybe the most easy and cheapper solution, in your localserver scripts. detect if servername == localhost then inestead of connecting to mysql server "localhost" connect directly to your "SERVER_IP", also you must configure your Server to accept external connections (if you have a dedicate IP is betther) to the MySQL server And to the data base.
Pros: you access directly to your server so all data is the same locally and globally
Cons: you are opening a port to the world so you need to be carefull and not open it globally, so take considerations about restricting acces to your ISP IP ranges only (or to your IP only) maybe make a script that grant global access to your dinamic IP only for a few time then remove the access.

Personally, I dont care about this because "in house" servers are only for development so all data is just to test, propouses (all data is downloaded via SQL dumps), and all vital data is in the production server. Only when needed, I do the Option 4, and restrict the acces only to my actuall IP (that is dedicated, on my office, but it cost me some additional money), but also some times I activated the access to my home IP that is dinamic. Just remember to UNGRANT access to NON using IPs.

I also had inplemented the Option 2, for a customer that has 2 services running Local and Global, but as I told you. this can cause, some "collisions" when same data is modified in both sides, then you must inplement a "versioning and priority system" with rules. or a warning system that says that X or Y data is dupplicated and as some admin for the solution.

Actually, the answer is yes IF AND ONLY IF your live host allows you to remote access mysql directly, and as far as I know, most host don't allow it for security reason. If you can remote access mysql, you can either create or google for script to sync/backup or better use commercial app such as SQLyog. To find out if you can remote access, either ask your live host directly or create a simple connect db w/ host is your domain or specific name provided by your host and run this script in your wamp w/ active internet connection; for example:

<?php
@ $db = mysql_pconnect('your_domain', 'your_username', 'your_passwd');
  
  if (!$db)
  {
     echo 'Error: Could not connect to MySQL';
     exit;
  }
  else
  {
     echo 'Successful connect to MySQL';
  }
  
$useDB = mysql_select_db('your_db');
 if (!$useDB )
  {
     echo 'Error: Could not open MySQL DB';
     exit;
  }
  else
  {
     echo 'Successful open MySQL DB';
  }
 
?>

Have fun,

FREE One A Day
FREE Photo
FREE Games
FREE Websites
FREE Portable GPS
FREE WAMP Guides

Interesting answers. I love this forum!

I'm not too hot on this topic...but
* Isn't there some sort of master/slave arrangement that you could set-up to solve this problem?
* Or is that just the same thing as the ''cluster idea''?

See: Replication: [dev.mysql.com]



Edited 1 time(s). Last edit at 07/01/2009 09:37AM by ronnystalker.

Again that can be answer with a yes and no ! LOL

This concepts are more related as Hardware than SOFTWARE isse (even that a software must be configured to make them work...)

So let imagine this, as a NETWORK of computers.
---
A Replication system is a system where you create a MASTER PC (usually the one with more HW power), and other SLAVE/MIRROR machines, that will act if and only if the master PC fails (I dont know well, but as I remember, the replication can be done only betwen 2 or 3 machines, because if you replicate in many machines the master becomes more and more unaccesible).

Anyway all DATA in the SLAVE machines are mirrors from the MASTER, and also this mirrowing process is done ASYNCRONOUS, this means that for some time the MORE ACURATE DATA is only in the MASTER (This is configurable by the admin).

So maybe you may get noticed, at this point, that it requires more resources also in the mirror machines, because all the data is the same resource consumming in all the machines.

We can see this as a BACKUP system in case of a failover.
-----

A cluster in the other hand, is totally diferent, this can be seen as a CLOUD of computers (like the internet), where all the data is NOT in each and all machines that conform it. So as in the internet, all data is divided in CHUNKS of especific data, and as is queryed, the user is routed to the Clouster Node (or nodes) that have that data. This resoults in better resources usage, because not only one machine has all the info and query under a HUGE database on each query request. This is how Search engines work, for example.

Also to mount a functional cluster that makes sence ... there solud be MANY machines in MANY locations! This means at least 2 or more machines, in 2 or more locations. and each one with a corresponding replication system to backup data in case of failover... as in this scenario the minimum "cluster" material is EIGHT MACHINES... and divided in at least two datacenters.... Do you got the bucks?

And yes you can make a cluster in only one site with two machines also, but then what's the point?

Also a cluster is more used as in LOW-LEVEL writing queries, and HIGH-LEVEL reading queries.

So we can also think, that DB Replication is a PREVIOS face for a DB Clouster.

very nice posts.

I have same situation I would like to make master/slave web server without big coasts , I've got 2 PC's and 2 static IP's. Databases will be medium size. It's looks like the solution about replication will fit me but is there some software for this, I wouldn't spend bid money on hardware replicator.

as mentioned, SQLyog will remote sync/backup but it's not free

Have fun,

FREE One A Day
FREE Photo
FREE Games
FREE Websites
FREE Portable GPS
FREE WAMP Guides