Just tried making the backup and it seems to have gone through successfully... at least at first glance.

(Q1) It took under half a minute to get this done (as opposed to half a day via the CP), is that normal for a dump that "weighs" about 500 megs?

Also, in the Threads CP under database tools I see the following summary for my board:

Totals 958672 (rows) 532.88 MB (data) 111.07 MB (indexes)

(Q2) But the total size of the dump is 483MB when checking out the file's properties using WinSCP. Is the backup truly complete?

(Q3) On a side note, am I doing the right thing by downloading the dump in binary? Or should this be done in ASCII? (I dragged the file onto my desktop using WinSCP and it started a binary transfer)

Yes its normal that a SQL Dump from the command line go incredibly fast, as it doens't get processed through apache or any php scripts.

the backup should be complete, not sure why the two would differ however... I've never had an SQL dump through command line not be complete.

Generally Binary mode is for graphics and the like; ASCII mode is generally for text.

Is the ASCII thing definite, or maybe I should do both just in case?

Still wondering about the size difference between the dump file and the data shown in the Admin CP...

Is there a way to dump all tables separately?

Download it in ASCII.

A database dump is not going to the same size as the database.

the database is a file or files that the database creates, in it's own format, to store the data.

A data dump is most certainly not that.

open the dump and read it. the first few lines describe a table, the next lines are composed of insert statements.

the sql dump file instructs mysql on how to create the table and insert the data, it is not an actual data file that the data base can use. It is nothing more than a file that the database would use to reconstruct from, but not to operate from.

Also remember that indices take space. A dump has no indices. Another thing to think about..... when you delete something from the database the database doesn't shrink. It does not give up that space. If you delete the equivalent of 100 megs of data out of the database it still doesn't shrink. Think about it, if you delete something that's in the middle of the table why would you want mysql to rewrite everything behind it in the table in order to regain that space?

That's what optimize does. Even if you do optimize your database the sql dump will nto be the same size as the actual mysql data table. They are apples and oranges.

Can you dump a table individually? Yes, read the documentation on mysql.com for the syntax.

Thanks man.

OK, since the db/dump size is not going to be identical then I will assume that the server is giving me a complete backup.

If I dump the DB as instructed earlier in this thread (into a single file) then that's all I need to restore my database should anything bad happen, right? It'll restore all those tables from that single file?

What's the case with indeces? If the dump doesn't contain them, then what happens if I restore a DB using a dump? I get everything but the indexes? Could that pose a problem? Does the Admin CP backup copy the indeces if you do a backup that way?

Oh, and I still can't believe that what used to take 10+ hours now takes under 30 seconds. It just seems that having command line access to the server makes an incredible difference. 10 hours or 30 secs... it's just unreal.

that's what my wife says as well

You are correct, you would use the following syntax to insert the tables into the database:
mysql -h localhost -uusername -ppassword database_name < dump_name.dump

And yes, I love using ssh to do all of my work on a server, it just saves time being able to edit files, create and upload dumps, etc.

command line access makes many tasks a heck of a lot easier. It also makes it easier to make a fatal mistake. Do take care when you log in via command line.


The indices are created when the table is created, and populated as the data is inserted, just like when the database is functioning normally.

lol yeh, be careful with the "rm" command ("rm -rf" more speficially)...