Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ÅäÖÃMySQLÖ÷´Ó¸´ÖÆ(Replication)


MySQLÖ§³Öµ¥Ïò¡¢Òì²½¸´ÖÆ£¬¸´Öƹý³ÌÖÐÒ»¸ö·þÎñÆ÷³äµ±Ö÷·þÎñÆ÷£¬¶øÒ»¸ö»ò¶à¸öÆäËü·þÎñÆ÷³äµ±´Ó·þÎñÆ÷¡£Ö÷·þÎñÆ÷½«¸üÐÂдÈë¶þ½øÖÆÈÕÖ¾Îļþ£¬²¢Î¬»¤ÈÕÖ¾ÎļþµÄÒ»¸öË÷ÒýÒÔ¸ú×ÙÈÕ־ѭ»·¡£µ±Ò»¸ö´Ó·þÎñÆ÷Á¬½Óµ½Ö÷·þÎñÆ÷ʱ£¬Ëü֪ͨÖ÷·þÎñÆ÷´Ó·þÎñÆ÷ÔÚÈÕÖ¾ÖжÁÈ¡µÄ×îºóÒ»´Î³É¹¦¸üеÄλÖᣴӷþÎñÆ÷½ÓÊÕ´ÓÄÇʱÆð·¢ÉúµÄÈκθüУ¬È»ºó·âËø²¢µÈ´ýÖ÷·þÎñÆ÷֪ͨÏÂÒ»´Î¸üС£
 
ΪʲôʹÓÃÖ÷´Ó¸´ÖÆ£¿
1¡¢Ö÷·þÎñÆ÷/´Ó·þÎñÆ÷ÉèÖÃÔö¼ÓÁ˽¡×³ÐÔ¡£Ö÷·þÎñÆ÷³öÏÖÎÊÌâʱ£¬Äã¿ÉÒÔÇл»µ½´Ó·þÎñÆ÷×÷Ϊ±¸·Ý¡£
2¡¢Í¨¹ýÔÚÖ÷·þÎñÆ÷ºÍ´Ó·þÎñÆ÷Ö®¼äÇзִ¦Àí¿Í»§²éѯµÄ¸ººÉ£¬¿ÉÒԵõ½¸üºÃµÄ¿Í»§ÏìӦʱ¼ä¡£µ«ÊDz»ÒªÍ¬Ê±ÔÚÖ÷´Ó·þÎñÆ÷ÉϽøÐиüУ¬ÕâÑù¿ÉÄÜÒýÆð³åÍ»¡£
3¡¢Ê¹Óø´ÖƵÄÁíÒ»¸öºÃ´¦ÊÇ¿ÉÒÔʹÓÃÒ»¸ö´Ó·þÎñÆ÷Ö´Ðб¸·Ý£¬¶ø²»»á¸ÉÈÅÖ÷·þÎñÆ÷¡£ÔÚ±¸·Ý¹ý³ÌÖÐÖ÷·þÎñÆ÷¿ÉÒÔ¼ÌÐø´¦Àí¸üС£
 
MySQLʹÓÃ3¸öÏß³ÌÀ´Ö´Ðи´Öƹ¦ÄÜ(ÆäÖÐ1¸öÔÚÖ÷·þÎñÆ÷ÉÏ£¬ÁíÁ½¸öÔÚ´Ó·þÎñÆ÷ÉÏ¡£µ±·¢³öSTART SLAVEʱ£¬´Ó·þÎñÆ÷´´½¨Ò»¸öI/OỊ̈߳¬ÒÔÁ¬½ÓÖ÷·þÎñÆ÷²¢ÈÃÖ÷·þÎñÆ÷·¢ËͶþ½øÖÆÈÕÖ¾¡£Ö÷·þÎñÆ÷´´½¨Ò»¸öÏ߳̽«¶þ½øÖÆÈÕÖ¾ÖеÄÄÚÈÝ·¢Ë͵½´Ó·þÎñÆ÷¡£´Ó·þÎñÆ÷I/OÏ̶߳ÁÈ¡Ö÷·þÎñÆ÷Binlog DumpÏ̷߳¢Ë͵ÄÄÚÈݲ¢½«¸ÃÊý¾Ý¿½±´µ½´Ó·þÎñÆ÷Êý¾ÝĿ¼Öеı¾µØÎļþÖУ¬¼´ÖмÌÈÕÖ¾¡£µÚ3¸öÏß³ÌÊÇSQLỊ̈߳¬´Ó·þÎñÆ÷ʹÓôËÏ̶߳ÁÈ¡ÖмÌÈÕÖ¾²¢Ö´ÐÐÈÕÖ¾Öаüº¬µÄ¸üС£SHOW PROCESSLISTÓï¾ä¿ÉÒÔ²éѯÔÚÖ÷·þÎñÆ÷ÉϺʹӷþÎñÆ÷ÉÏ·¢ÉúµÄ¹ØÓÚ¸´ÖƵÄÐÅÏ¢¡£
 
ĬÈÏÖмÌÈÕ־ʹÓÃhost_name-relay-bin.nnnnnnÐÎʽµÄÎļþÃû£¬ÆäÖÐhost_nameÊÇ´Ó·þÎñÆ÷Ö÷»úÃû£¬nnnnnnÊÇÐòÁкš£ÓÃÁ¬ÐøÐòÁкÅÀ´´´½¨Á¬ÐøÖмÌÈÕÖ¾Îļþ£¬´Ó000001¿ªÊ¼¡£´Ó·þÎñÆ÷¸ú×ÙÖмÌÈÕÖ¾Ë÷ÒýÎļþÀ´Ê¶±ðÄ¿Ç°ÕýʹÓõÄÖмÌÈÕÖ¾¡£Ä¬ÈÏÖмÌÈÕÖ¾Ë÷ÒýÎļþÃûΪhost_name-relay-bin.index¡£ÔÚĬÈÏÇé¿ö£¬ÕâЩÎļþÔÚ´Ó·þÎñÆ÷µÄÊý¾ÝĿ¼Öб»´´½¨¡£ÖмÌÈÕÖ¾Óë¶þ½øÖÆÈÕÖ¾µÄ¸ñʽÏàͬ£¬²¢ÇÒ¿ÉÒÔÓÃmysqlbinlog¶ÁÈ¡¡£µ±SQLÏß³ÌÖ´ÐÐÍêÖмÌÈÕÖ¾ÖеÄËùÓÐʼþºó£¬ÖмÌÈÕÖ¾½«»á±»×Ô¶¯É¾³ý¡£
 
´Ó·þÎñÆ÷ÔÚÊý¾ÝĿ¼ÖÐÁíÍâ´´½¨Á½¸ö״̬Îļþ--master.infoºÍrelay-log.info¡£×´Ì¬Îļþ±£´æÔÚÓ²ÅÌÉÏ£¬´Ó·þÎñÆ÷¹Ø±Õʱ²»»á¶ªÊ§¡£Ï´δӷþÎñÆ÷Æô¶¯Ê±£¬¶ÁÈ¡ÕâЩÎļþÒÔÈ·¶¨ËüÒѾ­´ÓÖ÷·þÎñÆ÷¶ÁÈ¡Á˶àÉÙ¶þ½øÖÆÈÕÖ¾£¬ÒÔ¼°´¦Àí×Ô¼ºµÄÖмÌÈÕÖ¾µÄ³Ì¶È¡£
 
ÉèÖÃÖ÷´Ó¸´ÖÆ£º
 
1¡¢È·±£ÔÚÖ÷·þÎñÆ÷ºÍ´Ó·þÎñÆ÷ÉÏ°²×°µÄMySQL°æ±¾Ïàͬ£¬²¢ÇÒ×îºÃÊÇMySQLµÄ×îÐÂÎȶ¨°æ±¾¡£
2¡¢ÔÚÖ÷·þÎñ


Ïà¹ØÎĵµ£º

show innodb status (mysql)


µ±Ç°»îÔ¾µÄÊÂÎñ:
------------
TRANSACTIONS
------------
Trx id counter080157601
Purge done for trx's n:o <0 80154573 undo n:o <0 0
History list length 6
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, proce ......

MySQLÓëÊÂÎñ


×÷ÕߣºÀÏÍõ
MySQL5.X¶¼ÒѾ­·¢²¼ºÃ¾ÃÁË£¬µ«ÊÇ»¹ÓкܶàÈËÈÏΪMySQLÊDz»Ö§³ÖÊÂÎñ´¦ÀíµÄ£¬Õâ²»µÃ²»¹ÖËûÃÇÊǹª¹ÑÎŵģ¬Æäʵ£¬Ö»ÒªÄãµÄMySQL°æ±¾Ö§³ÖBDB»òInnoDB±íÀàÐÍ£¬ÄÇôÄãµÄMySQL¾Í¾ßÓÐÊÂÎñ´¦ÀíµÄÄÜÁ¦¡£ÕâÀïÃ棬ÓÖÒÔInnoDB±íÀàÐÍÓõÄ×î¶à£¬ËäÈ»ºóÀ´·¢ÉúÁËÖîÈçOracleÊÕ¹ºInnoDBµÈÁîMySQL²»Ë¬µÄÊÂÇ飬µ«ÄÇЩÉÌÒµÉϵĶ·ÕùÓ ......

mysql ¶¨Ê±Êý¾Ý±¸·Ý

1.±àдshell½Å±¾
vi /data/www/project_name/bin/mysql_backup.sh
#!/bin/bash
#This is a ShellScript For Auto DB Backup
#Powered by liuzheng

#ϵͳ±äÁ¿¶¨Òå
DBName=test
DBUser=root
DBPasswd=123456
BackupPath=/tmp/mysql_backup/

NewFile="$BackupPath"db$(date +%y%m%d ......

mysql innodb ÐÔÄÜÓÅ»¯

ĬÈÏÇé¿öÏ£¬innodbµÄ²ÎÊýÉèÖõķdz£Ð¡£¬ÔÚÉú²ú»·¾³ÖÐÔ¶Ô¶²»¹»ÓÃ
±ÈÈç×îÖØÒªµÄÁ½¸ö²ÎÊý
innodb_buffer_pool_size
ĬÈÏÊÇ8M
innodb_flush_logs_at_trx_commit ĬÈÏÉèÖõÄÊÇ1 Ò²¾ÍÊÇͬ²½Ë¢ÐÂlog(¿ÉÒÔÕâôÀí½â)
innodb_buffer_pool_size£º
ÕâÊÇInnoDB×îÖØÒªµÄÉèÖ㬶ÔInnoDBÐÔÄÜÓоö¶¨ÐÔµÄÓ°Ï졣ĬÈϵÄÉèÖÃÖ»ÓÐ8M£¬ËùÒÔ ......

ÈçºÎÓÅ»¯MysqlǧÍò¼¶¿ìËÙ·ÖÒ³,limitÓÅ»¯¿ìËÙ·ÖÒ³

MySQLÊý¾Ý¿âÓÅ»¯´¦ÀíʵÏÖǧÍò¼¶¿ìËÙ·ÖÒ³·ÖÎö£¬À´¿´Ï°ɡ£
Êý¾Ý±í collect ( id, title ,info ,vtype) ¾ÍÕâ4¸ö×ֶΣ¬ÆäÖÐ title Óö¨³¤£¬info ÓÃtext, id ÊÇÖð½¥£¬vtypeÊÇtinyint£¬vtypeÊÇË÷Òý¡£ÕâÊÇÒ»¸ö»ù±¾µÄÐÂÎÅϵͳµÄ¼òµ¥Ä£ÐÍ¡£ÏÖÔÚÍùÀïÃæÌî³äÊý¾Ý£¬Ìî³ä10ÍòƪÐÂÎÅ¡£
×îºócollect Ϊ 10ÍòÌõ¼Ç¼£¬Êý¾Ý¿â±íÕ¼ÓÃÓ²ÅÌ1.6G ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ