Advertisement
Advertisement


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?

2015/09/17
1
159
9/17/2015 4:15:41 PM


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
2017/04/13

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.

2009/11/25

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
2016/03/03

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!

2018/12/06

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.

2013/05/19

Source: https://stackoverflow.com/questions/1795176
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]