'데이터베이스' 카테고리의 글 목록 (2 Page)

▶Mysql 리플리케이션(mysql 5.x 버전 기준!)

- korea IDC 시스템엔지니어팀 -

#MYsql 리플리케이션이란?
# Replication은 3.23.15부터 지원되기 시작한 기능으로 ‘복제’라는 사전적 의미에 맞게 마스터의 MySQL 서버의 데이터를 여러 대의 슬레이브 MySQL 서버의 데이터와 동기화 시켜주는 기능이다. 주로, MySQL의 데이터를 실시간으로 백업하거나, 데이터 서버의 부하분산을 하고자 할 때 많이 사용된다.

0. 서버 설정

1번서버 - master 서버 : 1차네임서버 : 192.168.1.111
2번서버 - slave 서버 : 2차네임서버 : 192.168.1.222

mysql 버전은 모두 mysql 5.X 이다.
두 서버 모두 mysql 데이터는 /free/mysql_data 에 위치해 있다고 가정한다.

1. Master 서버 설정

# vi /etc/my.cnf

(1) my.cnf 파일에서 #log-bin=mysql-bin 부분의 주석을 반드시 해제해준다
슬레이브 서버에서 저 바이너리 로그를 기준으로 데이터 리플리케이션을 실행 하기 때문에 저 로그파일이 꼭 필요하다!

(2) server-id = 1 로 설정한다.
마스터서버와 슬레이브서버의 번호가 서로 다르기만 하면 되기 때문에 원하는 번호를 지정한다.

(3) slave 서버 에서 접속 할 수 있는 Mysql 계정을 생성해준다
# mysql -u root -p 로 디비 접속
mysql > GRANT REPLICATION SLAVE ON *.* TO 유저명 @접속허용할IP IDENTIFIED BY '패스워드';

접속허용할 IP에 특정 IP만을 부여 할 수도 있고 '%' 를 적어주면 모든 외부의 접속을 허용한다는 의미이다.
mysql > use mysql
mysql > select * from user \G;
명령로 설정이 되어 있는지 확인 할 수 있다.

아래 셋팅은 실제 마스터 서버인 1차네임서버 서버의 설정 내용이다.

접속을 허용한 유저 이름은 : nayana
nayana의 접속을 허용한 IP : 192.168.1.222 (2차 네임서버)

슬레이브 서버(192.168.1.222) 에서 nayana 라는 계정의 Replication 접속을 허용한 것을 볼 수 있다.

(4) mysql을 재시작 하고 마스터 서버의 동작을 확인한다.
"mysql-bin.000022" 라는 바이너리 로그 파일을 생성한 것을 볼 수 있다.

2. Slave 서버 설정
(1) 마스터 서버의 DB를 슬레이브 서버의 DB에 복사한다!(= 최초1회는 직접 복사해서 동기화해준다!)
이유는?

mysql 리플리케이션은 rsync와 다르게 동기화로 엮여진 시점부터의 DB변화를 동기화 시킨다.

[ 동기화 전 DB 구조]
마스터               슬레이브
1                        1
2                        2
3

########## 동기화 후 마스터 서버에 4 라는 DB를 추가하면? #########

[ 동기화 후 DB 구조]
마스터               슬레이브
1                        1
2                        2
3                        4
4

이렇게 4 라는 DB만 추가되고 3이라는 DB는 추가되지 않는다. 애초에 처음부터 DB내용이 달랐기 때문!
rsync 였다면 양쪽에 모두 똑같아 졌을 것이다.

[ 마스터 서버 ]
# cd /free
# tar zcvf mysql.tar.gz mysql_data
# sz mysql.tar.gz

압축한 파일을 다운받아 슬레이브 서버의 /free/mysql_data 디렉토리에 플어준다. 이로서 최초 동기화를 완료했다.

(2) /etc/my.cnf 파일을 수정한다!
57 , 62 번 라인은 주석처리해주고 98번 라인부터는 아래와 같이 주석을 풀고 마스터 서버에 접속할 정보를 적는다.

(3) mysql 을 재시작 한후 mysql에 접속하여 동기화가 되었는지 확인한다.
동기화가 정상적으로 되었다면 아래와 같이 "Waiting for master to send event" 라는 구문이 출력된다!

이제 마스터 서버에서 DB를 생성하거나 삭제하면 슬레이브 서버에서도 똑같이 동기화가 될 것이다!! 올레~!

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

8기가 램에 맞는 Mysql config 셋팅 값 (0)	2023.11.14
MySQL 5.6.x Replication(MySQL 5.6 이중화) (0)	2023.11.14
MySQL Replication (마스터-슬레이브 동기화) 설정 (0)	2023.11.14
LVM을 이용한 MySQL 스냅샷 백업 (0)	2023.11.14
mysql root 패스워드 설정 및 패스워드 변경 방법 (1)	2023.11.14

MySQL Replication (마스터-슬레이브 동기화) 설정

MySQL 2014/04/08 17:08

서론
응용 프로그램 혹은 웹 페이지의 작은 기능 단위 연동을 위해서는, rsync로 db 파일을 동기화하는 경우가 있었다.
하지만 rsync 서비스도 정상인지 봐야 하고 스케줄링(crontab) 주기도 설정해야 하고 간혹 문제가 생기기도 하고 테이블 index가 꼬이거나 파일이 깨지는 등 번거로운 방법이다.

그래서 향후에는 작은 부분이더라도 Replication을 하기로 했다.
특정 DB 혹은 특정 Table만 동기화 설정이 가능하기 때문이다.

선행 작업

Master에서 Slave로 동기화하기를 원하는 DB(및 Table)은 미리 Dump를 떠서 최초 1번은 Slave에 생성해 놓아야 한다.

Master 서버 설정

우선 server-id가 1로 설정되어 있는지 확인한다.

Master는 보통 1로 설정한다. 그래야 Slave에서 부가적인 설정을 할 필요가 없다.

# cat /etc/my.cnf -n | grep server-id
67 server-id = 1

이번엔 mysql에 접속해서 상태를 확인한다.

File, Position 값을 각각 메모해 둔다(mysql-bin.189, 91120973).

마지막으로 본 서버에 접속해서 동기화 받아갈 Slave 서버들에 권한을 주자.

mysql> GRANT REPLICATION SLAVE ON *.* TO 유저@'%' IDENTIFIED BY '패스워드';
mysql> FLUSH PRIVILEGES;

*.*은 DB.Table 이고 %는 ip 모두 허용... 인데 GRANT는 익숙한 구문이므로 자세한 설명은 생략한다.

Slave 서버 설정

Master와 같이 server-id 값을 확인한다.

는 Master와 다른 값을 가져야 하므로 2로 수정하는게 좋겠다.

# cat /etc/my.cnf -n | grep server-id
67 server-id = 2

Slave의 mysql에 접속해서 동기화 받을 DB가 존재하는 Master 서버의 정보를 입력할 차례다.

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.xxx.xxx', MASTER_USER='유저',MASTER_PASSWORD='패스워드', MASTER_LOG_FILE='mysql-bin.189', MASTER_LOG_POS=91120973;
mysql> START SLAVE;

Master에서 메모한 2개의 값을 MASTER_LOG_FILE 부분과 MASTER_LOG_POS 부분에 대입한다.

Slave가 Master에게서 제대로 동기화 받아오는지 확인해 보자.

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.xxx.xxx
                                     ...(생략)...

Waiting for master to send event 문구가 표시되면 정상이다.

여기서 일단 끝인데... 조금 더 세부적으로...

모든 DB를 동기화하는 것이 아니라, 특정 DB 특정 Table만 동기화 하고 싶다면 아래와 같이 my.cnf 파일에 설정한다.

# vi /etc/my.cnf
[mysqld]
...(생략)...
replicate-do-table = DB.Table1
replicate-do-table = DB.Table2
replicate-do-db = DB1
replicate-do-db = DB2

Slave에 설정할 수도 있고 Master에 설정할 수도 있다.
한 라인에 여러 값을 기입할 수는 없었다. 여러 라인에 각각 지정해야 한다.
replication-ignore-table은 동기화 받지 않을 테이블만 기록하는 설정이다.
이 외에도 여러 설정이 존재한다(더 자세한건 구글링을).

참고 사항
1. 기본적인 것이지만 iptables 등의 방화벽 설정에서 서버 ip, port를 open해야 한다.
2. 역시 기본적인 것이지만 my.cnf 파일을 수정했다면 mysqld service를 재시작해야 한다.

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

MySQL 5.6.x Replication(MySQL 5.6 이중화) (0)	2023.11.14
Mysql 리플리케이션(mysql 5.x 버전 기준!) (0)	2023.11.14
LVM을 이용한 MySQL 스냅샷 백업 (0)	2023.11.14
mysql root 패스워드 설정 및 패스워드 변경 방법 (1)	2023.11.14
MySQL 외부 접근 허용하기 (0)	2023.11.14

DB: MySQL / DB人

2007/08/14 02:09

http://seuis398.blog.me/70020889375

전용뷰어 보기

LVM은 Linux 시스템에서 디스크 Volume을 논리적으로 관리할 수 있도록 해준다.

대략적인 구조는 아래와 같다.

LVM의 기능중에 Logical Volume 스냅샷 생성 기능을 활용하면,

InnoDB 스토리지 엔진의 경우 온라인 백업(Hot Backup)을 수행할 수 있고,

MyISAM의 경우에는 몇 초 정도의 TABLE LOCK으로 온라인 백업을 수행할 수 있다. (Warm Backup)

Logical Volume의 스냅샷을 생성하면, 즉시 Logical Volume 간 물리적인 복사가 이루어 지는 것은 아니고,

스냅샷 Volume 상에 Exception Table이라는 것이 생성되어 변경된 디스크 블럭의 정보를 보관하여,

그 정보를 바탕으로 스냅샷 생성 시점의 데이터를 읽을 수 있도록 해준다.

LVM을 이용한 MySQL 스냅샷 백업을 위해서는 아래와 같은 환경이 구성되어 있어야 한다.

- Linux 시스템에 LVM이 설치되어 있어야 한다. (LVM 1, 2 모두 가능)

- MySQL의 data 디렉토리는 LVM의 Logical Volume 상에 존재해야 한다.

data 디렉토리로 사용할 Logical Volume의 크기는,

DB의 사이즈 뿐만 아니라, 스냅샷을 생성을 위한 Volume의 크기까지 고려해서 결정해야 한다.

스냅샷 Volume의 크기는 원본 Volume의 50% 정도면 충분하다.

1. MyISAM 스토리지 엔진 사용시 테이블 락을 설정한다.

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.01 sec)

2. 스냅샷 Volume을 생성한다.

# lvcreate --snapshot -L 10G -n backup /dev/vg/mysqldata

lvcreate -- WARNING: the snapshot will be automatically disabled once it gets full

lvcreate -- INFO: using default snapshot chunk size of 64 KB for "/dev/vg/backup"

lvcreate -- doing automatic backup of "vg"

lvcreate -- logical volume "/dev/vg/backup" successfully created

현재 MySQL Data 디렉토리가 있는 Logical Volume(mysqldata)의 스냅샷을 생성하였다.

스냅샷 Volume의 이름은 backup으로 지정하였고, /dev/vg/backup 생성하였다.

(스냅샷 생성 시간은 1~2초 정도 걸렸다.)

스냅샷 생성 결과는 lvscan 명령으로 확인해 보면 된다.

# lvscan

lvscan -- ACTIVE Original "/dev/vg/mysqldata" [20 GB]

lvscan -- ACTIVE Snapshot "/dev/vg/backup" [9.96 GB] of /dev/vg/mysqldata

lvscan -- 2 logical volumes with 29.96 GB total in 1 volume group

lvscan -- 2 active logical volumes

3. 1번 과정에서 테이블 락을 설정하였다면, 락을 해제한다.

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

4. Snapshot Volume을 백업을 위한 새로운 MySQL 데몬의 data 디렉토리로 Mount 하고,

mysqldump 툴을 사용하여 백업을 받는다.

# mount -t ext3 /dev/vg/backup /usr/local/mysql_backup/data/

# /usr/local/mysql_backup/safe_mysqld &

# /usr/local/mysql_backup/bin/mysqldump --all-databases > backup.sql

MySQL DB의 스냅샷 백업이 진행되는 동안에도, 기존 MySQL 서버는 정상적으로 서비스 되므로

백업에 영향을 받지 않고, 정상적으로 서비스를 유지할 수 있다.

하지만 스냅샷 생성을 하면 해당 시점부터 원본 Volume과 스냅샷 Volume에 대해 중복으로 I/O가 발생하며,

변경 작업이 과도하게 많이 일어나거나, 외부 스토리지가 아닌 일반적인 SCSI, SATA 디스크를 쓰는 경우,

스냅샷 유지를 위해 증가한 I/O로 인하여 퍼포먼스 저하를 일으킬 수 있으니 유의하자.

6. 백업을 위한 임시 MySQL 데몬을 종료하고, 스냅샷 Volume을 삭제한다.

# /usr/local/mysql_backup/bin/mysqladmin shutdown

# umount /dev/vg/backup

# lvremove -f /var/vg/backup

lvremove -- doing automatic backup of volume group "vg"

lvremove -- logical volume "/dev/vg/backup" successfully removed

백업이 완료된 후에는 스냅샷 Volume을 반드시 삭제해야 한다.

<장점>

- 테이블 락으로 인한 서비스 중단없이 시점 백업을 받을 수 있다. (온라인 백업)

- 리플리케이션 Slave 구성을 간편하게 할 수 있다.

<단점>

- 스냅샷 생성 후 유지하는 동안 I/O가 늘어난다.

- LVM 자체에 대한 안정성 검증(LVM 버그 등)이 필요하다. (Volume이 날라가 버린다면 낭패..)

[출처] LVM을 이용한 MySQL 스냅샷 백업|작성자 돌고래사육사

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70020889375&beginTime=0&jumpingVid=&from=search&redirect=Log&widgetTypeCall=true&topReferer=http%3A%2F%2Fsearch.naver.com%2Fsearch.naver%3Fsm%3Dtab_hty.top%26where%3Dnexearch%26ie%3Dutf8%26query%3DLVM%2Bsnapshot>

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

Mysql 리플리케이션(mysql 5.x 버전 기준!) (0)	2023.11.14
MySQL Replication (마스터-슬레이브 동기화) 설정 (0)	2023.11.14
mysql root 패스워드 설정 및 패스워드 변경 방법 (1)	2023.11.14
MySQL 외부 접근 허용하기 (0)	2023.11.14
mysql 원격 접속하기 (0)	2023.11.14

1.mysql root 패스워드 설정 및 패스워드 변경 방법

드 설정 및 변경

초기 mysql root 패스워드 설정하기

mysql> use mysql;

mysql> update user set password=password('123456') where user='root';

mysql> flush privileges;

마지막 라인을 실행시켜야 적용이 된다..

설정 후 잘 되는지 테스트해보자

# mysql -u root -p 엔터

# Enter password: ******

※패스워드 변경하는 방법은 패스워드 설정하는 방법과 같다.

2.[mysql] 계정 생성/ DB권한부여 / 패스워드 변경/ 삭제

리눅스/데이터베이스 2010년 05월 25일 23시 43분

1. GRANT 명령어를 사용하여 계정생성 및 DB 권한 주기

mysql> GRANT ALL PRIVILEGES ON *.* to mk@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

// 모든 DB 접속권한 부여 및 root 권한 부여, mk라는 유저는 localhost 에서만 접속가능 (내부접속)

mysql> flush privileges; //권한로드

: 모든곳(외부)에서 접속가능하게 하려면 mk@localhost 부분을 mk@'%' 로 바꿔주면 됨

: mk계정이 특정한 곳에서만 접속 가능하게 하려면 mk@localhost 부분을 mk@'ip주소' 로 바꿔주면 됨

: mk계정이 특정 DB에 접속을 하며(보이며) 해당 DB에'만' root 권한(모든 명령어) 을 줄때는 *.* 부분을 DB명.* 로바꿔주면 됨

: mk계정에 특정 명령어(권한)만 사용하게 할때는 ALL PRIVILEGES 부분을 바꿔주면 됨

ex) GRANT select, insert, delete, update ~~.... (select, insert, delete, update 만 실행 할 수 있음)

* GRANT 명령어가 아닌 mysql DB의 user테이블과 db 테이블을 이용하는 방법도 있지만 번거로우므로 패스..

2. 계정 패스워드 변경

root 권한으로 mysql 접속

mysql> show databases; //모든 db 목록 출력

mysql> use mysql; //mysql 이라는 db를 사용

mysql> show tables; // mysql DB의 모든 table 목록을 출력

mysql> show columns from user; // user 테이블의 구조를 살펴본다. desc user 로 해도 됨

mysql> UPDATE user SET password = password('변경패스워드') WHERE user = '계정';

// 매치된 row 가 있는지, 변경된게 몇개인지에 대한 결과가 나온다. 모든 결과가 0이라면 제대로 적용되지 않은것. 특정 계정만 바꾸려고 했으니 당연히 1이라는 결과값이 나와야 한다.

/* Result Example

Query OK, 1 row affected (0.02 sec)

일치하는 Rows : 1개 변경됨 : 1개 경고 : 0개

*/

mysql> flush privileges;

// mysql DB 선택후 바로 UPDATE 명령어를 내리면 된다. 여타 명령어는 DB를 확인하며 찾아가는 과정임..

3. 계정 삭제

root 계정 접속후

mysql> use mysql;

mysql> drop user 계정명;

// 관련 DB 정보 삭제

3.Mysql 비밀번호 변경 방법

Mysql, 리눅스 Aug 31

2010.## 1. root 비밀번호를 분실했을 경우

1) 암호 초기화

# killall mysqld (데몬을 모조리 죽입니다)

# cd /usr/local/mysql (mySsql 설치된 폴더 이동)

※ mysql 데몬이 죽었기 때문에, 의외로 이거 모르시는 분이 많은데 sql 명령을 넣으려면 sql이 설치된 디렉토리로 가야합니다.

# ./bin/safe_mysqld –skip-grant &

여기까지 진행하면 root 비밀번호가 초기화됩니다. 즉 root 비밀번호가 삭제되는거죠.

2) 새 암호 넣기

2.1) MySQL 3.x ~ 4.01

# ./bin/mysql (mysql 실행)

mysql>use mysql <- mysql 데이타베이스 연결

mysql>update user set password=password(‘새암호’) where user=’root’;

Query OK, 2 rows affected (0.05 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILEGES; <- 끝에 ; 를 꼭 입력해야합니다.

Query OK, 0 rows affected (0.00 sec)

mysql> exit (끝내기)

2.2) MySQL 4.1 ~ MySQL 5.x

MySQL 4.1 이상 버전에서는 비밀번호를 해쉬알고리즘을 기반으로한 인증 프로토콜을 사용합니다.

그리고 해당 기능이 하위버전의 Client와 호환이 되지 않기에 서버를 4.1이상으로 업그레이드를 한 후에는 다음과 같은 명령어를 사용하여 인증이 가능토록 해야 합니다.

# ./bin/mysql (mysql 실행)

mysql>use mysql <- mysql 데이타베이스 연결

mysql> update user set password=OLD_PASSWORD(‘새암호’) WHERE user = ‘root’;

Query OK, 2 rows affected (0.05 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

## 2. 유저 로그인 경우 비밀번호 변경

$ mysql -u디비아이디 -p 디비이름

Enter password:

mysql>set password = password(‘새암호’); <== 새로운 비밀번호로 저장하기

<추가설명>set password = old_password(‘새암호’); <== 기존 비밀번호로 저장하기

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit <== MySQL 빠져나올 때

4.이번에 알려드릴꺼는 MySQL 5.X버전대의 악조건 바로 패스워드입니다.

아시는분은 아시겠지만 MySQL 4.X버전대에서 5.X버전대로 옴겨오면서

패스워드방식이 바뀐걸로 압니다. 그래서 5.X버전대에서 4.x버전대를 요구하는 경우도 종종생기게되죠.

오류문 : Client does not support authentication protocol requested by server; consider upgrading MySQL client

이 문제를 해결할 방법은 두가지 입니다. 하나는 사용하시는 root 패스워드를 올드패스워드로 바꿔주는것이고

하나는 MySQL 버전을 다운그레이드하는 방법이 있습니다.

여기서 저는 첫번째 방법을 설명하도록 하겠사옵니다~

오토셋 화면에서 파일 - MySQL 콘솔창 을 클릭 해줍니다.

하셧다면 mysql> 이런상태로 명령프롬프트창이 뜰탠데요. 그상태에서

mysql>SET PASSWORD FOR 엔터

->'root'@'localhost' = OLD_PASSWORD('사용하시던비밀번호'); 엔터

이렇게 해주시면 오류없이 잘됩니다. 그리고 마무리로 mysql>FLUSH PRIVILEGES; 엔터 입력해주는 센스

다음으로

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('사용하시던비밀번호') 엔터

-> WHERE Host = 'localhost' AND User = 'root'; 엔터

mysql> FLUSH PRIVILEGES; 엔터

위대로 실행합니다. ^^

마지막으로 exit 로 콘솔을 꺼주는 센수~

이제부터가 좀 힘들어지는데 ; ^^ 잘따라오세염 ~

MySQL을 꺼줍니다. (중요)

시작-실행-cmd 로 명령프롬프트를 실행하셔서

C:\AutoSet\Server\mysql\bin 폴더로 이동시켜줍니다.

----------방법1-----------

명령프롬프트를 켜시면 C:\Documents and Settings\Administrator> 이렇게 되어있으실탠데요.

C:\로 옴기시려면 C:\Documents and Settings\Administrator>cd .. 엔터

C:\Documents and Settings\>cd .. 엔터 이렇게 하시면

C:\> 이렇게 바뀝니다 . 이상태에서

C:\cd AutoSet\Server\mysql\bin 엔터 이렇게 해주시면 C:\AutoSet\Server\mysql\bin> 폴더로 잡히게 됩니다 ^^

-----------------------------

그리고 MySQL실행 옵션을 설정해서 MySQL를 실행시킵니다.

C:\AutoSet\Server\mysql\bin>mysqld-max-nt.exe --old-password 엔터

이렇게 하시면 키보드커서가

C:\AutoSet\Server\mysql\bin>mysqld-max-nt.exe --old-password

| 이렇게 깜빡이게 됩니다. 이렇게 하셧다면 성공! CMD창을 꺼주시구요.

다시 방법1대로 하셔서 C:\AutoSet\Server\mysql\bin> 이쪽폴더로 옵니다.

이제는 간단하게 mysql -uroot -p사용하시던비밀번호 엔터 하시면

아까 보셧던 콘솔창처럼 됩니다. (여기서오토셋에서콘솔창키면되지하시는데!안됩니다.저를따라오세염 ~)

mysql> SELECT localhost, root, 사용하시던패스워드 FROM mysql.user 엔터

-> WHERE LENGTH(사용하시던패스워드) > 16; 엔터

이렇게 하셔서 오류가 없으시다면 성공입니다 ^^

이제부터 root는 올드패스워드를 사용하게됩니다. 사용하시는데에 이상은 없을꺼같구요.

5.X버전대에서는 올드패스워드를 한개만 지원하는거 같더군요.

4.X버전대에서는 무한개 가능하던데 ㅠㅠ 아무튼 이글보시구 모두들 오류없으시길 ~

.

그림설명

위의 그림과 비교해 보면서 확인한다

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

MySQL Replication (마스터-슬레이브 동기화) 설정 (0)	2023.11.14
LVM을 이용한 MySQL 스냅샷 백업 (0)	2023.11.14
MySQL 외부 접근 허용하기 (0)	2023.11.14
mysql 원격 접속하기 (0)	2023.11.14
MySQL Replication을 이용하여 DBMS 단방향 이중화하기 (0)	2021.11.26

MySQL 외부 접근 허용하기

Share the post "MySQL 외부 접근 허용하기"

FacebookTwitterGoogle+E-mail

ODBC등 외부에서 MySQL에 접속을 하려면, MySQL 접속이 허용되어있어야 합니다.

1. 테이블 살펴보기

먼저, mysql에 들어가서 다음 방법을 이용해 접근 상태를 확인합니다.

mysql> use mysql;

mysql> select host, user, password from user;

출력된 정보를 확인해보면, localhost나 127.0.0.1만 등록이 되어있는 것을 확인 할 수 있는데, 외부에서 접근이 되게 하려면, 따로 등록을 해줘야 합니다.

2. 권한 설정 하기

설정을 할 때, 특정 IP나 특정 IP대역만 허용을 하거나, 전체를 허용되게 하는 방법이 있습니다.

1) 특정 IP 접근 허용 설정

mysql> grant all privileges on *.* to ‘root’@‘192.168.56.101’ identified by ‘root의 패스워드’;

2) 특정 IP 대역 접근 허용 설정

mysql> grant all privileges on *.* to ‘root’@‘192.168.%’ identified by ‘root의 패스워드’;

3) 모든 IP의 접근 허용 설정

mysql> grant all privileges on *.* to ‘root’@‘%’ identified by ‘root의 패스워드’

3. 등룩확인 후 적용시키기

등록이 끝났으면, 계정에 설정한 IP혹은 %가 등록 되어있는지 확인을합니다.

mysql> select host, user, password from user;

mysql> flush privileges;

4. my.cnf에서 외부 접속 관련 내용 변경하기

my.cnf 설정파일에서 bind-address라는 부분을 주석처리 해줍니다.

vi /etc/my.cnf

# bind-address = 127.0.0.1

5. mysql 재시작

주석처리가 끝났으면, mysql을 재시작 해줍니다.

/etc/init.d/mysqld restart

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

LVM을 이용한 MySQL 스냅샷 백업 (0)	2023.11.14
mysql root 패스워드 설정 및 패스워드 변경 방법 (1)	2023.11.14
mysql 원격 접속하기 (0)	2023.11.14
MySQL Replication을 이용하여 DBMS 단방향 이중화하기 (0)	2021.11.26
MySQL 서버 최적화 하기 (0)	2021.11.26

리눅스에 설치된 mysql 에 원격으로 접속하기 위해선 다른 설정이 필요합니다.

매번 리눅스에 들어가서 명령어를 입력하기가 힘들기 때문에 원격접속하기 윈한 방법을 설명합니다.

DB라는 db와, USER라는 db 계정이 생성되어 있는 상태에서 원격 접속 허용 설정을 하겠습니다.

우선 , mysql에 접속하여 user 테이블과 db 테이블에 값을 추가해줍니다.

( 이때 반드시 root 계정으로 접속 하여서 진행하셔야 합니다. )

mysql> insert into user (host,user,password) values('##.##.##.##','USER',password('pswd'));

Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> insert into db values('##.##.##.##','USER','DB','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y');

Query OK, 1 row affected (0.00 sec)

호스트 주소에 아래과 같이 특정 아이피 대신 % 를 입력하면 어느곳에서나 원격으로 mysql 접속이 가능하게 됩니다.

아래와 같이 사용할 경우, 사용이 편리 하긴 하겠지만, 보안상 취약 하기 때문에 권장 하지는 않습니다.

그러나 대규모 팀 프로젝트를 진행시에는 어쩔수 없겠죠?

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

로 mysql을 적용 시킨 후, 원격 접속을 진행해보겠습니다.

SELECT Host FROM mysql.user WHERE user='root';

INSERT INTO mysql.user (host,user,password) VALUES ('192.168.4.%','root',password('password'));

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.%';

FLUSH PRIVILEGES;

insert into user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('211.111.208.146','test',password('rhrorqhdks'),'','','');

GRANT ALL PRIVILEGES ON *.* TO 'root'@'211.111.208.146';

SELECT Host FROM mysql.user WHERE user='kddi';

INSERT INTO mysql.user (host,user,password) VALUES ('211.111.208.146','root',password('rhrorqhdks'));

5.5 버전용 유저 생성 및 mysql.ini

insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string) values('127.0.0.1','root', password('kddi123'),'','','','');

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';

skip-name-resolve 항목 리마크

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

mysql root 패스워드 설정 및 패스워드 변경 방법 (1)	2023.11.14
MySQL 외부 접근 허용하기 (0)	2023.11.14
MySQL Replication을 이용하여 DBMS 단방향 이중화하기 (0)	2021.11.26
MySQL 서버 최적화 하기 (0)	2021.11.26
MSSQL 미러링 환경에서 로그 축소 방법 (0)	2021.11.26

MySQL Replication을 이용하여 DBMS 단방향 이중화하기

웹서버 부하로 인해 L4를 이용하여 로드밸런싱으로 웹서버의 부하를 해결하였지만, DB 서버의 부하로 인하여 사이트가 느리게 열리는 현상이 발생하게 되었습니다

DB 서버를 이중화하는 방법은 없을까 하여 찾아보니 MySQL의 리플리케이션이라는 기능이 있더군요 이 기능을 이용하면 DB를 이중화 할 수 있는다는 것을 알게 되었습니다

이번 포스팅에서는 MySQL의 리플리케이션은 무엇이고, 리플리케이션을 이용한 DB를 이중화하는 방법을 알아보도록 하겠습니다.

MySQL Replication(복제)란?

리플리케이션(Replication)은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이며, 사용하기 위한 최소 구성은 Master / Slave 구성을 하여야 됩니다.

Master DBMS 역할 :

웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달하게 됩니다

(웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용됩니다)

Slave DBMS 역할 :

Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영하게 됩니다

(웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용됩니다)

MySQL Replication(복제) 사용목적

MySQL 리플리케션(Replication)은 사용목적은 크게 실시간 Data 백업과 여러대의 DB서버의 부하를 분산 시킬수 있습니다

1, 데이터의 백업

예로 Master 서버를 데이터의 원본서버, Slave서버를 백업서버로 지칭하겠습니다

먼저 Master 서버에 DBMS의 등록/수정/업데이터가 생기는 즉시 Slave 서버의 변경된 데이터를 전달하게 됩니다 이러한 과정으로 데이터의 백업을 할수 있으며, 또한 Master 서버의 장애가 생겼을 경우 Slave 서버로 변경하여 사용할수 있습니다.

그림으로 표현한다면 먼저 사용자가 사용하는데 발생하는 쿼리를 Master 서버에 요청하며, Master 서버의 발생된 쿼리를 Slave 서버로 전달하게되어 백업의 용도로 사용할수 있습니다

2. DBMS의 부화분산

사용자의 폭주로 인해 1대의 DB서버로 감당할수 없을때, MySQL 리플리케이션(Replication)을 이용하여 같은 DB 데이터를 여러대를 만들수 있기에 부하를 분산하수 있습니다

그림으로 표현한다면 Master 서버를 등록/수정/삭제를 사용하는 서버로 사용하고, Slave 서버를 데이터를 읽는용도로 사용하게 되면 DBMS의 부하를 분산하는 용도로 사용할 수 있게 됩니다

MySQL Replication 주의사항

MySQL Replication을 사용시 다음과 같은 주의하여야 될 사항들이 있습니다 반드시 필독 후 하시고 진행하시기 바랍니다.

1. 호환성을 위해 Replication을 사용하는 MySQL의 동일하게 맞추는것이 좋습니다

2. Replication을 사용하기에 MySQL 버전이 다른 경우 Slave 서버가 상위 버전 이여야 합니다

3. Replication을 가동시에 Master 서버, Slave 순으로 가동시켜야 합니다

MySQL Replication 구성하기

이번 리플리케이션 포스팅에서는 2대의 DBMS를 이용하여 등록/수정/삭제를 하는 Master 서버와 Select 를 사용하는 Slave 서버로 하겠으며, 구성은 다음과 같습니다

위 그림과 같이 웹서버에서 데이터 등록/수정/삭제는 Master 서버로 구성하고, 데이터를 읽을경우 Slave 서버로 구성하여 MySQL DBMS를 이중화 하도록 하겠습니다.

MySQL Replication 설정하기 - (Master 서버)

이제 MySQL Replication을 설정 해보도록 하겠으며, 먼저 Master 서버의 설정부터 하겠습니다

MySQL 리플리케이션을 사용하기 위해선 먼저 DB, 계정, 리플리케이션 계정을 생성하여 됩니다

구성정보는 아래와 같이 하겠습니다.

[Master 서버 DB, 계정정보]

IP : 192.168.65.148(Master), 192.168.65.149(Slave)

DadaBases : repl_db

ID : user1

PW : test123

[Replication 계정 정보]

IP : 192.168.65.148 - (Master)

ID : repl_user

PW : test456

- Master 서버에 데이터를 Slave 서버로 복제하기 위해선 MySQL 계정이 필요합니다

- MySQL root 계정으로 사용하는것은 보안상 좋지 않기 때문에 복제계정을 생성하는것이 좋습니다

1. MySQL DB, 계정생성 및 권한설정

1) DB 생성

1	mysql> create database repl_db default character set utf8;

2) 계정생성

1	mysql> create user user1@'%' identified by 'test123';

3) 권한부여

1	mysql> grant all privileges on repl_db.* to user1@'%' identified by 'test123';

2. 리플리케이션 계정생성

1	mysql> grant replication slave on . to 'repl_user'@'%' identified by 'test456';

3. MySQL 설정 - my.cnf

1
2
3
4

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

처음 설치시 위와 같은 설정이되어 있으며, 없다면 새로 추가하시면 됩니다

4. MySQL 재시작

1	# service mysqld restart

5. Master 서버 정보 확인

1
2
3
4
5
6
7

* File : MySQL 로그파일

* Position : 로그 파일내 읽을 위치

* Binlog_Do_DB : 바이너리(Binary)로그 파일(변경된 이벤트 정보가 쌓이는 파일)

* Binlog_Ignore_DB : 복제 제외 정보

MySQL Replication 설정하기 - (Slave 서버)

Slave 서버설하기에 앞서 먼저 계정을 생성하겠습니다

MySQL DB, 계정생성 및 권한설정

1) DB 생성

1	mysql> create database repl_db default character set utf8;

2) 계정생성

1	mysql> create user user1@'%' identified by 'test123';

3) 권한부여

1	mysql> grant all privileges on repl_db.* to user1@'%' identified by 'test123';

이제 Slave 서버 설정을 하는 방법 2가지 방법이 있습니다

첫번째는 mysql에 들어가서 설정하는 방법과 mysql 설정파일(my.cnf)에서 설정하는 방법이 있으며, 먼저 mysql에서 설정하는 부분부터 알아보도록 하겠습니다

1. MySQL에 접속하여 설정

1) MySQL 설정 - my.cnf

1
2
3
4
5

# vi /etc/my.cnf

[mysqld]
server-id=2
replicate-do-db='repl_db'

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

2) MySQL 복원

1) Master DBMS에서 복제할 데이터베이스를 dump하여 복원합니다

3) Master 서버로 연결하기 위한 설정

1
2
3
4
5
6

mysql> change master to
master_host='192.168.65.148',
master_user='repl_user',
master_password='test456',
master_log_file='mysql-bin.000010',
master_log_pos=1487;

MASTER_HOST : Mster 서버 IP 입력

MASTER_USER : 리플리케이션 ID

MASTER_PASSWORD : 리플리케이션 PW

MASTER_LOG_FILE : MASTER STATUS 로그파일명

MASTER_LOG_POS : MASTER STATUS에서 position 값

4) MySQL 재시작

1	# service mysqld restart

2. MySQL에 my.cnf에서 설정하기

1) MySQL 설정 - my.cnf

1
2
3
4
5
6
7

[mysqld]
replicate-do-db='repl_db'
master-host=192.168.65.148
master-user=repl_user
master-password=test456
master-port=3306
server-id=2

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

master-host : Master 서버의 IP를 입력

master-user : Master 서버에 생성한 리플리케이션(Replication) ID 입력

master-password : Master 서버에 생성한 리플리케이션(Replication) PW 입력

master-port : MySQL에서 사용하는 포트 입력

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

2) MySQL 재시작

1	# service mysqld restart

MySQL Replication 상태 확인하기

MySQL 리플리케이션(Replication)이 정상적으로 완료되었다면 이제 상태를 확인해야 되겠죠? 다음과 같이 확인하시면 됩니다.

1. Master 서버 상태보기

1) 쓰레드 상태보기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: repl_user
   Host: 192.168.65.149:38488
     db: NULL
Command: Binlog Dump
   Time: 2434
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

Master 서버에서 위 내용과 같이 명령어를 입력하면 Id:1 쓰레드의 Slave서버(192.168.65.149)의 repl_user계정으로 연결되어 있는 것을 확인하실수 있습니다

2. Slave 서버 상태보기

1) 쓰레드 상태보기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 4294967261
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 90
  State: Waiting for master to send event
   Info: NULL

쓰레드1(ld: 1)에서는 Master 서버와 통신하기 위한 쓰레드이며, 스레드2(ld: 2)는 업데이트된 내용을 처리하기 위한 SQL 쓰레드 입니다 이러한 2개의 쓰레드에서는 오류가 발생하면 안된다고 합니다.

2) 쓰레드 주요인자 상태보기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.65.148
                Master_User: repl_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000012
        Read_Master_Log_Pos: 434
             Relay_Log_File: slave-relay-bin.000042
              Relay_Log_Pos: 419
      Relay_Master_Log_File: mysql-bin.000012
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: repl_db,repl_db
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 434
            Relay_Log_Space: 419
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Slave_IO_State : Master서버의 연결을 시도하고 Master서버로 부터 이벤트를 기다리며, 재연결하는지에 대해 알려줍니다

Master_Host : 연결된 Master서버 호스트 입니다

Master_User : Master서버 연결하는데 사용되는 사용자 입니다

Master_Port : Master서버 연결하는데 사용되는 포트 입니다

Connect_Retry : --master-connect-retry 옵션의 현재 값 입니다

Master_Log_File : I/O 쓰레드에서 현재 읽고 있는 바이너리 로그파일 이름 입니다

Read_Master_Log_Pos : I/O 쓰레드에서 현재 Master 서버의 바이너리 로그에서 읽은 곳가지의 위치 입니다

Relay_Log_File : SQL 쓰레드에서 현재 relay 로그파일 이름 입니다

Relay_Log_Pos : SQL 쓰레드에 의해 Relay 로그에서 읽고 실행한 곳까지의 위치 입니다

Relay_Master_Log_File : SQL 스레드에 의해 실행된 최근 Master서버의 바이너리 로그 파일의 이름입니다

Slave_IO_Running : I/O 쓰레드가 시작되어 Master서버의 성공적으로 연결되어있는지 여부 여부 입니다

Slave_SQL_Running : SQL 쓰레드가 시작되었는지의 여부 입니다

Replicate_Do_DB : Master서버에서 업데이트된 데이터를 반영될 DB 입니다

Replicate_Ignore_DB : 생략.

Replicate_Do_Table : 생략.

Replicate_Ignore_Table : 생략.

Replicate_Wild_Do_Table : 생략.

Replicate_Wild_Ignore_Table : 생략.

Last_Errno : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

Last_Error : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

Skip_Counter : 생략.

Exec_Master_Log_Pos : Master서버의 바이너리 로그의 Relay_Master_Log_File로 부터 SQL쓰레드의 의해 마지막 이벤트의 위치 입니다

Relay_Log_Space : 존재하는 모든 Relay 로구우ㅏ 전체 사이즈 입니다

Until_Condition : 생략.

Until_Log_File : 생략.

Until_Log_Pos : 생략.

Master_SSL_Allowed : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Master_SSL_CA_File : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Master_SSL_CA_Path : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Master_SSL_Cert : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Master_SSL_Cipher : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Master_SSL_Key : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

Seconds_Behind_Master : Master서버에서 실행된 이벤트의 타임스탬프 이후 경과된 시간(초 단위)의 수 입니다

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.65.148
                Master_User: repl_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000010
        Read_Master_Log_Pos: 98
             Relay_Log_File: slave-relay-bin.000010
              Relay_Log_Pos: 370
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: repl_db,repl_db,repl_db,repl_db
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1007
                 Last_Error: Error 'Can't create database 'repl_db'; database exists' on query. Default database: 'repl_db'. Query: 'create database repl_db default character set utf8'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 233
            Relay_Log_Space: 1123
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

간혹 위와 같이 에러가 발생하면서 Slave 서버가 작동이 잘안되는 경우가 있습니다

위와 같은 경우는 Slave 서버에 에러가 발생하면 에러가 발생하였던 시점으로 부터Master 서버로 부터 갱신된 쿼리를 실행하지 않게되며, 이경우 에러를 넘겨야 다음 쿼리를 실행하게 됩니다

1
2
3

vi /etc/my.cnf
[mysqld]
slave-skip-errors=all

위와 같은 옵션을 잠시 설정하여 반영하시기 바랍니다. my.cnf에서 설정을 반영하신 후에는 MySQL을 재시작하시기 바랍니다

출처: <http://server-talk.tistory.com/240>

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

MySQL 외부 접근 허용하기 (0)	2023.11.14
mysql 원격 접속하기 (0)	2023.11.14
MySQL 서버 최적화 하기 (0)	2021.11.26
MSSQL 미러링 환경에서 로그 축소 방법 (0)	2021.11.26
MSSQL CPU를 많이 차지하는 쿼리 조회 (0)	2021.11.26

MySQL 서버 최적화 하기

시스템 요소 및 스타트 업 파라미터 튜닝

우리는 시스템-레벨의 요소를 가지고 시작을 할 것인데, 그 이유는 이런 요소들이 초창기부터 시스템 성능을 개선 시키기 위해서 사용되었기 때문이다.

OS를 잘 다루는 것이 매우 중요하다. 다중 CPU 시스템을 잘 다루기 위해서는, 솔라리스 (왜냐하면 이 OS는 쓰레드를 잘 처리한다) 또는 리눅스 (그 이유는 2.4 및 이후 버전의 커널은 SMP를 잘 지원한다)를 잘 다루는 것이 좋다. 구형 커널 버전의 리눅스는 파일 크기를 2GB만큼 디폴트로 사용한다는 점을 알아두자. 만일 여러분이 이러한 커널을 가지고 있고 2GB 보다 큰 파일을 필요로 한다면, ext2 파일 시스템에 대한 LFS (Large File System)을 가지고 있어야 한다.

상용 MySQL을 사용하기 전에, 우리는 여러분이 이것을 먼저 테스트 해 보기를 권장한다.

사용할 수 있는 다른 팁으로는 다음과 같은 것이 있다:

만일 여러분이 충분한 RAM을 가지고 있다면, 모든 스왑 (swap) 디바이스 (device)를 삭제할 수가 있다. 어떤 OS는 사용 가능한 메모리를 가지고 있다고 하더라도 문장 안에서 스왑 디바이스를 사용하기도 한다.
외부 잠금을 사용하지 말 것. MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하도록 디폴트로 만들어졌다. --external-locking 및 --skip-external-locking 옵션은 외부 잠금을 명확하게 활성화 및 비활성화 시킨다.

외부 잠금을 비 활성화 시키는 것은 MySQL을 하나의 서버에서만 구동 시키기만 하면 MySQL의 기능성에는 아무런 영향을 주지 않는다는 점을 알아두자. myisamchk를 구동 시키기 전에는 서버를 다운 (down)시켜야 한다는 점 (또는 관련된 테이블을 잠그거나 플러시)을 반드시 기억하기 바란다.

외부 잠금을 비 활성화 시킬 수 없는 유일한 경우는, 여러 대의 MySQL 서버 (클라이언트가 아님)를 하나의 데이터에 구동시킬 때, 또는 서버가 테이블을 우선 플러시한 다음에 잠그도록 명령하지 않은 채로 테이블 검사용 myisamchk를 구동 시킬 경우에만 해당된다. 하나의 데이터에 동시에 여러 대의 MySQL 서버가 접속을 하도록 하는 것은 일반적으로 권장하지 않는 다는 것을 알아두기 바란다. (MySQL클러스터는 예외임)

LOCK TABLES 및 UNLOCK TABLES 명령문은 내부 잠금을 사용하기 때문에 외부 잠금이 비 활성화 되어 있는 경우에는 이것을 사용할 수가 있다.

서버 파라미터 튜닝

여러분은 아래의 명령어를 사용해서 mysqld 서버가 디폴트로 사용하는 디폴트 버퍼의 크기를 알아볼 수가 있다:

shell> mysqld --verbose --help

이 명령어는 모든 mysqld 옵션 리스트와 구성 가능한 시스템 변수 리스트를 보여 준다. 이 결과에는 디폴트 변수 값이 포함되어 있고 아래와 같이 보이게 된다:

back_log                          50
binlog_cache_size                 32768
bulk_insert_buffer_size           8388608
connect_timeout                   5
date_format                       (No default value)
datetime_format                   (No default value)
default_week_format               0
delayed_insert_limit              100
delayed_insert_timeout            300
delayed_queue_size                1000
expire_logs_days                  0
flush_time                        1800
ft_max_word_len                   84
ft_min_word_len                   4
ft_query_expansion_limit          20
ft_stopword_file                  (No default value)
group_concat_max_len              1024
innodb_additional_mem_pool_size   1048576
innodb_autoextend_increment       8
innodb_buffer_pool_awe_mem_mb     0
innodb_buffer_pool_size           8388608
innodb_concurrency_tickets        500
innodb_file_io_threads            4
innodb_force_recovery             0
innodb_lock_wait_timeout          50
innodb_log_buffer_size            1048576
innodb_log_file_size              5242880
innodb_log_files_in_group         2
innodb_mirrored_log_groups        1
innodb_open_files                 300
innodb_sync_spin_loops            20
innodb_thread_concurrency         8
innodb_thread_sleep_delay         10000
interactive_timeout               28800
join_buffer_size                  131072
key_buffer_size                   8388600
key_cache_age_threshold           300
key_cache_block_size              1024
key_cache_division_limit          100
long_query_time                   10
lower_case_table_names            1
max_allowed_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connect_errors                10
max_connections                   100
max_delayed_threads               20
max_error_count                   64
max_heap_table_size               16777216
max_join_size                     4294967295
max_length_for_sort_data          1024
max_relay_log_size                0
max_seeks_for_key                 4294967295
max_sort_length                   1024
max_tmp_tables                    32
max_user_connections              0
max_write_lock_count              4294967295
multi_range_count                 256
myisam_block_size                 1024
myisam_data_pointer_size          6
myisam_max_extra_sort_file_size   2147483648
myisam_max_sort_file_size         2147483647
myisam_repair_threads             1
myisam_sort_buffer_size           8388608
net_buffer_length                 16384
net_read_timeout                  30
net_retry_count                   10
net_write_timeout                 60
open_files_limit                  0
optimizer_prune_level             1
optimizer_search_depth            62
preload_buffer_size               32768
query_alloc_block_size            8192
query_cache_limit                 1048576
query_cache_min_res_unit          4096
query_cache_size                  0
query_cache_type                  1
query_cache_wlock_invalidate      FALSE
query_prealloc_size               8192
range_alloc_block_size            2048
read_buffer_size                  131072
read_only                         FALSE
read_rnd_buffer_size              262144
div_precision_increment           4
record_buffer                     131072
relay_log_purge                   TRUE
relay_log_space_limit             0
slave_compressed_protocol         FALSE
slave_net_timeout                 3600
slave_transaction_retries         10
slow_launch_time                  2
sort_buffer_size                  2097144
sync-binlog                       0
sync-frm                          TRUE
sync-replication                  0
sync-replication-slave-id         0
sync-replication-timeout          10
table_cache                       64
thread_cache_size                 0
thread_concurrency                10
thread_stack                      196608
time_format                       (No default value)
tmp_table_size                    33554432
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
wait_timeout                      28800

현재 구동 중에 있는 mysqld 서버의 경우, 여기에 접속을 해서 아래의 명령문을 입력하면 이 서버의 현재 시스템 변수 값을 알아 볼 수가 있다:

mysql> SHOW VARIABLES;

또한 아래의 명령어를 사용하면 구동 중에 있는 서버의 몇몇 통계치와 상태 인디케이터 (indicator)를 확인할 수가 있다:

mysql> SHOW STATUS;

시스템 변수 및 상태 정보는 mysqladmin를 사용해서 얻을 수도 있다:

shell> mysqladmin variables
shell> mysqladmin extended-status

모든 시스템 변수 및 상태 변수에 대한 전체 설명은, Section 5.2.2, “서버 시스템 변수”, 및 Section 5.2.4, “서버 상태 변수”를 참조하기 바란다.

MySQL은 확장이 가능한 알고리즘을 사용하기 때문에 소량의 메모리만을 사용해서도 구동 시킬 수가 있다. 하지만, 일반적으로 보다 많은 메모리를 사용하면 보다 좋은 성능을 얻어낼 수가 있다.

MySQL 서버를 튜닝할 때에는 key_buffer_size 와 table_cache 값을 정확히 구성하는 것이 매우 중요하다. 여러분은 다른 변수를 변경하기 전에 이 변수들을 올바르게 설정했는지에 대해 확인을 해야 한다.

이래의 예제는 서로 다른 런 타임 구성을 위한 전형적인 변수 값을 나타내는 것이다:

최소 256MB의 메모리와 많은 테이블을 가지고 있고 중간 정도 성능의 클라이언트와 함께 최고의 성능이 나오도록 하고 싶다면, 아래와 같은 것을 사용해야 한다:
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &
만일 여러분이 128MB의 메모리와 적은 수의 테이블만을 가지고 있지만, 정렬할 것이 많이 있다면, 아래와 같이 사용한다:
shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

만일 동시 접속 수가 많이 존재한다면, 각각의 접속에 대해서 mysqld 이 적은 량의 메모리만을 사용하도록 구성되지 않는 한 스왑핑 (swapping) 문제가 발생될 수도 있다. mysqld는 모든 접속에 대해서 충분한 메모리가 있는 경우에 성능이 좋게 된다.
메모리는 작고 접속은 많은 경우에는, 아래와 같이 한다:
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
           --read_buffer_size=100K &
또는:

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
           --table_cache=32 --read_buffer_size=8K \
           --net_buffer_length=1K &

만일 사용할 수 있는 메모리보다 큰 테이블에서 GROUP BY 또는 ORDER BY 연산을 실행한다면, 열을 읽은 후 정렬 연산 속도를 증가 시키기 위해서는 read_rnd_buffer_size 값을 늘려 주어야 한다.

MySQL을 설치하면, support-files 디렉토리는 my.cnf 샘플 파일을 가지게 된다: my-huge.cnf, my-large.cnf, my-medium.cnf, 및 my-small.cnf. 여러분은 이것을 시스템 최적화용으로 사용할 수 있을 것이다. (윈도우의 경우, MySQL 설치 디렉토리를 찾아 보기 바란다.)

여러분이 mysqld 또는 mysqld_safe용 옵션을 명령어 라인에서 지정하였다면, 이것은 서버 호출에 대해서만 효력을 나타낸다. 서버가 구동될 때마다 사용될 수 있도록 하기 위해서는 옵션 파일에서 지정해야 한다.

파라미터 변경 효과를 보기 위해서는 아래와 같이 입력한다:

shell> mysqld --key_buffer_size=32M --verbose --help

변수 값은 결과 끝 부분에 나타난다. --verbose 및 --help 옵션은 마지막에 오도록 한다. 그렇지 않으면, 명령어 라인 상에서 이것 뒤에 나오는 옵션들이 결과에 영향을 주지 못하게 된다.

쿼리 옵티마이저 성능 제어하기

쿼리 옵티마이저의 업무는 SQL쿼리를 실행하기 위한 옵티말 플랜 (optimal plan)을 찾는 것이다. MySQL 5.0.1에서는 보다 유연한 쿼리 최적화 방식을 제공하고 있다.

여러 가지 플랜에 관련해서 옵티마이저가 실행하는 방식은 두 가지 시스템 변수를 통해서 제어된다:

optimizer_prune_level 변수는 각 테이블에 접속 한 열의 숫자를 추정해서 옵티마이저로 하여금 몇몇 플랜들은 건너 띄도록 만든다. 우리의 경험상 이러한 종류의 “경험적 추정 (educated guess)”은 옵티말 플랜을 거의 빠트리지 않으며, 놀라울 정도로 쿼리 컴파일 시간을 절약 시켜 준다. 이런 까닭으로 이 옵션이 디폴트 (optimizer_prune_level=1)가 되는 것이다. 하지만, 만일 옵티마이저가 보다 우수한 쿼리 플랜을 빠트렸다고 생각을 한다면, 이 옵션을 (optimizer_prune_level=0)으로 설정해 놓을 수도 있지만 이렇게 하면 보다 오랜 쿼리 처리 시간이 소요된다.
optimizer_search_depth 변수는 옵티마이저가 각각의 불완전한 플랜을 나중에 확장할 수 있는지 조사하도록 만든다. optimizer_search_depth 값이 작을수록 쿼리 컴파일 시간이 작게 된다. 예를 들면, 12,13, 또는 그 이상의 테이블을 가지고 있는 쿼리는, optimizer_search_depth가 쿼리 안에 있는 테이블 숫자와 비슷해지는 경우에는 몇 시간 또는 하루 정도가 걸리게 된다. 동시에, 만일 optimizer_search_depth와 함께 컴파일 되는 테이블이 3개 또는 4개 정도라면, 옵티마이저는 동일 쿼리에 대해서는 몇 분 밖에 걸리지 않을 것이다. 만일 여러분이 optimizer_search_depth에 가장 적당한 값이 어떤 것인지를 확신할 수 없다면, 이 값을 0으로 설정해서 옵티마이저로 하여금 이 값을 자동으로 판단하도록 만들면 된다.

컴파일 및 링크 작업이 어떻게 MySQL 속도에 영향을 주는가

아래에 있는 대부분의 테스트들은 리눅스에서 MySQL 벤치 마크를 사용해서 실행한 것들이긴 하지만, 여러분은 이를 통해서 다른 OS에 대한 지침을 얻을 수가 있을 것이다.

-static에 링크를 하면 가장 빠른 실행 속도를 얻을 수가 있을 것이다.

리눅스에서는, 서버를 pgcc 및 -O3와 함께 컴파일 하는 것이 가장 좋은 방법이다. 이러한 옵션을 가지고 sql_yacc.cc를 컴파일 하기 위해서는 약 200MB의 메모리가 필요한데, 그 이유는 gcc 또는 pgcc가 모든 함수를 처리하기 위해서 많은 메모리를 필요로 하기 때문이다. libstdc++ 라이브러리를 포함하지 않도록 MySQL을 구성할 경우에는 CXX=gcc로 설정하도록 한다.

보다 좋은 컴파일러와 컴파일 옵션을 사용하면 어플리케이션 처리에서 10~30%의 속도 증가를 얻어낼 수가 있다. 이것은 특히 여러분이 스스로 MySQL을 컴파일할 때에는 특히 중요하다.

표준 MySQL 바이너리 배포판은 모든 문자 셋을 지원하도록 컴파일 되어 있다. 여러분이 스스로 MySQL을 설치할 때에는 여러분이 사용하고자 하는 문자 셋만을 지원하도록 해주어야 한다. 이것은 --with-charset 옵션을 configure에 지정해 줌으로서 가능하다.

아래의 리스트는 우리가 이미 만들어 놓은 몇 가지 측정 값들이다:

여러분이 pgcc를 사용하고 모든 것을 -O6로 컴파일 한다면, mysqld 서버는 gcc 2.95.2 보다 1% 정도 속도가 개선된다.
여러분이 동적으로 (-static 없이) 링크를 한다면, 리눅스에서는 13% 정도 속도가 느려지게 된다. 여러분은 여전히 클라이언트 어플리케이션에 대해서는 MySQL 라이브러리를 동적으로 링크 시킬 수가 있다는 점을 알아 두기 바란다. 성능에 대해서는 서버 쪽이 보다 심각해지는 것이다.
동일한 호스트에 있는 서버로 접속을 하는 클라이언트의 경우, 만일 여러분이 유닉스 소켓 파일을 사용하지 않고 TCP/IP를 사용하는 경우라면, 7.5% 정도의 속도 저하가 생기게 된다. (유닉스의 경우, 만일 여러분이 호스트 이름 localhost에 접속을 한다면, MySQL은 디폴트로 소켓 파일을 사용한다.)
클라이언트에서 서버로의 접속을 TCP/IP로 하는 경우에는, 다른 호스트에 있는 원격 서버와의 접속이 동일 호스트에 있는 서버와의 접속보다 약 8-11% 정도 느려지게 된다.
보안 접속을 사용해서 벤치마크 테스트를 구동 시키면 (모든 데이터를 내부 SSL을 가지고 암호화 함) 성능은 암호화를 하지 않은 접속에 비해 55% 정도 느려지게 된다.
여러분이 --with-debug=full를 사용해서 컴파일을 하는 경우에는, 대부분의 쿼리가 20% 정도 느려지게 된다. 어떤 쿼리들은 심각할 정도로 오래 걸리기도 한다; 예를 들면, MySQL 벤치 마크는 35% 정도 느려지게 된다. 만일 여러분이 --with-debug (=full 없이)를 사용한다면, 속도는 15% 정도 느려진다. --with-debug=full을 사용해서 컴파일 된 mysqld의 경우, 여러분은 서버가 시작될 때 --skip-safemalloc 옵션을 사용해서 런 타임시의 메모리 검사를 비 활성화 시킬 수가 있다. 이렇게 하면 --with-debug를 사용해서 구성했을 때 얻어지는 수준만큼의 실행 속도를 얻을 수가 있게 된다.

MySQL의 메모리 사용 방법

아래의 리스트는 mysqld 서버가 메모리를 사용하는 방법을 설명하는 것이다:

키 버퍼 (변수 key_buffer_size)는 모든 쓰레드가 공유한다; 서버가 사용하는 다른 버퍼들은 필요할 경우에 할당된다.
각각의 접속은 쓰레드-관련 공간을 사용한다:
스택 (디폴트 192KB, 변수 thread_stack)
연결 버퍼 (변수 net_buffer_length)
결과 버퍼 (변수 net_buffer_length)
연결 버퍼와 결과 버퍼는 필요할 경우에 max_allowed_packet까지 동적으로 커진다. 쿼리가 동작을 하는 동안에는 현재 쿼리 스트링의 복사본도 역시 할당된다.
모든 쓰레드는 동일 베이스 메모리를 공유한다.
쓰레드를 더 이상 사용하지 않으면, 그 쓰레드가 다시 쓰레드 캐시로 들어 가지 않는 한 여기에 할당된 메모리는 시스템에 돌려지게 된다.
압축된 MyISAM 테이블만이 메모리에 매핑이 된다. 그 이유는 4GB의 32-비트 메모리 공간은 대부분의 커다란 테이블용으로는 부족하기 때문이다. 64-비트 주소 공간을 가지는 시스템이 보다 보편화 되면, 우리는 이 점을 보완할 예정이다.
테이블을 순차적으로 스캔하는 각각의 요청은 읽기 버퍼 (read buffer) (변수 read_buffer_size)를 할당한다.
임의적인 순서로 열을 읽는 경우에는 (예를 들면, 정렬 순으로), 디스크 검색을 피하기 위해서 랜덤 읽기 버퍼 (random-read buffer ) (변수 read_rnd_buffer_size)를 할당할 수도 있다.
모든 조인 (join)은 단일 패스 내에서 실행되며, 대부분의 조인 (join)은 임시 테이블을 사용하지 않고서도 실행할 수 있다. 대부분의 임시 테이블은 메모리-기반 해시 (hash) 테이블이다. 길이가 긴 열을 가지고 있는 임시 테이블 (모든 컬럼 길이의 합으로 계산된) 또는 BLOB 컬럼을 가지고 있는 테이블은 디스크에 저장된다.
만일 내부 힙 (heap) 테이블의 크기가 tmp_table_size를 초과한다면, MySQL은 필요하다면 메모리 내에 있는 힙 테이블을 디스크 기반 MyISAM 테이블로 변경시켜서 자동으로 이것을 처리한다. 여러분은 tmp_table_size 옵션을 mysqld에 설정하거나 또는 클라이언트 프로그램에 있는 SQL 옵션 SQL_BIG_TABLES를 설정함으로써 임시 테이블의 크기를 증가 시킬 수가 있다.
대부분의 정렬 요청은 정렬 버퍼를 할당하며 결과 셋 크기에 따라서 두 개의 임시 파일을 0으로 만든다.
거의 모든 파싱 (parsing)과 계산 (calculating)은 로컬 메모리에 저장된다. 작은 아이템의 경우에는 메모리 오버 헤드가 거의 발생하지 않기 때문에 일반적인 슬로우 메모리 할당 및 해제 (freeing)은 피할 수가 있다. 메모리는 예상치 못한 대형 스트링에 대해서만 할당된다. 이것은 malloc() 과 free()를 가지고 실행된다.
열려 있는 각각의 MyISAM 테이블에 대해서는 인덱스 파일이 일단 열리게 된다; 데이터 파일은 현재 구동 중에 있는 각각의 쓰레드에 대해서 한번 열리게 된다. 각각의 현재 쓰레드에 대해서는, 테이블 구조, 각 컬럼에 대한 컬럼 구조, 그리고 3 × N 크기의 버퍼가 할당된다 (여기에서 N 은 최대 열 길이이며, BLOB 컬럼을 계산하지는 않는다). BLOB 컬럼은 BLOB 데이터 길이보다 5에서8 바이트를 더 필요로 한다. MyISAM 스토리지 엔진은 내부 용도로 하나의 열 버퍼를 추가로 관리한다.
BLOB 컬럼을 가지고 있는 각 테이블에 대해서는, 버퍼가 BLOB에 들어 있는 큰 값을 읽을 수 있도록 동적으로 커진다. 만일 여러분이 테이블을 스캔한다면, BLOB 값 만큼 커다란 버퍼가 할당된다.
사용 중에 있는 테이블에 대한 핸들러 구조 (Handler structure)는 캐시 안에 저장되며 FIFO 방식으로 다루어진다. 캐시는 디폴트로64개의 엔트리를 가진다. 동시에 구동되는 두 개의 쓰레드가 하나의 테이블을 사용한다면, 캐시는 그 테이블용으로 두 개의 엔트리를 가지게 된다.
FLUSH TABLES 명령문 또는 mysqladmin flush-tables 명령어는 한번도 사용되지 않은 테이블을 닫아 버리며 사용 중에 있는 모든 테이블은 현재 실행 중에 있는 쓰레드가 종료가 되면 닫히게끔 표시를 해 둔다. 이러한 방식이 사용 중에 있는 대부분의 메모리를 효과적으로 해제 시키는 방법이다. FLUSH TABLES은 모든 테이블이 닫히기 전까지는 리턴되지 않는다.

ps 및 다른 시스템 상태 프로그램은 mysqld가 많은 메모리를 사용한다고 보고할 수도 있다. 이것은 서로 다른 메모리 주소 상에 있는 쓰레드 스택에 의해 발생될 수도 있다. 예를 들면, Solaris 버전의 ps는 메모리를 사용하고 있는 스택 사이에서 사용되지 않는 메모리를 계산한다. swap -s를 사용해서 사용 가능한 스왑을 검사 해보면 이것을 알아볼 수가 있게 된다.

MySQL의 DNS사용 방법

새로운 클라이언트가 mysqld에 접속을 하게 되면, mysqld는 그 요청을 처리하기 위해 새로운 쓰레드를 만든다. 이 쓰레드는 우선 호스트 이름 캐시에 있는 호스트 이름을 검사한다. 만약에 여기에 없다면, 쓰레드는 호스트 이름을 알아내기 위한 시도를 한다:

만일 OS가 gethostbyaddr_r() 와 gethostbyname_r() 호출을 지원한다면, 쓰레드는 이것들을 사용해서 호스트 이름을 알아낸다.
만일 OS가 위와 같은 호출 (thread-safe)을 지원하지 않는다면, 쓰레드는 뮤텍스 (mutex)를 잠그고 gethostbyaddr() 및 gethostbyname()를 대신 호출한다. 이와 같은 경우가 되면, 다른 첫 번째 쓰레드가 뮤텍스 잠금을 해제하기 전까지 어떠한 쓰레드도 호스트 이름 캐시에 들어 있지 않는 호스트 이름을 알아낼 수가 없게 된다.

여러분은 --skip-name-resolve 옵션을 사용해서 mysqld를 시작함으로써 DNS 호스트 이름 룩업 (lookup)을 비 활성화 시킬 수가 있다. 하지만, 이렇게 되면 여러분은 MySQL 그랜트 테이블에 있는 IP번호만을 사용할 수가 있게 된다.

만일 여러분이 매우 느린 DNS와 많은 수의 호스트를 가지고 있다면, --skip-name-resolve를 사용해서 DNS를 비 활성화 시키거나 또는 HOST_CACHE_SIZE 값 (디폴트 값: 128)을 늘린 다음에 mysqld를 재 컴파일 해주면 보다 좋은 성능을 얻을 수가 있게 된다.

--skip-host-cache 옵션을 사용해서 서버를 시작하면 호스트 이름 캐시를 비 활성화 시킬 수가 있다. 호스트 이름 캐시를 초기화 (clean) 시키기 위해서는, FLUSH HOSTS 명령문 또는 mysqladmin flush-hosts 명령어를 실행하면 된다.

TCP/IP 접속을 전체적으로 허용하지 않기 위해서는, mysqld를 --skip-networking 옵션을 사용해서 시작한다.

출처 : MySQL 코리아

출처: <http://www.dbguide.net/db.db?cmd=view&boardUid=146599&boardConfigUid=9&boardIdx=129&boardStep=1>

저작자표시 (새창열림)

'데이터베이스' 카테고리의 다른 글

mysql 원격 접속하기 (0)	2023.11.14
MySQL Replication을 이용하여 DBMS 단방향 이중화하기 (0)	2021.11.26
MSSQL 미러링 환경에서 로그 축소 방법 (0)	2021.11.26
MSSQL CPU를 많이 차지하는 쿼리 조회 (0)	2021.11.26
SP_LOCK (0)	2021.11.26

노땅의 IT 세계