 I have DB1 (source db) with x number of members and DB2 (target db) with y number of members. Now I need to update DB2 with DB1 members (no overwrite, no duplicates, just add new/missing members). What do you guys suggest the best way to go about it (phpmyadmin)? I assume that, after backing up the target db, I should export the "Profiles" table from DB1 and then do a straight import of the resulting file to DB2, right? Thanks in advance! |
Actually... What I probably need is a query that will update the Profiles table on DB2 with the Profiles on DB1 table... no? |
Yes, you could use the query manager of your CPanel to do so! Take backups just in case! |
wow, wait wait, check the primary key of the table and make sure you have serial unique values in each before you run the query! i ain't sure if it will try to update the existing records! |
Both databases belong to the same site, its just that I accidentally lost a number of members (you don't want to know...). So, I assume that there are no problems with keys and serials, right? In essence, I am trying to restore member accounts. Now, if I do an import, will phpmyadmin ignore duplicates and just append the missing members? Unless there is a bug with the db structure, I figure it should. However, a query should do a cleaner job, I think. PS Got those backups indeed! |
ummm i'd love to know so that i won't do the mistake myself too! how come both DBs come from the same site but have different records? are they old/new version or something? |
I messed with the db pruning settings for user inactivity period and Dolphin "cleaned" a few hundred members from my site.... If there were a few dozens, I'd just mass mail a photo of myself with a tin foil hat and have them re-register.... Now, I have the necessary backups, so there is hope. I am just going in circles as to how to effectively restore these accounts. Option A: Export "Profiles" table from "old" db; Import file to "current" db. I'd expect for phpmyadmin to add missing members and throw a bunch of harmless errors when it finds duplicates. As long as there is no overwrite, and I end up with both database's members I should be fine. Option B: Export "Profiles" table from "old" db; Then run magic query that will transfer missing members from my "old" table to my "current" database. I am leaning towards "B", but I think I need BoonEx expertise to write the query.... |
I see what you mean, the only thing i don't know is this, does Dolphin do the pruning by deleting the profiles or does it also re-arrange the table records (highly unlikely but who knows) The other aspect is you didn't only lose profiles but activity so probably you wanna merge the old and the new db back together! if you had a recent backup of the full db you'd just roll back to it! look at this link, might be helpful! |
Doubt it rearranges table records, but will check.... If not... Transfer db1 member accounts to db2 by: INSERT IGNORE INTO `database1`.`profiles` SELECT * FROM `database2`.`profiles` Or, to update/merge the entire db, by doing the same but for all tables. PS And send a prayer to Poseidon.... |
just back it all up and go ahead! |
I'll give it a rest and try it tomorrow. Hopefully, one of our BoonEx guys might comment on this too. |
INSERT IGNORE INTO `database1`.`profiles` SELECT * FROM `database2`.`profiles` Anyone know how I may add a condition to the above query that accounts for the duplicate key? |
It worked!!!!!!
So, if you ever find yourself in my situation, there is hope! |
