MySQL: changing ROW FORMAT to DYNAMIC or COMPRESSED

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

If you've changed your database properties to use innodb_large_prefix = 1 and innodb_file_format = Barracuda in my.cnf as below:

innodb_large_prefix            = 1
innodb_file_format             = Barracuda

you may want to convert all your existing tables to use DYNAMIC or COMPRESSED ROW_FORMAT.

For a single table, you need to run the following:

mysql> ALTER TABLE test ROW_FORMAT=DYNAMIC;


To convert all tables in a given database, here is a short bash script to do it:

#!/bin/bash


DATABASE=some_db

ROW_FORMAT=DYNAMIC
#ROW_FORMAT=COMPRESSED

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

for TABLE in $TABLES ; do
    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT;"
    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT" | mysql $DATABASE
done

The script assumes you can use mysql without specifying a password (i.e. password added to .my.cnf).