How to change MySQL data directory?

In case you're a Windows user and landed here to find out that all answers are for Linux Users, don't get disappointed! I won't let you waste time the way I did.

A little of bullshit talk before solution:

MySQL uses a Data directory to store the data of different databases you create. Well, in the end, it has to store them in the form of files with some added jugglery in the application and file format to ensure the Database promises that you learned in Databases classes are intact.

Now you want to make sure there is enough space to store large databases which you might create in future, and so you thought, Hey! I want to put it into another drive which has got more space.

So you do the following.

Step - 1 : Stopping MySQL service.

  Window Key + R - will open Run
  servies.msc    - will open services manager
  Locate MySQL80 (80 is for version 8.0, look for the one you've).
  Stop it.       (Right click, Stop)

Step - 2 : Finding out the current Data directory

 Goto C:\ProgramData\MySQL\MySQL Server 8.0

By default, there should be a folder here named Data, this is the one which is used by MySQL in default setting (provided they haven't found another better location), but let's check that out.

Find my.ini file, should be right there.

Open it in an editor (Notepad++ maybe).

Do a CTRL+F to find out datadir in the file.

Whatever is mentioned here is the actual location currently under use by MySQL for data directory. This is what you want to change.

Step - 3 : Replacing it with a new data directory.

Let's say you want your new data directory to be W:__MySQL_Data. Let's change datadir value in my.ini file to this value. Keep the previous value commented so that you won't have to remember it.

 # Path to the database root
 # datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
   datadir=W:/__MySQL_Data

Now use xcopy to copy the default datadir to W:\. Launch command prompt (Window + R, cmd, Enter)

 >> xcopy "\C:\ProgramData\MySQL\MySQL Server 8.0" "W:\" /E /H /K /O /X

And rename the copied folder to the new datadir value that you changed. Here: W:/__MySQL_Data

Why not simply copy? Well because that's not COOL!, this helps you not lose permissions on the copied folder, so that when you restart MySQL80, it won't give a stupid error: "The MySQL80 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs." - Courtesy:Microsoft

Step - 4 : Restarting the service

 Well, go back to the Services Manager to Start again, 
 "MySQL80" that you stopped, to restart it again.

Step - 5 : Done! Now get back to work !!

If like me you are on debian and you want to move the mysql dir to your home or a path on /home/..., the solution is :

• Stop mysql by "sudo service mysql stop"
• change the "datadir" variable to the new path in "/etc/mysql/mariadb.conf.d/50-server.cnf"
• Do a backup of /var/lib/mysql : "cp -R -p /var/lib/mysql /path_to_my_backup"
• delete this dir : "sudo rm -R /var/lib/mysql"
• Move data to the new dir : "cp -R -p /path_to_my_backup /path_new_dir
• Change access by "sudo chown -R mysql:mysql /path_new_dir"
• Change variable "ProtectHome" by "false" on "/etc/systemd/system/mysqld.service"
• Reload systemd by "sudo systemctl daemon-reload"
• Restart mysql by "service mysql restart"

One day to find the solution for me on the mariadb documentation. Hope this help some guys!

I could successfully alter the data directory of MySQL or MariaDB, and resolve all related issues on Fedora 30. I think the following steps will work on other distributions.

Note: Users of Debian-based distributions like Ubuntu should search how to disable and edit AppArmor and then follow the following steps.

Disabling SELinux

First of all, let me mention that RedHat-based Linux Distributions (RHELs) like Fedora, CentOS, etc. use SELinux that enforces mandatory access control policies. So it's better to disable it during the following steps and later enable it with some tweaks.

Open the SELinux configuration file

nano /etc/selinux/config

Locate the line that contains SELINUX=enforcing and change its value to SELINUX=disabled, save the file and reboot your system.

Changing the datadir of MySQL

Stop the MySQL services

systemctl stop mysqld.service

Make a new directory for MySQl's data directory. Due to some reasons which are out of the scope of this solution, it's highly recommended to not create a data directory under the /home directory, but maybe some of you like me prefer it (it costs more steps).

mkdir /home/eloy/applications/mysql-datadir/

Set ownership and permissions of the new directory to the default MySQL's data directory(/var/lib/mysql):

chown --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/
chmod --reference=/var/lib/mysql   /home/eloy/applications/mysql-datadir/

Copy all files from the default directory to the new one

cp -rp /var/lib/mysql/*   /home/eloy/applications/mysql-datadir/

Edit the /etc/my.cnf file, add add the following line under [mysqld] section:

[mysqld]
datadir=/home/eloy/applications/mysql-datadir/

Now you can start your MySQL service via the following command

systemctl start mysqld.service

But if the data directory is created under /home, MySQL won't start and you would see the following errors and warnings after journalctl -xe:

Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32190) 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [Warning] Can't create test file /home/eloy/applications/mysql-datadir/eloy-fedora-laptop.lower-> 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: [113B blob data] 
Oct 05 10:22:03 eloy-fedora-laptop mysqld[8362]: 2021-10-05 10:22:03 0 [ERROR] Aborting

Resolving /home issues

Make sure that all the parent directories of the new datadir upwards have x (execute) permissions for all (user, group, and other). I prefer to not use a recursive script so:

chmod +x /home/eloy/applications/mysql-datadir
chmod +x /home/eloy/applications
chmod +x /home/eloy/
chmod +x /home

As it is mentioned creating datadir under /home directory is tricky because by default MySQL does not allow it. Create a file under /etc/systemd/system/mariadb.service.d and put the following lines in:

#open an editor to create a file
nano /etc/systemd/system/mariadb.service.d/centreon.conf 

copy the following lines to the new centreon.conf file and save it

[Service]
ProtectHome=false 
#ProtectSystem=off

Apply the changes by running the following command

systemctl daemon-reload

Now you can run the MySQL service:

systemctl start mysqld.service

Enabling SELinux

Again edit the /etc/selinux/config file, and change the line of SELINUX=disabled to SELINUX=enforcing. Save the file and reboot your system.

To query the current status of SELinux use the following commands, it should print enforcing as an output.

getenforce

the SELinux context uses mysqld_db_t and if it is not set correctly mysqld process will be aborted, so you need to update it:

semanage fcontext -a -t mysqld_db_t "/home/eloy/applications/mysql-datadir(/.*)?"

restorecon -Rv /home/eloy/applications/mysql-datadir

Now you can run MySQL. Cheers ;-)

It is also possible to symlink the datadir. At least in macOS, dev environment.

(homebrew) e. g.

# make sure you're not overwriting anything important, backup existing data
mv /usr/local/var/mysql [your preferred data directory] 
ln -s [your preferred data directory] /usr/local/var/mysql

Restart mysql.

First , you should know where is your config file ? where is your config file ?

IF you installed with apt-get or yum install 。

config file may appear in

/etc/mysql/my.cnf

datafile may appear in

/var/lib/mysql

and what you should do is

1. stop mysql
2. change the mysql data to new dirctory
3. change the config file
4. reload the config file
5. restart mysql

and then jobs done.

But if you didn't install it with apt or yum,the direcotry may not like this ,and you can find the mysql files with

whereis mysql

For one of the environment I changed mysql data directory to new location but during restart of mysql server, it was taking time. So I checked the port, and found that other process was listening on mysql port. So I killed the process and restarted mysql server and it worked well.

I followed below steps for changing data directory which worked excellent. change mysql data directory in Linux

The above steps are foundation and basic. I followed them and still got error of "mysql failed to start".

For the new folder to store mysql data, you need to make sure that the folder has permissions and ownership mysql:mysql.

Beside this, it needs to check the permissions and ownership of parent directory of mysql if having, say, /data/mysql/. Here /data/ directory should be root:root. I fixed the error of "mysql failed to start" after changing ownership of parent directory of /mysql. The OS in my VM is RHEL 7.6.

In addition to the accepted answer, if you want to change the datadir in the config file programmatically in a bash script or in a dockerfile, you can use sed:

sed -i 's|# datadir\t= /var/lib/mysql|datadir\t= custom_path|g' /etc/mysql/mysql.conf.d/mysqld.cnf

This uncomments the setting and sets the value to custom_path. An important detail here is that the character to the left of = is a tab and the one to the right is a space. Took me a while to find it out.

I followed @sMyles's guide, but failed to start mysqld again. Why don't we make a soft link to change the datadir?

sudo cp -rp /var/lib/mysql /home/workspace
sudo mv /home/workspace/mysql /home/workspace/mysql_data
sudo chown --reference=/var/lib/mysql /home/workspace/mysql_data
sudo chmod --reference=/var/lib/mysql /home/workspace/mysql_data
sudo systemctl stop mysqld
sudo mv /var/lib/mysql /var/lib/mysql.orig
sudo ln -s /home/workspace/mysql_data /var/lib/mysql
sudo systemctl start mysqld

This worked for me with MySQL8

Transfer files to external Drive

sudo rsync -av /var/lib/mysql /datadrive

Edit MySQL config

sudo nano /etc/mysql/my.cnf

Add Lines

[mysqld]
datadir = /datadrive/mysql
log_error = /datadrive/mysql/error.log

Edit AppArmor

sudo nano /etc/apparmor.d/tunables/alias

Add Lines

alias /var/lib/mysql/ -> /datadrive/mysql/,

Restart MySQL

sudo systemctl restart mysql

Check if it worked

sudo mysql
select @@datadir;

If you are using SE linux, set it to permissive mode by editing /etc/selinux/config and changing SELINUX=enforcing to SELINUX=permissive

After trying many solutions, here is a summary of what worked for me (Ubuntu 22.04 and mysql server 8.0.29):

1. Completely uninstall mysql

   • sudo systemctl stop mysql
   • sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
   • sudo rm -r /etc/mysql /var/lib/mysql (cautious: this will remove all your existing data ; you can rename this directory otherwhile)
   • sudo apt autoremove
   • sudo apt autoclean

2. Reinstall mysql server and client

   • sudo apt update
   • sudo apt install mysql-server mysql-client

3. Note mysql datadir default location

   • sudo grep -R --color datadir /etc/mysql/* (usually : /var/lib/mysql)

[Jump to step 7 if you already have mounted the new datadir location, here /db]

4. Create a new directory (here I'll use /db ; db standing for database) for mounting the partition where you want to locate mysql datadir (in my config /dev/sdb1)

   • sudo mkdir /db

5. Edit /etc/fstab

   • sudo nano /etc/fstab
   • add this line at the end of the file:

   /dev/sdb1 /db ext4 defaults 0 0

   • save and quit (Ctrl + x and y)

6. Reboot

   • sudo reboot
   • after reboot check that /dev/sdb1 is correctly mounted on /db : df -h /db

7. Move datadir location

   • Make mysql the owner of the new datadir location : sudo chown mysql:mysql /db
   • stop mysql server : sudo systemctl stop mysql
   • copy all the contents of the default data directory /var/lib/mysql (see step 3) to the new data directory /db : sudo rsync -avzh /var/lib/mysql/ /db

All the contents of /var/lib/mysql directory should be now copied to the new directory /db.

performance_schema/table_lock_waits_109.sdi
performance_schema/threads_110.sdi
performance_schema/tls_channel_stat_190.sdi
performance_schema/user_defined_fun_188.sdi
performance_schema/user_variables_b_176.sdi
performance_schema/users_144.sdi
performance_schema/variables_by_thr_183.sdi
performance_schema/variables_info_186.sdi
sys/
sys/sys_config.ibd

sent 4.44M bytes  received 2.70K bytes  1.78M bytes/sec
total size is 182.35M  speedup is 41.09

/db directory is now prepared to be the new mysql data directory.

8. Configure AppArmor to allow /db to be a mysql data directory by editing the AppArmor alias file /etc/apparmor.d/tunables/alias as follows

   • sudo nano /etc/apparmor.d/tunables/alias
   • add this line at the end of the file

   alias /var/lib/mysql -> /db,

   • save and quit (Ctrl + x and y)
   • restart AppArmor: sudo systemctl restart apparmor

9. Change the data directory from /var/lib/mysql to /db

   • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
   • uncomment the line #datadir = /var/lib/mysql and change it to datadir = /db
   • save and quit (Ctrl + x and y)

10. Restart and connect to mysql

    • sudo systemctl start mysql
    • sudo mysql -u root -p
    • You should now be logged in mysql
    • sql> select @@datadir; should return

+-----------+
| @@datadir |
+-----------+
| /db/      |
+-----------+
1 row in set (0,00 sec)

That's it!

Thanks to Shahriar Shovon : How to Change MySQL/MariaDB Data Directory on Ubuntu