How to copy a table from one mysql database to another mysql database

Question

I need to copy a table from one database to another. This will be a cronjob. Which one is the best way to do it? PHP script or Shell Script. The problem with PHP, both databases has different usernames and passwords so I can't do it like this.

CREATE TABLE db1.table1 SELECT * FROM db2.table1 

Should I just connect first DB get all records and insert all to new database using WHILE loop or there is a better way?

I prefer a shell script to do this instead of PHP script.

Thanks

Answer 1

If you need to copy the table on the same server you can use this code:

USE db2;  CREATE TABLE table2 LIKE db1.table1;  INSERT INTO table2       SELECT * FROM db1.table1; 

It's copy+pasted from here: codingforums.com

It's not my solution, but I find it useful.

Answer 2

I'd dump it. Much less complicated than anything PHP based.

mysqldump -u user1 -ppassword1 databasename > dump.sql mysql -u user2 -ppassword2 databasename < dump.sql 

MySQL reference: 4.5.4. mysqldump — A Database Backup Program