MySQL ReplicationÀ» ÀÌ¿ëÇÏ¿© DBMS ´Ü¹æÇâ ÀÌÁßÈÇϱâ
À¥¼¹ö ºÎÇÏ·Î ÀÎÇØ L4¸¦ ÀÌ¿ëÇÏ¿© ·Îµå¹ë·±½ÌÀ¸·Î À¥¼¹öÀÇ ºÎÇϸ¦ ÇØ°áÇÏ¿´Áö¸¸, DB ¼¹öÀÇ ºÎÇÏ·Î ÀÎÇÏ¿© »çÀÌÆ®°¡ ´À¸®°Ô ¿¸®´Â Çö»óÀÌ ¹ß»ýÇÏ°Ô µÇ¾ú½À´Ï´Ù
DB ¼¹ö¸¦ ÀÌÁßÈÇÏ´Â ¹æ¹ýÀº ¾øÀ»±î ÇÏ¿© ã¾Æº¸´Ï MySQLÀÇ ¸®Çø®ÄÉÀ̼ÇÀ̶ó´Â ±â´ÉÀÌ ÀÖ´õ±º¿ä ÀÌ ±â´ÉÀ» ÀÌ¿ëÇϸé DB¸¦ ÀÌÁßÈ ÇÒ ¼ö Àִ´ٴ °ÍÀ» ¾Ë°Ô µÇ¾ú½À´Ï´Ù
À̹ø Æ÷½ºÆÿ¡¼´Â MySQLÀÇ ¸®Çø®ÄÉÀ̼ÇÀº ¹«¾ùÀÌ°í, ¸®Çø®ÄÉÀ̼ÇÀ» ÀÌ¿ëÇÑ DB¸¦ ÀÌÁßÈÇÏ´Â ¹æ¹ýÀ» ¾Ë¾Æº¸µµ·Ï ÇÏ°Ú½À´Ï´Ù.
MySQL Replication(º¹Á¦)¶õ? |
¸®Çø®ÄÉÀ̼Ç(Replication)Àº º¹Á¦¸¦ ¶æÇϸç 2´ë ÀÌ»óÀÇ DBMS¸¦ ³ª´²¼ µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â ¹æ½ÄÀ̸ç, »ç¿ëÇϱâ À§ÇÑ ÃÖ¼Ò ±¸¼ºÀº Master / Slave ±¸¼ºÀ» ÇÏ¿©¾ß µË´Ï´Ù.
Master DBMS ¿ªÇÒ :
À¥¼¹ö·Î ºÎÅÍ µ¥ÀÌÅÍ µî·Ï/¼öÁ¤/»èÁ¦ ¿äû½Ã ¹ÙÀ̳ʸ®·Î±×(Binarylog)¸¦ »ý¼ºÇÏ¿© Slave ¼¹ö·Î Àü´ÞÇÏ°Ô µË´Ï´Ù
(À¥¼¹ö·Î ºÎÅÍ ¿äûÇÑ µ¥ÀÌÅÍ µî·Ï/¼öÁ¤/»èÁ¦ ±â´ÉÀ» ÇÏ´Â DBMS·Î ¸¹ÀÌ »ç¿ëµË´Ï´Ù)
Slave DBMS ¿ªÇÒ :
Master DBMS·Î ºÎÅÍ Àü´Þ¹ÞÀº ¹ÙÀ̳ʸ®·Î±×(Binarylog)¸¦ µ¥ÀÌÅÍ·Î ¹Ý¿µÇÏ°Ô µË´Ï´Ù
(À¥¼¹ö·Î ºÎÅÍ ¿äûÀ» ÅëÇØ µ¥ÀÌÅ͸¦ ºÒ·¯¿À´Â DBMS·Î ¸¹ÀÌ »ç¿ëµË´Ï´Ù)
MySQL Replication(º¹Á¦) »ç¿ë¸ñÀû |
MySQL ¸®Çø®ÄɼÇ(Replication)Àº »ç¿ë¸ñÀûÀº Å©°Ô ½Ç½Ã°£ Data ¹é¾÷°ú ¿©·¯´ëÀÇ DB¼¹öÀÇ ºÎÇϸ¦ ºÐ»ê ½Ãų¼ö ÀÖ½À´Ï´Ù
1, µ¥ÀÌÅÍÀÇ ¹é¾÷
¿¹·Î Master ¼¹ö¸¦ µ¥ÀÌÅÍÀÇ ¿øº»¼¹ö, Slave¼¹ö¸¦ ¹é¾÷¼¹ö·Î ÁöĪÇÏ°Ú½À´Ï´Ù
¸ÕÀú Master ¼¹ö¿¡ DBMSÀÇ µî·Ï/¼öÁ¤/¾÷µ¥ÀÌÅÍ°¡ »ý±â´Â Áï½Ã Slave ¼¹öÀÇ º¯°æµÈ µ¥ÀÌÅ͸¦ Àü´ÞÇÏ°Ô µË´Ï´Ù ÀÌ·¯ÇÑ °úÁ¤À¸·Î µ¥ÀÌÅÍÀÇ ¹é¾÷À» ÇÒ¼ö ÀÖÀ¸¸ç, ¶ÇÇÑ Master ¼¹öÀÇ Àå¾Ö°¡ »ý°åÀ» °æ¿ì Slave ¼¹ö·Î º¯°æÇÏ¿© »ç¿ëÇÒ¼ö ÀÖ½À´Ï´Ù.
±×¸²À¸·Î Ç¥ÇöÇÑ´Ù¸é ¸ÕÀú »ç¿ëÀÚ°¡ »ç¿ëÇϴµ¥ ¹ß»ýÇÏ´Â Äõ¸®¸¦ Master ¼¹ö¿¡ ¿äûÇϸç, Master ¼¹öÀÇ ¹ß»ýµÈ Äõ¸®¸¦ Slave ¼¹ö·Î Àü´ÞÇÏ°ÔµÇ¾î ¹é¾÷ÀÇ ¿ëµµ·Î »ç¿ëÇÒ¼ö ÀÖ½À´Ï´Ù
2. DBMSÀÇ ºÎȺлê
»ç¿ëÀÚÀÇ ÆøÁÖ·Î ÀÎÇØ 1´ëÀÇ DB¼¹ö·Î °¨´çÇÒ¼ö ¾øÀ»¶§, MySQL ¸®Çø®ÄÉÀ̼Ç(Replication)À» ÀÌ¿ëÇÏ¿© °°Àº DB µ¥ÀÌÅ͸¦ ¿©·¯´ë¸¦ ¸¸µé¼ö Àֱ⿡ ºÎÇϸ¦ ºÐ»êÇϼö ÀÖ½À´Ï´Ù
±×¸²À¸·Î Ç¥ÇöÇÑ´Ù¸é Master ¼¹ö¸¦ µî·Ï/¼öÁ¤/»èÁ¦¸¦ »ç¿ëÇÏ´Â ¼¹ö·Î »ç¿ëÇÏ°í, Slave ¼¹ö¸¦ µ¥ÀÌÅ͸¦ Àд¿뵵·Î »ç¿ëÇÏ°Ô µÇ¸é DBMSÀÇ ºÎÇϸ¦ ºÐ»êÇÏ´Â ¿ëµµ·Î »ç¿ëÇÒ ¼ö ÀÖ°Ô µË´Ï´Ù
MySQL Replication ÁÖÀÇ»çÇ× |
MySQL ReplicationÀ» »ç¿ë½Ã ´ÙÀ½°ú °°Àº ÁÖÀÇÇÏ¿©¾ß µÉ »çÇ×µéÀÌ ÀÖ½À´Ï´Ù ¹Ýµå½Ã Çʵ¶ ÈÄ ÇϽðí ÁøÇàÇϽñ⠹ٶø´Ï´Ù.
1. ȣȯ¼ºÀ» À§ÇØ ReplicationÀ» »ç¿ëÇÏ´Â MySQLÀÇ µ¿ÀÏÇÏ°Ô ¸ÂÃߴ°ÍÀÌ ÁÁ½À´Ï´Ù
2. ReplicationÀ» »ç¿ëÇϱ⿡ MySQL ¹öÀüÀÌ ´Ù¸¥ °æ¿ì Slave ¼¹ö°¡ »óÀ§ ¹öÀü ÀÌ¿©¾ß ÇÕ´Ï´Ù
3. ReplicationÀ» °¡µ¿½Ã¿¡ Master ¼¹ö, Slave ¼øÀ¸·Î °¡µ¿½ÃÄÑ¾ß ÇÕ´Ï´Ù
MySQL Replication ±¸¼ºÇϱâ |
À̹ø ¸®Çø®ÄÉÀÌ¼Ç Æ÷½ºÆÿ¡¼´Â 2´ëÀÇ DBMS¸¦ ÀÌ¿ëÇÏ¿© µî·Ï/¼öÁ¤/»èÁ¦¸¦ ÇÏ´Â Master ¼¹ö¿Í Select ¸¦ »ç¿ëÇÏ´Â Slave ¼¹ö·Î ÇÏ°ÚÀ¸¸ç, ±¸¼ºÀº ´ÙÀ½°ú °°½À´Ï´Ù
À§ ±×¸²°ú °°ÀÌ À¥¼¹ö¿¡¼ µ¥ÀÌÅÍ µî·Ï/¼öÁ¤/»èÁ¦´Â Master ¼¹ö·Î ±¸¼ºÇÏ°í, µ¥ÀÌÅ͸¦ ÀÐÀ»°æ¿ì Slave ¼¹ö·Î ±¸¼ºÇÏ¿© MySQL DBMS¸¦ ÀÌÁßÈ Çϵµ·Ï ÇÏ°Ú½À´Ï´Ù.
MySQL Replication ¼³Á¤Çϱâ - (Master ¼¹ö) |
ÀÌÁ¦ MySQL ReplicationÀ» ¼³Á¤ Çغ¸µµ·Ï ÇÏ°ÚÀ¸¸ç, ¸ÕÀú Master ¼¹öÀÇ ¼³Á¤ºÎÅÍ ÇÏ°Ú½À´Ï´Ù
MySQL ¸®Çø®ÄÉÀ̼ÇÀ» »ç¿ëÇϱâ À§Çؼ± ¸ÕÀú DB, °èÁ¤, ¸®Çø®ÄÉÀÌ¼Ç °èÁ¤À» »ý¼ºÇÏ¿© µË´Ï´Ù
±¸¼ºÁ¤º¸´Â ¾Æ·¡¿Í °°ÀÌ ÇÏ°Ú½À´Ï´Ù.
[Master ¼¹ö DB, °èÁ¤Á¤º¸]
IP : 192.168.65.148(Master), 192.168.65.149(Slave)
DadaBases : repl_db
ID : user1
PW : test123
[Replication °èÁ¤ Á¤º¸]
IP : 192.168.65.148 - (Master)
ID : repl_user
PW : test456
- Master ¼¹ö¿¡ µ¥ÀÌÅ͸¦ Slave ¼¹ö·Î º¹Á¦Çϱâ À§Çؼ± MySQL °èÁ¤ÀÌ ÇÊ¿äÇÕ´Ï´Ù
- MySQL root °èÁ¤À¸·Î »ç¿ëÇϴ°ÍÀº º¸¾È»ó ÁÁÁö ¾Ê±â ¶§¹®¿¡ º¹Á¦°èÁ¤À» »ý¼ºÇϴ°ÍÀÌ ÁÁ½À´Ï´Ù
1. MySQL DB, °èÁ¤»ý¼º ¹× ±ÇÇѼ³Á¤
1) DB »ý¼º
1 | mysql> create database repl_db default character set utf8; |
2) °èÁ¤»ý¼º
1 | mysql> create user user1@ '%' identified by 'test123' ; |
3) ±ÇÇѺο©
1 | mysql> grant all privileges on repl_db.* to user1@ '%' identified by 'test123' ; |
2. ¸®Çø®ÄÉÀÌ¼Ç °èÁ¤»ý¼º
1 | mysql> grant replication slave on *.* to 'repl_user' @ '%' identified by 'test456' ; |
3. MySQL ¼³Á¤ - my.cnf
1 2 3 4 | vi /etc/my.cnf [mysqld] log -bin=mysql-bin server-id=1 |
óÀ½ ¼³Ä¡½Ã À§¿Í °°Àº ¼³Á¤À̵Ǿî ÀÖÀ¸¸ç, ¾ø´Ù¸é »õ·Î Ãß°¡ÇÏ½Ã¸é µË´Ï´Ù
4. MySQL Àç½ÃÀÛ
1 | # service mysqld restart |
5. Master ¼¹ö Á¤º¸ È®ÀÎ
1 2 3 4 5 6 7 | mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 1487 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
* File : MySQL ·Î±×ÆÄÀÏ
* Position : ·Î±× ÆÄÀϳ» ÀÐÀ» À§Ä¡
* Binlog_Do_DB : ¹ÙÀ̳ʸ®(Binary)·Î±× ÆÄÀÏ(º¯°æµÈ À̺¥Æ® Á¤º¸°¡ ½×ÀÌ´Â ÆÄÀÏ)
* Binlog_Ignore_DB : º¹Á¦ Á¦¿Ü Á¤º¸
MySQL Replication ¼³Á¤Çϱâ - (Slave ¼¹ö) |
Slave ¼¹ö¼³Çϱ⿡ ¾Õ¼ ¸ÕÀú °èÁ¤À» »ý¼ºÇÏ°Ú½À´Ï´Ù
MySQL DB, °èÁ¤»ý¼º ¹× ±ÇÇѼ³Á¤
1) DB »ý¼º
1 | mysql> create database repl_db default character set utf8; |
2) °èÁ¤»ý¼º
1 | mysql> create user user1@ '%' identified by 'test123' ; |
3) ±ÇÇѺο©
1 | mysql> grant all privileges on repl_db.* to user1@ '%' identified by 'test123' ; |
ÀÌÁ¦ Slave ¼¹ö ¼³Á¤À» ÇÏ´Â ¹æ¹ý 2°¡Áö ¹æ¹ýÀÌ ÀÖ½À´Ï´Ù
ù¹ø°´Â mysql¿¡ µé¾î°¡¼ ¼³Á¤ÇÏ´Â ¹æ¹ý°ú mysql ¼³Á¤ÆÄÀÏ(my.cnf)¿¡¼ ¼³Á¤ÇÏ´Â ¹æ¹ýÀÌ ÀÖÀ¸¸ç, ¸ÕÀú mysql¿¡¼ ¼³Á¤ÇÏ´Â ºÎºÐºÎÅÍ ¾Ë¾Æº¸µµ·Ï ÇÏ°Ú½À´Ï´Ù
1. MySQL¿¡ Á¢¼ÓÇÏ¿© ¼³Á¤
1) MySQL ¼³Á¤ - my.cnf
1 2 3 4 5 | # vi /etc/my.cnf [mysqld] server-id=2 replicate- do -db= 'repl_db' |
Server-id : Master ¼¹öÀÇ server-id¸¦ Á¦¿ÜÇÏ°í 1~(2^32)-1³»ÀÇ ¼ýÀÚ·Î ¼³Á¤ÇÏ½Ã¸é µË´Ï´Ù
replicate-do-db : º¹Á¦ÇÏ°íÀÚ ÇÏ´Â µ¥ÀÌÅͺ£À̽º¸¦ ÀǹÌÇϸç 2°³ÀÌ»óÀÇ µ¥ÀÌÅͺ£À̽º¸¦ ÇÒ°æ¿ì replicate-do-db¸¦ Ãß°¡ÇÏ½Ã¸é µË´Ï´Ù
2) MySQL º¹¿ø
1) Master DBMS¿¡¼ º¹Á¦ÇÒ µ¥ÀÌÅͺ£À̽º¸¦ dumpÇÏ¿© º¹¿øÇÕ´Ï´Ù
3) Master ¼¹ö·Î ¿¬°áÇϱâ À§ÇÑ ¼³Á¤
1 2 3 4 5 6 | mysql> change master to master_host= '192.168.65.148' , master_user= 'repl_user' , master_password= 'test456' , master_log_file= 'mysql-bin.000010' , master_log_pos=1487; |
MASTER_HOST : Mster ¼¹ö IP ÀÔ·Â
MASTER_USER : ¸®Çø®ÄÉÀÌ¼Ç ID
MASTER_PASSWORD : ¸®Çø®ÄÉÀÌ¼Ç PW
MASTER_LOG_FILE : MASTER STATUS ·Î±×ÆÄÀϸí
MASTER_LOG_POS : MASTER STATUS¿¡¼ position °ª
4) MySQL Àç½ÃÀÛ
1 | # service mysqld restart |
2. MySQL¿¡ my.cnf¿¡¼ ¼³Á¤Çϱâ
1) MySQL ¼³Á¤ - my.cnf
1 2 3 4 5 6 7 | [mysqld] replicate- do -db= 'repl_db' master-host=192.168.65.148 master-user=repl_user master-password=test456 master-port=3306 server-id=2 |
replicate-do-db : º¹Á¦ÇÏ°íÀÚ ÇÏ´Â µ¥ÀÌÅͺ£À̽º¸¦ ÀǹÌÇϸç 2°³ÀÌ»óÀÇ µ¥ÀÌÅͺ£À̽º¸¦ ÇÒ°æ¿ì replicate-do-db¸¦ Ãß°¡ÇÏ½Ã¸é µË´Ï´Ù
2) MySQL Àç½ÃÀÛ
1 | # service mysqld restart |
MySQL Replication »óÅ ȮÀÎÇϱâ |
MySQL ¸®Çø®ÄÉÀ̼Ç(Replication)ÀÌ Á¤»óÀûÀ¸·Î ¿Ï·áµÇ¾ú´Ù¸é ÀÌÁ¦ »óŸ¦ È®ÀÎÇØ¾ß µÇ°ÚÁÒ? ´ÙÀ½°ú °°ÀÌ È®ÀÎÇÏ½Ã¸é µË´Ï´Ù.
1. Master ¼¹ö »óź¸±â
1) ¾²·¹µå »óź¸±â
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> show processlist\G *************************** 1. row *************************** Id: 1 User: repl_user Host: 192.168.65.149:38488 db: NULL Command: Binlog Dump Time: 2434 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 2 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 2 rows in set (0.00 sec) |
Master ¼¹ö¿¡¼ À§ ³»¿ë°ú °°ÀÌ ¸í·É¾î¸¦ ÀÔ·ÂÇϸé Id:1 ¾²·¹µåÀÇ Slave¼¹ö(192.168.65.149)ÀÇ repl_user°èÁ¤À¸·Î ¿¬°áµÇ¾î ÀÖ´Â °ÍÀ» È®ÀÎÇϽǼö ÀÖ½À´Ï´Ù
2. Slave ¼¹ö »óź¸±â
1) ¾²·¹µå »óź¸±â
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> show processlist\G; *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 4294967261 State: Has read all relay log ; waiting for the slave I/O thread to update it Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 90 State: Waiting for master to send event Info: NULL |
¾²·¹µå1(ld: 1)¿¡¼´Â Master ¼¹ö¿Í Åë½ÅÇϱâ À§ÇÑ ¾²·¹µåÀ̸ç, ½º·¹µå2(ld: 2)´Â ¾÷µ¥ÀÌÆ®µÈ ³»¿ëÀ» ó¸®Çϱâ À§ÇÑ SQL ¾²·¹µå ÀÔ´Ï´Ù ÀÌ·¯ÇÑ 2°³ÀÇ ¾²·¹µå¿¡¼´Â ¿À·ù°¡ ¹ß»ýÇÏ¸é ¾ÈµÈ´Ù°í ÇÕ´Ï´Ù.
2) ¾²·¹µå ÁÖ¿äÀÎÀÚ »óź¸±â
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.65.148 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 434 Relay_Log_File: slave-relay-bin.000042 Relay_Log_Pos: 419 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: repl_db,repl_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: 434 Relay_Log_Space: 419 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 1 row in set (0.00 sec) |
Slave_IO_State : Master¼¹öÀÇ ¿¬°áÀ» ½ÃµµÇÏ°í Master¼¹ö·Î ºÎÅÍ À̺¥Æ®¸¦ ±â´Ù¸®¸ç, À翬°áÇÏ´ÂÁö¿¡ ´ëÇØ ¾Ë·ÁÁÝ´Ï´Ù
Master_Host : ¿¬°áµÈ Master¼¹ö È£½ºÆ® ÀÔ´Ï´Ù
Master_User : Master¼¹ö ¿¬°áÇϴµ¥ »ç¿ëµÇ´Â »ç¿ëÀÚ ÀÔ´Ï´Ù
Master_Port : Master¼¹ö ¿¬°áÇϴµ¥ »ç¿ëµÇ´Â Æ÷Æ® ÀÔ´Ï´Ù
Connect_Retry : --master-connect-retry ¿É¼ÇÀÇ ÇöÀç °ª ÀÔ´Ï´Ù
Master_Log_File : I/O ¾²·¹µå¿¡¼ ÇöÀç Àаí ÀÖ´Â ¹ÙÀ̳ʸ® ·Î±×ÆÄÀÏ À̸§ ÀÔ´Ï´Ù
Read_Master_Log_Pos : I/O ¾²·¹µå¿¡¼ ÇöÀç Master ¼¹öÀÇ ¹ÙÀ̳ʸ® ·Î±×¿¡¼ ÀÐÀº °÷°¡ÁöÀÇ À§Ä¡ ÀÔ´Ï´Ù
Relay_Log_File : SQL ¾²·¹µå¿¡¼ ÇöÀç relay ·Î±×ÆÄÀÏ À̸§ ÀÔ´Ï´Ù
Relay_Log_Pos : SQL ¾²·¹µå¿¡ ÀÇÇØ Relay ·Î±×¿¡¼ ÀÐ°í ½ÇÇàÇÑ °÷±îÁöÀÇ À§Ä¡ ÀÔ´Ï´Ù
Relay_Master_Log_File : SQL ½º·¹µå¿¡ ÀÇÇØ ½ÇÇàµÈ ÃÖ±Ù Master¼¹öÀÇ ¹ÙÀ̳ʸ® ·Î±× ÆÄÀÏÀÇ À̸§ÀÔ´Ï´Ù
Slave_IO_Running : I/O ¾²·¹µå°¡ ½ÃÀ۵Ǿî Master¼¹öÀÇ ¼º°øÀûÀ¸·Î ¿¬°áµÇ¾îÀÖ´ÂÁö ¿©ºÎ ¿©ºÎ ÀÔ´Ï´Ù
Slave_SQL_Running : SQL ¾²·¹µå°¡ ½ÃÀ۵Ǿú´ÂÁöÀÇ ¿©ºÎ ÀÔ´Ï´Ù
Replicate_Do_DB : Master¼¹ö¿¡¼ ¾÷µ¥ÀÌÆ®µÈ µ¥ÀÌÅ͸¦ ¹Ý¿µµÉ DB ÀÔ´Ï´Ù
Replicate_Ignore_DB : »ý·«.
Replicate_Do_Table : »ý·«.
Replicate_Ignore_Table : »ý·«.
Replicate_Wild_Do_Table : »ý·«.
Replicate_Wild_Ignore_Table : »ý·«.
Last_Errno : °¡Àå ÃÖ±Ù¿¡ »ç¿ëµÈ Äõ¸®ÀÇ ¿¡·¯¸Þ½ÃÁöÀÇ ¹øÈ£·Î ¸®Åϵ˴ϴÙ
Last_Error : °¡Àå ÃÖ±Ù¿¡ »ç¿ëµÈ Äõ¸®ÀÇ ¿¡·¯¸Þ½ÃÁöÀÇ ¹øÈ£·Î ¸®Åϵ˴ϴÙ
Skip_Counter : »ý·«.
Exec_Master_Log_Pos : Master¼¹öÀÇ ¹ÙÀ̳ʸ® ·Î±×ÀÇ Relay_Master_Log_File·Î ºÎÅÍ SQL¾²·¹µåÀÇ ÀÇÇØ ¸¶Áö¸· À̺¥Æ®ÀÇ À§Ä¡ ÀÔ´Ï´Ù
Relay_Log_Space : Á¸ÀçÇÏ´Â ¸ðµç Relay ·Î±¸¿ì¤¿ Àüü »çÀÌÁî ÀÔ´Ï´Ù
Until_Condition : »ý·«.
Until_Log_File : »ý·«.
Until_Log_Pos : »ý·«.
Master_SSL_Allowed : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Master_SSL_CA_File : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Master_SSL_CA_Path : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Master_SSL_Cert : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Master_SSL_Cipher : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Master_SSL_Key : Master¼¹ö¿¡ ¿¬°áÇϱâ À§ÇØ Slave¿¡ ÀÇÇØ »ç¿ëµÈ SSL ÆĶó¹ÌÅÍ ÀÔ´Ï´Ù
Seconds_Behind_Master : Master¼¹ö¿¡¼ ½ÇÇàµÈ À̺¥Æ®ÀÇ Å¸ÀÓ½ºÅÆÇÁ ÀÌÈÄ °æ°úµÈ ½Ã°£(ÃÊ ´ÜÀ§)ÀÇ ¼ö ÀÔ´Ï´Ù
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.65.148 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 98 Relay_Log_File: slave-relay-bin.000010 Relay_Log_Pos: 370 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: repl_db,repl_db,repl_db,repl_db Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can' t create database 'repl_db' ; database exists ' on query. Default database: ' repl_db '. Query: ' create database repl_db default character set utf8' Skip_Counter: 0 Exec_Master_Log_Pos: 233 Relay_Log_Space: 1123 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: NULL 1 row in set (0.00 sec) ERROR: No query specified |
°£È¤ À§¿Í °°ÀÌ ¿¡·¯°¡ ¹ß»ýÇÏ¸é¼ Slave ¼¹ö°¡ ÀÛµ¿ÀÌ À߾ȵǴ °æ¿ì°¡ ÀÖ½À´Ï´Ù
À§¿Í °°Àº °æ¿ì´Â Slave ¼¹ö¿¡ ¿¡·¯°¡ ¹ß»ýÇÏ¸é ¿¡·¯°¡ ¹ß»ýÇÏ¿´´ø ½ÃÁ¡À¸·Î ºÎÅÍMaster ¼¹ö·Î ºÎÅÍ °»½ÅµÈ Äõ¸®¸¦ ½ÇÇàÇÏÁö ¾Ê°ÔµÇ¸ç, ÀÌ°æ¿ì ¿¡·¯¸¦ ³Ñ°Ü¾ß ´ÙÀ½ Äõ¸®¸¦ ½ÇÇàÇÏ°Ô µË´Ï´Ù
1 2 3 | vi /etc/my.cnf [mysqld] slave-skip-errors=all |
À§¿Í °°Àº ¿É¼ÇÀ» Àá½Ã ¼³Á¤ÇÏ¿© ¹Ý¿µÇϽñ⠹ٶø´Ï´Ù. my.cnf¿¡¼ ¼³Á¤À» ¹Ý¿µÇϽŠÈÄ¿¡´Â MySQLÀ» Àç½ÃÀÛÇϽñ⠹ٶø´Ï´Ù