Help with cloning entries accross databases

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Help with cloning entries accross databases

Post by GreyWolf »

This is for all you PHP/SQL fundies.

I need a PHP function that will update the entries in database B with entries from database A.

The tables are already in place.

PLEASE PLEASE PLEASE HELP!
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Help with cloning entries accross databases

Post by RuadRauFlessa »

Are the database structures exactly the same?
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

Yes. The tables I want the entries cloned in, are EXACTLY the same.
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Help with cloning entries accross databases

Post by Ron2K »

I'd personally do this at the database layer (I've had experience with a Windows Service that used to sync data between a client's intranet database and live site database, and it was NOT pretty) - perhaps some form of replication?
Kia kaha, Kia māia, Kia manawanui.
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

Ron2K wrote:I've had experience with a Windows Service that used to sync data between a client's intranet database and live site database, and it was NOT pretty
this is exactly what I need the function for. Why was it not pretty?
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Help with cloning entries accross databases

Post by Ron2K »

There's a few things (amongst others) that your code would need to consider:
  • How do you keep track of what you've synced and what you haven't synced?
  • If an item is changed in both locations (inbetween syncs), how do you merge the changes?
  • How do you handle any form of communications failure?
It looks simple on the surface, but you'll find that it's anything but simple when you actually do it. After inheriting said service when the original developer left, and then spending the next nine months fixing the weirdest bugs in the system, my preference is now to rather use built in database server capabilities that serve this purpose (i.e. replication on SQL Server) than try to reinvent the wheel. ;)

The DBAs on this forum would be able to give you better advice with respect to replication - my knowledge of it is very shaky. (I've only experimented with it once, in a test environment - and that was five years ago.)
Kia kaha, Kia māia, Kia manawanui.
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

How do you keep track of what you've synced and what you haven't synced?
BAsically I see it as a cron job that runs once a night, compares and updates
If an item is changed in both locations (inbetween syncs), how do you merge the changes?
Its a 1 way update.
How do you handle any form of communications failure?[/list]
Best part, the databases sit on the same server
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
KALSTER
Forum Moderator
Posts: 5439
Joined: 12 Oct 2008, 02:08

Re: Help with cloning entries accross databases

Post by KALSTER »

Something for you guys to laugh at:

Being an ignorant noob, I'd try and use one of those action recording programs and have it record me importing each database into Excel and running a compare and replace in there (maybe with an if function or something), exporting it afterwards. Then set the sequence to run after hours.
"It is the mark of an educated mind to be able to entertain a thought without accepting it." - Aristotle
Intel i5 2500; AsRock Z77 Extreme 4; Asus GTX580; 4x 2GB DDR3 1333; Intel 520 240GB SSD + 2x WD 3TB + 2TB Samsung; Samsung 22X DVD/RW; 23" LG W2343T-PF; Huntkey 700W
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

bump
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
-Prometheus-
Resident Drama Llama
Posts: 967
Joined: 05 Mar 2008, 02:00
Contact:

Re: Help with cloning entries accross databases

Post by -Prometheus- »

........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
Last edited by -Prometheus- on 04 Apr 2011, 03:48, edited 1 time in total.
BBLounge - Broadband and Technology forum
Please like our facebook page
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Help with cloning entries accross databases

Post by RuadRauFlessa »

+1 Prometheus (AKA the thief who stole fire - may your liver regrow for eternity).... The questions posed by Prometheus is valid. However if you want to do it for whatever scaly reason you have one of three options..... 1) Triggers - Which personally I won't do. 2) SQL data replication - probably the best depending on how quickly you need the data to be accessible. 3) execute the DML query twice but each time on a different database.

But still I would like to know why you want to replicate the data to an identical database on the same machine. If it was on another server you could justify it for a data cent re or something.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

Ok. I have created 2 sites. An intranet site and a website. They both run of the Joomla cms with the sobi2 cck component for storing certain information. Now all the information will be entered on the intranet site, but the client wants some of it shared on the website. I have explored all the options and replicating the information is the best and easiest one I could come up with.
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Help with cloning entries accross databases

Post by RuadRauFlessa »

Errr how about security rights on the same site ?

One website... 2 roles... have internal users assigned to one role and external ones to another. Or better yet don't register external people and then just don't give them access to sensitive information.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
hamin_aus
Forum Moderator
Posts: 18358
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: EVGA GTX680 SC
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Help with cloning entries accross databases

Post by hamin_aus »

I have a PHP script which will move all tables and their contents from one database to another - PM me your mail address if you want and I can mail it to you, but be warned that although the script works on the DB's it was written for, I did not write it and cannot help you edit the code to suit your requirements...


What is wrong with a job that creates a mysqldump of the tables you want, gzips it and copies it to the other server where it can then be restored?


I would avoid using replication if your DB is mySQL, it's not the most reliable out there... it's idea of replication is to send every insert update and delete that happens on the master to the slave as a separate text file that gets executed sequentially... so rudimentary it hurts.
Image
GreyWolf
Registered User
Posts: 4754
Joined: 06 Aug 2003, 02:00
Processor: PHENOM II 945
Motherboard: Asus M4A78
Graphics card: HIS ICEQ 4850 1GB
Memory: 4GB CORSAIR XMS II 1066
Location: , location, location!

Re: Help with cloning entries accross databases

Post by GreyWolf »

Ruad...unfortunately the client wanted 2 sites. The intranet site is actually very complicated and the website is very simple, with only a small section needing to be shared.

Thanks jammin. You have pm
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
Post Reply