Syncing Dev and Live mySql dbs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Vic Kick Ass
Forum Newbie
Posts: 5
Joined: Tue Apr 13, 2004 10:33 pm

Syncing Dev and Live mySql dbs

Post by Vic Kick Ass »

I am using mySql on a mac/unix on the dev server, and a remote Free BSD mySql on the live server. I am using phpMyAdmin to deal with both of them.

What is the simplest / best way to regularly update the live server with records I have added to the dev server?

For example, say I add 20 extensive records on the dev server, and later want to move them to the live server. When I used ASP/SQL/Access I would just cut and paste the records via Access - but I am not sure how to do a similar thing with phpMyAdmin.

All I know how to do is export the dev data to a sql file, then truncate the table on the live server, then run the sql export on the live server - but that seems kinda dicey to me (am I right?) - and leaves a bit of a window wherein data is incomplete and generally screwed up.

HELP ME SPOCK!
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

different os' requried that you handle things a little differently...

2 methods:

1. export/import - but there are risks if there is a heavly load on the server (best to do when there are no users on the app)

2. export your data to text file (via code or mysqldump) and then use phpmyadmin or other gui tool to load the new data

One option here is to simply stop users from using the site for a while - a down for maintainence type page ordupllicate the tables and merge those tables at a later date

hth
Vic Kick Ass
Forum Newbie
Posts: 5
Joined: Tue Apr 13, 2004 10:33 pm

Post by Vic Kick Ass »

regarding the 2nd approach... when I output my dev data as a SQL file, and then try to import it into the live server, the live server complains about duplicate keys, etc... which is why I have had to first truncate the live table, and THEN import the new data - but this really seems like a terrible hack and slash to me. how could I go about exporting only the new data? OR if existing records are updated, exporting only changing, non-key values (or deleting only the modified records from the live server).

Sounds like a job for superman?
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Drop the pk column first, the autonumber will take care of itself...the issue is going to become overwriting live data that you are not gonna want to loose.

Another option is to write a script that will get the data from the dev server and push it up to the production server...

As to the approach of only exporting changed data, a script (and a date column or exported flag (tiny int 1 or 0)) can read the dev db and pull out the data based on that criteria...
Vic Kick Ass
Forum Newbie
Posts: 5
Joined: Tue Apr 13, 2004 10:33 pm

Post by Vic Kick Ass »

This is good advice and pointing me in the right direction! Thanks!
Post Reply