[글쓴이:] aiseki@gmail.com

문자메시지 발송서비스 서버 구축을 위해서 mariadb (replication) 를 사용
1,000,000건/일 + @ 처리가 가능해야 한다

서버스펙

Xeon (16core)
samsung 860 pro 1Tb * 2 (RAID0)
32Gb memory

구성

L4 : Active – Standby
mariadb : Master – Slave

서버설치

http://mirror.kakao.com/ubuntu-releases/focal/

mariadb 설치

Download MariaDB Server

sudo apt-get install software-properties-common dirmngr apt-transport-https
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirror.yongbok.net/mariadb/repo/10.5/ubuntu focal main'

sudo apt update
sudo apt install mariadb-server

mysql 사용자 설정

mysql -uroot -p

# create user username@localhost identified by 'password';
create user my@'%' identified by '1234';

# grant select on database.table to username@localhost;
grant select on *.* to my@'%';

# grant all privileges on database.table to username@localhost;
grant all privileges on *.* to my@'%';

flush privileges;

replication

master

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

vim /etc/mysql/mariadb.conf.d/50-server.cnf

systemctl restart mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 37
 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal-log mariadb.org binary distribution
 Copyright (c) 2000, 2018, 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.000001 |     2098 |              |                  |
 +------------------+----------+--------------+------------------+
 1 row in set (0.000 sec)
 MariaDB [(none)]>

show master status;

slave

[mysqld]
server-id = 2
relay_log=mysql-relay-bin
log_slave_updates = 1
read_only = 1

vim /etc/mysql/mariadb.conf.d/50-server.cnf

systemctl restart mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 36
 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution
 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 MariaDB [(none)]> stop slave;
 MariaDB [(none)]> CHANGE MASTER TO
     -> MASTER_HOST='###.###.###.###',
     -> MASTER_PORT=3306,
     -> MASTER_USER='repl',
     -> MASTER_PASSWORD='1234',
     -> MASTER_LOG_FILE='mysql-bin.000001',
     -> MASTER_LOG_POS=2098;
 MariaDB [(none)]> start slave;

 MariaDB [(none)]> show slave status \G;
 * 1. row *
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: ###.###.###.###
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000001
            Read_Master_Log_Pos: 2098
                 Relay_Log_File: mysql-relay-bin.000002
                  Relay_Log_Pos: 906
          Relay_Master_Log_File: mysql-bin.000001
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                Replicate_Do_DB:
            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: 2098
                Relay_Log_Space: 1215
                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: 0
  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
                 Master_SSL_Crl:
             Master_SSL_Crlpath:
                     Using_Gtid: No
                    Gtid_IO_Pos:
        Replicate_Do_Domain_Ids:
    Replicate_Ignore_Domain_Ids:
                  Parallel_Mode: optimistic
                      SQL_Delay: 0
            SQL_Remaining_Delay: NULL
        Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Slave_DDL_Groups: 3
 Slave_Non_Transactional_Groups: 0
     Slave_Transactional_Groups: 0
 1 row in set (0.000 sec)
 ERROR: No query specified
 MariaDB [(none)]>

show slave status \G;