PDA

View Full Version : Restore any size SQL backup using phpMyAdmin


Zylantex
19 Aug 2011, 19:29
The biggest problem with restoring a sql backup is hitting the php timeout wall.

There is a way around this and it's incredibly simple.

You just need a tool to break your backup into intelligent chunks which are small enough not to timeout during the restore process.

This is done using SQLDumpSplitter 2 (http://www.mediafire.com/file/wrc42isljbwplbs/SQLDumpSplitter.zip).

The procedure is simple:

1. Create a holding directory called say "Split Files" on your desktop.
2. Unzip and run SqlDumpSplitter.exe
3. Select your backup file
4. Set the maximum file size you want to work with say 10240 KB ( I have used 30000 KB sucessfully)
5. Set the target directory that you created in Step 1
6. Click the word Execute.

The program will create a sub folder in your target directory called SQLDumpSplitterResult.

The split files will be found there one of which will have a filename ending in DataStructure.sql.

The rest will be named the same as your original backup file with a numerical sequence identifier added.

Now you are ready to use phpMyAdmin to import your backup.

I would strongly advise against attempting this process unless you are comfortable with phpMyAdmin.

Import the file ending in DataStructure.sql first as this recreates your file structure, tables etc.

Import the other files in the directory one at a time in numerical order.

Don't be impatient as the process is limited by your upload speed.

If for any reason the process does timeout simply restart the whole process and pick a smaller file size in Step 4.

You the must recommence the phpMyAdmin process completely from scratch including the import of the first file ending in DataStructure.sql

That's about all there is to it.

Regards,

Zylantex


*** Full credit must be given to Philip Lehmann-Böhm http://www.PhilipLB.de (http://www.philiplb.de/) who wrote this great tool. ***

pipsteal
29 Dec 2011, 11:08
hi this is bala, when i m try to do this same procedure, it ll send some error as follows.

Error
SQL query:

INSERT INTO `newtbl_action`
VALUES ( 1, 1, 3, 'Content', 'view', 'View' ) , ( 2, 1, 3, 'Content', 'edit', 'EditPage' ) , ( 3, 1, 3, 'Content', 'editcontent', 'EditContent' ) , ( 4, 1, 3, 'Content', 'configcontent', 'ConfigContent' ) , ( 5, 1, 3, 'Content', 'addcontent', 'AddNode' ) , ( 6, 1, 3, 'Content', 'deletecontent', 'DeleteNode' ) , ( 7, 1, 3, 'Content', 'publish', 'PublishNode' ) , ( 8, 1, 3, 'Content', 'options', 'NodeOptions' ) , ( 9, 1, 3, 'Widget', 'viewwidget', 'View' ) , ( 10, 1, 3, 'Widget', 'configwidget', 'Config' ) , ( 11, 1, 3, 'Widget', 'configeditor', 'ConfigEditor' ) , ( 12, 1, 3, 'Widget', 'editwidget', 'Edit' ) , ( 13, 2, 3, 'BaseWidget', 'config', 'Config' ) , ( 14, 2, 3, 'BaseWidget', 'edit', 'Edit' ) , ( 15, 3, 3, 'Editor', 'switch', 'SwitchMode' ) , ( 16, 3, 3, 'Editor', 'tableoverlay', 'TableOverlay' ) , ( 17, 4, 3, 'List', 'list', 'List' ) , ( 18, 1, 3, 'Content', 'rate', 'Rate' ) ;


MySQL said:

#1062 - Duplicate entry '1' for key 'PRIMARY'

please give me some solution for this.. thanks
balahttp://imageshack.us/photo/my-images/508/databaseerror.png/

Zylantex
30 Dec 2011, 10:42
If for any reason the process does timeout simply restart the whole process and pick a smaller file size in Step 4.

That should sort it out for you as it drops an re-creates all the tables. Don't forget to make sure the forum is not in use. In particular make sure you are not using the forum yourself in another tab.