Export Local MySQL Table Into Remote Database

Question

I am trying to export only some tables in a local database, into a remote database which has the same name of these tables + some other tables (I want to overwrite the remote database tables which has the same name with the local ones I have from my database, both databases are called wordpress).

After a bit of Google'ing, I thought the following command should work:

mysqldump -u USER --single-transaction --compress --order-by-primary --databases wordpress wp_posts wp_postmeta wp_comments wp_commentmeta wp_users wp_usermeta -pPASSWORD1 | mysql -u USER -pPASSWORD2 --port=REMOTE_PORT --host=REMOTE_HOST 

But it gives me following error:

mysqldump: Got error: 1049: Unknown database 'wp_posts' when selecting the database 

Why?

Answer 1

--databases lets you specify which dbs you want to dump so doing what you are doing you are telling it to dump all those databases which after wordpress are tables in the wordpress database.

You want this

mysqldump -u USER --single-transaction --order-by-primary wordpress wp_posts wp_postmeta wp_comments wp_commentmeta wp_users wp_usermeta -pPASSWORD1 | mysql -u USER -pPASSWORD2 --port=REMOTE_PORT --host=REMOTE_HOST wordpress 

Just remove the --databases flag. I would also add the --compress flag to the mysql client to import. Also make sure you create the database on the remote side

CREATE DATABASE wordpress; 

Here is a mysqldump help

Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help