{"id":1489,"date":"2026-05-13T23:25:38","date_gmt":"2026-05-13T14:25:38","guid":{"rendered":"http:\/\/www.igoni.kr\/?p=1489"},"modified":"2026-05-13T23:26:16","modified_gmt":"2026-05-13T14:26:16","slug":"mysql-replication-%ec%84%a4%ec%a0%95","status":"publish","type":"post","link":"http:\/\/www.igoni.kr\/?p=1489","title":{"rendered":"mysql replication \uc124\uc815"},"content":{"rendered":"

\uc548\ub155\ud558\uc138\uc694, \uace0\ub2c8\uc785\ub2c8\ub2e4. Mysql \ub370\uc774\ud130\ubcf5\uc824\ub974 \uc704\ud55c\u00a0 replication \uc124\uc815\uc808\ucc28\uc5d0 \ub300\ud574 \uc5c5\ub370\uc774\ud2b8 \ud588\uc2b5\ub2c8\ub2c8\ub2e4.<\/p>\n<\/colgroup>\n\n\n\n
\"\"<\/figure><\/td>\n<\/tr>\n
perplexity\uc5d0\uc11c \uc0dd\uc131\ud55c AI\uc774\ubbf8\uc9c0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \n\n
  1. 5.5\ubd80\ud130\ub294 innodb\ub97c \uae30\ubcf8\uc5d4\uc9c4\uc73c\ub85c \uc0ac\uc6a9\ud558\uae30 \ub54c\ubb38\uc5d0, Myisam\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0, \ud14c\uc774\ube14 \uad6c\uc131\uc2dc \uc5d4\uc9c4\ud0c0\uc785\uc744 \ub530\ub85c \uae30\uc7ac\ud574 \uc8fc\uc5b4\uc57c \ud568.<\/li>\n<\/ol>\n
      \n\n
    • \n
        \n\n
      • Master1 : 192.168.20.11<\/li>\n\n
      • Master2 : 192.168.20.12<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
          \n\n
        1. Mysql replication \uad6c\uc131\ud558\uae30(Dual Master)<\/li>\n\n
        2. Master1 my.cnf\ud30c\uc77c \uc218\uc815\n
            \n\n
          1. \/etc\/my.cnf\ud30c\uc77c\uc5d0 \ud574\ub2f9 \ub0b4\uc6a9 \ucd94\uac00\ud558\uae30\n
            $> vi \/etc\/my.cnf\nserver-id       = 1\nbinlog-do-db=wordpress (\ub9ac\ud50c\ub9ac\ucf00\uc774\uc158 \uc0ac\uc6a9\ud560 DB\uba85, \uc5ec\ub7ec\uac1c\uc758 DB\uc77c \uacbd\uc6b0, \ud55c\uce78\uc529 \ub744\uc6b0\uba74 \ub428. wordpress mysql, \uc804\uccb4 DB\ub97c \ubcf5\uc81c\ud560\uacbd\uc6b0\uc5d4\ub294 \ub530\ub85c \uc124\uc815\ud558\uc9c0\uc54a\uc544\ub3c4 \ub428)<\/code><\/pre>\n<\/li>\n<\/ol>\n<\/li>\n\n
          2. Master2\u00a0\/etc\/my.cnf\ud30c\uc77c\uc5d0 \ub0b4\uc6a9 \ubcc0\uacbd\n
            $> vi \/etc\/my.cnf\nserver-id       = 2\nbinlog-do-db=wordpress<\/code><\/pre>\n<\/li>\n\n
          3. Master1\uc5d0 master \ud50c\ub7ec\uadf8\uc778 \uc124\uce58 \ubc0f \ud65c\uc131\ud654\n
            mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';\nQuery OK, 0 rows affected (0.00 sec)\nmysql> set global rpl_semi_sync_master_enabled=1;\nQuery OK, 0 rows affected (0.00 sec)\nmysql> set global rpl_semi_sync_master_timeout =1000;\nQuery OK, 0 rows affected (0.00 sec)\nmysql> show variables like 'rpl_semi_sync%';\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~--+\n| Variable_name                      | Value |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~--+\n| rpl_semi_sync_master_enabled       | ON    |\n| rpl_semi_sync_master_timeout       | 1000  |\n| rpl_semi_sync_master_trace_level   | 32    |\n| rpl_semi_sync_master_wait_no_slave | ON    |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~--+<\/code><\/pre>\n<\/li>\n\n
          4. Master2\uc11c\ubc84\uc5d0\uc11c \ucffc\ub9ac \uc801\uc6a9\ud560 \uc218 \uc788\uac8c \uad8c\ud55c \ubd80\uc5ec\n
            mysql> grant replication slave on *.* to 'repl_test'@'192.168.20.12' identified by 'repltest';\n\\\\Query OK, 0 rows affected (0.00 sec)<\/code><\/pre>\n<\/li>\n\n
          5. Master2\uc11c\ubc84\uc5d0 \uc2ac\ub808\uc774\ube0c\u00a0\ud50c\ub7ec\uadf8\uc778 \uc124\uce58 \ubc0f \ud65c\uc131\ud654\n
            mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';\nmysql> set global rpl_semi_sync_slave_enabled=1;\nmysql> show variables like 'rpl_semi_sync%';\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+\n| Variable_name                   | Value |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+\n| rpl_semi_sync_slave_enabled     | ON    |\n| rpl_semi_sync_slave_trace_level | 32    |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+\n2 rows in set (0.01 sec)<\/code><\/pre>\n<\/li>\n\n
          6. Master1\uc11c\ubc84\uc5d0\uc11c \uc811\uc18d\ud560 \uc218 \uc788\uac8c \uad8c\ud55c \uc124\uc815\n
            mysql> grant replication slave on *.* to 'repl_test'@'192.168.20.11' identified by 'repltest';<\/code><\/pre>\n<\/li>\n\n
          7. \uc591\ucabd Master\uc11c\ubc84\uc758 bin log, postion \ucd08\uae30\ud654\n
            mysql> reset master;<\/code><\/pre>\n<\/li>\n\n
          8. \uac01 \uc11c\ubc84 Master\uc0c1\ud0dc \ud655\uc778\n
            \n
            \n
            \n
            #\u00a0Master1\uc5d0\uc11c\u00a0\ud655\uc778<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> show master status;\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n| mysql-bin.000001 |      100 | wordpress    |                  |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n1 row in set (0.00 sec)\n<\/code><\/pre>\n
            \n
            \n
            \n
            #\u00a0Master2\uc5d0\uc11c\u00a0\ud655\uc778<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> show master status;\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n| mysql-bin.000001 |      100 | wordpress    |                  |\n+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-+~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-+\n1 row in set (0.00 sec)<\/code><\/pre>\n
            \n
            \n
            <\/div>\n<\/div>\n<\/div><\/li>\n\n
          9. \uc591\ucabd \uc11c\ubc84 Master\uc5f0\uacb0 (File\uacfc Position\uc744 \ub9de\ucd94\uba74 \ub428)\n
            \n
            \n
            \n
            #\u00a0Master1\uc5d0\uc11c\u00a0\uc218\ud589<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> change master to  master_host='192.168.20.12', master_user='repl_test', master_password='repltest', master_log_file='mysql-master-bin.000001', master_log_pos=100;<\/code><\/pre>\n
            \n
            \n
            \n
            \n\n#\u00a0Master2\uc5d0\uc11c\u00a0\uc218\ud589\n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> change master to  master_host='192.168.20.11', master_user='repl_test', master_password='repltest', master_log_file='mysql-master-bin.000001', master_log_pos=100;<\/code><\/pre>\n
            \n
            \n
            <\/div>\n<\/div>\n<\/div><\/li>\n\n
          10. \uac01\uc11c\ubc84 \uc5f0\uacb0\uc0c1\ud0dc\ud655\uc778\n
            \n
            \n
            \n
            Master1)<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> show slave status \\G;\n*************************** 1. row ***************************\n               Slave_IO_State:\n                  Master_Host: 192.168.20.12\n                  Master_User: repl_test\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: mysql-bin.000001\n          Read_Master_Log_Pos: 107\n               Relay_Log_File: ha1-relay-bin.000001\n                Relay_Log_Pos: 4\n        Relay_Master_Log_File: mysql-bin.000001\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 107\n              Relay_Log_Space: 107\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File:\n           Master_SSL_CA_Path:\n              Master_SSL_Cert:\n            Master_SSL_Cipher:\n               Master_SSL_Key:\n        Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Master_Server_Id: 0\n1 row in set (0.00 sec)\nERROR:\nNo query specified<\/code><\/pre>\n
            \n
            \n
            \n
            Master2)<\/div>\n<\/div>\n<\/div>\n<\/div>\n
            mysql> show slave status \\G;\n*************************** 1. row ***************************\n               Slave_IO_State:\n                  Master_Host: 192.168.20.11\n                  Master_User: repl_test\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: mysql-bin.000001\n          Read_Master_Log_Pos: 107\n               Relay_Log_File: ha2-relay-bin.000001\n                Relay_Log_Pos: 4\n        Relay_Master_Log_File: mysql-bin.000001\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 107\n              Relay_Log_Space: 107\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File:\n           Master_SSL_CA_Path:\n              Master_SSL_Cert:\n            Master_SSL_Cipher:\n               Master_SSL_Key:\n        Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Master_Server_Id: 0\n1 row in set (0.00 sec)\nERROR:\nNo query specified<\/code><\/pre>\n
            \n
            \n
            <\/div>\n<\/div>\n<\/div><\/li>\n<\/ol>","protected":false},"excerpt":{"rendered":"
            \uc548\ub155\ud558\uc138\uc694, \uace0\ub2c8\uc785\ub2c8\ub2e4. Mysql \ub370\uc774\ud130\ubcf5\uc824\ub974 \uc704\ud55c\u00a0 replication \uc124\uc815\uc808\ucc28\uc5d0 \ub300\ud574 \uc5c5\ub370\uc774\ud2b8 \ud588\uc2b5\ub2c8\ub2c8\ub2e4. perplexity\uc5d0\uc11c \uc0dd\uc131\ud55c AI\uc774\ubbf8\uc9c0 5.5\ubd80\ud130\ub294 innodb\ub97c \uae30\ubcf8\uc5d4\uc9c4\uc73c\ub85c…<\/p>","protected":false},"author":1,"featured_media":1491,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[423,383,405],"class_list":["post-1489","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-linux-","tag-data-sync","tag-mysql","tag-replication"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t