Wednesday, November 12, 2014

mysqldump with INSERT … ON DUPLICATE

Thanks to stackoverflow for another helpful mysqldump tidbit. This logic will allow you to do updates to your database via a mysqldump. Similar to querying with...
  
  create table db1.table1 like db2.table1;
  insert into db1.table1 select a,b,c from db2.table1 as db2t1 ON DUPLICATE KEY UPDATE a=db2t1.a,b=db2t1.b,c=db2t1.c;
posted by RolandoMySQLDBA
  
  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.
  
Keep this paradigm in mind

mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using --replace (or --insert-ignore) and --no-create-info into DUMP2
load DUMP2 into DB3