OS : CentOS 7.4

mysql : mariadb-5.5.56-2

1. master 서버

a. 환경 설정

[root@ns1 opt]# vi /etc/my.cnf

server-id=1
log-bin=mysql-bin

[root@ns1 opt]# systemctl restart mariadb

b. 권한 설정

[root@ns1 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 121
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> grant replication slave on *.* to "pdns"@"Slave_IP" identified by "패스워드";

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

c. DB dump

[root@ns1 opt]# mysqldump -u root -p -A > master.sql

해당 sql 파일을 2차 디버 서버로 복사

2. slave 서버

a. 환경 설정

[root@ns2 opt]# vi /etc/my.cnf

server-id=2

log-bin=mysql-bin

b. db import 및 restart

[root@ns2 ehost]# mysql -u root -p < /home/ehost/master.sql

[root@ns1 opt]# systemctl restart mariadb

3. replication 설정

a. master 서버

[root@ns1 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 124
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1184 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

b. slave 서버

[root@ns2 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 124
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to master_host="master_IP", master_user="pdns", master_password="패스워드", master_log_file="mysql-bin.000002", master_log_pos=1184;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_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 | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | master_IP | pdns | 3306 | 60 | mysql-bin.000002 | 1184 | mariadb-relay-bin.000002 | 529 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 1184 | 825 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)

4. mysql replication 의 단점

create database 문은 replication 되지 않음으로 인해 신규 디비를 생성할 경우 master / slave 모두 create 필수