How to link 2 MySQL Databases with bash script
I want to insert rows from a specific table in a database to another database with a bash script. The two databases are on different servers. How can I do that?
I want something like this:
INSERT INTO db1.table1(row1, row2) SELECT row1,row2 FROM db2.table2;
But the databases are on different servers.
Thank you.
I think you need to use ssh to execute a remote command and mysldump to get the rows from the remote db and mysql to insert the row to the local db.
ssh <remoteuser>@<remoteServer> mysqldump -t -u <remoteDbUser> -p<remoteDbPass> <remoteDbName> <remoteDbTable> -w<whereCondition> | mysql -u <localDbUser> -p<localDbPass> <localDbName>
Pay attenction that:
Use the man page for more information about the mysqldump command.
I would recommend using the pt-archiver tool in Percona Toolkit. It can copy or move rows from one instance to another. It does its work efficiently and incrementally.
Example:
$ pt-archiver --no-delete
--source h=host1,D=db1,t=table1
--dest h=host2,D=db2,t=table2
--columns col1,col2
--where "1=1" --limit 1000 --commit-each
If you want to do the operation between two MySQL databases on the same server, it is easy. See this thread:
http://forums.mysql.com/read.php?61,3063,4004#msg-4004
If you want to transfer the data from a database on a different server, or if it is a different kind of database, it is more difficult and you'll probably have to use software or build some. MySQL doesn't have the database link feature that Oracle has
链接地址: http://www.djcxy.com/p/75330.html上一篇: 从mysql中选择，并从其他行中计数