Things can be stressful when yo ur database is having a pro blem. During times o f stress, it's no rmal to want to get things back up and running as quickly as po ssible. This co uld be dangero us—if yo u resto re fro m backup in an attempt to reco ver fro m an erro r, are yo u sure yo u'll so lve the pro blem? What if yo u lo se data?
Keeping co o l and fo llo wing a series o f steps will help yo u so lve any database pro blem yo u might enco unter.
Step 1: Collect Information
Tech suppo rt staff have co untless sto ries o f clueless users who repo rt "co mputer pro blems." What is the pro blem—is the mo use no t wo rking? Is it the printer? Is it a pro blem with Excel, o r Firefo x? So metimes narro wing do wn the pro blem is half the battle.
It is very impo rtant to clearly define the pro blem with yo ur MySQL database in very specific terms. If so meo ne is experiencing slo w query times, find o ut what query is slo w. Define slow—did a query that used to take o ne minute no w take an ho ur?
Be sure to ask "the o bvio us" questio ns—what MySQL server (and what versio n o f the server) are yo u using? Ho w is it co nfigured? What versio n o f the client o r pro gram are yo u using? Many times there are no pro blems —just a co nfused user co nnected to the wro ng database, o r using the wro ng versio n o f the so ftware.
Checking the versio n o f MySQL is straightfo rward—just use the --ve rsio n argument. Type the fo llo wing at the Unix pro mpt:
cold1:~$ mysql --version
mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1 cold1:~$
Getting co nfiguratio n settings is easy as well—yo u can use the SHOW VARIABLES co mmand. Co nnect to MySQL as ro o t and run this co mmand.
Type the fo llo wing at the MySQL pro mpt: mysql> SHOW VARIABLES;
+---+--- ---+ | Variable_name | Value | +---+--- ---+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 |
... many lines omitted ...
| tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.1.69 |
| version_comment | Source distribution
| | version_compile_machine | x86_64 | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | | warning_count | 0 | +---+--- ---+ 277 rows in set (0.00 sec)
mysql>
So me interesting variables are:
Variable De script io n
datadir The physical lo catio n o f MySQL's data files. Yo u sho uld check this value if yo u suspect yo urdisk is running lo w o n disk space, to make sure yo u are lo o king at the co rrect disk. table_type The default table type—mo re than likely MyISAM o r Inno DB.
versio n The specific versio n o f MySQL yo u are using. This is useful when tracking do wn versio nspecific issues o r bugs. One additio nal co mmand will give yo u even mo re data: SHOW ST AT US.
Type the fo llo wing at the MySQL pro mpt: mysql> SHOW STATUS;
+---+---+ | Variable_name | Value | +---+---+ | Aborted_clients | 1 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 153 | | Bytes_sent | 16489 |
... many lines omitted ...
| Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 15 | | Threads_running | 1 | | Uptime | 249757 | | Uptime_since_flush_status | 249757 | +---+---+ 291 rows in set (0.00 sec)
mysql>
So me interesting variables are:
Variable De script io n
Abo rted_clients, Abo rted_co nnects
The number o f abo rted clients and co nnectio ns. If either number is increasing, there may be a netwo rk pro blem.
Created_tmp_disk_tables, Created_tmp_files, Created_tmp_tables
Number o f tempo rary o bjects MySQL has created in o rder to answer queries. If the number is increasing, so me queries may be perfo rming po o rly.
Slo w_queries The number o f queries that MySQL co nsiders to be "slo w." A largenumber co uld indicate perfo rmance pro blems. Uptime Ho w lo ng the MySQL server has been running. If clo se to zero , theMySQL server was recently restarted, o r crashed and was restarted. If yo u are interested in seeing ho w Abo rt e d_clie nt s changes o ver time, yo u co uld run SHOW ST AT US; in the mo rning and again in the afterno o n, and co mpare yo ur numbers. Yo u co uld also reset MySQL's co unters to see ho w things change o ver time. To do this yo u can use the FLUSH ST AT US co mmand.
Type the fo llo wing at the MySQL pro mpt: mysql> FLUSH STATUS; SHOW STATUS;
Query OK, 0 rows affected (0.00 sec)
+---+---+
| Variable_name | Value |
+---+---+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
... many lines omitted ...
| Uptime | 250182 |
| Uptime_since_flush_status | 0 |
+---+---+
291 rows in set (0.00 sec) mysql> No tice ho w the Abo rt e d_clie nt s and Upt im e _since _f lush_st at us statistics were reset to zero . If yo u've co llected all info rmatio n, and yo u still have an issue...
Step 2: Get More Information
Once yo u have a clear understanding o f the pro blem, and are aware o f details like so ftware versio ns, yo u can start researching additio nal info rmatio n. So me additio nal questio ns yo u might ask include:
Did the data center have any po wer o r co nnectio n issues? Did anyo ne add, remo ve, o r change so ftware recently?
Is there any significance to to day—did the web site get mo re traffic? Is end-o f-mo nth pro cessing taking place?
Physically check the server. Many servers will beep o r flash lights to alert users when there is a hardware pro blem. Are there any messages o n the server's mo nito r? Are there any strange entries in the server lo g files?
On a Unix system, yo u can use dm e sg to see the co nso le messages. Yo u can also use the t ail co mmand to see the last entries o f the lo g files. Let's try it!
Type the fo llo wing at the Unix pro mpt:
cold1:~$ tail mysql/data/cold1.useractive.com.err
070606 15:28:51 mysqld started
070606 15:28:51 InnoDB: Started; log sequence number 0 43655
070606 15:28:51 [Note] /users/username/mysql/libexec/mysqld: ready for connectio ns.
Version: '5.0.41-OREILLY' socket: 'mysql.sock' port: 0 Source distribution
The o utput fro m yo ur erro r lo g will be different, but it will be similar to the abo ve.