Tag Archives: mysql

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 – InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

오라클 홈페이지에 가니 플러거블 스토리지 엔진 기반으로 동작하는 MySQL 아키텍쳐 그림을 볼 수 있었습니다. 플러거블(Pluggable)이라 함은 스토리지 방식을 선택할 수 있다는 것인데요. 이는 각각의 스토리지가 컴포넌트처럼 동작한다는 의미를 내포합니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

MySQL 5.5.5 이전 버젼에서는 MyISAM이 기본 스토리지 엔진이었습니다, 그런데 5.5.5부터는 InnoDB가 기본적으로 붙도록 바뀌었습니다. 쓰는 입장에서는 별 차이를 느낄 수 없을 것이라 생각됩니다만, 고도화 된 연산을 하는 부분에서는 성능차이가 발생 할 수 있을 것으로 생각됩니다. 즉, 개발된지 오래 된 MyISAM은 성능적으로 좀 느린 피드백을 줄 가능성이 높습니다. 그렇지만 256TB의 대용량을 지원 및 검색 기능은 무시 할 수 없을것 같군요.

MyISAM 스토리지의 기본피쳐는 아래와 같습니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

이와 비교할만한 DB로 InnoDB를 살펴봅시다.  보시는 바와 같이 스토리지는 64TB로 제한적이지만 주 메모리에 데이터와 인덱스(Index)를 상시 캐싱(Caching)하는 구조로 인한 퍼포먼스상의 잇점을 무시할 수 없을 것 같습니다. 즉, 인덱싱이 잘 되어져 있는 DB를 쓰시고 계신다면 설계시 감안했던 모든 잇점을 다 살리실 수 있을 것입니다.

플러거블 스토리지 엔진을 가진 MySQL 아키텍쳐 - InnoDB는 인메모리 캐싱을 하는 고성능 솔루션

좀더 자세한 기술적 자료는 아래 링크를 참조하세요.

https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/storage-engines.html

CentOS 6.x에 APM(Apache+PHP+MySQL) 설치 및 초기 설정 방법

Centos 6.x를 Minimal Install로 설치하면 Apache 및 MySQL이 설치되어져 있지 않습니다.

본 포스트에서는 이런 환경에서 Apache, PHP, MySQL을 설치하고 기본 설정까지 해 보도록 하겠습니다. 참고로 저는 Centons 6.8을 설치하였습니다.

먼저 Root로 로그인하여 다음과 같이 패키지를 설치합니다.

# yum install -y lynx wget
# yum install -y httpd
# yum install -y mysql-server mysql
# yum install -y php php-mysql php-gd php-mbstring php-mcrypt

여기서 Apache, MySQL이 부팅시 자동실행 되도록 하기 위해 다음의 커맨드를 실행합니다.

# chkconfig httpd on
# chkconfig mysqld on

설치가 완료 되었는지 확인하는 방법은 다음과 같습니다.

# service httpd start
# service httpd status
# service mysqld start # service mysqld status # php -v

마지막으로 MySQL 설치 후 Root Password를 다음과 같이 설정 해 줍니다.

# /usr/bin/mysqladmin -u root password 'P@ssw0rd'
# mysql -u root -p
Enter Password:*****

MAC에 Apache, PHP, MySQL 설치 – MAMP로 쉽게 설치 할 수 있어

집에 방치되어져 있는 Mac Mini를 서버로 사용해보려고 이것 저것 찾다 보니 MAMP라는 솔루션을 찾았습니다.

MAMP는 Mac OS에서 Apache, MySQL, PHP를 사용하게 해 주는 솔루션입니다.

각각 따로 따로 설치하지 않아도 되며, 하나만 설치하면 MySQL, PHP, Apache가 한방에 설치됩니다.

 

참고로 MAMP는 http://www.mamp.info/en/downloads/ 에서 받으실 수 있습니다. 즉, 맥에 웹서버 설치하는 것을 도와주는 도우미라고나 할까요.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

 

아래와 같이 pkg 다운로드를 완료하신 후 이를 실행하시면 MAMP가 MAC에 깔리게 됩니다.

 

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

 

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

설치가 본격적으로 시작되기 전 User Password를 다시 물어봅니다. 이는 시스템의 중요한 곳에 파일을 쓰기 때문이며, 이 때문에 보안상 해당 Machine의 주인이 맞는지 재차 확인하기 위한 것입니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

설치가 완료되었으면 Launchpad를 클릭하여, MAMP를 실행합니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAMP를 설치하면 아래와 같이 MAMP외에 Sequel Pro, MAMP PRO도 같이 설치됩니다. 아마 의도적으로 그렇게 만드신 것 같습니다.

참고로, MAMP PRO는 유료이므로, 저는 MAMP만 사용 할 예정이므로 MAMP를 실행하여 설정을 진행하였습니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAMP PRO를 채크하는 것도 안하게 채크하고, MAMP 실행.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

포트는 기본적으로 Apache Port가 8888번으로 설정되어져 있는데, 저는 아래와 같이 80번으로 설정하였습니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

그러면 다음과 같이 브라우져가 뜹니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

그리고 Apache Server, MySQL Server가 동작하고 있다는 표시로 녹색컬러가 칠해집니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

그리고 하나 더, MAMP를 설치하면 MYSQL을 효율적으로 관리할 수 있는 phpMyAdmin이 자동으로 설치되는데요, 다음의 URL로 접속하시면 사용이 가능합니다.

http://localhost/phpmyadmin

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

그런데 하나 문제점… 아무나 phpMyAdmin에 접속이 가능하네요.

이는 터미널을 열어 /Applications/MAMP/bin//phpMyAdmin 폴더로 이동하신 후 config.inc.php를 바꿔주면 해결이 됩니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

아래와 같이 원래 있던 auth_type을 주석을 달아주시고, 대신 cookie로 변경해주시면 됩니다.

MAC에 Apache, PHP, MySQL 설치 - MAMP로 쉽게 설치 할 수 있어

그리고 브라우져를 닫았다 다시 여시면, 다음부터는 ID, Password를 물어봅니다.

 

이상 간단히 MAMP 설치를 통해 MAC에서 웹서비스를 돌릴 수 있는 방법을 알아 보았습니다.

shell script를 이용한 mysql 자동 백업(backup)

다음 script를 /etc/cron.daily/mysql_back.sh로 만들어 두면 백업이 끝납니다.
MySQL DB를 dump해서 다른 server에 옮겨 둡니다. 같은 서버에 데이타를 두면
server crash가 발생할 때… 대응이 불가능하죠?… -..-a

#!/bin/bash
BACKUP_DIR=/home/backup

DATE=$(date +%Y%m%d)
rm -rf $BACKUP_DIR/people2-$DATE.sql

/usr/local/mysql/bin/mysqldump -u 사용자아뒤 -p비밀번호 DB이름 > $BACKUP_DIR/people2-$DATE.sql

gzip -f $BACKUP_DIR/people2-$DATE.sql

ncftpput -u 사용자아이디 -p 로그인비밀번호 hostname.com /home/db_backup/ $BACKUP_DIR/people2-$DATE.sql.gz

mysql의 백업과 복구 (backup & restore)

우선.. mysql을 접속합니다.
접속까지의 명령어는 기본 명령어 설명에 있으니 생략을;;

로그파일이 많이 쌓여서 디스크 용량이 부족한 경우 일단 로그파일을 지웁니다.

mysql>RESET MASTER

로그 파일은 XXX-bin.000001 같은 형식으로 쌓입니다.
위치는 디폴트로 설치했다면 /usr/local/mysql/var/에 있을 겁니다.
RESET MASTER를 입력하면 로그 파일들이 지워집니다.

백업을 하는 방법은 쉘에서 하는 법과 mysql내에서 하는법 두가지가 있습니다.

★mysql내에서 백업하는 법

mysql>backup table <table1>[, table2, table3…] to ”/usr/local/backup”;

의 형식이구요. 물론 뾰족괄호같은건 입력하는거 아니구요 대괄호안은 여러 테이블 백업시 입력하는 겁니다. to다음은 저장할 디렉토리명입니다. 작은따옴표에 넣어서 입력하면 됩니다.
리스토어는

mysql>restore table <table1>[, table2, table3…] from ”/usr/local/backup”;

★쉘에서 백업하는 방법
mysqldump란 실행프로그램은 /usr/local/mysql/bin 에 있습니다.(물론 기본설치시)
그러므로 거기까지 들어가서 실행합니다.
mysqldump를 이용해서 백업을 하면 텍스트형태로 백업이 되기 때문에vi에서 열어서 볼 수 있습니다. DB생성, 테이블생성구조등이 나와있기 때문에 이용해서 다른 작업도 가능합니다.

전체 데이터베이스 백업하기
>./mysqldump -uroot -ppass -e –all-databases > /usr/local/backup/alldata.sql
데이터베이스 단위로 백업하기
>./mysqldump -uroot -ppass -e –databases db1 > /usr/local/backup/db1.sql

*옵션에서 e를 붙이는 이유는 extended-insert로 리스토어시 보다 빠르게 하기 위해 쿼리를 변형하는 옵션입니다.

리스토어 하기
>./mysql -uroot -ppass < /usr/local/backup/alldata.sql
innodb로 리스토어 하려면 alldata.sql을 vi로 열어서 Type=MyISAM부분을 삭제하면 자동으로 innodb로 저장됩니다.

물론 -u다음에 붙어있는 root는 mysql아이디이기 때문에 알맞는 아이디로 바꾸시구요 -p뒤에 있는 pass란 글자도 마찬가지..
-p라고만 입력하고 엔터를 치면 비밀번호를 물어봅니다. 그 때 비밀번호를 입력해줘두 됩니다.

mysql 백업에서 복구까지

데이터베이스 관리자와 개발자들은 정기적인 데이터 백업의 중요성을 잘 알 것이다. 만약 디스크나 서버가 고장날 경우 백업을 미리 해 뒀는지 여부는 1년치 업무결과를 잃어버리느냐 아니면 몇시간만에 정상으로 복귀하느냐를 결정한다.

하지만 다행스럽게도 MySQL 사용자는 백업과 복구를 할 수 있는 간편하게 할 수 있는 내장 전용 툴을 사용할 수 있다. 이툴을 이용하면 이기종 플랫폼으로 MySQL 데이터베이스를 간편하게 옮길 수 있으며, 또 다른 포맷으로 레코드를 저장하거나불러오기도 가능하다.

데이터베이스 파일 복사
MySQL 데이터베이스 백업의 기본은 데이터베이스 파일 자체를 복사하는 것이다. MySQL은 이기종 플랫폼에서도 동일한 테이블포맷을 사용하므로 MySQL 테이블과 인덱스 파일을 플랫폼을 넘나들며 쉽게 복사할 수 있다(물론 양쪽 플랫폼에 동일한 MySQL버전이 설치돼 있어야 한다).

실제 복사하는 방법을 살펴 보자. 먼저 MySQL은 모든 데이터베이스를 data/ 디렉토리에 저장되며, 이는 다시 각데이터베이스를 위한 서브디렉토리로 구분된다. 테이블과 테이블 인덱스는 파일로 저장되고, 파일명은 테이블명과 확장자를 합쳐서붙여진다.

가장 쉬운 데이터베이스 복사 방법은 전체 data/ 디렉토리를 백업용 미디어에 복사하고, 언제든지 복구할 수 있도록 저장해 두는것이다. 복사를 자동화하는 스크립트를 작성하는 것도 좋은 방법이다. 스크립트를 작성했으면 이를 crontab에 추가해데이터베이스 업데이트 주기에 따라 매일 혹은 매주 단위로 실행되도록 설정할 수 있다.

한가지 주의할 것은 윈도우와 유닉스 플랫폼 사이에서 파일을 이동하는 경우다. 유닉스 파일명은 대소문자를 구분하지만 윈도우는그렇지 않으므로, MySQL 테이블명에 대소문자가 뒤섞인 있으면 문제가 발생할 가능성이 있다(물론 테이블내의 데이터는 영향을받지 않는다). 최악의 경우 데이블명과 코드를 수정하기 전까지 제대로 실행되지 않을 수도 있다. 따라서 최선의 방법은 테이블명을소문자만으로 작성하는 것이다.

테이블을 텍스트 파일로 저장하기
파일복사 외에 MySQL에 기본 설치된 mysqldump 툴을 사용하는 방법도 있다. 이툴은 테이블과 데이터베이스는 물론데이터베이스 전체를 텍스트 파일로 변환해 저장한다. 사용법도 매우 단순해, 내보낼 데이터베이스 이름을 인자로 다음과 같이실행하면 된다.

$ mysqldump -u root -p secret stocksdb

mysqldump 툴은 MySQL 서버와 접속해 주어진 암호정보로 로그인한 후, 일련의 SQL 명령어를 이용해 테이블 구조와 데이터를 다음과 같은 형태로 출력한다.


— Dumping data for table ‘portfolio’

INSERT INTO portfolio VALUES (1,’DHDU’,2589,77.56); INSERT INTOportfolio VALUES (2,’YHOO’,3200,45.65); INSERT INTO portfolio VALUES(3,’WMT’,100,53.29);

물론 이를 콘솔에 출력하는 것은 그리 유용하지 못하다. 여러분이 원하는 것은 이를 파일로 저장해 백업하고나 복구하는데 이용할 수 있어야 한다. 이를 위해서는 출력 형태를 파일로 다시 정해주기만 하면 된다.

$ mysqldump -u root -p secret stocksdb > stocksdb.sql [/code]

만약 특정 테이블만 백업하려면 데이터베이스 이름 뒤에 테이블 이름을 붙이면 된다. 다음은 stocksdb 데이터베이스에서 users 테이블에 저장된 구조와 데이터만을 추출하는 구문이다.

$ mysqldump -u root -p secret stocksdb users > users.sql

또한 시스템 내의 모든 데이터베이스를 저잧하려면 다음과 같이 –all-databases 옵션을 이용한다.–all-databases 옵션을 사용하면, mysqldump 명령 결과에 각 데이터베이스를 초기화하는 CREATEDATABASE문이 포함된다. 이렇게 하면 데이터베이스 전체를 한 번에 복구하기가 더 쉬워진다.

$ mysqldump -u root -p secret –all-databases > backup.sql

테이블 구조만 백업하고자 하면 다음과 같이 –no-data 옵션을 사용하면 된다. 이 옵션은 애플리케이션을 처음 설치해서, 레코드가 없는 테이블의 복사본을 만들어야 하는 경우 대단히 유용하다.

$ mysqldump -u root -p secret –no-data stocksdb > stocksdb.sql

물론 테이블 구조는 제외하고 데이터만 백업하는 경우는 다음과 같은 구문을 이용하면 된다.

$ mysqldump -u root -p secret –no-create-info stocksdb > stocksdb.sql

텍스트 백업 파일로 MySQL 테이블 복구하기
지금까지 데이터베이스와 테이블 백업 방법을 살펴 봤다. 그러나 이것은 문제 해결의 절반에 불과하다. 나머지 절반은 실제 문제가발생했을 때 이를 복구하는 방법을 아는 것이다. 다행스럽게도 MySQL은 복구과정도 손쉽다. mysqldump 명령의 결과물은일련의 SQL 명령어로 구성돼 있기 때문에, MySQL 클라이언트가 간단하게 기존 데이터베이스 구조와 컨텐트를 재구축할 수 있다.

모든 데이터베이스를 mysqldump–all-databases 명령을 이용해 backup.sql 파일에 복사했다고 가정하면 다음과 같은 구문으로 이를 복구할 수 있다.

$ mysql -u root -p secret < backup.sql

–all-databases 옵션을 사용하지 않고 일부 테이블이나 데이터베이스를 선택적으로 백업한 경우는 MySQL 명령 실행시데이터베이스의 위치를 알려주는 -D 옵션을 추가한다. 예를 들어 stock2 데이터베이스의 stocksdb.sql 파일에 저장된테이블을 복구하는 구문이라면 다음과 같다.

$ mysql -u root -p secret -D stocks2 < stocksdb.sql

다른 포맷으로 내보내고 읽어들이기
MySQL 데이터베이스는 SQL 포맷 외에도 다양한 형식으로 저장할 수 있다. mysqldump 툴은 CSV나 사용자가 원하는구분문자(delimiter)를 이용해 다양한 포맷으로 레코드를 저장할 수 있는데, 이를 위해서는 mysqldump 명령어에–fields-terminated-by 인자만 추가하면 된다.

$ mysqldump -u root -p secret –no-create-info –tab=/tmp –fields-terminated-by=’,’ stocksdb

이와 같이 실행하면 mysqldump는 stocksdb 테이블에 있는 레코드의 각 필드를 쉼표로 분리해 /tmp 디렉토리에저장한다. 별도의 라인 터미네이터를 사용하려면 –lines-terminated-by 인자를 사용해 특정 구분문자로 라인을표시할 수 있다.

다른 데이터 포맷을 불러들이는 것도 가능하다. 예를 들어 다음과 같이 탭으로 분리한 테이블 레코드가 있다고 가정하자.

1 DHDU 2589 77.56
2 YHOO 3200 45.65
3 WMT 100 53.29

이 때 mysqlimport 툴을 이용하면 MySQL 데이터베이스 파일로 읽어들일 수 있다. 단 소스 파일의 베이스 이름이 데이터가 삽입될 위치를 결정한다는 점에 유의하자.

$ mysqlimport -u root -p secret –fields-terminated-by=’ ‘ test /tmp/portfolio.txt

백업을 할때 항상 두 카피 이상을 유지하도록 한다(하나는 다른 위치에). 또한 MySQL 매뉴얼 내용 가운데 다양한 데이터를 내보내고 불러들이는 방법을 참고해 여러분의 환경에 응용할 수 있는 가장 좋은 방법을 찾길 바란다.

무엇보다 필자는 여러분이 문제가 발생한 데이터베이스를 복구해야 하는 상황에 놓이지 않기를 진심으로 바란다. 그러나 효율적으로복구할 수 있는 툴이 있고, 이를 이용하면 최악의 경우에도 사용자의 불편을 최소화할 수 있다는 사실만으로도 여러분은 좀더 쉽게잠을 청할 수 있을 것이다. @

실수로 삭제한 mysql DB 복구방법

delete나 truncate 같은 명령으로 데이터를 지웠을때 복구하는 방법입니다.
mysql의 모든 명령들은 로그를 남기기때문에 가능합니다.
일단 mysql의 DB가 쌓이는 디렉토리로 이동합니다.
대게 /usr/local/mysql/data /usr/local/mysql/var 등으로 설정해 놓으셨을겁니다.
해당 디렉토리로 이동하면 xxx-bin.001 xxx-bin.002 같은 바이너리 파일들이 존재할겁니다.
이게 바로 DB를 첨 생성할때부터 지금까지 쌓여온 로그인데요 이 파일을 활용하시면 삭제된 데이터도 살릴수 있습니다.
바이너리 파일이기때문에 그냥 접근하시면 이상한 문자들이 보이구요
mysqlbinlog 명령을 이용하셔서 사용해야 합니다.
다음은 예제입니다.

일단 DB 디렉토리로 이동한다.
# cd /usr/local/mysql/data

바이너리 파일을 소스파일로 변환한다.
# ../mysqlbinlog leopit-bin.002 > rescue.sql

필요한 구문만 가져온다.
# grep “insert” rescue.sql > rescue1.sql

이렇게 하시면 rescue1.sql은 지금까지의 인서트구문이 다 들어옵니다.
여기서 필요없는 부분은 지우세요

이제 최종적으로 DB에 삽입만 하시면 됩니다.
# mysql -u 계정아이디 -p DB이름 < rescue1.sql

MySQL root 패스워드 분실했을 경우

1>암호 초기화
# killall mysqld (데몬을 죽입니다)

# cd /usr/local/mysql (mysql이 깔린 곳으로 갑니다)

# ./bin/safe_mysqld –skip-grant &

여기까지가 초기화

2>새 암호 넣기
# ./bin/mysql (sql 실행)

mysql>use mysql

mysql>update user set password=password(‘새암호’) where user=’root’;
(root 암호 변경합니다. ‘새암호’ 자리에 새암호를 넣으세요
예>update user set password=password(‘1234′) where user=’root’;

mysql> FLUSH PRIVILEGES
mysql> exit

# ./bin/mysql -uroot -p1234 으로 들어와지면 성공입니다.