Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB.

Are they compressed in order to reduce disk space usage?

Is that 80MB real physical-storage size?

share|improve this question
    
The size in the database might actually be a lot higher because of indexes defined on the tables. The SQL only contains the index definition. The database stores the actual index data. Depending on the table/index size, this could mean that the database is a lot bigger than the 80mb –  a_horse_with_no_name Sep 26 '12 at 8:57
add comment

1 Answer

up vote 1 down vote accepted

It depends on how you export your database:

if you use mysqldump, then the result is a flat text file containing the sql commands.

you can compress it manually or in one commandline like this:

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

see: http://www.ducea.com/2006/10/28/compressing-mysqldump-output/


if your question is, if the tables are stored compressed in the working directory, there are some possibilities:

if you have MyISAM tables that would work if they are read-only, you can Generate Compressed, Read-Only MyISAM Tables with myisampack

And by setting InnoDB configuration options, you can create tables where the data is stored in compressed form.

see: dev.mysql.com/doc/refman/5.5/en/innodb-compression.html

just alter the table to add the compressed option:

ALTER TABLE `tablename` ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE =4
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.