[리눅스] MySQL Replication 설정

MySQL Replication 설정

Master Server

1.환경설정

[root@svm101 ~]$ vi /etc/my.cnf

...
[mysqld]
...
#log setting
log-bin = mysql-bin #로그파일명
max_binlog_size = 100M #로그파일크기
expire_logs_days = 7 # 로그보존주기 

#Replication for master server
server-id = 3 #서버 식별자(유니크)
binlog_do_db = racktables #리플리케이션DB명(생략시엔 전체DB를 리플리케이션함)
#binlog_do_db = test1 #여러 개의 DB일경우, 계속 추가
#binlog_ignore_db = test2 #Replication 제외 DB 지정

2.mysql 재가동(MasterServer)

[root@svm101 ~]$ service mysqld restart

[root@svm101 ~]$ mysql -u root -p
Enter password:

3.유저추가(MasterServer)

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

FLUSH PRIVILEGES;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

// DB Write 금지
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+----------------------+------------------+
| File             | Position | Binlog_Do_DB         | Binlog_Ignore_DB |
+------------------+----------+----------------------+------------------+
| mysql-bin.000001 |   873110 | zabbix,racktables_db |                  |
+------------------+----------+----------------------+------------------+
1 row in set (0.00 sec)

4.데이터 백업(MasterServer)

[root@svm101 ~]$ mysqldump -u root -p zabbix > Backup_zabbix.sql
Enter password:

[root@svm101 ~]$ mysqldump -u root -p racktables_db > Backup_racktables_db.sql
Enter password:

[root@svm101 ~]$ ls -l | grep sql
-rw-r--r--  1 root root  262824 2014-04-09 00:43 Backup_racktables_db.sql
-rw-r--r--  1 root root 2027131 2014-04-09 00:43 Backup_zabbix.sql

DB Write 금지해제

UNLOCK TABLES;

SHOW MASTER STATUS\G

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 895793
    Binlog_Do_DB: zabbix,racktables_db
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Slave Server

1.환경설정

[root@cvm121 ~]$ vi /etc/my.cnf

...
[mysqld]
...
#Replication for master server
server-id = 4 #서버 식별자(유니크)
replicate-do-db = zabbix #리플리케이션DB명(생략시엔 전체DB를 리플리케이션함)
replicate-do-db = racktables_db #여러 개의 DB일경우, 계속 추가

[root@cvm121 ~]$ mysql -u root -p
Enter password:

create database zabbix;

create database racktables_db;

mysql> create database zabbix;
Query OK, 1 row affected (0.00 sec)

mysql> create database racktables_db;
Query OK, 1 row affected (0.00 sec)

[root@cvm121 ~]$ mysql -u root -p racktables < Backup_racktables.sql
Enter password:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=846712;

stop slave;

flush privileges;

start slave;

SHOW SLAVE STATUS\G

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=846712;
Query OK, 0 rows affected (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 871719
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 25258
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: zabbix,racktables_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: 871719
              Relay_Log_Space: 25414
              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:
1 row in set (0.00 sec)
mysql>

 

참고URL

- http://juhyunsik.blogspot.kr/2012/05/mysql-replication.html

- http://www.zosel.net/entry/MySQL-Replication-%EC%84%A4%EC%A0%95Master-Slave-Master-Master