AWS EC2/EBS 상의 MariaDB master/slave Replication

Master server

$ mysql -uroot -p

mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'172.%' IDENTIFIED BY 'repl-password-here';

mysql> flush tables with read lock;

mysql> reset master;

# Make note of the file name and file position

mysql> show master status;

$ /etc/init.d/mysql stop

$ xfs_freeze -f /mnt/mysql

# Take AWS EC2 EBS snapshot and when it begins do next step

$ xfs_freeze -u /mnt/mysql

$ /etc/init.d/mysql start

$ mysql -uroot -p

mysql> unlock tables;

Wait for WAS to finish writing the EBS snapshot before proceeding.

Slave server

1. EBS snapshot is complete
2. create a new volume from snapshot
3. umount old volume
4. umount /data
5. Detach "/data" volume
6. Attach new "/data" volume
7. mount /data
8. lsblk
9. sudo dd if=/dev/xvdX of=/dev/null bs=1M
10. syncing to the master

$ /etc/init.d/mysql start

$ mysql -uroot -p

mysql> CHANGE MASTER TO

 MASTER_HOST='172.x.x.x',

 MASTER_USER='repl',

 MASTER_PASSWORD='repl-password-here',

 MASTER_LOG_FILE = 'mysql-bin.000001',

 MASTER_LOG_POS = 107;

mysql> start slave;

mysql> show slave status \G

Reference

• https://jrklein.com/2017/03/22/mysql-master-slave-replication-on-aws-ec2-ebs/
• https://www.tecmint.com/mariadb-master-slave-replication-on-centos-rhel-debian/

저작자표시

• 카카오스토리
• 트위터
• 페이스북

'SQL > MySql' 카테고리의 다른 글

AWS EC2/EBS 상의 MariaDB master/slave Replication  (0)	2019.02.13
mysql dump 백업 파일 한글 깨짐 대응  (4)	2018.09.02
Mysql CPU 100 상회시 분석  (0)	2016.07.03
mysql federated engine 사용 테이블 select 쿼리 out of memory 발생 이슈  (0)	2016.05.12
Mysql 스키마 보기 쿼리  (0)	2008.12.22