How to change MySQL data directory?

Question

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?

Booted

For windows - look here: dba.stackexchange.com/questions/24403/… – Indoeuropean

For mac user, all you need is this and the 1-4 step from @user1341296's answer. – Hydranth

I know that this is an old question, but please specify the operating system, otherwise, people becomes crazy, year by year, to guess this :) from macOS to Microsoft Windows to whatever Linux flavor and FreeBSD etc. thanks – Cercus

Answer 1

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.

Profligate

The above failed to solve the problem for me on Ubuntu 12.04 (Precise). I found out that one needs to edit the file /etc/apparmor.d/tunables/alias to include a line "alias /var/lib/mysql/ -> /newpath/," With this in place, I did not need any changes in any of the other AppArmor files. It worked immediately after restarting AppArmor with "/etc/init.d/apparmor restart" and MySQL with "restart mysql". – Dewberry

Apparmor is not relevant on CentOS. There's SELinux instead. Can read on how to disable or manage here blogs.oracle.com/jsmyth/entry/selinux_and_mysql – Staley

on mak's edition (which is necessary) do not forget the comma at the end of the line alias /var/lib/mysql/ -> /newpath/, – Horseshoes

Can't stress enough how important Michel's comment above is: ADD TRAILING COMMA and save some neurons. Sorry about the caps. – Tybie

For anyone moving their datadir to ZFS on linux (like I did), make sure you also add: innodb_use_native_aio=0 in the [mysqld] section of your my.cnf file, since ZFS on linux does not support AIO. dev.mysql.com/doc/refman/5.5/en/… – Karissakarita

Even by following the steps above, you may still not able to start MySQL if you are running CentOS or RHEL, due to SELinux. If you do "ls -Z", you will see the SELinux context are different in /var/lib/mysql and /new/datadir. You need to change the SELinux context using "chcon". For details see: wiki.centos.org/HowTos/… – Indo

I used this on debian 6. The appArmor part was not necessary, becauce the package was not installed. – Odonto

no mentions about user/group permissions for the new folder? – Counteraccusation

do we need to change socket address also ?socket=/var/lib/mysql/mysql.sock. if not then why not ? – Grishilda

if you follow the answer, your new mysql path is '/newpath/mysql', please watch out when change the config file. – Mosby

I also had to add some permissions to the new folder. Otherwise mysql doesn't start: MySQL Community Server 5.6.30 did not start. Please check logs for more details. And in the log file: [Warning] Can't create test file /vagrant/mysql/is1.lower-test – Donnie

@user3338098 I just make it run half a year ago, do not remember any detail anymore – Mosby

please note if your MySQL version is 5.7 then path is changes here is path sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf – Polydipsia

Stuck at "Starting MySQL database server mysqld" [fail] on server restart after changing database path to mine (a folder at home location). Returning to "/var/lib/mysql/" makes restart going again. Any suggestions? – Enthusiast

Doesn't work for me. I get Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details when trying to restart mysqld, and the error is that the disk is full. It's got 500GB of free space... – Poston

If you are using an NTFS external disk, then the permissions may fail to copy along with the files. You might have to follow this instructions before copying askubuntu.com/questions/11840/… – Sparse

Worked for me on Ubuntu 18.04 following @mak's comment – Savdeep

I performed these exact changes and it worked but the website loads insanely slow now and it was super fast prior to making these changes. What could be causing this. And the server is a monster in terms of hardware so it ain't that. – Diandiana

Will symbolic link work? ln -s /new/data/dir /var/lib/mysql – Polygyny

In ubuntu 20, I didn't have to follow the apparmor steps for mariadb. – Kyle

I also got problems after starting MySQL service in ubuntu, with the message: Can't generate a unique log-filename binlog and MYSQL_BIN_LOG::open failed to generate new file name, so you have to go to the file: /etc/mysql/mysql.conf.d/mysqld.cnf and uncomment the line that says: log_bin = /var/log/mysql/mysql-bin.log; save it and start MySQL service again. – Turney

Answer 2

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

Tumbrel

Glad to hear :P – Tumbrel

Great thanks, and to add if you want to completely script this like I did you can use this: sed -i 's|datadir=/var/lib/mysql|datadir=/data/var/lib/mysql|g' /etc/my.cnf – Auten

This didn't work for me. I tried this and a lot of different other alternatives. I'm coming to the same conclusion as user3338098 when he says this is the only way that works: serverfault.com/questions/503887/… – Levorotation

@Levorotation all that link says is he had to remove and reinstall ... chances are you both have some other server specific issue, as both MySQL and MariaDB both support the datadir configuration, and i recently did this (6 months ago) on another server without issues – Tumbrel

@Tumbrel yes, it was remove and install but after mounting /var/lib/mysql elsewhere i.e. a mega partition unlike the one /var was previously mounted to. If there is a specific issue with our systems it would be great to work out that this is. This was on a fresh stock standard install of Centos 7 OS with RAID 10 with 8x HDD but usually such disk array details are transparent to software. – Levorotation

the ownership point is very important and unblocked me – Turdine

I could not find the file my.cnf in your location, but instead here: /etc/mysql/mysql.conf.d/mysqld.cnf – Turney

Answer 3

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.

Rocha

Well thats what I cannot do. I want to change the directory but I want to create new databases in new directory. And Want to access both old directory and new directory databases. – Booted

@MySQL DBA: if you use some ln -s static symbolic lynx won't that make it ? – Rocha

Well I am bit confused with the use of this command. As I explained before I want to create new databases in new directory. Is it possible with symlink. – Booted

you can create a new directory, use symbolic link to make the file appear in the new dir and change your my.cnf – Rocha

yes but then in that case mysql is not reading the symlinks. I have done this but I cannot read those symlinks as databases. – Booted

@MySQL DBQ: so means that's pretty much impossible. – Rocha

I am sorry RageZ. MySQL is able to read symlinks and can be used as a database. Thanks for all your help. – Booted

Answer 4

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 !!

Ascender

Thanks that was helpful but warning: my.ini is not writable by a normal user unless you change its permissions. Oh and ProgramData is a hidden folder so you have to change the Explorer folder settings to see it. – Edmundoedmunds

>> xcopy "\C:\ProgramData\MySQL\MySQL Server 8.0" "W:\" /E /H /K /O /X In this line it should be "C:\ – Kronos

Answer 5

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

Hermy

Answer 6

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!

Raddi

The file /etc/systemd/system/mysqld.service isn't here. Any idea where I am to look? – Knit

For those using MariaDB the service file is located at /lib/systemd/system/[email protected] – Knit

Answer 7

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

Georgiegeorgina

perhaps this not works for other distribution, but if you are using ubuntu, this works fine for me and i change path for every new installed ubuntu server to enable raid0. – Georgiegeorgina

I know people are downvoting this, but I want to say you that this had saved my day. Thanks! – Appreciation

had to run "sudo systemctl restart apparmor" before starting mysql – Pigtail

Answer 8

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.

Chinook

this is not exactly addressing the question as stated -- however this was exactly what I was looking for -- and it is a neat way of handling an edge case/bug that my.cnf does not handle spaces well: bugs.mysql.com/bug.php?id=26033. I will link this workaround on that bug report. – Wist

Answer 9

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

Parboil

Answer 10

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

Desex

Answer 11

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.

Atoll

Answer 12

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.

Phalan

Answer 13

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

Gymno

Working on Ububntu 22 and moving mysql to /home/data/. That was the only method which working for me. SELinux is off. apparmor reconfigured. ProtectHome=false was set everywhere and still nothing. The binding did work – Developer

Answer 14

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.

Cyathus

Answer 15

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/

Consubstantial

Answer 16

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.

Crabwise

Answer 17

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;

Probably

Answer 18

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

Playreader

down voted because it doesn't work as detailed at serverfault.com/a/733998/279553 – Salami

Answer 19

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.

Gamut

Answer 20

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

Vinosity

Welcome to Stack Overflow! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. – Cardinal

Answer 21

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.

Wester

I tried selinux in RHEL7.6 by changing SELINUX=enforcing to SELINUX=permissive, didn't work for me. – Wester

Answer 22

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.

Shutz

Answer 23

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

Dodecahedron

Answer 24

You can always create symbolic links to point an appearing path to somewhere physically existing, and this can solve your problem.

• Create the empty database (in case if it is not there)
• Stop MySQL server
• Move the database folder within MySQL data path to a new location
• Create a symbolic link within MySQL data path that points to the new path
• Start MySQL server

Symbolic link: C:\Program Files\MySQL X\data\mydb <=> X:\MySQL\mydb

Basically, MySQL will still think your database is still within it's original location, while OS redirects all file I/O operations to the new location discretely as if it were happening in the old location.

Note: On Linux systems, you may need to figure out some permission issues; but with Windows, it should work out of the box, unless otherwise the new location has some inherited special permission facts.

Godewyn

Answer 25

Be careful if the directory is in another drive that might not be mounted by the thime MySQL is started. Also, if you are having trouble with MariaDB, be aware that it disables apparmor.

I first tried the soft-link solution, but it did not work.

Then I tried the mount-bind solution but, after boot, it only worked if MariaDB was restarted, and after the external drive was mounted.

Finally I did the following:

To find where your database is, type:

sudo mysql
select @@datadir;
exit;

For me, it was in /var/lib/mysql/. Then I executed:

sudo systemctl stop mysql
mkdir /media/username/drivename/mysql
sudo cp -R /var/lib/mysql/* /media/username/drivename/mysql
sudo rm -R /var/lib/mysql

Then, I changed MariaDB's configuration file, which I found following these instructions:

The MariaDB/MySQL tools read configuration files in the following order:
1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
2. "/etc/mysql/conf.d/*.cnf" to set global options.
3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
4. "~/.my.cnf" to set user-specific options.
If the same option is defined multiple times, the last one will apply.

So I did:

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

Where I changed datadir = /media/username/drivename/mysql. Then I found multiple permission errors, not only with the new folder location for the database but hierarchy pathing too, which was solved like this:

sudo chown -R mysql:mysql /media/username/drivename/mysql
sudo chmod -R 755 /media/username/drivename/mysql
sudo chmod 755 /media/username/drivename
sudo chmod 755 /media/username
sudo chmod 755 /media

The last thing that I had to do, though, is to insert a delay into MariaDB service to give time for the folder to mount:

sudo systemctl edit --full mariadb.service

So I inserted ExecStartPre=/bin/sleep 10 before any other ExecStartPre.

And finally, it worked after reboot. Hope it helps.

Bensky

Answer 26

This worked for me on Ubuntu 22.04 LTS: Blog post

Chuckchuckfull

Answer 27

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

Caenogenesis

as detailed here serverfault.com/questions/503887/… selinux is not at fault there is some other issue preventing the changing of the datadir property – Salami

Answer 28

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

Zoophyte