0

The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table and looking to the future this seems the way to go. The documentation says that this can be done by setting “inno_file_per_table = 1” in my.cnf. Obviously I would need to back up and reload the data into the separate tables but the procedures I have seen are not that clear. Besides my database schema there are also schemas present for information_schema, mysql and performance schema which I suspect come into the equation. MySQL is running under Mint Linux and although I have seen an answer to this question under Windows I am not 100% confident on translating this to a Linux OS. What is the best way to achieve this change. It is acceptable at this stage for there to be a few hours downtime.

Thank you

  • Are you running MySQL 5.6 ??? If you are not sure, please run SELECT VERSION(); and tell us what is says. – RolandoMySQLDBA Mar 2 '15 at 16:43
  • Yes it is 5.6.22... – MichaelJohn Mar 2 '15 at 16:45
1

STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

If you are running MySQL 5.6, login to mysql and run

mysql> SET GLOBAL innodb_file_per_table = 1;

If you are not running MySQL 5.6, restart mysqld

# service mysql restart

STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Extract_InnoDB_From_ibdata1.sql

STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql

If you are OK with it, then run it

mysql ${MYSQL_CONN} < Extract_InnoDB_From_ibdata1.sql

CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

GIVE IT A TRY !!!

0

I wanted to do a backup of all data before trying anything but had repeated problems because of the mysql and performance_schema databases - Dump All Databases in MySQL - Fails on mysql and performance_schema . In the end I decided to back up my data and try something different with the option of reinstalling everything if it all went wrong. This is what I did:-

  1. Back up my data as both a self-contained (sql) file and as a project folder.
  2. Stopped the server and edited /etc/mysql/my.cnf to contain “innodb_file_per_table = 1”
  3. Deleted ibdata1, ib_logfile0 and ib_logfile1, and all my data
  4. Restarted the server
  5. Reloaded my data from the self contained file

So far everything looks fine with SQL scripts and Python programmes searching and manipulating the data as expected. The total space taken on disc is now just over 20GB with ibdata1 having shrunk from over 40GB to 17MB.

Obviously I am not yet expert enough to recommend this to others but this worked for me. Others comments welcome...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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