MySQLµÄ±íËøÎÊÌâ(¶þ)——InnoDB±íËøÎÊÌâ
Ê×ÏȲ¹³äÏÂMySQLµÄ±íËøÎÊÌâ(Ò»)û½²µ½µÄϵͳ¿ÉÒÔͨ¹ýÉèÖÃmax_write_lock_count²ÎÊýÀ´¿ØÖÆдµÄÓÅÏȼ¶,µ±Ò»¸ö±íµÄ¶ÁËø´ïµ½Õâ¸öÊýµÄʱºò,MySQL»áÔÝʱµÄ,½µµÍдµÄÓÅÏȼ¶.MySQL¾¡Á¿ÉÙÓÃһЩ¿´Ëƾ«Ã¸´ÔÓµÄÓï¾ä,ÒòΪÄ㸴ÔÓ¿ÉÄܵ¼Ö¸ø±í¼ÓµÄ¶ÁËøʱ¼ä¾Í³¤,»áµ¼ÖÂдµÄ²Ù×÷ÎÞ·¨½øÐÐ. innodbºÍMyISAM×î´óµÄÇø±ðÔÚÓÚinnodbÖ§³ÖÊÂÎñ´¦ÀíºÍÐм¶Ëø,Ðм¶Ëø´øÀ´µÄºÃ´¦ÊÇ¿ÉÒÔÖ§³Ö´ó¹æÄ£µÄ²¢·¢Ó¦ÓÃ(ÉèÖõĺõÄÇé¿öÏÂ),µ±È»¿ØÖƵĶÔÏóԽС,²½Öè¾ÍÔ½¸´ÔÓ,ÄÇôÏûºÄµÄ×ÊÔ´¾ÍÔ½¶à. ¿ÉÒÔͨ¹ýÔÚMySQLÀïÃæ²é¿´×´Ì¬±äÁ¿µÄ·½Ê½À´²é¿´innodbËøµÄʹÓÃÇé¿ö: mysql> show status like "innodb_row_lock%";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec) »¹¿ÉÒÔͨ¹ýshow engine innodb status\G²é¿´Ò»´ó¶ÑÐÅÏ¢.½øÐмòÒª·ÖÎö. innodbÓÐÁ½ÖÖÀàÐ͵ÄÐм¶Ëø:¹²ÏíËøºÍÅÅËûËø;ΪÁËÔÊÐíÐÐËøºÍ±íËø¹²´æ.ʵÏÖ¶à¿ÅÁ£¶ÈËø»úÖÆ,innodb»¹ÓÐ2ÖÖÄÚ²¿Ëø(Intention locks),Õâ¾ÍÊÇÒâÏò¹²ÏíËøºÍÒâÏòÅÅËûËø.ÕâÁ½ÖÖËù¶¼ÊÇ±í¼¶Ëø. a)¹²ÏíËø(S):ÔÊÐíÒ»¸öÊÂÎñÈ¥¶ÁÒ»ÐÐ,×èÖ¹ÆäËûÊÂÎñ»ñµÃÏàͬÊý¾Ý¼¯µÄÅÅËûËø. b)ÅÅËûËø(X):ÔÊÐí»ñµÃÅÅËûËøµÄÊÂÎñ¸üÐÂÊý¾Ý,×èÖ¹ÆäËûÊÂÎñ»ñÈ¡ÏàͬÊý¾Ý¼¯µÄ¸÷Ïî¶ÁËøºÍÅÅËûдËø. c)ÒâÏò¹²ÏíËø(IS):ÊÂÎñ´òËã¸øÊý¾ÝÐмӹ²ÏíËø,ÊÂÎñÔÚÈ¡µÃÒ»¸öÊý¾ÝÐеĹ²ÏíËø֮ǰ±ØÐëÏÈÈ¡µÃ¸Ã±íµÄISËø. d)ÒâÏòÅÅËûËø(IX):ÊÂÎñ´òËã¸øÊý¾ÝÐмÓÅÅËûËø,ÊÂÎñÔÚÈ¡µÃÒ»¸öÊý¾ÝÐеÄÅÅËûËø֮ǰ±ØÐëÏÈÈ¡µÃ¸Ã±íµÄIXËø. ÇëÇóËøÄ
Ïà¹ØÎĵµ£º
×î½üÓõ½ÁËÒ»¿îÊý¾Ý¿âͼÐλ¯¹ÜÀí¹¤¾ßNavicat for MySQL £¬Ê¹ÓÃÖ®ºóÓÐÒ»Öָоõ£ºÔÀ´±à³Ì¿ÉÒԺܸßЧ£¡
ÒÔǰʹÓÃMySQLʱ£¬Ã¿´Î¶¼Ö»ÄÜÔÚÀàËÆDOSµÄ½çÃ濪·¢£¬Ê¹Óùٷ½ÌṩµÄ¹¤¾ßºÜÈÝÒ׾ͱ»¿¨µ½ÁË£¬¾õµÃ»¹ÊÇ×ÖÌõ½çÃæ±È½Ï¸ßЧ£¬µ«ÊdzõѧMySQL,ºÜ¶à¹¦Äܶ¼»¹Ã»ÓÐÍêÈ«Çå³þ£¬Ê¹Óð²È«ÐÔÉϵĿ¼ÂÇÒ²²»¹»³ÉÊì¡£ÕâʱºòÎÒ¿ ......
//µ¼³ö
String mysql="mysqldump -uroot -proot --opt databasename > d:/test.sql";
java.lang.Runtime.getRuntime().exec("cmd /c "+mysql);
//µ¼Èë
String mysql="mysqladmin -uroot -proot create databasename";
java.lang.Runt ......
1¡¢±à¼MySQLÅäÖÃÎļþ£º
windows»·¾³ÖУº%MySQL_installdir%\my.ini¡¡//Ò»°ãÔÚMySQL°²×°Ä¿Â¼ÏÂÓÐmy.ini¼´MySQLµÄÅäÖÃÎļþ¡£
linux»·¾³ÖУº/etc/my.cnf
ÔÚ[MySQLd]ÅäÖöÎÌí¼ÓÈçÏÂÒ»ÐУº
skip-grant-tables
±£´æÍ˳ö±à¼¡£
2¡¢È»ºóÖØÆôMySQL·þÎñ
windows»·¾³ÖУº
net stop MySQL
net start MySQL
linux»·¾³ÖУº
/e ......
×÷Õߣº°½Ê¿Î°
Mysql ÓõÄÊÇLinux (non RPM packages)
Ò»¡¢½«mysql-5.5.0-m2-linux-i686-glibc23.tar.gzÏÂÔص½/home/ikmb ²¢ÔÚµ±Ç°Ä¿Â¼½âѹ£»
……..
mysql-5.5.0-m2-linux-i686-glibc23/data/mysql/
mysql-5.5.0-m2-linux-i686-glibc23/data/test/
…………
¶þ¡¢´´½¨MySQL×飬 ......
InnoDB ¸ø MySQL ÌṩÁ˾ßÓÐÊÂÎñ(commit)¡¢»Ø¹ö(rollback)ºÍ±ÀÀ£ÐÞ¸´ÄÜÁ¦(crash recovery capabilities)¡¢¶à°æ±¾²¢·¢¿ØÖÆ(multi-versioned concurrency control)µÄÊÂÎñ°²È«(transaction-safe (ACID compliant))ÐÍ±í¡£InnoDB ÌṩÁËÐм¶Ëø(locking on row level)£¬ÌṩÓë Oracle ÀàËƵIJ»¼ÓËø¶ÁÈ¡(non-locking read in SELEC ......