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

mysqlÐÔÄܵļì²éºÍµ÷ÓÅ·½·¨

À´Ô´£ºhttp://www.sudone.com/linux/mysql_debug.html
×÷ÕߣºAyou
ÎÒÒ»Ö±ÊÇʹÓÃmysqlÕâ¸öÊý¾Ý¿âÈí¼þ£¬Ëü¹¤×÷±È½ÏÎȶ¨£¬Ð§ÂÊÒ²ºÜ¸ß¡£ÔÚÓöµ½ÑÏÖØÐÔÄÜÎÊÌâʱ£¬Ò»°ã¶¼ÓÐÕâô¼¸ÖÖ¿ÉÄÜ£º
1¡¢Ë÷ÒýûÓн¨ºÃ£»
2¡¢sqlд·¨¹ýÓÚ¸´ÔÓ£»
3¡¢ÅäÖôíÎó£»
4¡¢»úÆ÷ʵÔÚ¸ººÉ²»ÁË£»
1¡¢Ë÷ÒýûÓн¨ºÃ
Èç¹û¿´µ½mysqlÏûºÄµÄcpuºÜ´ó£¬¿ÉÒÔÓÃmysqlµÄclient¹¤¾ßÀ´¼ì²é¡£
ÔÚlinuxÏÂÖ´ÐÐ
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
ÊäÈëÃÜÂ룬Èç¹ûûÓÐÃÜÂ룬Ôò²»ÓÃ-p²ÎÊý¾Í¿ÉÒÔ½øµ½¿Í»§¶Ë½çÃæÖС£
¿´¿´µ±Ç°µÄÔËÐÐÇé¿ö
show full processlist
¿ÉÒÔ¶àÔËÐм¸´Î
Õâ¸öÃüÁî¿ÉÒÔ¿´µ½µ±Ç°ÕýÔÚÖ´ÐеÄsqlÓï¾ä£¬Ëü»á¸æÖªÖ´ÐеÄsql¡¢Êý¾Ý¿âÃû¡¢Ö´ÐеÄ״̬¡¢À´×ԵĿͻ§¶Ëip¡¢ËùʹÓõÄÕʺš¢ÔËÐÐʱ¼äµÈÐÅÏ¢
ÔÚÎÒµÄcacheºó¶Ë£¬ÕâÀïÃæ´ó²¿·Öʱ¼äÊÇ¿´²»µ½ÏÔʾÈκÎsqlÓï¾äµÄ£¬ÎÒÈÏΪÕâÑù²ÅËã±È½ÏÕý³£¡£Èç¹û¿´µ½ÓкܶàsqlÓï¾ä£¬ÄÇôÕą̂mysql¾ÍÒ»¶¨»áÓÐÐÔÄÜÎÊÌâ
Èç¹û³öÏÖÁËÐÔÄÜÎÊÌ⣬Ôò¿ÉÒÔ½øÐзÖÎö£º
1¡¢ÊDz»ÊÇÓÐsqlÓï¾ä¿¨×¡ÁË£¿
ÕâÊdzöÏֱȽ϶àµÄÇé¿ö£¬Èç¹ûÊý¾Ý¿âÊDzÉÓÃmyisam£¬ÄÇôÓпÉÄÜÓÐÒ»¸öдÈëµÄÏ̻߳á°ÑÊý¾Ý±í¸øËø¶¨ÁË£¬Èç¹ûÕâÌõÓï¾ä²»½áÊø£¬ÔòÆäËüÓï¾äÒ²ÎÞ·¨ÔËÐС£
²é¿´processlistÀïµÄtimeÕâÒ»Ï¿´¿´ÓÐûÓÐÖ´ÐÐʱ¼äºÜ³¤µÄÓï¾ä£¬ÒªÁôÒâÕâЩÓï¾ä¡£
2¡¢´óÁ¿ÏàͬµÄsqlÓï¾äÕýÔÚÖ´ÐÐ
Èç¹û³öÏÖÕâÖÖÇé¿ö£¬ÔòÓпÉÄÜÊǸÃsqlÓï¾äÖ´ÐеÄЧÂʵÍÏ£¬Í¬ÑùÒªÁôÒâÕâЩÓï¾ä¡£
È»ºó°ÑÄãËù»³ÒɵÄÓï¾äͳͳ¼¯ºÏһϣ¬ÓÃdesc£¨explain£©À´¼ì²éÕâЩÓï¾ä¡£
Ê×ÏÈ¿´¿´Ò»¸öÕý³£µÄdescÊä³ö£º
mysql> desc select * from imgs where imgid=1651768337;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | imgs  | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0


Ïà¹ØÎĵµ£º

ÈçºÎµ¼Èë.sqlÎļþµ½mysqlÖÐ

ÈçºÎµ¼Èë.sqlÎļþµ½mysqlÖУ¿
C:\mysql\bin>mysql  -u  Óû§Ãû -p  Êý¾Ý¿âÃû  <   c:/test.sql    (source "c:\adsense.sql" )
ÖмäµÄ¿Õ¸ñÊÇÒ»¸ö¿Õ¸ñλ¡£
ͬʱʹÓÃ200¶àMBµÄsqlÎļþ¡£
ÀýÈ磺
C:\Program Files\MySQL\bin>mysql -u root -p myrosz & ......

MySQLµ¼³öµ¼ÈëÃüÁîµÄÓÃÀý

¡¡1.µ¼³öÕû¸öÊý¾Ý¿â
¡¡¡¡mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû > µ¼³öµÄÎļþÃû
¡¡¡¡mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
¡¡¡¡2.µ¼³öÒ»¸ö±í
¡¡¡¡mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû ±íÃû> µ¼³öµÄÎļþÃû
¡¡¡¡mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
¡¡¡¡3.µ¼³öÒ»¸öÊý¾Ý¿â½á¹¹
¡ ......

ÓÃmysqldumpslow·ÖÎömysqlµÄslow query log

mysqlÓÐÒ»¸ö¹¦ÄܾÍÊÇ¿ÉÒÔlogÏÂÀ´ÔËÐеıȽÏÂýµÄsqlÓï¾ä£¬Ä¬ÈÏÊÇûÓÐÕâ¸ölogµÄ£¬ÎªÁË¿ªÆôÕâ¸ö¹¦ÄÜ£¬
ÒªÐÞ¸Ämy.cnf»òÕßÔÚmysqlÆô¶¯µÄʱºò¼ÓÈëһЩ²ÎÊý¡£Èç¹ûÔÚmy.cnfÀïÃæÐ޸ģ¬ÐèÔö¼ÓÈçϼ¸ÐÐ
long_query_time = 1
log-slow-queries = /var/youpath/slow.log
log-queries-not-using-indexes
long_query_time ÊÇÖ¸Ö´ ......

fedora12Ï°²×°apache+php+mysql(LAMP)+phpmyadmin

1 °²×°MySQL5
ÓÃÏÂÁÐÃüÁî°²×°MySQL£º
yum install mysql mysql-server
È»ºóÎÒÃÇΪMySQL´´½¨ÏµÍ³Æô¶¯Á´½Ó£¨ÕâÑùµÄ»°£¬MySQL¾Í»áËæ×ÅϵͳÆô¶¯¶øÆô¶¯£©£¬²¢Æô¶¯MySQL·þÎñÆ÷£º
chkconfig –levels 235 mysqld on
/etc/init.d/mysqld start
ÔËÐÐ
mysqladmin -u root password yourrootsqlpassword
À´ÎªrootÓû§ÉèÖà ......

mysqlÃüÁî_»ù´¡

ÏÔʾmysql»ù±¾ÐÅÏ¢£ºmysql>status;
²é¿´±äÁ¿£ºshow variables like '%version%';
±¸·ÝÓ뻹ԭÃüÁÔÚ×îºó»¹ÓÐ
1.±¸·ÝMySQLÊý¾Ý¿âµÄÃüÁî:
ÔÚcmdÖнøÈëµ½mysqlµÄlibĿ¼ÏÂÖ´ÐÐÈçÏ£º
mysqldump -hhostname -uusername -ppassword databaseName > backupfile.sql
Àý£ºmysqldump -hlocalhost -uroot -p123456 test ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ