SMF Support > SMF 2.0.x Support

Cannot backup DB

(1/3) > >>

Krash.:

Trying to backup a large (180mb) database with phpmyadmin prior to forum upgrade.  Small tables and groups of tables will d/l uncompressed or zipped, but large tables will not (smf_messages is 102mb).  I get incomplete downloads uncompressed, and zip just hangs and d/l never starts.  Not getting any errors - window goes blank after zip hangs for a while.  Trasnsfer rates are very slow (dialup speeds at times).  Haven't been able access cpanel backup, so phpmyadmin is only option.  Not surprisingly, it's a GoDaddy account.  ::)

Any ideas?

MrPhil:
So the problem is that a large table's backup is too large to download, even if compressed? That would mean that you would have to split the table into several pieces. One way would be to fix and modify the SMF backup to split large table into multiple pieces, saved under different names. You could use MySQL queries to do SELECT * FROM {prefix}{tablename} LIMIT 1000,{starting record 0, 1000, 2000, etc.} ORDER BY {some field such as an ID} and write into file backup_{tablename}_{starting record}.sql.

I don't see anything in phpMyAdmin > Export that will let you select a section of a table, nor does there seem to be a clean way to copy just part of a table to another table. It would probably cleanest to write a PHP script to dump a table in sections. Of course, if you start with the SMF backup code, that means taking care of all the little problems in it so you get a clean backup.

Storman™:
Maybe try MySQLdumper

If you can upload the required files by ftp then you're in business. It will split into pieces or even download large db's in one chunk.

Easy to restore db's as well. Personally it's a tool that gets used all the time and highly recommended.

 ;)

Krash.:

--- Quote from: MrPhil on May 10, 2012, 04:15:13 PM ---So the problem is that a large table's backup is too large to download, even if compressed? That would mean that you would have to split the table into several pieces. One way would be to fix and modify the SMF backup to split large table into multiple pieces, saved under different names. You could use MySQL queries to do SELECT * FROM {prefix}{tablename} LIMIT 1000,{starting record 0, 1000, 2000, etc.} ORDER BY {some field such as an ID} and write into file backup_{tablename}_{starting record}.sql.

I don't see anything in phpMyAdmin > Export that will let you select a section of a table, nor does there seem to be a clean way to copy just part of a table to another table. It would probably cleanest to write a PHP script to dump a table in sections. Of course, if you start with the SMF backup code, that means taking care of all the little problems in it so you get a clean backup.

--- End quote ---

That's a bit hairy for me, and need to be able to confirm that the backup is good, which is another problem.  I've gotten to the GoDaddy backup function, which seems to work up to a point, but it's given me two .sql backups of 146 and 143mb.  If I empty smf_log_errors it'll knock off around 80mb, and shouldn't cause any problems.  I'd feel better if I could get a clean backup of the table, but that's a no go.


--- Quote from: Storman on May 10, 2012, 04:51:12 PM ---Maybe try MySQLdumper

--- End quote ---

Looked at that once before, but couldn't get it to work on my server.  Just tried again - it won't connect to the db server, and there's no docs with the d/l.

Colin:
Do you have SSH access?

Navigation

[0] Message Index

[#] Next page