Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases. The table name and fields name are different and field numbers are unequal. I need to transport all fields from one database to another. I can import the database as CSV format.

I can use a PHP script which will accomplish this.

But is there any other way to do this easily without any script.

share|improve this question
    
Two databases on the same server or on two different servers? –  Michael - sqlbot Jun 16 '13 at 18:56
    
Are all the tables MyISAM ??? –  RolandoMySQLDBA Jun 16 '13 at 19:32
    
@Michael-sqlbot different server. –  sakibmoon Jun 16 '13 at 20:09
    
@RolandoMySQLDBA yes they are both MYISAM –  sakibmoon Jun 16 '13 at 20:10
add comment

2 Answers

If you have the following scenario:

  • On DBServer1, Database db1 contains MyISAM tables only
  • On DBServer2, Database db2 is the target database
  • /var/lib/mysql is datadir for both DB Servers
  • 10.2.3.40 is the IP address of DBServer1

You could copy the MyISAM tables straight to the target database

From DBServer2, run these Linux commands

cd /var/lib/mysql/db2
scp IPADDRofServer1:/var/lib/mysql/db1/*.frm /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYD /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYI /var/lib/mysql/db2/.
chown -R mysql:mysql /var/lib/mysql/db2/.

That's it. No SQL Processing of any kind. The information_schema database on DBServer2 will autodetect the presence of new MyISAM and incorporate them accordingly.

Give it a Try !!!

CAVEAT : THIS DOES NOT WORK InnoDB TABLES AT ALL !!!

share|improve this answer
    
+1 but isn't it advisable to use FLUSH TABLES WITH READ LOCK before moving MyISAM tables around? Just in case there's some unflushed data in the filesystem cache. –  Bill Karwin Aug 15 '13 at 23:09
    
@BillKarwin Perhaps shutdown and cold backup would be better. Stupid MyISAM !!! –  RolandoMySQLDBA Aug 15 '13 at 23:42
add comment

Maybe the following will help that I used to prefix table names

SELECT
    CONCAT('CREATE TABLE ',db,'.',prfx,tb,' LIKE ',db,'.',tb,';', -- copy structure
    'INSERT INTO ',db,'.',prfx,tb,' SELECT * FROM ',db,'.',tb,';') -- copy data
FROM
    (SELECT table_schema db,table_name tb
     FROM information_schema.tables WHERE
     table_schema in (DB_NAME)
    ) A,
    (SELECT 'test_' prfx) B;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.