MYSQL2014. 8. 19. 17:55

/usr/local/mysql/bin/mysqldump --skip-comments --skip-extended-insert --no-data -u root -p -h dev.service.co.kr  project | sed 's/AUTO_INCREMENT=[0-9]* //' > dev.sql

/usr/local/mysql/bin/mysqldump --skip-comments --skip-extended-insert --no-data -u root -p -h db.service.co.kr project  | sed 's/AUTO_INCREMENT=[0-9]* //' > pro.sql

vimdiff dev.sql pro.sql

Posted by 다오나무
MYSQL2012. 10. 12. 11:58

작성일 : 2012년 02월 16일 

작성자 : 김창현

제   목 : mysqldump을 이용하여 전체 데이터베이스시 일부 데이터베이스 제외시키기 

 

MYSQL 데이터 이전 시 많은 수의 DB 데이터를 이전하다 보면 --all-database를 통해 전체 DB데이터를 백업 받을 수가 있다. 그러나 신규 시스템 이전시에 전체 DB중 일부 DB는 제외하고 싶을 때가 있을 것이다. 그렇다고 일일이 DB를 DUMP하여 IMPORT 시키기에는 업무 효율성이 떨어질 것이다.

--all-database의 경우에는 시스템 데이터베이스인 mysql도 같이 DUMP가 되며, 일일이 에디터를 통해 제거하는 방법도 불편 하다.

시스템 데이터베이스외에 좀더 많은 DB에 대해 제외를 시키고자 한다면 정말 난감하다.

 

이를 해결하기 위해 리눅스의 파이프를 통해 해결해보자!!

 

표에 나와 있는 예제는 egrep을 이용하여 mysql|mysql.old라는 데이터베이스를 제외시키고 나머지 DB에 대해서 전체 DUMP를 한다.

mysql -uroot --password='' --batch --skip-column-names --execute='show databases' | egrep -v "mysql|mysql.old" | xargs mysqldump --password='' --databases -Q

 

mysql 유틸을 이용하여 SQL의 전체 데이터베이스를 확인 할 수 있는 show database를 활용하여 데이터베이스 모든 목록을 추출하고, 추출된 데이터베이스 내용을 파이프를 통해 egrep으로 전달하여 -v 옵션을 통해 제외하고 싶은 문자패턴들을 나열하고 제외된 데이터베이스 목록을 mysqldump로 넘겨 추려진 데이터베이스 목록에 대해서만 덤프를 받을 수가 있다. 추출된 목록을 넘기는 경우 한번에 받은 목록을 한번에 인자 처리 할 수 없는 관계로 xargs 명령어를 통해 라인단위로 인자를 넘겨 주었다.

'MYSQL' 카테고리의 다른 글

[MySQL] DB Schema 비교  (0) 2014.08.19
mysql 디비테이블 오래된자료 자동삭제  (0) 2012.07.31
mysql 백업하ㅂ  (0) 2012.06.22
Posted by 다오나무
우분투2012. 9. 21. 20:36

mysql의 log-bin을 활성화시켜 놓고 오래 사용하는 경우 var디렉토리에 mysql-bin.XXXX 형태의 바이너리 로그 파일이 계속 쌓이게 됩니다.
때로는 이 로그를 분석해서 어떤 쿼리들이 실행되었었는지 확인도 되고, 백업이나 복구에 사용되기도 하기 때문에 유용할 수 있습니다.
특히 DB 리플리케이션을 쓰려면 이 로그가 필요합니다.
그러나 작은 파티션에 계속해서 로그가 쌓이다 보면 어느 순간 용량이 부족해지게 되면 mysql구동에 문제가 생겨 버립니다. 그래서, 적당한 용량으로 로그를 유지하는 간단한 방법을 소개합니다.

바로 로그의 만료 기간을 지정해 주는 것입니다.
mysql에 접속한 후 아래의 명령으로 시스템 변수들을 확인해 보시면 expire_logs_days 라는 값이 0으로 되어 있을 것입니다.

mysql> show variables;

이 변수를 아래와 같이 원하는 기간으로 지정해 줄 수 있습니다.

mysql> set global expire_logs_days = 3;

다시, 위의 show variables;로 확인해 보시면 3일로 변경된 것이 보일 것입니다.
이러면 3일간의 로그만 보존되게 되면서 용량 문제가 없어집니다!


Posted by 다오나무
MYSQL2012. 7. 31. 23:21

mysql 디비에 등록되는 접속자로그 또는 시스템관련 로그는 디비에 쌓이게 되면 용량이 장난아니게 늘어난다.
일정기간이 지나면 도움도 안될뿐더러 용량만 차지하게 된다.
로그는 그때그때 보는게 최고..

mysql 자체적으로는 자동삭제는 불가능하며 php를 이용한 방법과 crontab에 등록해서 사용하는 방법이 있다.
쉘에서 실행하는 명령은 mysql -e "명령" 이렇게 사용한다.

30일이 지난자료 삭제

mysql -u아이디 -p비밀번호 디비명 -e "DELETE FROM  테이블명 WHERE 날짜필드명 < date_add(date_format( now() , '%Y-%m-%d'), interval -30 day)"


-e 옵션에서 mysql query 뒤에 세미콜론 ;는 사용안하며, 큰따옴표가 사용이 안되므로 쿼리명령어 안에는 작은 따옴표를 사용하면 된다.

-e 옵션과 내부적인 명령의 차이 - 따옴표가 변경된걸 확인할수 있다.
#mysql -u아이디 -p비밀번호 디비명 -e "select date_add(date_format( now() , '%Y-%m-%d'), interval -30 day)"

mysql>select date_add(date_format( now() , "%Y-%m-%d"), interval -30 day);


위 명령을 사용하여 크론탭에 등록해서 사용하면 된다.
한꺼번에 디비를 삭제하면 시스템이 다운될수도 있으니 조금씩 조금씩 삭제하는게 좋다.

Posted by 다오나무
MYSQL2012. 6. 22. 21:40

MYSQL 을 백업하는 방법은 2가지가 있습니다.

 

1.  파일 복사 하기 

- data 디렉토리의 파일을 별도의 장소에 복사 놓는 방법입니다.  

 

 ① mysql 의 데이타가 저장되는 장소를 알아야 됩니다.  

      windows에 mysql이 설치된 경우 mysql 이 설치된 폴더의 my.ini 에서 저장된 곳을 알아 낼 수 있습니다. 
      메모장을 이용해서 my.ini를 열어 보면

      #Path to the database root
       datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

      

      datadir 이 mysql DB 가 저장되어 있는 위치 입니다.  

 

 ②  위의 경우 datadir의 위치의 해당 데이타베이스의 폴더를 다른 곳에 복사해서 저장해 놓으면 됩니다.

 

  ※  datadir폴더에 보면 여러 파일들이 있는데 그 용도는 다음과 같습니다. 

       *.frm - 테이블 구조 *.MYD - 데이타 *.MYI - index

  ※ 운영체제별 mysql DB 저장되는 곳의 위치는 아래 링크를 참고하세요.  

       http://www.mkyong.com/mysql/where-does-mysql-stored-the-data-in-my-harddisk/ 

 

  ※  InnoDB의 경우 조금 더 복잡한 과정을 거쳐야 한다고 합니다.  더 알게 되면 포스팅 하겠습니다.

       http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html 

     http://feedtome.springnote.com/pages/546778

      http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_databases


2. mysqldump를 이용해 테이블과 데이타를 쿼리문으로 백업하기

 

① 서버의 전체 DB 백업 하기

 mysqldump -u계정 -p비밀번호 -A > 모든DB.sql

(예) mysqldump -uroot -ppassword -A > All_database.sql


 특정 DB 백업 하기 

     mysqldump -u계정 -p비밀번호 특정DB명 > 저장할파일명.sql

    (예) mysqldump -uroot -ppassword northwind > northwind.sql

 

 특정 DB 의 특정 Table 만 백업

     mysqldump -u계정 -p비밀번호 특정DB명 특정Table명  > 저장할파일명.sql

     (예) mysqldump -uroot -ppassword northwind orders > northwind_orders.sql
 

④ 특정 DB 의 스키마만 백업하기

     mysqldump -u계정 -p비밀번호 -d 특정DB명   > 저장할파일명.sql

    (예) mysqldump -uroot -ppassword -d northwind > northwind.sql

 

⑤  InnoDB에서 트리거 , 프로시져, 함수 포함하여 백업하기

   - 트리거는 default값으로 백업이 실행되나 저장 프로시져는 백업되지 않는다. 

   - 저장 프로시져가 백업되게 하기 위해서는 옵션에  --routines 을 넣어줘야 한다.

     mysqldump -u계정 -p비밀번호 --routines 특정DB명 > 함수프로시져트리거.sql

     (예) mysqldump -uroot -ppassword --routines  northwind > northwind.sql

 

     ※ 트리거 , 프로시져, 함수 만 백업하기 - 쿼리문만  

       mysqldump -u계정 -p비밀번호 --routines  --no-create-info --no-data --no-create-db --skip-opt

       특정DB명 > 함수프로시져트리거.sql 


    (예) mysqldump -uroot -ppassword --routines --no-create-info --no-data --no-create-db --skip-opt 

           northwind > northwind_only_sp_trigger_function.sql

           이렇게 트리커 프로시져 함수만 백업한 경우는 반드시 Data와 테이블 스키마를 별도로 백업 받아 줘야 합니다.  

[출처] mysql 백업하기|작성자 불가사리

Posted by 다오나무
영삼이의 IT정보2012. 5. 18. 13:26

INNER JOIN : simple join이라고도 하며, 둘 이상의 테이블에서 join condition을 만족하는 행만 반환한다.

즉, 둘 이상의 테이블에 전부 존재하는 데이터만 조회한다는 것이다. (물론 ON조건에 만족하는 데이터)

집합으로 표현하자면 교집합이라 이해하면 된다.

간단한 예는 http://isstory83.tistory.com/entry/조인Join-쿼리

 

OUTER JOIN : OUTER JOIN 에는 LEFT , RIGHT, FULL OUTER JOIN 등의 세가지 형식이 있다.

INNER JOIN과 는 달리 두 테이블에서 지정된 쪽의 (LEFT or RIGHT) 모든 결과를 모두 보여준 후 반대쪽에 매칭되는 값을 보여주고, 값이 없어도 NULL로 보여주는 JOIN이다.

JOIN 이전에 나오는 테이블이 왼쪽(LEFT)테이블이 되고, JOIN 이후에 나오는 테이블은 오른쪽(RIGHT)테이블이 된다.

 

OUTER JOIN의 종류 및 사용

LEFT OUTER JOIN - A라는 테이블과 B라는 테이블이 있다면 그 두 테이블 중 왼쪽 테이블을 기준을 세우는 JOIN

SELECT t1.컬럼1, t1.컬럼2, t2.컬럼4, t2.컬럼5  FROM 테이블1 t1 LEFT JOIN 테이블2 t2 ON (t1.컬럼1 = t2.컬럼6)

왼쪽 TABLE1을 기준으로 컬럼1과 컬럼6를 매칭하여 결과 값을 보여주되 컬럼6에 3이 매칭되는 값이 없기에 NULL을 보여준다.

 

RIGHT OUTER JOIN - A라는 테이블과 B라는 테이블이 있다면 그 두 테이블 중 오른쪽 테이블을 기준을 세우는 JOIN

SELECT t1.컬럼1, t1.컬럼2, t2.컬럼4, t2.컬럼5 FROM 테이블1 t1 RIGHT JOIN 테이블2 t2 ON (t1.컬럼1 = t2.컬럼6)

테이블2의 컬럼6의 값 4에 해당하는 컬럼1이 없으므로 해당 데이터를 NULL로 보여준다.


FULL OUTER JOIN - 간단하게 LEFT와 RIGHT OUTER JOIN의 합이라고 생각하면 된다.

SELECT t1.컬럼1, t1.컬럼2, t2.컬럼4, t2.컬럼5 FROM 테이블1 t1 FULL OUTER JOIN 테이블2 t2 ON (t1.컬럼1 = t2.컬럼6)

 

참고적으로, 같은 두 개의 테이블을 INNER JOIN하면 아래와 같은 결과를 보여준다.

SELECT t1.컬럼1, t1.컬럼2, t2.컬럼4, t2.컬럼5 FROM 테이블1 t1 INNER JOIN 테이블2 t2 ON (t1.컬럼1 = t2.컬럼6)

위의 FULL OUTER JOIN에서 양쪽에 NULL이 들어간 데이터들이 사라진 것을 확인 할 수 있다.

Posted by 다오나무
영삼이의 IT정보2011. 10. 21. 12:18

최초 설치후에 MySQL root 의 비밀번호를 변경 하고 phpmyadmin 으로

관리 할수 있는 세팅을 처리 해보겠습니다.

   

   

먼저 phpmyadmin 으로 이동 하신후에

Mysql 데이터베이스를 선택하신후 user 테이블로 이동 합니다.

   

   

Sql 탭을 클릭하신후 이동 하셔서

다음과같은 명령어로 root 유저의 비밀번호를 변경 합니다.

   

여기서는 test 로 변경 하였습니다.

Password 함수의 내부에 test 문자열을 자신이 원하는 비밀번호로

변경 하시면 됩니다.

update user set password = password('test') where user = "root";

   

   

파란색 V 표시가 생기면 정상정으로 MySQL 에 질의가 이루어진것입니다.

   

   

그리고 나서 XAMPP 컨트롤의 MySQL 부분을 STOP 한후 Start 로

서버롤 재기동 시켜 주시면 반영이 됩니다.

   

   

   

MySQL 서버의 비밀번호는 변경이 되었으나

Phpmyadmin 에서 MySQL 접속정보가 변경이 되지 않아서

다음과 같은 화면을 볼수 있습니다.

   

   

   

C:\xampp\phpMyAdmin\

Xampp 가 설치된 디렉토리에 phpmyadmin 으로 이동 하신후

   

   

Config.inc.php 를 에디트플러스로 열어 보면 다음과 같은

내용이 보입니다.

   

Password 부분을 MySQL 에서 접속한 비밀번호로 변경 하게 되면

정상적으로 DB 에 접근 할수 있습니다.

Posted by 다오나무