OBSERVE:
/bin/kill -s ILL `cat mysql/data/cold1.useractive.com.pid`
We simulated a failure by using the Unix kill co mmand to send an ILL signal to MySQL's pro ce ss. Yo u do n't need to wo rry abo ut the details o n ho w we are shutting do wn MySQL.
Let's take a lo o k at the erro r lo g to see what happened. We'll use the -n o ptio n to view the last 43 lines o f the erro r lo g. Why 43? It happens to be the number o f lines added by the kill co mmand. In reality yo u wo uldn't kno w ho w many lines to view in the erro r lo g, so yo u might view the last 25 lines, then the last 10 0 lines if 25 was no t eno ugh.
Type the fo llo wing at the Unix pro mpt:
cold1:~$ tail -n 43 mysql/data/cold1.useractive.com.err
080925 15:14:04—mysqld got signal 4;
This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.
key_buffer_size=16384 read_buffer_size=258048 max_used_connections=1 max_connections=100 threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 31615 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation. thd=(nil)
Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong...
Cannot determine thread, fp=0xbe7ff48c, backtrace may not be correct. Stack range sanity check OK, backtrace follows:
0x8166ed3 0x40027f3c 0x40134a09 0x400282d4 0x81673c3 0x400250c8 0x401dc9ea
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0
080925 15:14:06 mysqld restarted
080925 15:14:06 InnoDB: Started; log sequence number 0 54140484
080925 15:14:06 [Note] /users/username/mysql/libexec/mysqld: ready for connectio ns.
Version: '5.0.41-OREILLY' socket: 'mysql.sock' port: 0 Source distribution cold1:~$
MySQL gave us a de script io n o f t he e rro r. The ve ry last m e ssage lets us kno w that MySQL was able to restart itself.
This failure is just a simulatio n, so no thing is actually wro ng with o ur MySQL server. If it wasn't a simulatio n, we co uld use this erro r lo g to search MySQL's web site fo r a so lutio n.
Step 3: T est
At this po int, if yo u identified so me physical pro blem such as a failed disk in a RAID (Redundant Array o f Inexpensive Disks), yo ur jo b will likely be do ne as so o n as yo u replace the disk and the RAID finishes rebuilding. Ho wever, if yo ur pro blem is so ftware based, yo u have a bit mo re wo rk to do .
Suppo se yo u read o n MySQL's fo rums that there is a kno wn bug with the versio n o f MySQL yo u are currently using. Yo u decide yo ur pro blem will be fixed by upgrading MySQL to the mo st current versio n. Do yo u go ahead and update yo ur database server?
NO!!!
Back in the first lesso n we stressed the impo rtance o f having a develo pment server. Yo u really need to have at least o ne no n-pro ductio n machine yo u can use to test all changes witho ut impacting yo ur pro ductio n server. What if the new versio n o f MySQL has bugs that are much wo rse than the bugs yo u are trying to fix? Ho w will yo u kno w if the new versio n o f MySQL will fix yo ur pro blem? Think abo ut ho w yo u will measure success o r failure. Will a query run faster after yo u apply the fix? Will the server sto p crashing when so meo ne tries to run a particularly difficult query? Be co mplete with yo ur testing criteria.
Here are the steps yo u need to co mplete when testing a fix to yo ur pro blem:
1. Make sure yo ur test server matches yo ur tro ubled server. Install o ld versio ns o f MySQL if necessary.
2. If po ssible, resto re yo ur last pro ductio n backup to yo ur test server. 3. Implement yo ur fix.
4. Evaluate the results. If the test failed, go back to step 1. If yo ur test is successful, yo u are ready to mo ve o n to the last step.
Step 4: Implement
Befo re yo u jump right in and implement yo ur fix, co nsider a few additio nal issues: Do yo u need to back up pro ductio n befo re yo u co ntinue?
Will the server be inaccessible fo r a perio d o f time? Can the fix wait until o ff-peak times?
What sho uld happen if the fix do esn't wo rk?
There are no standard co rrect answers fo r these questio ns. Fo r so me pro blems, it may be o kay to interrupt wo rk to implement a fix as so o n as po ssible. Other pro blems may have to wait until the night, a weekend, o r even a ho liday. As a database administrato r, yo u'll have to info rm the business users o f the situatio n and wo rk with them to co me to so me co nclusio n.
Yo u made it! Yo u're o n the path to beco ming an expert MySQL administrato r! Databases are co mplex pieces o f so ftware with many "mo ving parts." Each situatio n is unique, and each co mpany database is different. The key to yo ur success is to practice, practice, practice! Go o d luck!
Copyright © 1998-2013 O'Reilly Media, Inc.
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. See http://creativecommons.org/licenses/by-sa/3.0/legalcode for more information.