Change MySQL data directory

Ubuntu

Stop the 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 users should have sufficient permissions for the new folder /new_path/mysql_data.

Optionally modify the path of the old MySQL data store folder:

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

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

$ vim /etc/mysql/my.cnf

Caution

Other possible config paths:

/etc/mysql/mariadb.conf.d/50-server.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

Change the old data store folder path to the new one:

data-dir = /var/lib/mysql

to

data-dir = /new_path/mysql_data

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

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

Change the old data store folder path to the new one:

/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 sufficient permissions for the new folder /new_path/mysql_data.

Optionally modify the path of the old MySQL data store folder:

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

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

$ vim /etc/mysql/my.cnf

Change the old data store folder path to the new one:

data-dir = /var/lib/mysql

to

data-dir = /new_path/mysql_data

Enable AppArmor.

$ yum install policycoreutils-python
$ getenforce
$ manage context -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 8\data" c:\new_path\mysql_data

Optionally modify the path of the old MySQL data store folder:

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

Edit the datadir property in the C:\ProgramData\MySQL\MySQL Server 8\my.ini configuration file:

Change the old data store folder path to the new one:

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

to

data-dir = c:\new_path\mysql_data

Start MySQL service:

C:\> net start mysql56