[ MySQL/MariaDB - Replication ] DB복제

docker run -d \
    --name emmaster \
    -e MYSQL_DATABASE=mydb \
    -e MYSQL_USER=myuser \
    -e MYSQL_PASSWORD=9999 \
    -e MYSQL_ROOT_PASSWORD=9999 \
    -p 13306:3306 mariadb

docker run -d \
    --name emslaver1 \
    -e MYSQL_DATABASE=mydb \
    -e MYSQL_USER=myuser \
    -e MYSQL_PASSWORD=9999 \
    -e MYSQL_ROOT_PASSWORD=9999 \
    -p 13307:3306 mariadb

docker run -d \
    --name emslaver2 \
    -e MYSQL_DATABASE=mydb \
    -e MYSQL_USER=myuser \
    -e MYSQL_PASSWORD=9999 \
    -e MYSQL_ROOT_PASSWORD=9999 \
    -p 13308:3306 mariadb

docker exec -it emmaster bash
apt-get update
apt-get -y install vim

docker exec -it emslaver1 bash
apt-get update
apt-get -y install vim

docker exec -it emslaver2 bash
apt-get update
apt-get -y install vim

[mysqld]
server_id=1
log_bin=mysql.bin
character-set-server=utf8
collation-server=utf8_general_ci

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

# service mysql restart   << docker container 에서 요거 말고

$ docker stop emmaster
$ docker start emmaster
$ docker exec -it emmaster bash
# mysql -u root -p
MariaDB [(none)]> show variables like 'char%';   << 확인한다.

ⓐ 서버 ID버 ID가 맞게 설정되어 있는지 확인 (직접 SET포함)
MariaDB [(none)]> SET GLOBAL server_id=1;
MariaDB [(none)]> show variables like 'server_id';
+-----------------+--------+
| Variable_name  | Value   |
+-----------------+--------+
| server_id         | 1         |
+-----------------+--------+

ⓑ 사용자에게 Replication권한 부여
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO myuser;

ⓒ Master의 상태를 확인 및 기억한다. (File 및  Position은 Slave설정시 사용)
MariaDB [(none)]> show master status;
+----------------+----------+-----------------+---------------------+
 | File              | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+----------------+----------+-----------------+---------------------+
 | mysql.000001 |      485  |                     |                          |
+----------------+----------+-----------------+---------------------+

[mysqld]
server_id=2
log_bin=mysql.bin
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
character-set-server=utf8
collation-server=utf8_general_ci

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

# mysql -u root -p

MariaDB [(none)]> SET GLOBAL server_id=2;
MariaDB [(none)]> show variables like 'server_id';

MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.100',
MASTER_USER='myuser',
MASTER_PASSWORD='9999',
MASTER_PORT=13306,
MASTER_LOG_FILE='mysql.000001',
MASTER_LOG_POS=485,
MASTER_CONNECT_RETRY=10;

MariaDB [(none)]> START SLAVE;

※ 만약 에러가 나서 slave가 시작되지 않을 경우
MariaDB [(none)]> RESET SLAVE;
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> START SLAVE;