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

User · Answer

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   stop mysql change the mysql data to new dirctory change the config file  reload the config file 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 you gmail com  RUN cp -R -p  var lib mysql  dev shm  amp  amp        rm -rf  var lib mysql  amp  amp        sed -i -e  s  var lib mysql  dev shm mysql g   etc mysql my cnf  amp  amp         etc init d mysql restart   Available on Docker hub here  https   hub docker com r richardjecooke ruby-mysql-in-memory

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      Stop mysql   Follow the instructions above  it necessary  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  Correct permissions among the copied database directories   Ownership should be mysql mysql  directories should be 700  and files should be 660  Restart mysql   Depending on your installation  old version DB files should be updated

User · Answer

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

User · Answer

If you are using SE linux  set it to permissive mode by editing  etc selinux config and changing SELINUX enforcing to SELINUX permissive

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  gt   etc fstab mount -a   Restart the server   systemctl start mysql

[mysql] How to change MySQL data directory?

Examples related to mysql

Examples related to directory

Examples related to default