WordPress.org

Forums

Restoring Backup with phpMyAdmin (8 posts)

  1. Dave333
    Member
    Posted 5 years ago #

    I am unable to restore my back up with the process described here:

    http://codex.wordpress.org/Restoring_Your_Database_From_Backup

    I have created a new SQL database into which I am attempting to import the backup (though the instructions omit this part of the process so I am not sure about that part).

    Every time I try, I get to the point where I choose the back up file which is on my desktop, I click the "go" button as instructed in the final step (step 8) and nothing happens.

    It says to go grab a coffee as it can take a while. I've left it go for several hours at a time on each occasion, then I come back and it is still where I left it. The message at the bottom of the screen in the status bar says:

    "Loading http://websiteaddress.com:2082/3rdparty/phpMyAdmin/index.php?", completed 4 of 5 items."

    But it never gets any further.

    Should I have dropped all the tables in the newly created SQL DB before attempting to import the backup or will the import process simply overwrite the tables with those contained in the back up?

    I am guessing that several hours is more than enough time to restore small site with just a few pages and no posts. And so I assume it is hanging for some
    reason.

    I've spent so many hours on this that it might have been quicker to just build the site again from scratch. I'd rather be able to restore the database though.

    Is there another way to restore a backup?

    Lower down the page in those instructions it seems to give an alternate method of restoring the database. It says:

    "Assuming your backup is a .bz2 file, creating using instructions similar to those given for Backing up your database using Mysql commands, the following steps will guide you through restoring your database :"

    I am not sure what this sentence means as it is grammatically incorrect. It then gives a string of commands that can be used to restore the database. These instructions are so vague and don't say where to type this command.

    Where do I type this command?

  2. vtxyzzy
    Member
    Posted 5 years ago #

    How did you make the backup? The file you are importing into phpMyAdmin must be a SQL file, with no php.

    As for deleting the tables before importing, that depends on what is in the backup file. The tool I use for backup writes 'DROP TABLE IF EXISTS' commands in the SQL, so it does create the tables from scratch.

    Here is a sample of the SQL from my backup:

    #
    # Delete any existing table <code>wp_comments</code>
    #
    
    DROP TABLE IF EXISTS <code>wp_comments</code>;
    
    #
    # Table structure of table <code>wp_comments</code>
    #
    
    CREATE TABLE <code>wp_comments</code> (
      <code>comment_ID</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>comment_post_ID</code> bigint(20) unsigned NOT NULL DEFAULT '0',
      <code>comment_author</code> tinytext NOT NULL,
    ...
      PRIMARY KEY (<code>comment_ID</code>),
      KEY <code>comment_approved</code> (<code>comment_approved</code>),
      KEY <code>comment_post_ID</code> (<code>comment_post_ID</code>),
      KEY <code>comment_approved_date_gmt</code> (<code>comment_approved</code>,<code>comment_date_gmt</code>),
      KEY <code>comment_date_gmt</code> (<code>comment_date_gmt</code>)
    ) ENGINE=MyISAM AUTO_INCREMENT=210 DEFAULT CHARSET=utf8 ;
    
    #
    # Data contents of table <code>wp_comments</code>
    #
    
    INSERT INTO <code>wp_comments</code> VALUES (13, 300, 'Mike and ...
    INSERT INTO <code>wp_comments</code> VALUES (14, 378, 'rupert' ...
    INSERT INTO <code>wp_comments</code> VALUES (8, 300, 'Mac' ...
    ...
  3. Dave333
    Member
    Posted 5 years ago #

    Thanks for your reply vtxyzzy.

    I created the backup file with the wordpress plugin called: wp-db-backup. The backup file it creates is a zipped SQL file which I have unzipped.

    This plugin also writes DROP TABLE IF EXISTS' commands in the SQL and generates code similar to your sample that you posted.

    Is there some other way to restore a back up?

  4. vtxyzzy
    Member
    Posted 5 years ago #

    I don't know of another way to restore a backup. And, FWIW, restoring my backup only takes seconds for a SQL file of 4600+ lines.

    It sounds to me like your php installation may be missing something. Can you check with your host? Maybe the php error logs might give a clue.

  5. breakingball
    Member
    Posted 5 years ago #

    Hi - I hope that I understand your issue and that what I offer is of some use...

    I recently used wp-db-backup to help me move five of my sites to a new host with no issue. I would add that I used the plugin's default settings to back up my dbs, and that all were restored successfully to a new server.

    First thing that comes to mind reading your post is that there may be a maximum file size for importing databases to your server. If that's the case, and your db exceeds that maximum (usually ~10M), then it's possible that this is the cause of the incomplete import. There is no notification on the server side if this is the case, although you may see that any number of tables appear to have been imported.

    Also, when importing into a newly created database, any tables that exist in that database, including any that were created on earlier, failed import attempts, should be dropped before attempting to import anew. IOW, You want to import your tables into an empty database.

    If your attempts at importing continue to fail, you can still import your database manually (cutting and pasting) using the instructions at the bottom of this page.

    Good luck!

  6. Dave333
    Member
    Posted 5 years ago #

    Thanks vtxyzzy. My SQL file was only 700 kb.

    After much frustration I contacted my web host. I uploaded the SQL file to the server and asked him if he wouldn't mind restoring it. It took a few seconds and it was imported successfully. I asked him if he had any idea why I was unable to import the SQL file from my end. His sugestion was that it was possibly a fault due to incompatibility issue with the browser I was using. That was something I didn't even consider. I was attempting to do the import with my default browser: Safari.

    I have not yet tested this by trying Firefox but it sounds feasible as I have experienced this same problem in the past with another host. Again, I was using Safari.

    Thanks for your copy and paste tip breakingball. I was wondering if that was possible.

  7. themaclady
    Member
    Posted 5 years ago #

    I just did this and found that since I'm allowed only one DB, I had to delete the old one first, recreate it, then upload the file, unzipped.

    However, I still cannot find the hack. :-(

  8. Dave333
    Member
    Posted 5 years ago #

    What hack were you looking for?

    Judging from your handle I am guessing that you are on a Mac. And so I would love to know if you used Safari with phpMyAdmin to import your unzipped SQL file?

    Or did you have to do this with Firefox?

Topic Closed

This topic has been closed to new replies.

About this Topic