Ó²ÅÌÊý¾Ý»Ö¸´µ¼º½
RAIDÊý¾Ý»Ö¸´µ¼º½
 | ÍøÕ¾Ê×Ò³ | Êý¾Ý»Ö¸´×ÊÁÏ | Êý¾Ý»Ö¸´Èí¼þ | ×ÉѯÁôÑÔ | Êý¾Ý»Ö¸´²©¿Í | Êý¾Ý»Ö¸´ÂÛ̳ | 
Êý¾Ý»Ö¸´Èí¼þÏÂÔØ
¹«Ë¾¼ò½é Êý¾Ý»Ö¸´³É¹¦°¸Àý Êý¾Ý»Ö¸´±¨¼Û Êý¾Ý»Ö¸´Åàѵ Êý¾Ý»Ö¸´¼¼ÊõÎÄÕ Êý¾Ý»Ö¸´¿Í·þÖÐÐÄ Êý¾Ý»Ö¸´·þÎñÁªÏµ·½Ê½
ÄúÏÖÔÚµÄλÖ㺠±±ÑÇÊý¾Ý»Ö¸´¼¼ÊõÕ¾ >> Êý¾Ý»Ö¸´×ÊÁÏ >> ·þÎñÆ÷Óë´æ´¢¼¼Êõ >> ÎÄÕÂÕýÎÄ
MySQL¸´ÖÆÅäÖò½ÖèÎĵµ(Ö÷´Ó±¸·Ý»úÖÆ)            ¡¾×ÖÌ壺С ´ó¡¿
MySQL¸´ÖÆÅäÖò½ÖèÎĵµ(Ö÷´Ó±¸·Ý»úÖÆ)
×÷ÕߣºÎ´Öª    ÎÄÕÂÀ´Ô´£ºÍøÂç    µã»÷Êý£º    ¸üÐÂʱ¼ä£º2009-6-15

MySQL¸´ÖÆÅäÖò½ÖèÎĵµ
From CEWIKI
MySQL¸´ÖÆÅäÖò½Öè

±¾ÎĵµÖ÷Òª¶ÔÒ»¸öÖ÷·þÎñÆ÷£¬Ò»¸ö´Ó·þÎñÆ÷£¨¼ò³ÆÒ»Ö÷Ò»´Ó£©µÄ¸´ÖÆÅäÖò½Öè½øÐÐ˵Ã÷£¬Ò»Ö÷¶à´ÓµÄÅäÖ÷½·¨ÆäʵҲÊÇһģһÑùµÄ£¬Ö»²»¹ýÐèÒªÔö¼Ó¸ü¶àµÄ´Ó·þÎñÆ÷£¬Ôö¼ÓµÄ´Ó·þÎñÆ÷µÄÉèÖ÷½·¨¸úµÚÒ»¸ö´Ó·þÎñÆ÷ÍêÈ«Ò»Ñù¡£


Ŀ¼
•        1 µÚÒ»´ÎÅäÖÃÖ÷´Ó·þÎñÆ÷·½·¨
•        2 ÅäÖÃ˵Ã÷
•        3 Ö÷´Ó¸´ÖÆÅäÖ÷½·¨
•        4 ´´½¨¸´ÖÆÕʺÅ
•        5 ÔÚÖ÷·þÎñÆ÷µÄMySQLÅäÖÃÎļþÖÐÌí¼ÓÑ¡Ï´ò¿ª¶þ½øÖÆÈÕÖ¾¼Ç¼¹¦ÄÜ
•        6 ÔÚ´Ó·þÎñÆ÷µÄMySQLÅäÖÃÎļþÖÐÌí¼ÓÏà¹Ø¸´ÖÆÑ¡Ïî
•        7 ÖØÆô·þÎñÆ÷
•        8 ²ì¿´ÔËÐÐ״̬
•        9 ´ÓÒÑÓÐÖ÷·þÎñÆ÷ÅäÖÃһ̨еĴӷþÎñÆ÷
•        10 ÅäÖÃ˵Ã÷
•        11 Ö÷´ÓÅäÖ÷½·¨
•        12 ´ò¿ªÖ÷·þÎñÆ÷¶þ½øÖÆÈÕÖ¾¹¦ÄÜ
•        13 ´´½¨¸´ÖÆÕʺÅ
•        14 ±¸·ÝÖ÷·þÎñÆ÷µÄÊý¾ÝÎļþ
•        15 ±¸·ÝÖ÷·þÎñÆ÷µÄÊý¾ÝÎļþ
•        16 ¼Ç¼¸´ÖÆÆô¶¯¶Ïµã
•        17 ´ò°ü±¸·ÝÖ÷·þÎñÆ÷Êý¾ÝÎļþ
•        18 Ö÷·þÎñÆ÷±í½âËø
•        19 ÔÚ´Ó·þÎñÆ÷Éϻָ´Ö÷±¸·ÝÎļþ
•        20 Æô¶¯´Ó·þÎñÆ÷£¬ÉèÖø´Öƶϵã
•        21 ²ì¿´¸´ÖÆ״̬

µÚÒ»´ÎÅäÖÃÖ÷´Ó·þÎñÆ÷·½·¨
ÅäÖÃ˵Ã÷
ÕâÀïËù˵µÄ"µÚÒ»´ÎÅäÖÃÖ÷´Ó·þÎñÆ÷"Ö¸µÄÊÇ£¬ÒѾ­°²×°ºÃÁËMySQLÊý¾Ý¿â£¬µÚÒ»´ÎÅäÖÃÖ÷´Ó¸´Öƹ¦ÄÜ£¬Ö÷Êý¾Ý¿âÖл¹Ã»ÓÐÀúÊ·Êý¾Ý£¬Òò´Ë£¬Èç¹û MySQLµÄÊý¾ÝĿ¼Ï£¨Èç¹ûÊÇÓÃRPM°ü°²×°Êý¾ÝĿ¼ȱʡÊÇ/var/lib/mysql£©ÒѾ­ÓжàÓàµÄÎļþ£¬±ÈÈç¶þ½øÖÆÈÕÖ¾Îļþmysql- bin.*£¬master.info£¬relay-bin.*£¬relay-log.*£¬ÇëÏÈɾ³ý¡£ÒÔÏÂÊÇÒ»Ö÷Ò»´Ó½á¹¹µÄ¼òµ¥ÍØÆËͼ£º
 

 


Ö÷´Ó¸´ÖÆÅäÖ÷½·¨
´´½¨¸´ÖÆÕʺÅ
ÔÚÖ÷·þÎñÆ÷ÉÏ´´½¨¸´ÖÆÕʺţ¬ÊÚÓèÏàÓ¦µÄȨÏÞ¡£×¢Ò⣬¸´ÖÆÕʺŵĿÚÁî×îºÃ²»Òª³¬¹ý6룬¶øÇÒ²»Òª´ø"#"µÈÌØÊâ×Ö·û¡£´´½¨ÃüÁîÈçÏ£º

GRANT REPLICATION SLAVE ON *.* TO ¡®ÕʺÅÃû³Æ¡¯@¡¯%¡¯ IDENTIFIED BY ¡®¿ÚÁ

±ÈÈ磺
¡¡¡¡¡¡ GRANT REPLICATION SLAVE ON *.* TO ¡®repuser¡¯@¡¯%¡¯ IDENTIFIED BY ¡®111111¡¯

ÔÚÖ÷·þÎñÆ÷µÄMySQLÅäÖÃÎļþÖÐÌí¼ÓÑ¡Ï´ò¿ª¶þ½øÖÆÈÕÖ¾¼Ç¼¹¦ÄÜ
server-id = 16204
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

¡¡¸÷¸öÅäÖÃÑ¡ÏîµÄ˵Ã÷ÈçÏ£º
server-id = 16204
·þÎñÆ÷IDºÅ£¬ÕûÊýÖµ£¬±£Ö¤Î¨Ò»±êʶһ̨·þÎñÆ÷¾Í¿ÉÒÔ
log-bin=mysql-bin
´ò¿ª¶þ½øÖÆÈÕÖ¾
binlog-ignore-db=mysql
ºöÂÔmysqlÊý¾Ý¿â¸´ÖÆ
binlog-ignore-db=information_schema
ºöÂÔinformation_schemaÊý¾Ý¿â¸´ÖÆ

ÔÚ´Ó·þÎñÆ÷µÄMySQLÅäÖÃÎļþÖÐÌí¼ÓÏà¹Ø¸´ÖÆÑ¡Ïî
server-id =16214
master-host=172.20.16.204
master-user= repuser
master-password=111111
relay-log=relay-bin
relay-log-index=relay-bin
replicate-ignore-db=mysql
replicate-ignore-db=information_schema

¸÷¸öÅäÖÃÑ¡ÏîµÄ˵Ã÷ÈçÏ£º
server-id =16214
¡¡ ·þÎñÆ÷IDºÅ£¬ÕûÊýÖµ£¬±£Ö¤Î¨Ò»±êʶһ̨·þÎñÆ÷±ÈÈ磺16214
master-host=172.20.16.204
Ö÷·þÎñÆ÷IPµØÖ·£¬±ÈÈ磺172.20.16.204
master-user= repuser
¡¡Ö÷·þÎñÆ÷ÉÏ´´½¨µÄ¸´ÖÆÓû§ÕʺÅÃû³Æ£¬±ÈÈçÒÔÉÏ´´½¨µÄ£ºrepuser
master-password=111111
¡¡ Ö÷·þÎñÆ÷ÉÏ´´½¨µÄ¸´ÖÆÓû§ÕʺÅÃÜÂ룬±ÈÈçÒÔÉÏ´´½¨µÄ£º111111
relay-log=relay-bin
¡¡ ÖмÌÈÕÖ¾Ãû³Æ
relay-log-index=relay-bin
¡¡ ÖмÌË÷ÒýÎļþÃû³Æ
replicate-ignore-db=mysql
ºöÂÔmysqlÊý¾Ý¿â¸´ÖÆ
replicate-ignore-db=information_schema
¡¡ ºöÂÔinformation_schemaÊý¾Ý¿â¸´ÖÆ

ÖØÆô·þÎñÆ÷
ÏÈÖØÆôÖ÷·þÎñÆ÷£¬ÆðÀ´ºóÔÙÖØÆô´Ó·þÎñÆ÷

²ì¿´ÔËÐÐ״̬
ÔÚ´Ó·þÎñÆ÷ÉÏÖ´ÐÐÃüÁshow slave status\G
 


¡¡Èç¹û¸´ÖÆ״̬Slave_IO_Running£¬Slave_SQL_Running¶¼ÎªYes£¬ËµÃ÷¸´ÖÆÒѾ­ÅäÖÃÕýÈ·
¡¡¡¡¡¡¡¡ Slave_IO_Running: Yes
¡¡¡¡¡¡¡¡ Slave_SQL_Running: Yes


´ÓÒÑÓÐÖ÷·þÎñÆ÷ÅäÖÃһ̨еĴӷþÎñÆ÷
ÅäÖÃ˵Ã÷
ÕâÀïËù˵µÄ"´ÓÒÑÓÐÖ÷·þÎñÆ÷ÅäÖÃһ̨еĴӷþÎñÆ÷"Ö¸µÄÊÇ£¬Ö÷´Ó·þÎñÆ÷¶¼ÒѾ­°²×°ºÃÁËMySQLÊý¾Ý¿â£¬Ö÷·þÎñÆ÷ÒѾ­ÔÚÔËÐУ¬ÐèÒªÔö¼ÓÒ»¸öеĴӷþ ÎñÆ÷£¬ÒѾ­ÓкܶàÀúÊ·Êý¾ÝÐèÒª¸´ÖƵ½´Ó·þÎñÆ÷ÉÏ¡£Õ÷¶ÔÕâÑùµÄÓ¦Óó¡¾°£¬Çë°´ÏÂÁз½·¨ÅäÖø´ÖÆ¡£ÒÔÏÂÊÇÒ»Ö÷Ò»´Ó½á¹¹µÄ¼òµ¥ÍØÆËͼ£º
 

 

 

Ö÷´ÓÅäÖ÷½·¨
´ò¿ªÖ÷·þÎñÆ÷¶þ½øÖÆÈÕÖ¾¹¦ÄÜ
Èç¹ûÖ÷·þÎñÆ÷µÄ¶þ½øÖÆÈÕÖ¾¼Ç¼¹¦ÄÜûÓдò¿ª£¬ÔÚMySQLÅäÖòÎÊýÎļþ£¨±ÈÈç/etc/my.cnf£©ÖÐÌí¼ÓÑ¡ÏÖØÆôMySQL·þÎñ£¬´ò¿ª¶þ½øÖÆÈÕÖ¾¹¦ÄÜ¡£
¡¡¡¡ server-id = 16204
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

¡¡¸÷¸öÅäÖÃÑ¡ÏîµÄ˵Ã÷ÈçÏ£º
server-id = 16204
·þÎñÆ÷IDºÅ£¬ÕûÊýÖµ£¬±£Ö¤Î¨Ò»±êʶһ̨·þÎñÆ÷¾Í¿ÉÒÔ
log-bin=mysql-bin
´ò¿ª¶þ½øÖÆÈÕÖ¾
binlog-ignore-db=mysql
ºöÂÔmysqlÊý¾Ý¿â¸´ÖÆ
binlog-ignore-db=information_schema
ºöÂÔinformation_schemaÊý¾Ý¿â¸´ÖÆ

´´½¨¸´ÖÆÕʺÅ
ÔÚÖ÷·þÎñÆ÷ÉÏ´´½¨¸´ÖÆÕʺţ¬ÊÚÓèÏàÓ¦µÄȨÏÞ¡£×¢Ò⣬¸´ÖÆÕʺŵĿÚÁî×îºÃ²»Òª³¬¹ý6룬¶øÇÒ²»Òª´ø"#"µÈÌØÊâ×Ö·û¡£´´½¨ÃüÁîÈçÏ£º

GRANT REPLICATION SLAVE ON *.* TO ¡®ÕʺÅÃû³Æ¡¯@¡¯%¡¯ IDENTIFIED BY ¡®¿ÚÁ

±ÈÈ磺
¡¡¡¡¡¡ GRANT REPLICATION SLAVE ON *.* TO ¡®repuser¡¯@¡¯%¡¯ IDENTIFIED BY ¡®111111¡¯

±¸·ÝÖ÷·þÎñÆ÷µÄÊý¾ÝÎļþ
±¸·ÝÖ÷·þÎñÆ÷µÄÊý¾ÝÎļþ
Ö´ÐÐÒÔÏÂÃüÁ¸øÖ÷·þÎñÆ÷ËùÓбí¼ÓËø£¬½ûÖ¹¼ÌÐøдÈëÊý¾Ý£º
¡¡¡¡¡¡¡¡FLUSH TABLES WITH READ LOCK£»

¼Ç¼¸´ÖÆÆô¶¯¶Ïµã
Ö´ÐÐÒÔÏÂÃüÁ¼Ç¼´Ó·þÎñÆ÷¿ªÊ¼Æô¶¯¸´ÖƵĶϵ㣬°üÀ¨ÈÕÖ¾ÃûºÍÆ«ÒÆÁ¿¡£
¡¡ mysql>SHOW MASTER STATUS£»
±ÈÈ磺
¡¡ mysql> show master status\G
¡¡¡¡¡¡¡¡ file: mysql-bin.000025
¡¡¡¡¡¡¡¡ Position: 13729
¡¡¡¡¡¡¡¡ Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,mysql,information_schema

ÈÕÖ¾ÎļþÃûÊÇ£ºmysql-bin.000025
Æ«ÒÆÁ¿ÊÇ£º13729
¡¡ ¡¡¡¡¡¡ ×¢Ò⣬´Ó·þÎñÆ÷½«´ÓÕâ¸öλÖÿªÊ¼¸´ÖÆ¡£

´ò°ü±¸·ÝÖ÷·þÎñÆ÷Êý¾ÝÎļþ
´ò°üÐèÒª¸´ÖƵÄÊý¾Ý¿âÎļþ£¬Èç¹ûÊý¾Ý¿âÎļþ°²×°ÔÚ/var/lib/mysqlĿ¼Ï£¬¿ÉÒÔÖ´ÐÐÃüÁ
tar zcf /tmp/backup.tar.gz /var/lib/mysql

¸ÃÃüÁ°Ñ/var/lib/mysqlĿ¼ÏÂËùÓеÄÎļþ¶¼´ò°ü£¬Ò²°üÀ¨MySQLµÄϵͳ±í£¬¶øϵͳ±íÒ»°ã¶¼ÊDz»ÐèҪͬ²½µ½´Ó·þÎñÆ÷Éϵģ¬ ËùÒÔ×îºÃÑ¡ÔñÄãÐèÒª¸´ÖƵÄÊý¾Ý¿âĿ¼£¬½øÐдò°ü¾Í¿ÉÒÔÁË£¬ÈçÐèÒª¸´ÖÆÁ½¸öÊý¾Ý¿â£¨PRIVATE_DB1 ,PRIVATE_DB2£©µ½´Ó·þÎñÆ÷ÉÏ£¬Ö»Òª°ÑÕâÁ½¸öĿ¼´ò°ü£º
tar czf /tmp/backup.tar.gz PRIVATE_DB1 PRIVATE_DB2

Ö÷·þÎñÆ÷±í½âËø
Ö´ÐÐÒÔÏÂÃüÁ½â¿ªÖ÷·þÎñÆ÷µÄ¶ÁËø£¬Ö÷·þÎñÆ÷¿ÉÒÔ¼ÌÐø¸üÐÂÊý¾Ý£º
mysql> UNLOCK TABLES£»

ÔÚ´Ó·þÎñÆ÷Éϻָ´Ö÷±¸·ÝÎļþ
ÔÚ´Ó·þÎñÆ÷ÏàÓ¦µÄÊý¾Ý¿âĿ¼£¨±ÈÈ磺/var/lib/mysql£©Éϻָ´Ö÷·þÎñÆ÷Éϱ¸·ÝµÄÊý¾Ý¿âÎļþ£¬»Ö¸´Ê±ÐèÒª¹Ø±Õ´Ó·þÎñÆ÷µÄMySQL·þÎñ£¬±ÈÈ磺
Cd /var/lib/mysql
Tar xzf backup.tar.gz

Æô¶¯´Ó·þÎñÆ÷£¬ÉèÖø´Öƶϵã
ÔÚMySQLÖÐÖ´ÐÐÒÔÏÂÃüÁÉèÖø´ÖÆ¿ªÊ¼¶Ïµã£º
change master to master_user=¡®repuser¡¯,
¡¡¡¡¡¡¡¡ master_password=¡®654321¡¯ , master_host=¡®172.20.16.204¡¯,
¡¡¡¡¡¡¡¡ master_log_file=¡® mysql-bin.000025¡¯,master_log_pos=13729;

¡¡¡¡±íʾ´ÓÈÕÖ¾Îļþmysql-bin.000090£¬Î»ÖÃ98¿ªÊ¼¸´ÖÆ

²ì¿´¸´ÖÆ״̬
ÔÚ´Ó·þÎñÆ÷ÉÏÖ´ÐÐÃüÁshow slave status\G
 

 

¡¡Èç¹û¸´ÖÆ״̬Slave_IO_Running£¬Slave_SQL_Running¶¼ÎªYes£¬ËµÃ÷¸´ÖÆÒѾ­ÅäÖÃÕýÈ·

¡¡¡¡¡¡¡¡ Slave_IO_Running: Yes
¡¡¡¡¡¡¡¡ Slave_SQL_Running: Yes
±¾ÎÄתժ×Ô¡ºITѧϰÕß¡»http://www.itlearner.com/article/2009/4375.shtml

ÎÄÕ¼È룺a    ÔðÈα༭£ºa 
  • ÉÏһƪÎÄÕ£º

  • ÏÂһƪÎÄÕ£º ûÓÐÁË
  • ¡¾·¢±íÆÀÂÛ¡¿¡¾¼ÓÈëÊղء¿¡¾¸æËߺÃÓÑ¡¿¡¾´òÓ¡´ËÎÄ¡¿¡¾¹Ø±Õ´°¿Ú¡¿
    ÍøÓÑÆÀÂÛ£º£¨Ö»ÏÔʾ×îÐÂ10Ìõ¡£ÆÀÂÛÄÚÈÝÖ»´ú±íÍøÓѹ۵㣬Óë±¾Õ¾Á¢³¡Î޹أ¡£©
    ¹ØÓÚÎÒÃÇ | RAIDÊý¾Ý»Ö¸´ | ÓÑÇéÁ´½Ó | RSSÉú³É | XMLÉú³É | ÎÄÕÂHTMLµØͼ | ÏÂÔØHTMLµØͼ

    °æȨËùÓÐ ±±ÑÇÊý¾Ý»Ö¸´ÖÐÐÄ
    È«¹úͳһ¿Í·þµç»°:4006-505-808
    ±±¾©Êк£µíÇøÓÀ·á»ùµØ·á»ÛÖз7ºÅвÄÁÏ´´Òµ´óÏÃB×ù205ÊÒ
    ¾©ICP±¸05011939
    < lÄ1