Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
Restore any size SQL backup using phpMyAdmin
Zylantex
Join Date: Sep 2009
Posts: 170

France
by Zylantex Zylantex is offline 19 Aug 2011

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.

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 who wrote this great tool. ***
Attached Images
File Type: jpg SQLSplitter2.JPG (35.1 KB, 58 views)

Last edited by Princeton; 23 Aug 2011 at 18:29..
Views: 6989
Reply With Quote
Comments
  #2  
Old 29 Dec 2011, 11:08
pipsteal pipsteal is offline
 
Join Date: Feb 2011
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
bala
Reply With Quote
  #3  
Old 30 Dec 2011, 10:42
Zylantex Zylantex is offline
 
Join Date: Sep 2009
Real name: Chris
Originally Posted by Zylantex View Post
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.
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 21:04.

Layout Options | Width: Wide Color: