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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
  • 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 !!!


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

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

    CONCAT('CREATE TABLE ',db,'.',prfx,tb,' LIKE ',db,'.',tb,';', -- copy structure
    'INSERT INTO ',db,'.',prfx,tb,' SELECT * FROM ',db,'.',tb,';') -- copy data
    (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

Your Answer


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.