0

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.

4
  • Two databases on the same server or on two different servers? Commented Jun 16, 2013 at 18:56
  • Are all the tables MyISAM ??? Commented Jun 16, 2013 at 19:32
  • @Michael-sqlbot different server.
    – sakibmoon
    Commented Jun 16, 2013 at 20:09
  • @RolandoMySQLDBA yes they are both MYISAM
    – sakibmoon
    Commented Jun 16, 2013 at 20:10

2 Answers 2

1

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

2
  • +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. Commented Aug 15, 2013 at 23:09
  • @BillKarwin Perhaps shutdown and cold backup would be better. Stupid MyISAM !!! Commented Aug 15, 2013 at 23:42
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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