Move InnoDB File to Another Directory

Instead of moving the whole MySQL environment, it is possible to move only the InnoDB database file. In this case, the MySQL home directory is not changed, but InnoDB files are saved in the new directory. In the following example, replace the “target_dir” to the path name that you want to move to. For example, “new_drive/mysql”

  1. Dump or copy original database files to a safe location

  2. Stop mysqld

   service mysqld stop
  1. Copy the InnoDB file to new location
   mkdir target_dir_path
   chown mysql:mysql target_dir_path
   cp /var/lib/mysql/ibdata1 target_dir_path/ibdata1
  1. Edit my.cnf to use new directory for InnoDB database file location
   [mysqld]
   #
   default-storage-engine=INNODB
   # innodb_data_home_dir = ./
   innodb_data_file_path=ibdata1:2000M:autoextend
   ...
 
   [mysqld]
   #
   innodb_data_home_dir = target_dir_path
   innodb_data_file_path=ibdata1:2000M:autoextend
   ...
  1. Start mysqld
   service mysqld start

With these settings, MySQL uses the current home directory. Only the InnoDB file location is changed. The log files (ib_logfile0 and ib_logfile1) still exist at the MySQL home directory.

If MySQL fails to start after moving, it would be for one of three reasons:

  • misconfiguration in my.cnf

  • inappropriate permission to new directory for MySQL

  • SELinux issue. For the SELinux issue, please refer to the following section:
    SELinux for MySQL directory