如何将表从一个mysql数据库复制到另一个mysql数据库 [英] How to copy a table from one mysql database to another mysql database

问题描述

我需要将一个表从一个数据库复制到另一个数据库.这将是一个cronjob.哪一种是最好的方法? PHP脚本或Shell脚本. PHP的问题是，两个数据库都有不同的用户名和密码，所以我不能这样做.

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

我应该只连接第一个DB以获得所有记录，然后使用WHILE循环将所有记录插入到新数据库中吗?

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

我更喜欢使用Shell脚本而不是PHP脚本.

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

谢谢

推荐答案

我将其转储.比任何基于PHP的都要简单得多.

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参考: 4.5.4. mysqldump —数据库备份程序

这篇关于如何将表从一个mysql数据库复制到另一个mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！