Mysql export piped to another mysql server using a

I am trying to do an export of a table in 1 mysql server and pipe the data directly to the new sql server.

Lets say that I have a User Table that has 2 groups of users group = 1 or group = 2

What i want to do is something like:

SELECT * FROM `User` WHERE `User`.`group`=1 | mysql -h newhost.com -u root -p dbname newTable

Some of these queries will have joins if that makes a difference

I have tried:

mysql -e"SELECT * FROM `User` WHERE `User`.`group`=1" -u root -pxxxxxxx dbname| mysql -h newhost.com -u root -pxxxxxxxxx newDbName NewUserTable

This for some reason just throws tons of errors. It looks like it opens the mysql terminal then it just shows usage info.

Heres a closer example to what I am trying to do:

mysqldump -u root -pxxxxxxxx dbname Article --no_create_info --max-allowed-packet=1073741824 --lock-all-tables --where 'article_id in (SELECT article_id FROM Article LEFT JOIN Client on Article.client = Client.client_id LEFT JOIN Company on Client.company = Company.company_id WHERE Company.name="Company1")' | mysql -h rds.amazonaws.com -u root -pxxxxxxxx newdbname Article

When i run this in the terminal i get the mysql usage options output followed by: mysqldump: Got errno 32 on write

SOLUTION

Using mysqldump i was able to use a custom where statement that had a nested select that selected the ids of the table i was looking to move.

NOTE: When using nested SELECT you must include the table name with the column name. IE Table1.column1. Also make sure that all of your options come before your database name. And finally when piping the data to the mysql server do not include the table name after the database.

Final working command:

mysqldump -u root -pxxxxxxxx --no_create_info --max-allowed-packet=1073741824 --lock-all-tables --where 'article_id in (SELECT Article.article_id FROM Article LEFT JOIN Client on Article.client = Client.client_id LEFT JOIN Company on Client.company = Company.company_id WHERE Company.name="Company1")' dbname Article | mysql -h rds.amazonaws.com -u root -pxxxxxxxx newdbname

This should work. Using mysqldump with the table & where clause set.

mysqldump -u [user] -p [password] [dbname] User --no_create_info --where="User.group=1" | mysql -h [hostname] -u [root] -p [dbname]

More details on more elaborate queries using mysqldump can be found here. Including an example of how to hook a join into the query with this example:

mysqldump -u db_user -p db_name table_name --no_create_info \
--lock-all-tables --where 'id in (select tn.id from table_name as tn \
join related_table as rt on tn.related_table_id = rt.id \
where rt.some_field = 1)' > data.sql

The basic idea is the where clause has it’s own query which would do what I believe you are trying to achieve.