Copy mysql table between servers/databases


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).

,

  1. #1 by Mark on September 10, 2009 - 4:30 am

    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. #2 by Bricky on September 10, 2009 - 3:13 pm

    @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-transaction to mysqldump, to prevent it locking up the source db)

  3. #3 by dave on July 26, 2014 - 4:32 pm

    Hey there,

    I tried this using Windows mysql client nothing happens. tried many different things all over the web. Every time I run the command, it just does nothing at all.

    Any ideas on how to do with windows?

(will not be published)


css.php