Replacing parts of the database

ultramega · 2007-11-12 13:51:28

I am tweaking with two content-wise identifically sites for a while, and that makes me need some help with database things.

I managed to duplicate whole original site, but I have to correct some posts in the old and add more images. Newer one has already some layout and structural changes I have to keep, but articles should be synchronized.

I use phpMyadmin but I’m not very familiar with it. Could someone please give step-by-step help, how to replace needed tables in the copy-site’s db. Then I wouldn’t have to write same things twice. I think at least textpattern, txp_image and MLP-related translation tables have to be replaced.

Both sites share the same image-folder. New one uses images which the original site saved. I don’t want to save them manually to the new, ‘cause then the dir would contain duplicates.

This is basically good practising with databases for a novice like me. Would propably be handy info for other starters too. So if someone have words of wisdom, please tell :)

net-carver · 2007-11-12 14:42:58

ultramega

Not sure I understand your post 100% but here are a few thoughts for you…

First pearl of wisdom would be backup both DBs first — but you’ve done that already.

Second: If you are doing MLP stuff, there are potentially strings in the txp_lang table that you added if you use snippets on your site. They will need moving too. Don’t worry too much about moving the l10n_txp_ tables over, they can be recreated from the textpattern table (it acts as a master.)

What kind of content has changed between the two sites? Layout (pages/forms) or content (articles/renditions/snippets)? If there are no layout changes then perhaps copying textpattern, l10n_articles, txp_img and the txp_lang tables might do it.

By the way, maybe there’s another way to share the content between the two sites — like using rss to pull from one site to the other (kind of like the Textpattern Planet site). Then you wouldn’t need to synchronise the two DBs.

Regards,

ultramega · 2007-11-12 15:38:51

Basically as a database noob I just need simple how-to for that copying part; replacing things with other things in the DB. So in this case: changed articles, translations and image-info.

What that l10n_txp_ table contains? Do you mean I don’t have to touch to it?

I have made changes on the translation snippets, and layout, but on the new side. I’m going to just copy-paste them to old when needed, there is not so much of them.

I’m maintaining both sites simultaneously for a certain time and then I’ll continue just with the version 2. So later there is no need to keep them in sync. Intresting idea anyway to use rss like that.

net-carver · 2007-11-13 10:28:04

ultramega

You could use the same method that Rob Sable’s rss_admin_db_manager uses.

Behind the scenes it uses mysqldump to output an SQL file (optionally compressing it) and it uses the mysql command to import such files (after uncompressing them if needed.) Why not install rss_admin_db_manager on both sites, dump the Textpattern table from the source machine, move the file to the destination machine and then just import it.

If the above method works, it shouldn’t be too hard to get the process fully automated.

What that l10n_txp_ table contains? Do you mean I don’t have to touch to it?

You can transfer them but (if my failing memory serves correctly) I think the MLP pack will re-create them from the textpattern table if they are missing. [will need to check that]

ultramega · 2007-11-13 11:03:26

Actually I had installed that plugin on the old site, so both sites already have it. I just don’t know how to export/import just needed rows or tables, not the whole packet. :(

net-carver · 2007-11-13 11:22:32

Tables are easy with that plugin (if you have a recent version).

On the Extensions > DB Manager Tab you can individually ‘backup’ a table. Just move the corresponding file from the textpattern ‘files’ directory to your backup’s files directory and then, on that machine, go to the Extensions > DB Backup tab and ‘restore’ it.

That leaves you with updating rows from one DB to the other. Need to think about that one a little bit — it’s beyond what I know about those two programs at the moment.

ultramega · 2007-11-13 11:54:50

Hi! Funny, at the same time when you wrote the earlier, I forced myself to went back to the dark area called phpMyadmin. Finally managed to do that export/import there. So now I have correct articles and same amount of photos in both installations. Thanks anyway for the time you have spent with this :)

If someone needs to do the same thing: I first selected images-table, exported it from original as a .sql-file (using drop table option, then went to other database’s same table, and using import I was able to update rows with the exported file.

The scary “drop table” thing when exporting I first left unselected, but when I tried to import that to other database, it failed because “it already exist”. So as I understood correct, “drop table option” drops the existing table when importing fresh new data from the file.

net-carver · 2007-11-13 11:59:54

Yep, that will do it.

Textpattern CMS

Textpattern CMS support forum

#1 2007-11-12 13:51:28

Replacing parts of the database

#2 2007-11-12 14:42:58

Re: Replacing parts of the database

#3 2007-11-12 15:38:51

Re: Replacing parts of the database

#4 2007-11-13 10:28:04

Re: Replacing parts of the database

#5 2007-11-13 11:03:26

Re: Replacing parts of the database

#6 2007-11-13 11:22:32

Re: Replacing parts of the database

#7 2007-11-13 11:54:50

Re: Replacing parts of the database

#8 2007-11-13 11:59:54

Re: Replacing parts of the database

Board footer