CODEWITHSUNDEEP
databasewordpressshopp
Sabai
Sabai

Reputation: 1599

I have a 18MB MySQL table backup. How can I restore such a large SQL file?

I use a Wordpress plugin called 'Shopp'. It stores product images in the database rather than the filesystem as standard, I didn't think anything of this until now.

I have to move server, and so I made a backup, but restoring the backup is proving a horrible task. I need to restore one table called wp_shopp_assets which is 18MB.

Any advice is hugely appreciated.

Thanks, Henry.

Upvotes: 0

Views: 2093

Answers (5)

RST
RST

Reputation: 3925

Even though this is an old post I would like to add that it is recommended to not use database-storage for images when you have more than like 10 product(image)s.

Instead of exporting and importing such a huge file it would be better to transfer the Shopp installation to file-storage for images before transferring.

You can use this free plug-in to help you. Always backup your files and database before performing this action.

Upvotes: 0

arthurprs
arthurprs

Reputation: 4627

try HeidiSQL http://www.heidisql.com/

  1. connect to your server and choose the database
  2. go to menu "import > Load sql file" or simply paste the sql file into the sql tab
  3. execute sql (F9)

HeidiSQL is an easy-to-use interface and a "working-horse" for web-developers using the popular MySQL-Database. It allows you to manage and browse your databases and tables from an intuitive Windows® interface.

EDIT: Just to clarify. This is a desktop application, you will connect to your database server remotely. You won't be limited to php script max runtime, or upload size limit.

Upvotes: 2

Greg
Greg

Reputation: 2563

For large operations like this it is better to go to command line. phpMyAdmin gets tricky when lots of data is involved because there are all sorts of timeouts in PHP that can trip it up.

If you can SSH into both servers, then you can do a sequence like the following:

  1. Log in to server1 (your current server) and dump the table to a file using "mysqldump" --- mysqldump --add-drop-table -uSQLUSER -pPASSWORD -h SQLSERVERDOMAIN DBNAME TABLENAME > BACKUPFILE

  2. Do a secure copy of that file from server1 to server2 using "scp" --- scp BACKUPFILE USER@SERVER2DOMAIN:FOLDERNAME

  3. Log out of server 1

  4. Log into server 2 (your new server) and import that file into the new DB using "mysql" --- mysql -uSQLUSER -pPASSWORD DBNAME < BACKUPFILE

You will need to replace the UPPERCASE text with your own info. Just ask in the comments if you don't know where to find any of these.

It is worthwhile getting to know some of these command line tricks if you will be doing this sort of admin from time to time.

Upvotes: 2

RobertPitt
RobertPitt

Reputation: 57268

use bigdupm.

create a folder on your server witch is not easy to guess like "BigDump_D09ssS" or w.e

Download the http://www.ozerov.de/bigdump.php importer file and add them to that directory after reading the instructions and filling out your config information.

FTP The .SQL File to that folder along side the bigdump script and go to your browser and navigate to that folder.

Selecting the file you uploaded will start importing the SQL is split chunks and would be a much faster method!

Or if this is an issue i reccomend the other comment about SSH And mysql -u -p -n -f method!

Upvotes: 1

duckbox
duckbox

Reputation: 132

What I do is open the file in a code editor, copy and paste into a SQL window within phpmyadmin. Sounds silly, but I swear by it via large files.

Upvotes: -8

Related Questions