3. [이중화] zabbix DB replication

1. [Master] config 수정

[root@ZABBIX levi]# cat /etc/my.cnf
[mysqld]
...
 
## replication
server-id = 1  # 유니크 ID
log-bin=zabbix-bin # binlog 이름
max_binlog_size = 100M # 1개의 binlog당 최대 사이즈
expire_logs_days = 7  # bin log 보관 주기 7일
 
binlog_do_db = zabbix # binlog를 쌓을 db명
...

 

2. [Master] MariaDB 재기동

 

3. [Master] replication user 생성

MariaDB [mysql]> grant replication slave on *.* to 'monitor-backup'@'{backup ip}' identified by 'monitor-backup';
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> show grants for "monitor-backup"@"{backup ip}";
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for monitor-backup@{backup ip}                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'monitor-backup'@'{backup ip}' IDENTIFIED BY PASSWORD '*5142FDA9ADD794E54CEB40C630CC2B6A1490EB3B' |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

4. [Master] DB table Lock

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show processlist
    -> ;
+------+-------------+-----------+--------+---------+------+------------------------------+------------------------------------------------------+----------+
| Id   | User        | Host      | db     | Command | Time | State                        | Info                                                 | Progress |
+------+-------------+-----------+--------+---------+------+------------------------------+------------------------------------------------------+----------+
| 1133 | root        | localhost | NULL   | Query   |    0 | NULL                         | show processlist                                     |    0.000 |
| 1189 | zbx_monitor | localhost | zabbix | Query   |    4 | Waiting for global read lock | SELECT NULL FROM users u WHERE u.userid=3 FOR UPDATE |    0.000 |
+------+-------------+-----------+--------+---------+------+------------------------------+------------------------------------------------------+----------+
2 rows in set (0.00 sec)

 

5.  [Master] Master Status

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| zabbix-bin.000004 | 26825118 | zabbix       |                  |
+-------------------+----------+--------------+------------------+

 

6. [Master] DB Dump

[root@ZABBIX ~]# mysqldump -u root -p zabbix > zabbix.sql
 
 
## table lock 해제
MariaDB [(none)]> UNLOCK TABLES;

 

7. [Master] Dump 파일 -> Slave 복사

 

8. [Slave] config 수정

[root@ZABBIX-BACKUP]# cat /etc/my.cnf
[mysqld]
...
 
## replication
server-id = 2 # 유니크 ID
log-bin=zabbix-bin # binlog 이름
replicate-do-db=zabbix # replication 할 db
skip-slave-start # mysql 재기동 시 자동 replication 금지

 

9. [Slave] MariaDB 재기동

 

10. [Slave] Dump 파일 밀어넣기

[root@ZABBIX-BACKUP levi]# mysql -u root -p zabbix < zabbix.sql
Enter password:

 

11. [Slave] Master DB 접속 설정

CHANGE MASTER TO MASTER_HOST='{master ip}', MASTER_USER='monitor-backup', MASTER_PASSWORD='monitor-backup', MASTER_PORT=3306, MASTER_LOG_FILE='zabbix-bin.000004', MASTER_LOG_POS=26825118;

 

12. [Slave] Replication 시작

MariaDB [(none)]> START slave;

 

13. [Slave] 확인

MariaDB [zabbix]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: {master ip}
                  Master_User: monitor-backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: zabbix-bin.000031
          Read_Master_Log_Pos: 80649736
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 60952321
        Relay_Master_Log_File: zabbix-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: zabbix
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 87776909
              Relay_Log_Space: 2885348034
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 79381
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

 

14. 참고 사항

## replication 병렬 처리
## Mysql 10.x 이상에서 사용. 5.x ㅈ버전에서는 사용 불가능.
[root@MZABBIX-BACKUP zabbix]# cat /etc/my.cnf
[mysqld]
...
slave-parallel-threads          = 8 # 쓰레드 수
slave-parallel-max-queued       = 512K # 쓰레드가 사용할 큐 사이즈
...
 
 
 
 
## dump 시 database의 각 table 별로 병렬 처리
## 슬레이브에서 실행
mysqldump -h[source_database] --opt --single-transaction --skip-triggers --add-drop-table --no-create-db -u***** -p'*****' {DB NAME}  | mysql --protocol=tcp  -u***** -p'*****' {DB NAME}