This is one of those reminder-to-self style posts.
To copy mysql table tablename from database1 on server1 to database2 on server2, issue the following command (on either server)
mysqldump -h server1 -u user1 -ppassword1 database1 tablename | mysql -h server2 -u user2 -ppassword2 database2
user1, password1, user2, and password2 are the respective usernames and passwords to use on server1 and server2, respectively. Note that there is no space between the -p and password (unlike the other switches).
Popularity: 8% [?]
#1 by Mark - September 10th, 2009 at 04:30
Is there any compression used in this method? I don’t think there is, so for large databases I don’t think this is a good method.
#2 by Bricky - September 10th, 2009 at 15:13
@Mark
It’s hardly rocket science to put ssh and gzip in the pipe. So yes, I think this is a particularly good method, even for large databases. (I moved a 7GB db the other day using it, and, aside from the running it over ssh, the only change I had to make was to add
--single-transactionto mysqldump, to prevent it locking up the source db)