Duplicate-Copy-Replicate Database. PLEASE HELP.-{SOLVED}

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
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Duplicate-Copy-Replicate Database. PLEASE HELP.-{SOLVED}

Post by khaki_monster »

hello guyz!

this is like backuping database. how do i duplicate/ copy a database? a scenario is like this, I’d like to copy my database to another database before its Truncated (“Empty”). I use this statement

Code: Select all

“INSERT INTO newDatabase.newTable SELECT * FROM Database.Table”
to move those data into another database(“newDatabase”). Another problem is I cant insert/ append another batch of data with the same statement I use to insert on newDatabase. Why?

hope received some feed'backs from you pepz

tanx in advanced...

cheerz!
Last edited by khaki_monster on Fri Mar 17, 2006 4:46 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

likely, the primary keys are colliding creating errors.
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

so, do you have any suggestion or idea that you'd like to share? :) Moving/ Copying records from one database to another?

i really need some here :oops:

hope to received more feed'backs :roll:

tanx for the reply...

cheerz!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'd select all fields except the primary key (if auto-incremented)
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

woud it be difficult to control those fields like to update/querying?... given all the datails from old database was copied to new datbase.

tanx again...

cheerz!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Here is a sample of the query generated by phpMyAdmin when it copies a database. You may want to look into installing that and using it to copy your database as that would be easier if you are able to.

Code: Select all

CREATE DATABASE `cmsCopy`;

CREATE TABLE `cmsCopy`.`pages` (
`id` int( 2 ) unsigned NOT NULL AUTO_INCREMENT ,
`linkname` varchar( 255 ) NOT NULL default '',
`pagetitle` varchar( 255 ) NOT NULL default '',
`heading` varchar( 255 ) NOT NULL default '',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

INSERT INTO `cmsCopy`.`pages`
SELECT *
FROM `cms`.`pages` ;
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

:( wewwhh!! this is getting difficult for me...

it looks like your idea is some what the same with my previous statement. but thanx for sharing anyway and appreciated...

Code: Select all

“INSERT INTO newDatabase.newTable SELECT * FROM Database.Table”

how do you guyz duplicate a database and with each time appended/updated by new records?
i've been looking over google for some help... but no good results.

im gett'in prostrated already, more tips please... :?

cheerz!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Wouldn't it be better just to run a cron job that backs up the entire database on a nightly basis?
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

NO... My CMS is running with two database, database1 is where all temporary records are to be stored. records are being "cached" then move those temporary records to database2("as an archieve/backup") witch can updated anytime...

cron job is not a problem. i just use cron job for dumping my database server with mysqldump utility every month...

pls help me solved this one.... :roll:

tanx for the attention...

cheerz!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Moved to Databases
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
khaki_monster
Forum Commoner
Posts: 73
Joined: Tue Oct 11, 2005 12:36 am
Location: Philippines
Contact:

Post by khaki_monster »

like how please... i need some sample code. :oops:

:?
Post Reply