[resolved] Copying multisite database content to new database (26 posts)

  1. Roy
    Posted 5 years ago #

    I've got a multi setup with one main and five subsites. Because my host cannot run MySQL 5 with their current software, they've created a bypass to allow Joomla (and soon WP) users to run their software requiring MySQL 5. They've got one server running MySQL 5 on which only they themselves can set up a database. So now I'm trying to find a way to copy my existing multisite database on another server to the new database that I'm going to need as soon as 3.2 is released. What would be the easiest way to do this?
    Exporting/importing using the WP function does not sound much of an option. This would mean that I edit wp-config, install WP anew, probably loose my network, set it up again and import the exports.
    Would it work when I use the Plesk database export and the PHPmyAdmin import or is this going to give problems due to different software?
    Basically I need to copy my database to a database running on other software.

  2. Use the phpmyadmin export function.

  3. Roy
    Posted 5 years ago #

    I'll give it a go. I don't know what such an export do, but I guess it just makes a file of tables and content then? Sounds like a good way to go.

  4. It makes a complete copy of every single table in the db and all its content. Like a backup does. If you ever need to move or restore the db, this is what you do.

  5. Pretty much :)

  6. Roy
    Posted 5 years ago #

    Hm, 21 MB is too big a file. I've looked around a bit to see what I can do about that, but practically I have but a database admin with no serverspace it seems. I don't know my way around PHPmyAdmin enough to find ini files or anything, so I just asked my host to allow some more import :-)

  7. Roy
    Posted 5 years ago #

    It's even worse. The 21 MB file was corrupt. Yesterday I've made several efforts to make an export. Most of the time the browser of the entire computer crashed, one time I got a 160 MB file that doesn't open in Notepad and another time I got a 24 MB zip file that I also can't open using Notepad. Gzip didn't work at all. How big a file can a DB dump be of 6 sites with only a few thousand posts? 160 MB sounds way too big for me.
    I'm in contact with my host to see how to proceed.

    An alternative could be to use WP exports of the six sites, but I don't really want to set up the network anew...

  8. Roy
    Posted 5 years ago #

    My host took care of this. As of now (I'm at work) I can look through tht database and it looks alright. Now I'll experiment a little with wp-config.

  9. And THAT is why I advocate paying good money to good hosts :D ! Yay!

  10. Roy
    Posted 5 years ago #

    I pay little money for a descent host :-)
    No seriously, it's about the cheapest host in my country. One of these 300 sites per server, no telephone helpdesk kind of hosts. But they help out, think with me and even gave a how-to for the next time.

    But ehm, a 160 MB export for a 6 site multi setup with only a few thousand posts and almost no comments, isn't that a bit gigantic?

  11. That does seem a bit large. o.O A lot of plugins and options perhaps?

  12. Roy
    Posted 5 years ago #

    Not excessive I think (20, 25 only a few of which are used on all subsites), a stats plugin, four anti spam (akismet and bad behavior don't need my database I think), custom post types, alphabetical overview, multisite cross posting, that sort of things, nothing much which needs extra database tables.
    Oh well, I'm going home now, look a bit through the database and see what happens when I have WP use the new database.

  13. Actually bad behavior DOES use your DB but my biggest site is only 22 MB. Then again, I'm sure Andrea has a big daddy out there ;)

  14. Aw yeah, I've seen some huge ones. :)

    The db can fill up really big if:

    - you have post revisions on, which they are by default
    - you don;t empty spam. plugins often mark comments as spam, then leave them in the db. Akismet cleans 'em out after 30 days, but in the meantime, they can fill up.
    - transient feeds in the options tables. I really do not like how the dashboard feeds get saved in the db for every. single. site.
    - optimize the tables before export. deleting stuff leaves emty spaces that take up actual space. :D

    And in phpmyadmin, you don't have to select all tables. you can select some, do a zip, select the next batch, do another zip.. .and keep going. Sucks, but it works.

  15. Roy
    Posted 5 years ago #

    No for revisions.
    No for spam.
    No idea for 'trasient feeds'?
    4, Thanks for the tip.

    New problem btw. I started using the new database and noticed that ALL accented characters, but even just symbols such as ' or > have been replaced by code (no html codes btw, I didn't recognise the coding). That's a bit too much work to replace, about as much as just copying everything to the new database actually, since I doubt I'll have even one post withouth and I'll.

  16. db collation is my guess.

  17. search/replace the DB. I had that problem when I moved to MySQL5 a few years ago.

  18. Roy
    Posted 5 years ago #

    I started with search and replace, but when I realised that also meant replacing all he's, that did not seem like a good idea.
    Andrea, it looks like I don't have the choise when I export. The new database has tables in utf8_general_ci (most) and some in latin1_swedish_ci (like bad behavior), neither which tell me much, but utf8 sounds familiar :-P

  19. Yeah, the old WP standard was latin1 and the new is UTF8 and I WISH I could fnid my notes!

    The only thing I've got is a note that UTF-8 Database Converter worked on WP 2.7


    (looks like someone did it on 3.0 and it worked though http://wordpress.org/support/topic/plugin-utf-8-database-converter-works-with-301?replies=9 )

  20. Roy
    Posted 5 years ago #

    interesting. First sports, then more experimenting, but that could be in the weekend too. No rush as long as there is no 3.2 :-)

    [edit] the post tables already are in utf8, the tables that aren't are of bad behavior and shortstat.

  21. Roy
    Posted 5 years ago #

    Hm, half success. Dumézil is now correct, but a term such as rúnar still not. Some more experimenting later.

  22. Roy
    Posted 5 years ago #

    Hm, I tried a few, but none work. Dumézil was a 'search and replace' experiment... Hm, that's going to be an awfull lot of work...

  23. Roy
    Posted 5 years ago #

    Holy shmoly, this is going to be an awfull lot of work! However my website is in English, I refer a lot to the German and French languages (many accents), but also old Scandinavian languages (different characters), Anglo-Saxon, etc. I started with 'search and replace', but it is going to take ages before I replaced every character and I have to do it six times. I figured trying to use imports of the old website, but it only imports the posts that have changed characters in the titles. It would be nice to use the import function to simply overwrite everything. Is there a way to do that? It's the best that I can come up with as of now...

  24. dgilmour
    Posted 5 years ago #

    This reminds me of a similar problem I had following one of the WordPress upgrades. I posted some notes at the time which may be of interest: http://edubuzz.org/blogs/david/wordpress/changing-early-wpmu-db-to-utf8/

  25. Roy
    Posted 5 years ago #

    David. I've tried a few of the quick and dirties, but without success. Re-importing is not really an option, since I can't do that myself (the file is 80x too big!). I'm going to investigate the import trail a bit more. Perhaps I should just delete all posts (which won't delete the subsite) and import the content the WP way.

  26. Roy
    Posted 5 years ago #

    I've marked this thread as resolved, but it might not be a sollution that people want to hear when they are going through the same process after the release of 3.2.
    A summary of the above.
    My host had to make a new database using MySQL 5, so I had to transfer the data from the current database to the new one.
    I made a database dump using PHPmyAdmin of the old multi install.
    Since that was a 21 mb zip file (160 mb unzipped) I needed my host to import that file which succeeded.
    What I should have known beforehand, was that it would have been smart to edit the file to make all table collations the same, but the dump was imported, so I had to do this afterwards.
    There are plugins that even work with a 3.x multi install to change the collation of all tables. This doesn't change much though. All different characters (ð þ é â) were replaced with codes. I have tried numerous ways to change them, but ended up using the 'search and replace' plugin (which scans the database) and in larger sites I deleted all posts and reimported them using the built in WP tool.
    Now there are some minor things left (galleries disappeared, one plugin that uses the database doesn't work correctly yet), but the bulk of it is as it should be.

Topic Closed

This topic has been closed to new replies.

About this Topic