MySQL Master DB 백업하여 Replication Slave DB 구성하기

 

·       Version : MySQL 5.7.5 Later

 

MySQL DB 처음 구축할때 마스터DB 슬레이브DB 구성하는것이 아니라면 반드시 마스터 DB 데이터를 백업하여 슬레이브DB 구성 하여야 한다. 이때 특정 시점까지 (Point in Time) 마스터 DB 백업하면, 백업파일에는 백업이 완료된 시점의 바이너리 로그파일과 포지션 정보가 기록된다. 슬레이브DB에서는 마스터DB 백업 파일 이후로 발생된 데이터 변경사항을 전달받아 레플리케이션 한다.

 

InnoDB 스토리지 엔진은 트랜잭션을 지원하므로 특정 시점까지 백업한 백업 시점의 마지막 바이너리 로그 파일과 로그 포지션 정보로 마스터 DB 슬레이브 DB 연결하는 식으로 레플리케이션을 구성할 있다.

 

MySQL에서 데이터를 백업할 시점 백업(Point in Time) 수행하는 방법에는 가지가 있다. 첫번째 방법은 mysqldump 명령을 사용할때 –single=transaction옵션을 사용하는 방법이고, 두번째 방법은 xtrabackup 사용하는 것이다. 이번 포스트에서는 mysqldump 다루도록 한다.

mysqldump 사용한 시점 백업은 아래와 같다.

mysqldump --all-databases --master-data –single-transaction > all_databases.sql

 

백업이 완료 되었으면 아래 명령을 사용하여 Slave DB에서 백업 파일을 복원한다.

mysql -uroot -p ‘xxxx’ < all_databases.sql

 

슬레이브DB 백업파일 복원이 완료 되었으면 백업파일에서 마스터DB 로그 파일과 포지션 정보를 확인하여 레플리케이션 정보를 등록한다.

head -30 all_databases.sql

 

 

정보를 바탕으로 연결할 마스터DB 정보를 입력하고 연결을 시작한다.

--연결설정
mysql>CHANGE MASTER TO
MASTER_HOST=’1.2.3.4’,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’replication_password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000108’,
MASTER_LOG_POS=384679355;


--연결시작
mysql> start lave

 

슬레이브DB 복제가 시작되었으면 show slave status 명령을 사용하여 슬레이브DB 복제상태를 확인할 있으며 “Slave I/O thread”, “Slave SQL thread” 상태가 YES 표시되면 정상 작동이며 , NO라고 표시될 경우 비정상 작동이라고 판단할 있다.

 

[참고자료]

·       mysqldump — A Database Backup Program : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-transaction-options

 

 

2021-05-28 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MysqlDump, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브

MySQL/MariaDB 백업 & 복원 - mysqldump

 

  • Version : Mariadb 5.5.4.2-WinX64

 

MySQL/MariaDB 백업 및 복원에 대해서 알아본다. 이번 백업은 MySQL 시절부터 널리 알려진 mysqldump를 사용한다.

 

mysqldump는 논리적 백업을 수행한다. 데이터 크기가 비교적 작은 경우 유연하게 사용할 수 있다. 백업파일은 SQL 형식으로 백업 파일을 생성한다. 이렇게 생성된 백업 형식은 MariaDB, MySQL 또는 완전히 다른 DBMS에서 쉽게 가져올 수 있다.

 

mysqldump는 테이블 및 트리거를 덤프한다. 저장 프로시저나 뷰 등은 명시적으로 추가 매개변수를 사용하여 명시적으로 작성해야 한다.

 

[기본 사용 문법]

-p 옵션 후 명시적으로 패스워드를 적지 않으면 mysqldump 수행시 패스워드를 물어 본다.

mysqldump -u[아이디] -p[패스워드] > [저장파일명].sql

 

 

[전체 데이터베이스 백업& 복원]

MySQL/MariaDB 전체 데이터베이스를 백업받는다.

mysqldump -uroot -p -A > backup_full.sql

 

생성된 덤프를 이용한 복원

mysql -uroot -p < backup_full.sql

 

 

[특정 데이터베이스 백업]

sw_test 라는 데이터베이스만 백업

mysqldump -uroot -p sw_test > backup_sw_test.sql

 

 

[특정 데이터베이스의 특정 테이블 백업]

sw_test 데이터베이스의 tbl_a라는 테이블만 백업

mysqldump -uroot -p sw_test tbl_a > backup_sw_test_tbl_a.sql

 

 

[특정 데이터베이스의 테이블의 특정 값만 백업]

sw_test 데이터베이스의 tbl_a테이블의 emp_no가 100 이상 200이하의 데이터만 백업

mysqldump -uroot -p sw_test tbl_a -w'emp_no >= 100 and emp_no <= 200' > backup_sw_test_tbl_a.sql

 

 

[특정 데이터베이스의 테이블 definition 백업]

실제 데이터백업은 받지 않고 테이블 definition만 백업 받는다.

mysqldump -uroot -p sw_test --no-data > backup_sw_test_definition.sql

 

 

Sw_test 데이터베이스를 백업하여 test로 복원한다. 프로시저는 백업 및 복원에 포함되지 않은것을 확인할 수 있다.

 

백업

 

복원

 

프로시저는 백업 및 복원에 포함되지 않음

 

 

[참고자료]

Backup and Restore Overview : https://mariadb.com/kb/en/mariadb/backup-and-restore-overview/

 

 

 

2015-07-02 / 강성욱 / http://sqlmvp.kr

 

 

mysql백업, MariaDB 백업, MySQL Backup, mysqldump, mysql 복원, MariaDB 복원, 데이터베이스 백업, 데이터베이스 복원

+ Recent posts