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 |