Waqar Ahmed
asked on
Db dump with insert for new and update for existing records
I have 2 MySQL databases who are identical in every aspect.
But DB1 changes daily with either some records changed or few new added.
I need to take the DB dump daily and then import to DB2 for them to stay identical.
My question is that, how can I dump the DB 1 so the dump should insert only new records to DB2 and for records that are changed, just update them.
But DB1 changes daily with either some records changed or few new added.
I need to take the DB dump daily and then import to DB2 for them to stay identical.
My question is that, how can I dump the DB 1 so the dump should insert only new records to DB2 and for records that are changed, just update them.
ASKER
I am using mysql 5.1, and the DB1 is opperational with a live site so I don't want to touch it at all.
Your first solution of simply dump and import is not feasible as I am looking to do all this automatically at the run of cron.
I saw this guy (the best chosen solution) https://stackoverflow.com/questions/16974120/mysql-import-only-missing-rows
It all makes sense, but the only problem is what will happen to the updated records of DB1. How will the "INSERT IGNORE" handle that?
On the contrary your solution of "Replication" also makes good sense but I am not sure how I will implement that using plesk.
Your first solution of simply dump and import is not feasible as I am looking to do all this automatically at the run of cron.
I saw this guy (the best chosen solution) https://stackoverflow.com/questions/16974120/mysql-import-only-missing-rows
It all makes sense, but the only problem is what will happen to the updated records of DB1. How will the "INSERT IGNORE" handle that?
On the contrary your solution of "Replication" also makes good sense but I am not sure how I will implement that using plesk.
mysqldump -t --insert-ignore --skip-opt -u USER -pPASSWORD -h 127.0.0.1 database > database.sql
That export command with the -t --insert-ignore --skip-opt options will give you a sql dump file with no DROP TABLE or CREATE TABLE commands and every INSERT is now an INSERT IGNORE.
BONUS:
This will dump a single table in the same way:
mysqldump -t --insert-ignore --skip-opt -u USER -pPASSWORD -h 127.0.0.1 database table_name > table_name.sql
I needed this today and could not help but to share it!
That export command with the -t --insert-ignore --skip-opt options will give you a sql dump file with no DROP TABLE or CREATE TABLE commands and every INSERT is now an INSERT IGNORE.
BONUS:
This will dump a single table in the same way:
mysqldump -t --insert-ignore --skip-opt -u USER -pPASSWORD -h 127.0.0.1 database table_name > table_name.sql
I needed this today and could not help but to share it!
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
If that's not feasible, you could look at Replication. This sets up your DBs as Master (DB1) and Slave (DB2) - changes to the master are synced to the slave. You can read up on it here -> https://dev.mysql.com/doc/