Topic: Cannot backup DB

[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.]

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.

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.

Maybe try MySQLdumper

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?

[MrPhil]

That's a bit hairy for me, and need to be able to confirm that the backup is good, which is another problem.

Well, you could start with the SMF backup code. It needs fixing (see my sig > fixes), and then you would extend its function to read and back up just a section of a table at a time, rather than all records. It does require some PHP skills, of course.

To confirm that a backup is good, you can always make a scratch database and restore the backup file(s) into it. If no error messages, and the record count matches the original table(s) count, it's probably good. You could even make a scratch copy of your forum and see if it runs OK with the restored backup. If you want to be absolutely certain it's good, you'd have to write another PHP script to compare the two databases table by table and record by record. There may be code around somewhere to compare a backup file against a live database -- you could look around. You would need to put your live forum into maintenance mode while doing comparisons, so that it doesn't get updated in the meantime and give a false failure.

[Krash.]

I can restore the backup to a scratch db on my server, but looks like it would be difficult on GoDaddy.  phpmyadmin isn't working well, and host options are limited.  Got the forum back up & running in 1.1.12 last night, so it's not a panic job.  Cleaned up the db, and it's around 106mb, so may be able to get a good cpanel backup.

[Storman™]

As you've found, although GoDaddy is relatively cheap, the downside is that undertaking "normal" website tasks in the backend can be more frustrating than it's worth, partly due to the way they configure things.

Anyway, at least you are running for now. Maybe consider a different host at some stage if you want to expand your forum to the next level especially as your forum is growing in size.

I'll mark the topic as solved but feel free to post back at a later date if you encounter further issues. 

[Krash.]

Thanks.  It's the third GoDaddy forum I've worked on, and I've moved one to a better host, but this forum is just a part of a large website, so it would be a big project to move, and I wouldn't recommend it to the owner.  If I can get a good backup, I'll upgrade them to 2.0.2, otherwise we'll probably just clean up and leave it as is.

[nend]

GoDaddy has cPanel? I have been using them for a few years and haven't notice no such thing. Must be the dedicated or Virtual servers. I know in their custom control panel you can back up and restore MySql databases at the click of a mouse button.

[Krash.]

They have a Hosting Control Center, which is basically the same thing.

[nend]

Yeah that is it.

You go to your MySql database list and click the edit icon. On the top bar of that screen there are _ options, "Backup", "Restore", "Description", "Password", "Configuration". Click on "Backup" to backup the database, to restore one click "Restore". The databases are stored in your account in a protected folder. You can FTP or SSH to it to download the files.