1

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?

Madno
  • 193
  • 2
  • 14

1 Answers1

2

--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
Mike
  • 21,910
  • 7
  • 55
  • 79
  • Now I am getting: ERROR 1046 (3D000) at line 22: No database selected mysqldump: Got errno 32 on write – Madno Oct 28 '17 at 15:22
  • is the database called `wordpress` ? – Mike Oct 28 '17 at 16:21
  • Yes. It is called wordpress. – Madno Oct 28 '17 at 16:22
  • Ok i updated.. might be the remote mysql connection.. add a `--compress` flag and also make sure the database on the other side is created and I also added the mysql command to import those tables to a database called `mysql` So check the answer for the updated command – Mike Oct 28 '17 at 16:39