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.
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.
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
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.
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
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
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/mysql. Change /var/lib/mysql in 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.
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
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
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
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.
If you are using SE linux, set it to permissive mode by editing /etc/selinux/config and changing SELINUX=enforcing to SELINUX=permissive
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
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...
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.
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 !!
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.
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
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
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/
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 :
One day to find the solution for me on the mariadb documentation. Hope this help some guys!
Source: Stackoverflow.com