Change MySQL data directory

Ubuntu

Stop the MySQL service:

$ service mysql stop

Create a new folder destination:

$ mkdir /new_path/mysql_data
$ cp -R -p /var/lib/mysql /new_path/mysql_data

Caution

MySQL user should have enough credentials granted to the new folder /new_path/mysql_data.

Optionally modify the path of the older mysql data store folder location:

$ mv /var/lib/mysql /var/lib/mysql_old

Edit the datadir property into the /etc/mysql/my.cnf configuration file:

$ vim /etc/mysql/my.cnf

Replace the older data store folder path with the newer:

datadir = /var/lib/mysql

to

datadir = /new_path/mysql_data

Enable appamor. Edit the file /etc/apparmor.d/usr.sbin.mysqld:

$ vim /etc/apparmor.d/usr.sbin.mysqld

Replace the older data store folder path with the newer:

/usr/lib/mysql/plugin/ r,
/usr/lib/mysql/plugin/*.so* mr,

…

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

to

/new_path/mysql_data/plugin/ r,
/new_path/mysql_data/plugin/*.so* mr,

…

/new_path/mysql_data/ r,
/new_path/mysql_data/** rwk,

Start MySQL service:

$ service mysql start

Note

More information at http://www.ubuntugeek.com/how-to-change-the-mysql-data-default-directory.html.

CentOS and RHEL

Stop the service:

$ service mysqld stop

Create a new folder destination:

$ mkdir /new_path/mysql_data
$ cp -R -p /var/lib/mysql /new_path/mysql_data

Caution

The mysql user must have enough credentials granted to the new folder /new_path/mysql_data.

Optionally modify the path of the older mysql data store folder location:

$ mv /var/lib/mysql /var/lib/mysql_old

Edit the datadir property into the /etc/mysql/my.cnf configuration file:

$ vim /etc/mysql/my.cnf

Replace the older data store folder path with the newer:

datadir = /var/lib/mysql

to

datadir = /new_path/mysql_data

Enable appamor.

$ yum install policycoreutils-python
$ getenforce
$ semanage fcontext -a -t mysqld_db_t "/new_path/mysql_data(/.*)?"
$ restorecon -Rv /new_path/mysql_data

Start MySQL service:

$ service mysqld start

Note

More information at http://lifeofageekadmin.com/how-to-change-the-default-mysql-data-directory-on-rhel-6/

Windows

Stop the service:

C:\> net stop mysql56

Create a new folder destination:

C:\> robocopy "C:\ProgramData\MySQL\MySQL Server 5.6\data" c:\new_path\mysql_data

Optionally modify the path of the older mysql data store folder location:

C:\> ren "C:\ProgramData\MySQL\MySQL Server 5.6\data" "C:\ProgramData\MySQL\MySQL Server 5.6\data_old"

Edit the datadir property into the C:\ProgramData\MySQL\MySQL Server 5.6\my.ini configuration file:

Replace the older data store folder path with the newer:

datadir = C:\ProgramData\MySQL\MySQL Server 5.6\data

to

datadir = c:\new_path\mysql_data

Start MySQL service:

C:\> net start mysql56