I am trying to move some of the older partitions from larger tables located at the main data directory to another drive/mount point on the same server. I want all the partitions/tables to be accessible and live after moving. Based on some of the research I have found online, are the following steps safe to execute:? (on a high level)

  1. flush tables table for export

  2. Move the /var/lib/mysql/database/table .ibd and .cfg files to an alternate location, i.e. ~/

  3. Unlock the MySQL tables

  4. show create table <table>

  5. drop table <table>

  6. Create table table with the DATA DIRECTORY = '/path' option

  7. Alter table table discard tablespace

  8. copy all those copied .ibd and .cfg files from the ~/ directory to the /path/ directory

  9. Alter table table import tablespace

My innodb tables are partitioned monthly and the sizes are pretty big for each .ibd file which represents each partition for the table.

MySQL version = 5.7, hosted on Ubuntu 12.04.

  • Welcome on the DBA SE! Your Ubuntu seems a little bit old, afaik there aren't even security patches for that.
    – peterh
    Aug 23, 2018 at 6:34
  • Please provide a link to the 5.7 manual page that describes transportable tablespaces.
    – Rick James
    Aug 31, 2018 at 2:49


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.