Can someone explain to me the correct way to back up MYSQL DBs? I have a DB thats about 5MB but when ever i go in to it and choose the export function and then save, the generated file is only about 200k. Where is the other 4.8MB?? Thanks.
What parameters are you using for the export function? Since SQL statements have a lot of repitition, “zipped” and “gzipped” files will be not be anywhere near as large as uncompressed SQL.
Anyways, you haven’t provided enough information to be of much help at the moment. I exported a database that was over 5 megabytes just now using:
Structure: Add AUTO_INCREMENT value, Enclose table and field names with backquotes
SQL export compatibility: NONE
Data: Use hexadecimal for binary fields
Export type: INSERT
And the SQL file was 6,961k but the zip file was 908k.
Perl / MySQL / HTML CSS
I just followed these instructions i found after posting: http://www.tamba2.org.uk/wordpress/backup/
Same thing. I used the uncompressed method and still got about 200K.
I don’t know if you can or not, but try
- copying the largest table to a new table (ie database_name.old_table to database_name.new_table) and then
- export just new_table.
- After the export, empty new_table.
- Then import the SQL file from the export, and
- see if new_table still has the same number of rows as old_table.
Perl / MySQL / HTML+CSS
read your file with more or some editor?.. sometimes says “error at…” or something that clip your backup.
It just so happens I’ve been working with mysql backups today. I have a database that DH panel reports at 4.065 mb. I used ssh to do a mysql dump and pulled the sql file down thourugh FTP. It weighs in at 3.164mb. Next I used phpmyadmin to make a back up and it weighs in at 3.560.
The phpmyadmin file is a bit bigger becuase there’s some extra line seperators and the like.
It’s my guess that you’re missing a step when making the back up. I’m also going to guess that you’d rather do this thorugh phpmyadmin than ssh/telnet so I’ll go over thoes instructions.
First, log into phpmyadmin through the host name you created with the database you’d like to back up. You’ll need to log in with the user name/pass that you set up with the database as well.
Now, from the center panel, click Databases.
Click on your database_name.
Select the export tab
click select all underneath the list of tables
Leave all the other settings at default.
Place a check mark for “save as file” towards the bottom.
You should be prompted in a bit to download a database_name.sql file. Save this file and see what size it is. Are you now close to the size DH is currently reporting for your database?
Matt, it still came out to about 200k following your exact instructions.
I’m wondering if there is some kind of an error in your database, or something else gone wrong with phpmyadmin. Perhpas you’d be best off to contact support about the matter.