Move MySQL to Another Directory

In MySQL, it is possible to move the MySQL home directory, including all files, to a different location. This is useful when the current partition is full and all the MySQL files need to be at the same directory.

In the following example, the original MySQL directory is /var/lib/mysql and the new directory will be /new_drive/mysql_dir

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

2. Stop mysqld.

   service mysqld stop
   

3. Copy the MySQL directory to the new location.

   mkdir /new_drive/mysql_dr
   chown mysql:mysql /new_drive/mysql_dir
   cp -r /var/lib/mysql /new_drive/mysql_dir
   

   The log files do not need to be copied. In /new_dirve/mysql_dir, delete the log files (ib_logfile0 and ib_logfile1).

4. Edit my.cnf to point the new directory.

   [mysqld]
   datadir=/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   #
   default-storage-engine=INNODB
   innodb_data_file_path=ibdata1:2000M:autoextend
   ...
   
   [mysqld]
   datadir=/new_drive/mysql_dir
   socket=/new_drive/mysql_dir/mysql.sock
   user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   #
   default-storage-engine=INNODB
   innodb_data_file_path=ibdata1:2000M:autoextend
   ...
   [client]
   socket=/new_drive/mysql_dir/mysql.sock
   

5. Start mysqld.

   service mysqld start
   

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

• misconfiguration in my.cnf

• inappropriate permission to new directory

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