4個MySQL 資料庫基本除錯技巧

4個MySQL 資料庫基本除錯技巧

 

1. 資料庫日誌error.log

/var/log/mysql / error.log

/var/ lib/mysql / error.log

[pastacode lang=”markup” manual=”120714%2015%3A35%3A26%20%5BNote%5D%20Plugin%20’FEDERATED’%20is%20disabled.%0A120714%2015%3A35%3A26%20InnoDB%3A%20Initializing%20buffer%20pool%2C%20size%20%3D%208.0M%0A120714%2015%3A35%3A26%20InnoDB%3A%20Completed%20initialization%20of%20buffer%20pool%0A120714%2015%3A35%3A26%20InnoDB%3A%20Started%3B%20log%20sequence%20number%200%2067138180%0A120714%2015%3A35%3A27%20%5BNote%5D%20Event%20Scheduler%3A%20Loaded%200%20events%0A120714%2015%3A35%3A27%20%5BNote%5D%20%2Fusr%2Fsbin%2Fmysqld%3A%20ready%20for%20connections.%0AVersion%3A%20’5.1.63-0ubuntu0.10.04.1-log’%20socket%3A%20’%2Fvar%2Frun%2Fmysqld%2Fmysqld.sock’%20port%3A%203306%0A%C3%8A(Ubuntu)” message=”” highlight=”” provider=”manual”/]

 

2. 資料庫執行狀態

 

 

[pastacode lang=”markup” manual=”%24%20sudo%20service%20mysql%20status%0A%0Amysql%20start%2Frunning%2C%20process%20735″ message=”” highlight=”” provider=”manual”/]

 

 

[pastacode lang=”markup” manual=”%24%20ps%20-ef%20%7C%20grep%20mysql%0A%0Amysql%20735%201%200%20Jun12%20%2002%3A02%3A56%20%2Fusr%2Fsbin%2Fmysqld” message=”” highlight=”” provider=”manual”/]

 

[pastacode lang=”markup” manual=”%24%20sudo%20netstat%20-lnp%20%7C%20grep%20%3A3306%0A%0Atcp%200%200%20127.0.0.1%3A3306%200.0.0.0%3A*%20LISTEN%20735%2Fmysqld%0A%0A” message=”” highlight=”” provider=”manual”/]

 

3. 資料庫執行數據

 

 

[pastacode lang=”markup” manual=”mysqladmin%20-u%20root%20-p%20status%0A%0A%0A%0AUptime%3A%202680987%20Threads%3A%201%20Questions%3A%2017494181%20Slow%20queries%3A%200%20Opens%3A%202096%20Flush%20tables%3A%201%20Open%20tables%3A%2064%20Queries%20per%20second%20avg%3A%206.525″ message=”” highlight=”” provider=”manual”/]

 

Queries per second avg:  The average number of queries per second the database receives

Slow queries: The number of queries that have taken more than long_query_time seconds

 

$ mysqladmin -u root -p extended-status

 

4. 執行時間過長的 Query

 

 

 

[pastacode lang=”markup” manual=”%23%20Define%20in%20my.cnf%0A%0Alog_slow_queries%20%3D%20%2Fvar%2Flog%2Fmysql%2Fmysql-slow.log%0A%0Along_query_time%20%3D%202%0A%0A” message=”” highlight=”” provider=”manual”/]

 

[pastacode lang=”markup” manual=”%24%20mysqladmin%20-u%20root%20-p%20processlist” message=”” highlight=”” provider=”manual”/]

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *