1

I just had my site hacked and they were able to do some damage to my current database.

Anyway, I have a backup from few days ago. The problem is that the current database had a few thousand more posts and threads / users.

I am simply wondering how I could possibly go about merging the two databases?

The two databases have the exact structure, and I want the backup to overwrite any record from the current database, just that I want the current database to populate any new records like posts, threads, users and such.

I know you can merge two tables, if they have the exact structure, but what about two databases that have the same structure?

Maaz
  • 4,193
  • 6
  • 32
  • 50

3 Answers3

3

I assume you have a schema s1 and a schema s2.

To insert all rows of a table in s1 into a table in s2, while overwriting existing lines, you can use:

REPLACE INTO s2.table_name
SELECT * FROM s1.table_name;

If you do not want to touch existing lines:

INSERT INTO s2.table_name
SELECT * FROM s1.table_name
ON DUPLICATE KEY IGNORE;
Kijewski
  • 25,517
  • 12
  • 101
  • 143
  • By schema do you mean database? – Maaz Apr 02 '13 at 23:00
  • Yes, indeed. Though the word "database" may be "more right" in this case. http://stackoverflow.com/questions/298739/what-is-the-difference-between-a-schema-and-a-table-and-a-database – Kijewski Apr 02 '13 at 23:01
  • ON Duplicate, does that mean if any of the columns in the table have a value that is being injected then it wouldn't overwrite right? – Maaz Apr 02 '13 at 23:19
  • I'm also getting this error when I try the second code. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE' at line 3 – Maaz Apr 02 '13 at 23:20
  • If you supply `ON DUPLICATE KEY IGNORE` and a row with the same primary key is already in the table, then the insertion is skipped for that line. – Kijewski Apr 02 '13 at 23:20
  • Can you please paste the exact statement that you have executed? – Kijewski Apr 02 '13 at 23:21
  • `INSERT INTO shadygam_xfRestored.xf_user SELECT * FROM shadygam_forum.xf_user ON DUPLICATE KEY IGNORE;` – Maaz Apr 02 '13 at 23:22
  • Primary Key? is that a certain column that is given this attribute in the table? – Maaz Apr 02 '13 at 23:23
  • Hm, I do not see an error in your statement. Sorry. The primary key is often a special, synthetic (autoincrement) column. But it can be combination multiple existing columns (mostly in N-to-M mappings). – Kijewski Apr 02 '13 at 23:26
  • Still giving me an error, could I use something other than "IGNORE" to possibly overcome this error? – Maaz Apr 02 '13 at 23:28
  • Possible to ignore a match if two column's values match? for example `ON DUPLICATE table_1 OR table_2 IGNORE;` I don't know the syntax at all, but if the values of one table or the other match, don't overwrite. – Maaz Apr 02 '13 at 23:34
  • The syntax is `ON DUPLICATE KEY ...`, KEY is part of the keyword. – Kijewski Apr 02 '13 at 23:38
  • Well, the error presists, so I was just trying to brainstorm another way of going about it, that's all. – Maaz Apr 02 '13 at 23:48
0

there was some ways to do it: 1.) use Command line tools like schema Sync and mysql diff


2.) or Using SQLyog

find out more here http://blog.webyog.com/2012/10/16/so-how-do-you-sync-your-database-schema/

dr.Crow
  • 1,493
  • 14
  • 17
0

In my experience ON DUPLICATE KEY IGNORE did not work. Instead I found

INSERT IGNORE ta2_table SELECT * FROM ta1_table;

worked like a charm

Scott Reimers
  • 1