MySQL 데이터 디렉토리를 변경하는 방법?

MySQL 데이터 디렉토리를 변경하는 방법?

---

기본 MySQL 데이터 디렉토리를 다른 경로로 변경할 수 있습니까? 이전 위치에서 데이터베이스에 액세스 할 수 있습니까?

---

1. 다음 명령을 사용하여 MySQL을 중지하십시오.

   sudo /etc/init.d/mysql stop
   

2. /var/lib/mysql다음 명령을 사용하여 기존 데이터 디렉토리 (기본값은에 있음 )를 복사하십시오 .

   sudo cp -R -p /var/lib/mysql /newpath
   

3. 다음 명령으로 MySQL 구성 파일을 편집하십시오.

   sudo gedit /etc/mysql/my.cnf   # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
   

4. 에 대한 항목을 datadir찾아 경로 /var/lib/mysql를 새 데이터 디렉토리로 변경하십시오.

5. 터미널에서 다음 명령을 입력하십시오.

   sudo gedit /etc/apparmor.d/usr.sbin.mysqld
   

6. 로 시작하는 줄을 찾으십시오 /var/lib/mysql. /var/lib/mysql새로운 경로로 줄을 바꾸십시오 .

7. 파일을 저장하고 닫습니다.

8. 다음 명령으로 AppArmor 프로파일을 다시 시작하십시오.

   sudo /etc/init.d/apparmor reload
   

9. 다음 명령으로 MySQL을 다시 시작하십시오.

   sudo /etc/init.d/mysql restart
   

10. 이제 MySQL에 로그인하면 이전과 동일한 데이터베이스에 액세스 할 수 있습니다.

---

현재 데이터를 새 디렉토리에 복사하고 my.cnfMySQL 을 변경해야 합니다.

[mysqld]
datadir=/your/new/dir/
tmpdir=/your/new/temp/

서버가 실행되고 있지 않을 때는 데이터베이스를 복사해야합니다 .

---

먼저 mysql 서버를 중지해야합니다. 예 :

# /etc/init.d/mysql stop

그런 다음 이전 데이터 디렉토리 (예 : / var / lib / mysql)를 복사해야합니다. 다음을 통해 새 디렉토리에 대한 권한

# cp -R -p /var/lib/mysql /new/data/dir

이제 /etc/mysql/my.cnf새로운 데이터를 변경 하고 서버를 다시 시작할 수 있습니다

# /etc/init.d/mysql restart

---

빠르고 쉬운 작업 :

# 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파일을 편집 하고 아래 [mysqld]줄을 추가하십시오.

datadir=/new/dir/for/mysql/

CageFS (CloudLinux 유무에 관계없이)를 사용 중이고 MySQL 디렉토리를 변경하려는 경우이 파일에 새 디렉토리를 추가해야합니다.

/etc/cagefs/cagefs.mp

그런 다음이 명령을 실행하십시오.

cagefsctl --remount-all

---

컴퓨터에 데이터베이스를 유지하고 싶지만 외장 하드 드라이브에 데이터가 있고 두 장치를 사용하여 전환했습니다.

Mac에 있고 Homebrew를 사용하여 MySQL을 설치 한 경우이 방법이 적합합니다. 그렇지 않으면 datadir머신 에서 MySQL의 적절한 위치를 대체하기 만하면됩니다 .

#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

그런 다음 다시 전환하려면 다음을 수행하십시오.

mv mysql mysql.remote

mv mysql.local mysql

로컬 데이터베이스를 다시 사용하고 있습니다. 희망이 도움이됩니다.

---

먼저 mysqld를 중지

mysql_install_db --user=mysql \
                 --basedir=/opt/mysql/mysql \
                 --datadir=/opt/mysql/mysql/data

그런 다음 /etc/mysql/my.cnf
Start mysqld 에서 datadir을 변경 하십시오.

참고 :
# 1 : SELinux 설정을 조정해야 할 수도 있습니다 (문제 발생시 SELinux를 비활성화 한 상태로 시도). Apparmor (우분투)도 문제가 될 수 있습니다.

# 2 : MySQL 설치 DB 참조 참조

---

먼저 mysql을 중지하십시오

sudo service mysql stop

mysql 데이터를 새 위치로 복사하십시오.

sudo cp -rp /var/lib/mysql /yourdirectory/

apparmor를 사용하는 경우 다음 파일을 편집하고 다음을 수행하십시오.

sudo vim /etc/apparmor.d/usr.sbin.mysqld

/ your / your /로 / var / lib /를 대체 한 후 파일에없는 경우 다음을 추가하십시오.

    /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

---

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!

---

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.

---

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.

---

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.

---

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

---

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 && \
    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/

---

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

---

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

---

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.

---

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

---

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.

---

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

참고URL : https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory