MySQL Table Replicating question...

dolvmin · 02-20-2006, 11:26 AM

How do I copy a table from one db to another within the same MySQL server? Could I please have a command line answer. I'm creating a program that will need to do this in automation so that users who register to one forum can be automatically registered to several without needing to re-register. Thanks in advance...

dolvmin · 02-21-2006, 12:18 AM

I know this is a hard question, but is it really so hard that Linux Database Guru’s don't have an answer for it?

I asked a tech for a suggestion that works at my job site. He laughed at me when I told him I had my MySQL on Linux. His expression was as if I was working on a kid's project. He told me I could have easily found the answer in the HELP for SQL on Microsoft. I kept my head up high and believed in what I had. Now I'm not feeling so proud. Not necessarily because no one answered here, but rather because the last (10?) questions I had resulted in 0 responses and the 0 response here seems to be stacking a larger number. 7 of them I managed to fix on my own. 2 of them were resolved by switching distributions. This is the 10th.

This community was a great place to seek for answers. But lately it has felt as if it was a place to destroy hope.

MRMadhav · 02-21-2006, 01:05 AM

try this command in mysql but that does not recreate the keys though!

create table `newdatabase`.`newtablename` type=MyISAM select `field1`, `field2`,... from `originaldatabase`.`originaltable`

dolvmin · 02-21-2006, 01:35 AM

Problem with this line is if a table with the same name exist, it does not over write.

I did however find the answer for myself just now. Here's what I got (and this does work the way I want it too):

Code:

#! /bin/bash
# File created by Xenthorious on 02/21/2006
# This file copies table name smf_members and replaces the tables with the same name
# on all following databases.

# Obtain table information!
mysqldump -pPASSWORD -e web_main_forum_db smf_members > smf_members.dmp

# Replace installed table with the obtainee for database web_1_forum_db.
mysql -pPASSWORD -e'drop table smf_members;' web_1_forum_db
mysql -pPASSWORD web_1_forum_db < smf_members.dmp

# Replace installed table with the obtainee for database web_2_forum_db.
mysql -pPASSWORD -e'drop table smf_members;' web_2_forum_db
mysql -pPASSWORD web_2_forum_db < smf_members.dmp

# Replace installed table with the obtainee for database web_3_forum_db.
mysql -pPASSWORD -e'drop table smf_members;' web_3_forum_db
mysql -pPASSWORD web_3_forum_db < smf_members.dmp

# Remove table export for security reasons.
rm smf_members.dmp

#EOF

BTW, thank you very much for responding. It's nice to see that someone gave a damn.