how to compress mysql databases?

Here we talk about enabling compression for mysql innodb tables. Follow the following steps to compress the tables and save disk space.

1 enable innodb_file_per_table

add a line “innodb_file_per_table” under [mysqld] in /etc/my.cnf. This will make databases use their own *.ibd files in their seperated database directories(such as /var/lib/mysql/mydatabase/, instead of sharing a single /var/lib/mysql/ibdata1

2 change innodb_file_format to Barracuda

The default innodb file format for innodb tables is Antelope. In mysql 5.5 version, y0u can use the following command to check the innodb file format the table uses :

SELECT * FROM information_schema.tables WHERE table_schema=’databasename’ and table_name=’tablename’\G;

If ROW_FORMAT=Compact or ROW_FORMAT=Redundant, the table is using Antelope format. If ROW_FORMAT=Compressed or ROW_FORMAT=Dynamic , the table is using Barracuda format.

You can also use the command:”show table status from databasename” to check the value of  ROW_FORMAT.

For mysql 5.6 or later version, you can also use the following command to check the file format a table uses.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME=’databasename/tablename’ \G;

Because the INNODB_SYS_TABLES is first introduced in mysql5.6, if you use the above command in mysql5.5, you will find the INNODB_SYS_TABLES is missing(INNODB_SYS_TABLES  does not exist.)

How to know mysql enables Barracuda? You can check the global variable :SHOW VARIABLES LIKE ‘innodb_file_format’;  By default it displays “Antelope”. Now add a line innodb_file_format = Barracuda under [mysqld] in /etc/my.cnf and restart mysqld, from now on the new created innodb tables will use  the Barracuda file format if created with the row_format=compressed option(but tables created without the compressed/dynamic option still use the  Antelope file format).

If you see the error “/usr/libexec/mysqld: unknown variable ‘innodb_file_format=Barracuda”, check the mysql version you installed. Barracuda format is only supported by mysql 5.5 or higher. You may need to upgrade mysql to support Barracuda and database compression.

3 use alter table command to compress a table

ALTER TABLE tablename ROW_FORMAT=COMPRESSED;

Now the table is compressed and you’ll find its database file (tablename.ibd) shrinks to a reduced size. This happens even the table was created before changing innodb_file_format to Barracuda in /etc/my.conf.

 

reference:

https://www.whatan00b.com/posts/enabling-innodb-compression-on-mysql-5-5/

https://www.percona.com/blog/2014/01/14/innodb-file-formats-here-is-one-pitfall-to-avoid/

 

 

Posted in tips of hosting