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?
-
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– user1822Commented Sep 26, 2012 at 8:57
Add a comment
|
1 Answer
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
