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

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

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!