· KLDP.org · KLDP.net · KLDP Wiki · KLDP BBS ·
MySQL¸®Çø®ÄÉÀ̼Ç

Dual-Master Replication in MySQL

¼ÛÀº¿µ (f405@sds.co.kr), ±èÈ«¼·(hskim@sds.co.kr), ¹æâÇö(winchild@sds.co.kr)-µî·Ï¹×Æ÷¸ÅÆà / (ÁÖ)»ïÁ¤µ¥ÀÌÅͼ­ºñ½º ¿¬±¸¼Ò


ÃÖÁ¾¼öÁ¤ÀÏ: 2006³â1¿ù2ÀÏ 01½Ã35ºÐ


1.1. Replication À̶õ?

ReplicationÀº 3.23.15ºÎÅÍ Áö¿øµÇ±â ½ÃÀÛÇÑ ±â´ÉÀ¸·Î ¡®º¹Á¦¡¯¶ó´Â »çÀüÀû Àǹ̿¡ ¸Â°Ô ¸¶½ºÅÍÀÇ MySQL ¼­¹öÀÇ µ¥ÀÌÅ͸¦ ¿©·¯ ´ëÀÇ ½½·¹À̺ê MySQL ¼­¹öÀÇ µ¥ÀÌÅÍ¿Í µ¿±âÈ­ ½ÃÄÑÁÖ´Â ±â´ÉÀÌ´Ù. ÁÖ·Î, MySQLÀÇ µ¥ÀÌÅ͸¦ ½Ç½Ã°£À¸·Î ¹é¾÷Çϰųª, µ¥ÀÌÅÍ ¼­¹öÀÇ ºÎÇϺлêÀ» ÇÏ°íÀÚ ÇÒ ¶§ ¸¹ÀÌ »ç¿ëµÈ´Ù.

Dual-Master ReplicationÀ» ±¸ÃàÇϱâ À§ÇØ, ¸ÕÀú Master-Slave·Î ±¸¼ºµÈ Replication »óŸ¦ ¸¸µé¾î¾ß ÇÑ´Ù.


1.2. How to Set Up Replication


1.2.1. MASTER ¿Í SLAVE ¼³Ä¡

MySQLÀ» master ¿Í slave ¼­¹ö¿¡ ¼³Ä¡ÇÑ´Ù. ¾ÈÁ¤¼ºÀ» À§ÇØ µÎ ¼­¹öÀÇ ¹öÀüÀ» ¸ÂÃçÁÖ´Â °ÍÀÌ ÁÁ´Ù. Replication ±â´ÉÀº 3.23.15ºÎÅÍ Áö¿øµÇ±â ½ÃÀÛÇÏ¿´À¸³ª 3.23.32ºÎÅÍ ¾ÈÁ¤È­µÇ¾ú´Ù°í ¾Ë·ÁÁ® ÀÖÀ¸¹Ç·Î, ±× ÀÌ»ó ȤÀº ÃֽŠ¹öÀüÀÇ MySQL À» ¼³Ä¡ÇÏ±æ ±ÇÀåÇÑ´Ù.

1.2.2. MASTER °èÁ¤»ý¼º

slave ¼­¹ö¿¡¼­ master ¼­¹ö¿¡ Á¢¼ÓÇÒ ¼ö ÀÖµµ·Ï, master ¼­¹ö¿¡ °èÁ¤À» ¸¸µç´Ù. »ç¿ëÀÚ¸¦ Ãß°¡ÇØ ÁÖ¾î¾ß ÇÑ´Ù´Â ¸»ÀÌ´Ù. ÀÌ °èÁ¤¿¡ REPLICATION SLAVE ±ÇÇÑÀ» ÁÖ¾î¾ß ÇÑ´Ù. replication¿¡¸¸ »ç¿ëÇÒ °èÁ¤À̶ó¸é Ãß°¡ÀûÀÎ ±ÇÇÑÀº ÁÖÁö ¾Ê¾Æµµ µÈ´Ù. slave ¼­¹ö¿¡¼­master ¼­¹ö¿¡ Á¢¼ÓÇÒ °èÁ¤°ú Æнº¿öµå¿¡ ±ÇÇÑÀ» ºÎ¿©ÇÏ´Â ¸í·ÉÀº ´ÙÀ½°ú °°´Ù.
master mysql > GRANT REPLICATION SLAVE ON *.*
            -> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

¿©±â¼­ user_nameÀº Áߺ¹µÇÁö ¾Ê´Â À̸§ÀÌ¸é µÇ¸ç, user_host ´Â slave·Î ¸¸µé ¼­¹öÀÇ ÁÖ¼Ò È¤Àº µµ¸ÞÀÎ ³×ÀÓÀ» Àû¾îÁØ´Ù. ÀÌ ÁÖ¼ÒÀÇ slave À¯Àú¸¸ master ¼­¹ö·Î Á¢¼ÓÇÒ ¼ö ÀÖ´Ù. 4.0.2 ÀÌÀü ¹öÀüÀÇ MySQL¿¡¼­´Â, REPLICATION SLAVE ±ÇÇÑÀÌ ¾øÀ¸¹Ç·Î, ´ÙÀ½°ú °°ÀÌ FILE ±ÇÇÑÀ¸·Î ´ë½ÅÇÑ´Ù.
master mysql > GRANT FILE ON *.*
            -> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

1.2.3. MASTER µ¥ÀÌÅÍ SLAVE ¿¡ º¹»ç

master ¼­¹öÀÇ ±âº» µ¥ÀÌÅ͸¦ ¹é¾÷ ¹Þ¾Æ, slave ¼­¹öÀÇ µ¥ÀÌÅͺ£À̽º¿¡ º¹»çÇÑ ÈÄ, µ¥ÀÌÅÍ µð·ºÅ丮¿¡¼­ ¾ÐÃàÀ» Ǭ´Ù.

HOT ¹é¾÷
master mysql > FLUSH TABLES WITH READ LOCK;
master shell > tar -cvf /tmp/mysql-snapshot.tar .
slave   shell > tar -xvf /tmp/mysql-snapshot.tar
master mysql > UNLOCK TABLES;

mysqldump ÀÌ¿ë ¹é¾÷
master Shell > mysqldump -u root -p ¡®password¡¯ -B db_name > dump_file.sql


1.2.4. MASTER ȯ°æ¼³Á¤

Master ¿Í Slave ÀÇ µ¥ÀÌÅÍ º£À̽º ȯ°æÀ» ¼³Á¤ÇÑ´Ù. ¿ì¼± master ¼­¹ö¸¦ ¼³Á¤Çϵµ·Ï ÇÑ´Ù.
master shell> vi /etc/my.cnf

master ¼­¹ö´Â µðÆúÆ®·Î ±¸¼ºÀÌ µÇ¾î ÀÖÀ» °ÍÀ̹ǷÎ, mysqld ¼½¼Ç¿¡ log-binÀÌ ÀÖ´Â Áö È®ÀÎÇÑ´Ù.
[mysqld]
log-bin
server-id  = 1

1.2.5. SLAVE ȯ°æ¼³Á¤

´ÙÀ½Àº slave ¼­¹öÀÇ È¯°æ¼³Á¤ÀÌ´Ù.
slave shell> vi /etc/my.cnf

mysqld ¼½¼ÇÀ¸·Î °¡¼­ server-id¸¦ master ¼­¹öÀÇ server-id¿Í ´Ù¸£°Ô ¼³Á¤ÇÑ´Ù. º» ¹®¼­¿¡¼­´Â 2·Î ¼³Á¤Çϵµ·Ï ÇÏ°Ú´Ù. slave ¼­¹ö¸¦ ¿©·¯ ´ë·Î ±¸ÃàÇÏ°íÀÚ ÇÒ ¶§¿¡ °¢ slave ¼­¹öÀÇ server-id´Â °¢°¢ ´Þ¶ó¾ß ÇÑ´Ù´Â °Í¿¡ ÁÖÀÇÇÏÀÚ. 2^32-1±îÁö °¡´ÉÇÏ´Ù.
[mysqld]
server-id  = 2
master-host	= xxx.xxx.xxx.xxx(user_host)
master-port	= 3306
master-user	= user_name
master-password	= user_password

master ¼­¹öÀÇ µ¥ÀÌÅ͸¦ ¹é¾÷ ¹Þ¾Ò´Ù¸é, slave ¼­¹ö¸¦ ½ÃÀÛÇϱâ Àü¿¡ slave ¼­¹öÀÇ µ¥ÀÌÅÍ µð·ºÅ丮¿¡ master ¼­¹öÀÇ µ¥ÀÌÅ͸¦ º¹»çÇØ µÐ´Ù. mysqldump¸¦ »ç¿ëÇß´Ù¸é, ´ÙÀ½À¸·Î °¡¼­ ¸ÕÀú, slave ¼­¹ö¸¦ ½ºÅ¸Æ®ÇÑ´Ù.

1.2.6. SLAVE ¼­¹ö ½ºÅ¸Æ®

slave ¼­¹ö¸¦ ½ºÅ¸Æ®ÇÑ´Ù.
slave shell > /etc/init.d/mysqld start

1.2.7. SLAVE ´ýÇÁÆÄÀÏ LOAD

mysqldump¸¦ »ç¿ëÇØ ¹é¾÷ ÆÄÀÏÀ» ¸¸µé¾ú´Ù¸é, slave ¼­¹ö¿¡ ´ýÇÁ ÆÄÀÏÀ» ·Îµå½ÃŲ´Ù.
slave shell > mysql -u root -p < dump_file.sql

1.2.8. MASTER °èÁ¤ ¼³Á¤

slave ¼­¹ö¿¡¼­ master-host, master-user, master-password µîÀÇ ¼³Á¤À» ´ÙÀ½°ú °°ÀÌ ¹Ù²Ü ¼öµµ ÀÖ´Ù. ¹°·Ð /etc/my.cnf¿¡¼­ ¼³Á¤ÇÏÁö ¾Ê¾ÒÀ» °æ¿ì¿¡µµ ¾µ ¼ö ÀÖ´Ù.
slave mysql >  CHANGE MASTER TO 
           ->	MASTER_HOST='master_host_name',
           ->	MASTER_USER='replication_user_name',
           ->	MASTER_PASSWORD='replication_password',
           ->	MASTER_LOG_FILE='recorded_log_file_name',
           ->	MASTER_LOG_POS=recorded_log_position; 

°¢ ¿É¼ÇÀÇ ÃÖ´ë ±æÀÌ´Â ´ÙÀ½°ú °°´Ù.
MASTER_HOST	60
MASTER_USER	16
MASTER_PASSWORD	32
MASTER_LOG_FILE	255

1.2.9. SLAVE ¾²·¹µå ½ºÅ¸Æ®

slave ¾²·¹µå¸¦ ½ºÅ¸Æ®ÇÑ´Ù.
slave mysql > START SLAVE;

1.2.10. SUCCESS CERTIFICATION

mysql/data/slave.errÀ» È®ÀÎÇÏ¿© ´ÙÀ½°ú °°Àº ¸Þ½ÃÁö°¡ ÀÖÀ¸¸é ¼º°øÀûÀ¸·Î ¼³Á¤µÈ °ÍÀÌ´Ù.
Slave I/O thread: connected to master 'user_name@user_host:3306',  replication started in log 'FIRST' at position 4
 

1.3. How to Set Up Dual-Master Replication


¿ì¼± ÀÌÈÄ¿¡¼­´Â Áö±Ý±îÁö master ¶ó°í ĪÇß´ø ¼­¹ö¸¦ mysql1 ¼­¹ö¶ó°í ÇÏ°í, slave¶ó ĪÇß´ø ¼­¹ö¸¦ mysql2 ¼­¹ö¶ó ÇÏ°Ú´Ù. µà¾ó ¸¶½ºÅÍ ¸®Çø®ÄÉÀ̼ÇÀ» ±¸ÃàÇÒ µÎ ´ëÀÇ ¼­¹ö¿¡´Â µ¿ÀÏ ¹öÀüÀÇ ÃֽŠMySQLÀÌ ¼³Ä¡µÇ¾î ÀÖÀ¸¸ç, Master-Slave ¸®Çø®ÄÉÀ̼ÇÀÌ ±¸ÃàµÈ »óÅ¿¡ ÀÖ´Ù°í °£ÁÖÇÑ´Ù.

ÀÌ¹Ì ¾Õ¿¡¼­ ¸®Çø®ÄÉÀÌ¼Ç ±¸Ãà¿¡ ´ëÇØ ÀÚ¼¼È÷ ¼³¸íÇÏ¿´À¸¹Ç·Î, °úÁ¤¿¡ ´ëÇؼ­¸¸ ±â¼úÇϱâ·Î ÇÏ°Ú´Ù.

1.3.1. SLAVE STOP

mysql2 ¼­¹ö·Î À̵¿ÇÑ ÈÄ, mysql2 ¼­¹öÀÇ mysql ±¸µ¿À» ¸ØÃá´Ù.
mysql2 shell > /etc/init.d/mysqld stop

1.3.2. SLAVE LOG DELETE

mysql2 ¼­¹öÀÇ -bin log¸¦ »èÁ¦ÇÑ´Ù.

1.3.3. SLAVE RESTER

mysql2 ¼­¹öÀÇ mysqlÀ» ±¸µ¿½ÃŲ´Ù.
mysql2 shell > /etc/init.d/mysqld start

1.3.4. GRANT REPLICATION SLAVE

d. mysql2 ¼­¹ö¿¡¼­ GRANT REPLICATION SLAVE¸í·ÉÀ» ½ÇÇàÇÑ´Ù. Dual-Master¶õ °ÍÀÌ ¼­·Î°¡ ¼­·ÎÀÇ masterÀÌÀÚ slave°¡ µÇ´Â °ÍÀ̹ǷÎ, ÀÌÀüÀÇ ¼³Ä¡¿¡¼­ slave¿´´ø mysql2°¡ mysql1 ¼­¹öÀÇ À¯Àú¸¦ slave À¯Àú·Î °®°Ô µÈ´Ù.
mysql2 mysql > GRANT REPLICATION SLAVE ON *.*
            -> TO 'users_name'@'users_host' IDENTIFIED BY 'users_password';

1.3.5. MASTER SETUP

ÀÌÁ¦ mysql1 ¼­¹ö·Î À̵¿ÇÏ¿©, ¼³Á¤À» °è¼ÓÇÑ´Ù. ¿ì¼±, mysql1 ¼­¹öÀÇ mysql ±¸µ¿À» ¸ØÃá´Ù.
mysql1 shell > /etc/init.d/mysqld stop

1.3.6. MASTER CONFIGURATION

mysql1 ¼­¹öÀÇ /etc/my.cnf ÆÄÀÏÀ» ¼öÁ¤ÇÑ´Ù. mysqld ¼½¼ÇÀ¸·Î °¡¼­ mysql2 ¼­¹ö¸¦ ¸¶½ºÅÍ·Î °£ÁÖÇϵµ·Ï Á¤º¸¸¦ Ãß°¡ÇÑ´Ù.
[mysqld]
server-id  = 1		<= ±×´ë·Î µÎ°í, ¾Æ·¡ ³»¿ëÀ» Ãß°¡ÇÑ´Ù.
master-host	= users_host
master-port	= 3306
master-user	= users_name
master-password	= users_password

1.3.7. MASTER START

mysql1 ¼­¹öÀÇ mysqlÀ» ±¸µ¿½ÃŲ´Ù.
mysql1 shell > /etc/init.d/mysqld start

1.3.8. SUCCESS CERTIFICATION

mysql/data/mysql1.errÀ» È®ÀÎÇÏ¿© ´ÙÀ½°ú °°Àº ¸Þ½ÃÁö°¡ ÀÖÀ¸¸é ¼º°øÀûÀ¸·Î ¼³Á¤µÈ °ÍÀÌ´Ù.
Slave I/O thread: connected to master 'ccotti@222.112.137.172:3306',  replication started in log 'FIRST' at position 4

Áö±Ý±îÁö º°´Ù¸¥ ¹®Á¦¾øÀÌ ¼³Ä¡¸¦ ÁøÇàÇÏ¿´´Ù¸é, °¢ ¼­¹öÀÇ mysql ¸ð´ÏÅÍ¿¡¼­ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇÏ°í, µÎ ¼­¹ö°¡ ¼­·Î ¿¬µ¿ÀÌ µÇ´Â °ÍÀ» È®ÀÎÇÒ ¼ö ÀÖÀ» °ÍÀÌ´Ù.


1.4. Àå¾Öº¹±¸


À§ÀÇ ¼³Á¤¿¡¼­ µÎ ´ëÀÇ ¼­¹ö Áß ÇÑ ´ë°¡ Àå¾Ö¸¦ ÀÏÀ¸Å°´Â °æ¿ì ÇÑ ¼­¹ö¸¦ ¸®ºÎÆÃÇÑ´Ù°í °¡Á¤ÇÒ ¶§, º°µµÀÇ ¼³Á¤ÀÌ ¾ø´Ù¸é ±âÁ¸ÀÇ MySQL ¸®Çø®ÄÉÀÌ¼Ç ±¸¼º¿¡¼­´Â µÎ ¼­¹ö °£ÀÇ µ¿±âÈ­°¡ ¿øÈ°È÷ ÀϾÁö ¾Ê¾Ò´Ù. ±×·± °æ¿ì ´ÙÀ½À» ¼ø¼­´ë·Î ÁøÇàÇϸç, Àå¾Ö¸¦ º¹±¸ÇÒ ¼ö ÀÖ´Ù. ¿ì¼± mysql1 ¼­¹ö¸¦ Àç½ÃÀÛÇØ¾ß ÇÑ´Ù°í °¡Á¤ÇÏÀÚ.

1. mysql1ÀÇ mysql/data/ ÀÇ mysql1-bin.*¸¦ Áö¿î´Ù.

2. mysql1ÀÇ mysqld¸¦ ½ÃÀÛÇÑ´Ù.
mysql1 shell > /etc/init.d/mysqld start

3. mysql2ÀÇ mysql ¸ð´ÏÅÍ¿¡¼­ ´ÙÀ½ ¸í·É¾î¸¦ ½ÇÇàÇÑ´Ù.
mysql2 mysql > slave stop;
mysql2 mysql > slave reset;
mysql2 mysql > slave start;

1.5. Âü°í

¡Ü master¿Í slave µ¥ÀÌÅÍ ÀÏÄ¡ ¹æ¹ý
- master mysqlÀ» Á¤Áö½ÃÅ°°í ´ë»ó ÆÄÀϵéÀ» ¹é¾÷(º¹»ç) - master mysqlÀ» ±¸µ¿
-> ÀÌ ÈÄ º¯°æ»çÇ×µéÀÌ bin-log¿¡ ±â·ÏµÊ
- slave¿¡ ¹é¾÷ÇÑ DB ÆÄÀϵéÀ» º¹»ç ÈÄ ±¸µ¿
-> masterÀÇ bin-log¸¦ Âü°íÇÏ¿© µ¥ÀÌÅÍ ÀÏÄ¡µÊ ¡Ø ÀÌ ¶§, º¹»çÇÑ ÆÄÀÏÀÇ ¼ÒÀ¯ÀÚ(mysqlÀÎÁö?) È®ÀΠöÀú ¡Ø my.cnf ¼³Á¤¿¡¼­ ƯÁ¤ DB¸¦ ¼±ÅÃÇÑ °æ¿ì master¿Í slave ¸ðµÎ µ¿ÀÏÇÏ°Ô ¼³Á¤ÇØ¾ß ÇÔ
(ÇÑ ÂÊÀº ¼³Á¤ÇÏÁö ¾Ê°í ÇÑ ÂÊÀº ¼³Á¤ÇÑ °æ¿ì ¿Àµ¿ÀÛ)
¡Ø my.cnf ÁÖÀÇ»çÇ× : mysql_safe ½ÇÇà ½Ã DB_DIR ¿É¼Ç¿¡ µû¶ó ºÒ·¯¿À´Â À§Ä¡ ´Þ¶óÁü
¡Ü slave¿¡¼­ 'LOAD TABLE FROM MASTER' ³ª 'LOAD DATA FROM MASTER' ¸í·ÉÀ»
»ç¿ëÇϱâ À§Çؼ­´Â replication °èÁ¤¿¡ ´ÙÀ½Àº ±ÇÇÑ Ãß°¡ ÇÊ¿ä
- SUPER, RELOAD, SELECT ±ÇÇÑÀ» replication °èÁ¤¿¡ ºÎ¿©
¡Ü ´ÙÀ½ ¸í·ÉÀ» ÅëÇØ mysqlÀÇ ³»ºÎcache¸¦ clear½ÃÅ°°í ¾²±â ¹æÁö °¡´É
¡Ø mysql ±âº» Å×À̺íÀÎ MyISAM Å×À̺íÀ» »ç¿ëÇÒ °æ¿ì - mysql> FLUSH TABLES WITH READ LOCK;
¡Ü ¾²±â ¹æÁö ÇØÁ¦ ¸í·É
- mysql> UNLOCK TABLES;
¡Ü slaveÀÇ mysqlÀ» replication ¹ÌÀû¿ëÇÏ°í ±¸µ¿ ¹æ¹ý
- /usr/local/bin/mysqld_safe --skip-slave-start ¡Ü slave µ¿ÀÛ ±¸µ¿ ¹æ¹ý - mysql> start slave;
¡Ø slave ¼³Á¤ ¹ÌÀÎ½Ä µîÀÇ ¹®Á¦ ¹ß»ý ½Ã
mysql> change master to ¸í·ÉÀ» »ç¿ëÇÏ¿© ¼³Á¤
¡Ü replication Á¤»óµ¿ÀÛ È®ÀÎ

- mysql> show processlist;
¶Ç´Â mysql> show processlist\G ; »ó¼¼ÇÑ ³»¿ë È®ÀÎ
- mysql> show slave status;
¶Ç´Â mysql> show slave status\G ; »ó¼¼ÇÑ ³»¿ë È®ÀÎ ¶Ç´Â mysql> show master status;
- error ·Î±× È®ÀÎ


ID
Password
Join
How sharper than a hound's tooth it is to have a thankless serpent.


sponsored by andamiro
sponsored by cdnetworks
sponsored by HP

Valid XHTML 1.0! Valid CSS! powered by MoniWiki
last modified 2006-11-14 17:37:19
Processing time 0.0091 sec