How to overwrite sql base with backup...

[thatjustit]

Hello,
the whole this is too hard to explain, but I think it will be enough if I write only about the last problem. So, I want to overwrite some tables in sql of my forum, but only that ones which are different. The equal ones should have no changes at all. I want also to skip datas with are exactly same. In easier words I want to make a sql operation on my SMF forum which will: skip if equals. Is there any possibility to do that?
Best regards,

[Arantor]

As a mod package, or a regular backup being restored?

[thatjustit]

Regular backup.

[Arantor]

Well, therein lies the challenge. The regular backup is a single massive list of instructions. You'll have to open the .sql file in a decent editor (like Notepad++) and cut the bits you don't want to update.

[MrPhil]

OK, so you have a MySQL database, and you have a backup (.sql file) for it, which presumably has instructions to DROP existing tables and recreate and repopulate them. Right? And you want to only replace (drop/create/insert) tables which are different in content? (between the live database and the backup) What's the point in that? Other than perhaps being faster (not dropping/creating/inserting a table, only to end up exactly where you started), you will end up with the exact same state as you would have otherwise.

I don't know of any way to have MySQL do this for you. MySQL would have to read the table and compare its contents to the SQL in the backup, and decide if they are equal or not. To complicate matters, there's no guarantee that the physical order of records hasn't been shuffled at some point, so it would be an even slower process. That is, if MySQL made a test backup (.sql file) of a table, it might have to sort both of them (.sql files) before it could compare them.

If all you're trying to do is save some time during a database restore, I don't think you're going to get anywhere with it.

[thatjustit]

Well, it is quite close, but maybe I should explain it more. Near 3 years ago I moved my phpbb forum to another server and all of national characters changed to '?'. Then about 2 years ago I moved from phpbb to smf script. Ofcourse the '?' stayed, but for now I found an interesting solution to fix it. It's quite long to explain, but at the moment I did backup of my smf forum and on my localhost with php script using mysql database I fixed all of that '?' to my national characters (like ę, ż, ź, ó, etc). And now I want to upload that fixed database from beginning of the January (for example) to current database with current data from February.

And that is a little problem. How could I upload that backup with changing only the changed topics and posts with invalid characters? It's about few thousand posts and it'd took too long time to edit it by myself. I just want to upload that backup without any errors from mysql like "duplicate tables detected", etc.

Ofcourse I could do it by stopping forum for few days, but I don't want to do it. I could also make it directly on the forum server, not on my localhost, but I wouldn't like to do that, because on shared hosting (soon vps) it would took about month and maybe cause some errors, so the localhost way is more safe.

I am not sure, but I hope I explained it well (it was not easy for me though ).

[MrPhil]

Ah, so you only want to change certain records within tables where you've corrected accented characters? I would edit the .sql file to REPLACE just those records (instead of INSERT). It doesn't sound like you want to replace full tables.

[thatjustit]

Hmm, replace is nice idea, but what if I have that .sql file, which I want to upload, just almost that big as the target .sql is? It's ofcourse in same structure, with all tables etc.

Should I remove lines with tables info and just upload by replace the rest of database? There's about 10% changes, but I don't think I could export only changes, because its about posts and it's very 'mixed' there.

[MrPhil]

Have you thought about running your modified .sql file and a backup of your current database (also an .sql file) through a file compare program such as 'fc' or 'diff'? that would eliminate all the records which are unchanged. Then you use a good editor to move stuff around in the modified records to create REPLACE or UPDATE commands (instead of INSERT).

Without seeing the modified backup and a backup of the database, I can't give any suggestions beyond that. (I wouldn't do that much work for you for free, anyway.) If you're looking to update/replace just changed records, you should be able to modify the .sql file to do that. It will take some hand editing to cut it down to size, but it should be possible to reduce your .sql file to just changed records, and then change the INSERT commands to REPLACE or UPDATE.

[thatjustit]

Thanks for your time and help. I think it'll work in that way - I am going to test it soon.

[Masterd]

Did you tried this, JereManU? I'm marking this as solved until you get back.