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?
Stop MySQL using the following command:
sudo /etc/init.d/mysql stop
Copy the existing data directory (default located in
/var/lib/mysql) using the following command:
sudo cp -R -p /var/lib/mysql /newpath
edit the MySQL configuration file with the following command:
sudo gedit /etc/mysql/my.cnf # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
Look for the entry for
datadir, and change the path (which should be
/var/lib/mysql) to the new data directory.
In the terminal, enter the command:
sudo gedit /etc/apparmor.d/usr.sbin.mysqld
Look for lines beginning with
/var/lib/mysqlin the lines with the new path.
Save and close the file.
Restart the AppArmor profiles with the command:
sudo /etc/init.d/apparmor reload
Restart MySQL with the command:
sudo /etc/init.d/mysql restart
Now login to MySQL and you can access the same databases you had before.
Read more... Read less...
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/
/etc/my.cnf file, and under
[mysqld] add this line:
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:
And then run this command:
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.
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
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.