MySQL 데이터 경로 변경하는 방법

MySQL의 데이터가 저장되는 경로는 기본적으로 /var/lib/mysql에 저장이 됩니다. 하지만 다른 추가적인 HDD에 저장을 하고 싶을때는 데이터가 저장될 경로를 변경하시면 됩니다.


데이터 저장소 생성하기

/var/lib/mysql의 기존 경로를 /media/hdd/mysql로 변경하는 방법입니다. 시작을 하기 전에 mysql server를 stop을 합니다.

$ sudo service mysql stop
stop mysql Create the new directory:

새롭게 데이터가 저장될 경로를 생성합니다.

$ mkdir /media/hdd/mysql

기존의 /var/lib/mysql의 정보를 새로운 경로인 /media/hdd/mysql로 복사를 합니다.

$ cp -R /var/lib/mysql /array2/mysql


데이터 저장소 연결하기

기존의 경로인 /var/lib/mysql을 /media/hdd/mysql로 변경하는 방법입니다.

$ sudo vi /etc/mysql/my.cnf
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3307
basedir         = /usr
#datadir                = /var/lib/mysql
datadir         = /media/hdd/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

새롭게 저장할 폴더의 권한을 mysql:mysql로 변경을 합니다.

$ chown -R mysql:mysql /media/hdd/mysql

기존의 mysql폴더를 backup을 한 뒤에 생성한 mysql의 폴더를 기존의 mysql로 연결을 해줍니다.

$ mv /var/lib/mysql /var/lib/mysql-old
$ ln -s /media/hdd/mysql /var/lib/mysql 

AppArmor에 datadir을 등록한 뒤에, apparmor profiles을 다시 로딩을 합니다.

$ echo "alias /var/lib/mysql/ -> /your/new/datadir/," >> /etc/apparmor.d/tunables/alias
$ sudo /etc/init.d/apparmor reload

mysql서버를 재시작해주면 이제 datadir이 변경된 것을 확인할 수 있습니다.

$ sudo service mysql restart


  1. hada 2018.01.25 15:34

    기존의 /var/lib/mysql의 정보를 새로운 경로인 /media/hdd/mysql로 복사를 합니다.

    $ cp -R /var/lib/mysql /array2/mysql

    이부분에서 복사경로가 /media/hdd/mysql이게 아닌데 오타인가요????

  2. 궁금 2018.04.10 01:35

    도움이 되었습니다.

    그런데 변경된 디렉토리로 심볼릭링크를 만들었는데 굳이 my.cnf 에서 datadir 을 수정할 필요가 있나요?
    해보니 my.cnf 를 수정하지 않아도 되긴한데, 어떤 차이가 있어서 그렇게 하신건지 궁금해서 글 남겨봅니다.

    전 centos 에서 작업을 했는데, 마지막 mysql 실행시에 permission denied 오류가 발생하더군요.
    구글링 해보니 selinux 때문인데, selinux 를 permissive 시키니 정상 작동하네요.
    https://blogs.oracle.com/jsmyth/selinux-and-mysql

    감사합니다.

  3. 나그네 2019.02.08 13:07

    순서대로 진행했는데, Error가 났습니다. systemctl mysql status or journalctl -xe 확인하라고 해서 systemctl 확인했는데,

    active : failed(results : exit-code) ~~~
    process : 9377 ExecStart=/usr/sbin/mysqld --demonize --pid-file=/run/mysqld/mysqld.pid ( code=exited, status=1/FAILURE)

    ~~~~~
    Failed to start MYSQL Community Server.

    이렇게 에러가 뜨는데 혹시 무슨 문제인지 아신다면 해결책 좀 부탁드리겠습니다(비밀글로했더니 안보이네요ㅠ)

    • 쌍쌍바나나 2019.02.08 13:39 신고

      일단 mysql이 제대로 시작하는지 확인이 필요하겠네요! 설치경로가 동일한가요? 동일하게 이전에 시작이 되었던건데 경로변경하고 다시시작이 안되는거라면 전체 로그부탁드려요!

MySQL 외부접속 허용하는 방법

MySQL Server를 설치한 후에 외부에서 접근하는 방법에 대해서 설명하려고 합니다. 외부 접근을 하기 위해서는 두가지를 변경해야 합니다.

  • bind-address 변경
  • 계정 권한 변경

bind-address 변경하기

shell에서 아래와 같이 입력을 하면, mysql configure파일이 확인이 가능합니다.

$ sudo vi /etc/mysql/my.cnf

configure파일이 열리면 항목중에 [mysqld]라는 항목이 있습니다. 하단에 보면 bind-address의 값이 기존에는 127.0.0.1로 지정이 되어있습니다. 그 값을 0.0.0.0으로 변경합니다. 이렇게 변경이 되면 이제 외부에서도 접근이 가능합니다.

[mysqld]
bind-address            = 0.0.0.0
#bind-address           = 127.0.0.1


계정 권한 변경하기

mysql을 접속을 합니다.

$ mysql -u root -p

  접속을 한 뒤에는 계정의 mysql의 databases를 선택하고, root에게 모든 IP에서 허용이 가능하도록 권한을 변경합니다. *은 모든 database와 table을 의미하고, %는 모든 호스트를 의미합니다. 권한 및 계정에 대해서 더 자세히 알고 싶으시면 http://ourcstory.tistory.com/45 을 확인하세요.

$ grant all privileges on dbname.table to userid@host identified by 'password';
$ grant all privileges on *.* to root@'%' identified by 'password';

  이렇게 bind-address와, 계정의 권한을 변경한 뒤에 아래와 같이 재시작을 해주면, 외부에서도 접근이 가능합니다. 만약에 공유기를 사용하시는 분이라면 3306의 포트를 포트 포워딩을 통해 접속하시면 됩니다.

$ sudo service mysql restart


DELETE, UPDATE를 하는 경우 SAFE_UPDATE 모드 상태로 설정이 되어 있는 경우 아래와 같은 에러코드를 뱉습니다. 


에러 내용은 Key의 컬럼을 이용하지 않고, 업데이트를 할 수 없다는 내용입니다. 


Error Code: 1175

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column



# SQL_SAFE_UPDATES 모드 off
SET SQL_SAFE_UPDATES=0;
# delete query
delete from table where date(timestamp) between date('2016-05-29 00:00:00') and date('2016-05-30 23:59:59');

들어가며

  SQL을 사용하면서 시간에 대한 조건을 걸어서 검색을 하는 경우가 많습니다. 저 같은 경우에는 로그가 정확하게 해당 날짜에 올라오는 개수를 알고 싶을때나, 특정 시간대에 이상점을 알고, 그 이상점에 대해 자세하게 분석하기 위해 시간 조건을 걸어서 조금더 디테일하게 보는 경우가 있습니다. 아래 예제는 timestamp에 between <time> and <time>의 조건으로 검색하는 방법에 대해서 설명을 하려고 합니다. 


쿼리 예제

  해당 쿼리는 MySQL의 기준으로 사용한 쿼리입니다. 어떤 특정 table에서 모든 조건(*)을 검색하려고 합니다. 여기서 timestamp의 형은 SQL에서 데이터 타입인 TIMESTAMP의 형태를 갖고 있습니다. 이때 SQL에서 특정 날짜에 속해있는 rows를 가져오는 방법으로 between 'condition' and 'condition'을 사용하면 됩니다. 아래 예제는 2016년 5월 12일 00시 부터 23시 59분 59초, 즉 5월 12일에 있는 데이터를 사용자별로 몇개의 로그가 있는지 결과를 보는 쿼리문입니다.  


select count(*) from table
where timestamp between '2016-05-12 00:00:00' and '2016-05-12 23:59:59' group by user_id;


  위의 결과는 해당 group_id와 그에 해당하는 개수가 출력됩니다. 


| group_id | count(*) |
| group_A | 1239233 |

| group_B | 5432323 |

|     .....     |     .....       |

MySQL에서 사용자를 추가/제거 하는 방법과 권한을 부여하는 방법입니다. 

접속하기

$ mysql -u root -p

사용자 확인하기

mysql database를 선택하고, host, user, password를 확인합니다. 

mysql >use mysql; 

mysql > select host, user, password from user; 

 여기서 host는 localhost, '%'가 있습니다.  사용자 아이디 뒤에 @localhost, '%'에 따라서 외부 접근이 허용되는 권한을 줄 수 있습니다. localhost는 내부접근, '%'는 외부 접근입니다. 

사용자 추가 (권한 추가)

사용자 아이디를 만드는 방법, 비밀번호 추가, 외부접근 허용
userid에 추가하고자 하는 아이디를 입력하면 됩니다. 
identified by 'psasword'; password에 비밀번호를 함께 입력하면 패스워드를 설정할 수 있습니다. 

 mysql > create user userid 

mysql > create user userid@localhost identified by 'password';

mysql > create user 'userid'@'%' identified by 'password;

예) create user 'hiru'@'%' identified by 'hirururu'; 
(hiru라는 외부접근이 가능한 유저를 생성하고, 비밀번호는 hirururu로 설정하겠다.)

다른 방법은 

mysql > insert into user (host, user, password) values ('localhost', 'hiru', 'password('hirururu')); 

사용자 제거

mysql > drop user 'hiru';

mysql > delete from user where user ='hiru';


사용자에게 데이터베이스 사용권한을 부여하는 방법

MySQL 서버에 접속해서 모든 사용자가 Delete, Insert, Update의 권한이 있다면, 생각만해도 끔찍하지요. 
권한을 추가하고 삭제하기 위해서, GRANT와 REVOKE의 명령을 사용한다. 
SELECT, DELETE, UPDATE, INSERT의 모든 권한을 주기 위해서는 

 mysql > grant all privileges on dbname.table to userid@host identified by 'password';

mysql > grant select, insert, update on dbname.table to userid@host identified by 'password';

mysql > grant select, insert, update on dbname.table to userid@'192.168.%' identified by 'password';

(host가 192.168.X.X로 시작되는 모든 IP의 원격 접속을 허용한다는 의미입니다)
(dbname.table 대신 dbname.* 은 해당 database의 모든 table의 접근을 허용한다. *.*은 모든 접근을 가능하게 한다.) 

변경된 권한을 적용하기

mysql > flush privileges; 

권한을 삭제하는 방법

mysql > revoke all on dbname.table from username@host

권한을 확인하는 방법

mysql > show grants for userid@host

mysql > show grants for 'hiru'@'%';


  1. 디비왕 2016.03.23 14:25

    감사합니다.

MySQL 설치

http://www.mysql.com/downloads


Workbench는 MySQL에서 제공하는 DB를 관리하는 툴이다. 

DB의 Schema와 Table등을 추가, 삭제, 수정이 매우 쉽기 때문에 command에서 하는 작업을 원클릭으로 끝내는 방법을 설명하겠다.

MySQL을 설치하고 나서, MySQL-Workbench가 설치가 되었다면 Workbench를 통해서 DB를 관리하자.

직접 사용자가 MySQL Command Line Client를 통해서도 가능하다. 

$ show databases;

$ create database test;

$ use test;

$ show tables; 


최초에 Workbench를 실행하면 아래와 같다. +를 누르면 새로운 Connection을 Setup할 수 있다. 


내가 원격으로 서버에 있는 MySQL에 접근한다면 아래에 Hostname, Port, Username, Password를 입력한다. 추가한뒤에 추가된 Connection을 누르면 아래와 같이 해당 Instance를 관리하는 페이지로 이동한다.


일단 외부에서 접근이 가능하게 하도록 하기 위해서 Users and Privileges를 누르고 root에 Limit to Hosts Matching의 내용을 

localhost -> %로 변경해주자. 그럼 어디서든 접근이 가능하다. 지금은 개발테스트를 하기 위하기 때문에 걸림돌을 없애고 시작.



Schema, Table 추가 등은 왼쪽 아래에 있는 SCHEMAS를 통해 관리한다. 최초 Create Schema를 만들어보자. ($ create database test;) 와 똑같다

sakila, sys, worlds의 목록을 보기 위해서는 ($ show databases;)를 하면 된다.













+ Recent posts