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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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