How to change MySQL data directory?

Is it possible to change my default MySQL data directory to another path? Will I be able to access the databases from the old location?

1. Stop MySQL using the following command:

    sudo /etc/init.d/mysql stop
   

2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:

    sudo cp -R -p /var/lib/mysql /newpath
   

3. edit the MySQL configuration file with the following command:

    sudo gedit /etc/mysql/my.cnf   # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
       
   

4. Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.

5. In the terminal, enter the command:

    sudo gedit /etc/apparmor.d/usr.sbin.mysqld
    
   

6. Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.

7. Save and close the file.

8. Restart the AppArmor profiles with the command:

    sudo /etc/init.d/apparmor reload
    
   

9. Restart MySQL with the command:

    sudo /etc/init.d/mysql restart
    
   

10. Now login to MySQL and you can access the same databases you had before.

you would have to copy the current data to the new directory and to change your my.cnf your MySQL.

[mysqld]
datadir=/your/new/dir/
tmpdir=/your/new/temp/

You have to copy the database when the server is not running.

Quick and easy to do:

# Create new directory for MySQL data
mkdir /new/dir/for/mysql

# Set ownership of new directory to match existing one
chown --reference=/var/lib/mysql /new/dir/for/mysql

# Set permissions on new directory to match existing one
chmod --reference=/var/lib/mysql /new/dir/for/mysql

# Stop MySQL before copying over files
service mysql stop

# Copy all files in default directory, to new one, retaining perms (-p)
cp -rp /var/lib/mysql/* /new/dir/for/mysql/

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

datadir=/new/dir/for/mysql/

If you are using CageFS (with or without CloudLinux) and want to change the MySQL directory, you MUST add the new directory to this file:

/etc/cagefs/cagefs.mp

And then run this command:

cagefsctl --remount-all

First you should stop the mysql server. e.g.

# /etc/init.d/mysql stop

After that you should copy the old data directory (e.g. /var/lib/mysql) incl. privileges to your new directory via

# cp -R -p /var/lib/mysql /new/data/dir

now you can change in /etc/mysql/my.cnf the data new and restart the server

# /etc/init.d/mysql restart

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 wanted to keep a database on my machine, but also have a data on my external hard drive, and switch between using the two.

If you are on a Mac, and installed MySQL using Homebrew, this should work for you. Otherwise, you will just need to substitute the appropriate locations for the MySQL datadir on your machine.

#cd to my data dir location
cd /usr/local/var/

#copy contents of local data directory to the new location
cp -r mysql/ /Volumes/myhd/mydatadir/

#temporarily move the old datadir
mv mysql mysql.local

#symlink to the new location
ln -s /Volumes/myhd/mydatadir mysql

Then to when you want to switch back simply do:

mv mysql mysql.remote

mv mysql.local mysql

and you are using your local database again. Hope that helps.

First stop mysqld

mysql_install_db --user=mysql \
                 --basedir=/opt/mysql/mysql \
                 --datadir=/opt/mysql/mysql/data

Then change datadir in your /etc/mysql/my.cnf
Start mysqld

Notes:
#1: probably you have to adjust your SELinux settings (try out with SELinux disabled in case of troubles), Apparmor (Ubuntu) may also be issue.

#2: see MySQL Install DB Reference

First stop your mysql

sudo service mysql stop

copy mysql data to the new location.

sudo cp -rp /var/lib/mysql /yourdirectory/

if you use apparmor, edit the following file and do the following

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

Replace where /var/lib/ by /yourdirectory/ then add the follwoing if no exist to the file

    /yourdirectory/mysql/ r,
    /yourdirectory/mysql/** rwk,

Save the file with the command

:wq

Edit the file my.cnf

sudo vim /etc/mysql/my.cnf

Replace where /var/lib/ by /yourdirectory/ then save with the command

:wq

finally start mysql

sudo service mysql start

@see more about raid0, optimization ici

This solution works in Windows 7 using Workbench. You will need Administrator privileges to do this. It creates a junction (like a shortcut) to wherever you really want to store your data

Open Workbench and select INSTANCE - Startup / Shutdown Stop the server

Install Junction Master from https://bitsum.com/junctionmaster.php

Navigate to C:\ProgramData\MySQL\MySQL Server 5.6

Right click on Data and select "MOVE and then LINK folder to ..." Accept the warning Point destination to "Your new data directory here without the quotes" Click MOVE AND LINK

Now go to "Your new data directory here without the quotes"

Right click on Data Go to the security tab Click Edit Click Add Type NETWORK SERVICE then Check Names Click OK Click the Allow Full Control checkbox and then OK

Go back to Workbench and Start the server

This method worked for me using MySQL Workbench 6.2 on Windows 7 Enterprise.

Everything as @user1341296 said, plus...

You better not to change /etc/mysql/my.cnf Instead you want to create new file/etc/mysql/conf.d/ext.cnf (any name, but extension should be cnf)

And put in it your change:

[mysqld]
datadir=/vagrant/mq/mysql

In this way

• You do not need to change existing file (easier for automation scripts)
• No problems with MySQL version (and it's configs!) update.

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 ;-)

I often need to do this when upgrading machines, moving from box to box. In addition to moving /var/lib/mysql to a better location, I often need to restore old DB tables from an old MySQL installation. In order to do this...

1. Stop mysql. Follow the instructions above, it necessary.
2. Copy the database directories -- there will be one for each of your old installation's database -- to the new DATADIR location. But omit "mysql" and "performance_schema" directories.
3. Correct permissions among the copied database directories. Ownership should be mysql:mysql, directories should be 700, and files should be 660.
4. Restart mysql. Depending on your installation, old version DB files should be updated.

We had to move MySQL into /home directory because it was mapped from another physical disc. In order to make live simpler, instead of changing directory in my.cnf, we have mapped the new directory /home/mysql in the place of the original directory /var/lib/mysql. It works for us like a charm (tested on CentOS 7.1).

Create the new dir and set correct permissions.

mkdir -p /home/mysql
chmod 755 /home/mysql
chown mysql:mysql /home/mysql

Stop MySQL if running.

systemctl stop mysql

Move the data dir.

mv -f /var/lib/mysql/* /home/mysql

Create a permanent mount and execute it.

echo "/home/mysql /var/lib/mysql none    bind   0 0" >> /etc/fstab
mount -a

Restart the server.

systemctl start mysql

If you want to do this programmatically (no manual text entry with gedit) here's a version for a Dockerfile based on user1341296's answer above:

FROM spittet/ruby-mysql
MAINTAINER [email protected]

RUN cp -R -p /var/lib/mysql /dev/shm && \
    rm -rf /var/lib/mysql && \
    sed -i -e 's,/var/lib/mysql,/dev/shm/mysql,g' /etc/mysql/my.cnf && \
    /etc/init.d/mysql restart

Available on Docker hub here: https://hub.docker.com/r/richardjecooke/ruby-mysql-in-memory/

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

Under SuSE 13.1, this works fine to move the mysql data directory elsewhere, e.g. to /home/example_user/ , and to give it a more informative name:

In /var/lib/ :

# mv -T mysql /home/example_user/mysql_datadir
# ln -s /home/example_user/mysql_datadir ./mysql

I restarted mysql:

# systemctl restart mysql.service

but suspect that even that wasn't necessary.

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