시스템 엔지니어

1. partition table이 가능한지 확인 

 * mysql 5.1 이상 버전부터 사용가능

 mysql> show variables like '%partition%';

2. 파티션 분할 방법

 mysql> alter table [테이블 명] PARTITION BY RANGE ([파티션을 나눌 기준이 되는 index 값])

 (

 PARTITION [파티션 이름_1] VALUES LESS THAN ([index의 특정 값]),

 PARTITION [파티션 이름_2] VALUES LESS THAN MAXVALUE

 );

 ex) 

 mysql> alter table chk_history PARTITION BY RANGE (uidx)

 (

 PARTITION p202008 VALUES LESS THAN (6867072),

 PARTITION pmax VAUES LESS THEN MAXVALUE

 );

 → uid 값이 6867072 보다 적은 값은 p202008 파티션으로 분할하고 그 외의 값은 pmax 파티션에 분할

3. 파티션 추가 분할 방법

 mysql> alter talbe [테이블 이름] REOREGANIZE PARTION ([파티션 이름_2]) INTO

 (

 PARTITION [파티션 이름_3] VALUES LESS THAN ([index의 특정 값]),

 PARTITION [파티션 이름_2] VALUES LESS THAN MAXVALUE

 )

 ex)

 mysql> after table chk_history REORGANIZE PARTITION pmax INTO

 (

 PARTITION p202206 VALUES LESS THAN (13633138),

 PARTITION pmax VALUES LESS THAN MAXVALUE

 )

 -> pmax 파티션에서 uid 값이 13633138 값보다 작은 값들은 p202206 파티션으로 분할하고 나머지는 pmax 파티션에 분할

mysql 8.0 이상 버전은 패스워드 암호화 방식을 caching_sha2_password를 기본으로 사용

caching_sha2_password 암호화 방식은 보안연결을 사용하여 접속이 필요

 - SSL 보안연결을 사용

 - RSA 보안을 적용한 비암호 연결

php 7.4 이하 버전은 지원이 되지 않아 caching_sha2_password 방식 사용 불가

패스워드 인증 방식을 mysql5.6 이후 버전 방식인 mysql_native_password 방식으로 수정

1. 특정 계정 인증 방식 변경

mysql> ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';

2. mysql 설정 변경

 * 인증 방식 기본을 mysql_native_password 방식으로 사용하도록 설정

 # vi /etc/my.cnf

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

 [mysqld]

 default_authentication_plugin=mysql_native_password

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

 # /etc/init.d/mysql.server stop

 # /etc/init.d/mysql.server start

 - mysql_native_password를 기본 방식으로 수정한 경우 이미 생성된 계정은 암호 재설정 필요

 mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'mypassword';

1. Master DB LOCK

 - Master 서버의 장애로 인해 변경하는 것이 아닌 경우 데이터 변경을 막기 위해 LOCK을 걸고 진행

 - 기존 Matser 서버에서 실행

 mysql>FLUSH TABLES WITH READ LOCK;

2. Slave → Master 설정

 1) replication 계정 생성

  - Slave 서버를 Master 로 변경하기 위해 Slave 서버에도 replication 계정 생성

  mysql>grant replication slave on *.* to repl_user@'121.000.000.000' identified by 'test!@#$';

 2) Slave 종료

 mysql> stop slave;

 mysql> show slave status\G → 정상 종료 확인

 3) master 설정 추가

 # vi /etc/my.cnf

----------------------------------------------------------------------------------------------
[mysqld]
server-id=2
log-bin=mysql-bin → binary log 파일 경로 설정 추가
binlog-do-db='repltestdb' → replicate-do-db 에서 binlog-do-db로 변경
binlog-ignore-db='testdb' → replicate-ignore-db 에서 binlog-ignore-db로 변경
#read_only → read_only 설정 주석 또는 제거
----------------------------------------------------------------------------------------------

 * 만약 log-bin=mysql-bin 설정이 이미 되어있는 경우에는 삭제 (재구동시 자동으로 생성)

 # rm -f mysql-bin.*

 # /etc/init.d/mysql.server restart → 재구동하여 적용

 4) Master 서버 정보 확인

 mysql> show master status;

3. Master -> Slave 설정

 1) slave 설정 추가

----------------------------------------------------------------------------------------------
[mysqld]
server-id=1
replicate-do-db='repltestdb' → binlog-do-db 에서 replicate-do-db 로 변경
replicate-ignore-db='testdb' → binlog-ignore-db 에서 replicate-ignore-db 로 변경
skip-slave-start
read_only → DB 데이터가 변경되는 것을 막기 위해 추가
----------------------------------------------------------------------------------------------

 # /etc/init.d/mysql.server restart → 재구동하여 적용

 2) master 연결 설정

mysql> change master to
master_host='121.000.000.000',
master_user='repl_user',
master_password='test!@#$',
master_log_file='mysql-bin.000001',
master_log_pos=154;

 3) Slave 시작

 mysql> start slave;

 mysql> show processlist\G → 연결 확인

 4. replication 구성 확인

 1) Master 서버에서 프로세스 연결 확인

  mysql>show processlist\G

 * State 에서 binlog를 slave로 전송하였으며 업데이트 대기중인 상태 확인

 2) Slave 서버에서 확인

  mysql>show processlist\G

  * State 에서 log를 읽었으며 업데이트 대기중인 상태 확인

 mysql> show slave status\G

 3) replication 확인

  1) Master 서버 DB 데이터 입력

   mysql>insert into user(no,name,det,tel) values(9,'Css','Lang'.'070-1234-1234');

  2) Slave 서버에서 DB 조회하여 확인

  mysql>select * from user;

1) 오류 종류

1. 통신오류

- mysql 프로세스 오류, 네트워크 단절을 포함한 master ↔ slave 간 통신 문제가 발생할 경우 발생
Slave_IO_Running 상태가 No 또는 Connecting 상태로 변경된다.

mysql> show slave status\G

2. SQL오류

- SQL 오류는 master 서버에서 실행된 쿼리가 slave 서버에서 실행이 불가능한 경우 발생
Slave_SQL_Running 상태가 No 상태로 변경된다.

mysql> show slave status\G

2) 테스트 환경

1. 통신 오류

- 방화벽이 차단된 상황

2. SQL 오류

- SQL 권한 문제로 오류 발생한 상황

3) 오류 정보 확인

1. 통신 오류

- slave status 정보 확인 사항

2. SQL 오류

- slave status 정보 확인 사항

4) 오류 발생시 체크 사항

- replication 오류는 다양한 이유로 발생하기 때문에 Error정보와 Error코드를 통해 원인 확인이 필요
(mysql 기술문서에서 에러 코드 확인 가능 https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html )

1. 통신오류

- 통신오류는 연결이 비정상적인 상태이기에 Error정보와 Error코드가 에러원인과 다른 경우가 많이 있다.
- 오류가 발생할 수 있는 경우의 수를 하나씩 체크

- mysql 상태 확인

 * Master / Slave 서버 모두 mysql이 정상 실행중인지 확인

 # ps -ef | grep mysql  → 프로세스 동작 확인

 # netstat -tnlp | grep 3306(포트번호) → 실행 포트 확인

 # mysql -u root -p → 접속가능 여부 확인

 # tail -f /usr/local/mysql/data/error.log → mysql 에러로그 확인

- 네트워크 상태 확인

 * Master / Slave 서버 모두 네트워크 통신에 이상이 없는지 방화벽 허용이 되어있는지 확인

 # iptables -nL  → 방화벽 허용 확인

 # ifconfig, ethtool, ping → 네트워크 상태 확인

- replication 계정 확인

 * Slave 서버에서 Master 서버 replication 계정에 접속가능 하도록 허용되어 있는지 확인

  mysql > show grants for repl_user;

- max_allowed_packet 문제 확인

 * Master 서버의 허용패킷이 작아서 Slave 서버로 데이터가 정상 전달되지 못하여 에러발생

 mysql> show variables like 'max_allowed_packet'; -> 현재 설정값 확인(byte 단위)

2. SQL 오류

- SQL 오류는 master 서버에서 실행된 쿼리가 slave 서버에서 실행이 불가능한 경우 발생
- Error정보와 Error코드 확인이 중요

- 에러코드로 확인시 없는 테이블에 쿼리를 날린경우 발생하는 오류로 확인

 Error number 1146; Symbol: ER_NO_SUCH_TABLE; SQLSATATE:42s02

 Message: Table '$s.$s' doesn't exist

5) 오류 복구

1. mysql 프로세스 문제, 네트워크 문제

Replicaiton 자체 오류가 아닌 외부 요인으로 인한 오류로 문제 원인 해결시 대부분 자동으로 복구가 된다.
정상화 되었음에도 복구가 되지 않을 경우 slave 를 재시작하여 다시 적용
mysql> stop slave;
mysql> show slave status\G → 정상 종료 확인
mysql> start slave;
mysql> show slave status\G → 정상 연결 확인

Slave_IO_Running 상태가 Yes 로 복구되면 정상 연결된 상태

2.max_allowed_packet 문제

허용 값이 작아 에러가 발생한 경우 max_allowed_packet 크기를 올려준다.
mysql> SET GLOBAL max_allowed_packet = 1024 * 1024 * 16;
mysql> SET SESSION max_allowed_packet = 1024 * 1024 * 16;
mysql> show variables like 'max_allowed_packet'; → 설정값 다시 확인

일시적으로 허용 값을 올린 상태로 지속적인 적용을 위해 my.cnf 에 설정 변경
# vi /etc/my.cnf

3. 일시적으로 발생한 SQL 오류

- 일시적으로 발생한 오류로 데이터에 영향을 주지않는 경우 skip 하여 오류 복구

mysql> stop slave;
mysql> show slave status\G → 정상 종료 확인
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; → 1개의 쿼리를 건너뛰고 replication 진행
mysql> start slave;
mysql> show slave status\G → 상태 확인

※ 동일한 에러가 자주 발생하거나 여러번 발생하여 skip 이 어려운 경우
Errorno 를 기준으로 스킵하도록 설정
# vi /etc/my.cnf
---------------------------------------------------------------------------------------------
[mysqld]
slave-skip-errors=1146 → Errorno 를 입력 (여래개의 경우 , 로 구분. 전체는 all 입력)
----------------------------------------------------------------------------------------------
# /etc/init.d/mysql.server restart
mysql> start slave;
mysql> show slave status\G → 상태 확인

4. 지속적이거나 장기간 발생한 오류

(1) 오랜시간 에러 지속으로 DB 차이가 다량 발생한 경우
(2) replication 지연이 발생하여 Seconds_Behind_Maseter 가 많이 증가한 경우
(3) 일시적인 에러지만 데이터 정합성에 영향에 주는 경우
(4) binlog 파일이 깨진 경우

- 위 경우들은 skip 할 수 없거나 skip 하여도 재발생 가능성이 높은 경우로 master 서버에 데이터로 replication 재설정이 필요하다

※ replication 재설정
- replication 설정할 DB를 master 서버에서 dump 생성

 mysql> FLUSH TABLES WITH READ LOCK;  → replication 구성간 데이터 변화를 막기 위해 Lock 상태로 전환

 mysql> show master status;   → slave 서버에 설정한 binary log 파일과 Position 확인

 # mysqldump -u root -p repltestdb > repltestdb.sql

 mysql>  show master status\G  → binary log 와 position 확인

- slave 종료 후 dump import
 mysql> stop slave;

 mysql> show slave status\G;  → slave 종료확인

 * Slave 서버에서 DB import

 # mysql -u root -p repltestdb < repltestdb.sql

- binary log 와 position 정보 수정
  mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=4179;

- slave 시작

 mysql> start slave;

- 상태 확인
 mysql> show slave status\G  → 상태확인

- master 서버 LOCK 해제

 mysql > UNLOCK TABLES;

Master 서버 구성

1. Replication 계정 생성

mysql ((none))>use mysql
mysql (mysql)> grant replication slave on *.* to [Replication ID]@localhost identified by ‘[Replication PW]’;
mysql (mysql)> grant replication slave on *.* to [Replication ID]@[Slave 서버 IP] identified by ‘[Replication PW]’;