WordPress.org

Ready to get started?Download WordPress

Forums

phpmyadmin backup to 1-click backup format? (10 posts)

  1. paperlion
    Member
    Posted 9 years ago #

    After deleting an old install and mysql structure in the database, phpmyadmin generates an exhausted memory error trying to restore my database.

    Don't know if having a compressed version would have helped. Does anyone know if it's possible to create a compressed version using other utils now, after the fact, and if that would help the out-of-memory problem?

    Also; is there a way to convert the file generated by phpmyadmin to a 1-click backup format and restore it through this plugin? (Maybe it's better with memory issues?!)

    Thanks everyone.

    PS - Also; does this phpmyadmin error message make sense? Even if the problem is envelope data surrounding the file data, could that legitimately be over 3x the actual file size?

    Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 1942503 bytes) in /usr/share/phpmyadmin/libraries/read_dump.lib.php on line 115

  2. Kafkaesqui

    Posted 9 years ago #

    Since the processing overhead is always going to be several times more than just the data file it's importing, PHP can run into memory issues with large tasks. I believe this is why phpMyAdmin also tends to limit uploads to a max of 2MB.

    I'm not sure compressing your sql dump will help. However, have you tried breaking it up into smaller files?

  3. paperlion
    Member
    Posted 9 years ago #

    Thanks, kafkaesqui.

    Interesting. Is there any info on breaking on data files in the support area - didn't notice anything?

    What about the apparent need to delete the table structure when importing through phpmyadmin (per Tamba's tutorial)?

    Also, do I have to set up all the categories before a restore or will everything be dumped into 'general' otherwise?

  4. Kafkaesqui

    Posted 9 years ago #

    Q1> Dunno. However, as a sql dump file is just a regular text file, this shouldn't be a major chore to perform (i.e. cut and paste sections of it and save as new text files). The issue may only come to a head with your 2nd question.

    Q2> This would depend on how you handle the structure content from your sql dump. One would obviously want to import these first, but I think if one is *smart* about it and splits the contents towards an issue of size (say half-way through the INSERTs for wp_posts), they should do fine. Or if the structure is already in place in the database (such as after a typical installation of WP), importing just the data should work.

    Q3> Weren't the categories part of your data dump?

  5. Kafkaesqui

    Posted 9 years ago #

    By the way, documentation of some sort about this is a good idea. I'll drop in my todo an update to the database stuff on Codex. Thanks.

  6. paperlion
    Member
    Posted 9 years ago #

    Thanks again.

    Re: Q1> [file splitting] seems the posts are sandwiched between many other tables and fields. An append option on the database would fit the bill. Did I miss it? Would I just make a copy of the dump file and remove half the posts from one and the other half from the other?

    Q3> [Categories restore] Categories were part of my sql dump, but I wasn't sure if these could be picked up and put into place by WP from a restore (thought it might just be the posts themselves); looking at the dump file though; seems like it should work.

  7. paperlion
    Member
    Posted 9 years ago #

    PS - Does anyone know if the 1-Click Backup plugin is any better at handling larger files when it comes to restoring (I guess we'd know right away if it's overburdened at the backing up side - would let us know right away!?

  8. paperlion
    Member
    Posted 9 years ago #

    PPS - I just recalled, that phpmyadmin also just failed at restoring even an earlier copy of the dump of which a 1-Click Backup format was successfully restored a few weeks ago.

    Aside from the differences between the two utils, it is possible my host modified the server recently and didn't optimize the setup well? Is anyone aware of optimization guidelines - i.e., to get the most use of server memory, for backup and restore features (on Apache specifically of course [now under Debian/Linux]) that could be pointed out to them?

  9. paperlion
    Member
    Posted 9 years ago #

    Anybody have to restore a file that was too big for its own good?

    Would I just make a copy of the dump file and remove half the posts from one copy and the other half of the posts from the other copy? [There's a lot more data than just the posts on these sql dumps.

    Anyone do it differently? I'm sure there are some large blogs out there!
    Anyone?

  10. paperlion
    Member
    Posted 9 years ago #

    For anyone else who's interested, I found Podz's post on splitting files for a restore here:
    http://wordpress.org/support/topic.php?id=2537&page=2

    ..I would suggest splitting the .sql file into two.
    - get pspad ( http://www.pspad.com ) - it can handle sql files.
    ..- open the sql file in pspad.
    You will see stuff like this:

    # --------------------------------------------------------
    #
    # Table structure for table wp_categories
    #
    CREATE TABLE wp_categories (

    and following that, all the data for that table.
    Then there will be a gap, and it will show similar stuff for the next table.
    What you need to do is split your sql file into two, but without splitting a table.
    So from the start of the file, if you highlighted to about halfway through the document, and stopped the highlight at one of the
    #------------------------------------------------------------
    lines, then 'cut' and 'saved as an sql file' (it's name does not matter) then saved what was left as another sql file, you would have 2 files to import.
    That should then work..

    Unfortunately, this didn't work for me, as WP didn't recognize the files even though the database looked like it had been appended.

    Also, seems like this is good for slightly larger files, but those with many more posts would have all the posts as part of a single table, and according to the instructs here that tables not be split, this would not be a solution.

    Does this make sense?

    I understand command-line restore is basically limitless as to size of files.

    Is there a way to use this through a proxy with the host or something similar if it is not offered directly through the host?

    Is there a way to convert a phpmyadmin generated sql dump for use on a command line restore?

    thanks.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.