mysqldump merge instead of overwrite

Question

I have a dev server with a copy of a database which can be edited and a live real server with that same database in a different state. To move the database from the dev to the live I run from the dev server:

mysqldump -u root -p --opt db_name tbl_name | mysql -u user_name -p --host=live_IP -C db_name

With the appropriate values in db_name, tbl_name, user_name, and live_IP. However, this currently drops the table on the live server and copies the dev version over -- effectively overwriting everything and trashing any new data in the live table. What I really want is to have new lines in the dev server and conflicting lines come from the dev server's copy but that any new lines in the live server's copy to remain unchanged. This is some sort of a merge and I have not been able to find any mention to something like this in the documentation but it seems like it should be possible since it is a common need.

Answer 1

You can do this by adding mysqldump options before the pipe. In your case I think you want --insert-ignore, --no-create-db, and --no-create-info.