Dear all, our today’s topic is about finding out the cause of MySQL crash and in depth examining issue for BUG reporting. So what is exact mean of this article? While i reported BUGs for MySQL, developers(experts) every time ask for a core dump (with gdb) and full stack-trace. Yeap they are right, finding an issue is only one step. You must also provide all necessary information to MySQL developers if you really interested in fixing bugs in open source. You have to help to find the core of problem. For this purpose you must have a DEBUG MySQL edition on your hand to generate core dump and to see full stack-trace. Yes as you think, we will install MySQL from source with DEBUG option and will create core dump. But first of all let to tell a few theory: * There are several kinds of logging in MySQL:
1. General Log -> will log all kind of database activity. Surely you will not enable it in production because off heavy load, Maybe for a few minutes for auditing will be sufficient to your needs.
2. Slow Quer Log -> will log all queries which is default running more than 10 seconds. Ofcourse you can change default time to less value.
3. Error Log -> From start of MySQL it will log all warnings, errors and events to this log file. It is the first file where you must watch for any kind of problem. There will be somewhat stack-trace looking something like:
Thread pointer: 0x0 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... stack_bottom = 0 thread_stack 0x40000 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xab6ead] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x404)[0x736ee0] /lib64/libpthread.so.0[0x35df20f710] /usr/local/mysql/bin/mysqld[0xd27e2e] /usr/local/mysql/bin/mysqld[0xd1cc5e] /usr/local/mysql/bin/mysqld[0xc5d5f6] /usr/local/mysql/bin/mysqld[0xc5dca7] /usr/local/mysql/bin/mysqld[0xc5e152] /lib64/libpthread.so.0[0x35df2079d1] /lib64/libc.so.6(clone+0x6d)[0x35deee8b5d]
Appending trace to BUG report will be valuable, but most of times developers want core dump. Now it is time to install MySQL from source with DEBUG option. Go through every step: ** Install Dependencies:**
[root@localhost ~]# yum groupinstall "Development Tools" [root@localhost ~]# yum install cmake [root@localhost ~]# yum install ncurses ncurses-devel [root@localhost ~]# yum install wget
Download tar archive from: mysql-5.6.19.tar.gz Then:
1. [root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.19.tar.gz 2. [root@localhost ~]# tar -xvf mysql-5.6.19.tar.gz 3. [root@localhost ~]# groupadd mysql 4. [root@localhost ~]# useradd -r -g mysql mysql 5. [root@localhost ~]# cd mysql-5.6.19 6. [root@localhost mysql-5.6.19]# cmake -DWITH_DEBUG=1 7. [root@localhost mysql-5.6.19]# make 8. [root@localhost mysql-5.6.19]# make install
After install finished:
8. [root@localhost mysql-5.6.19]# cd /usr/local/mysql 9. [root@localhost mysql]# chown -R mysql 10. [root@localhost mysql]# chgrp -R mysql 11. [root@localhost mysql]# scripts/mysql_install_db --user=mysql 2014-07-21 03:52:05 14280 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!! 2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_SYNC_DEBUG switched on !!!!!!!!!
You will see difference in error log too. After all:
12. [root@localhost mysql]# chown -R root 13. [root@localhost mysql]# chown -R mysql data 14. [root@localhost mysql]# bin/mysqld_safe --user=mysql & 15. [root@localhost mysql]# bin/mysqld_safe --user=mysql &  14525 [root@localhost mysql]# 140721 03:55:41 mysqld_safe Logging to '/var/log/mysqld.log'. 140721 03:55:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
AS you see MySQL is started and at this point you can begin to test whatever you want. When you connect you will see difference:
[root@localhost bin]# ./mysql -u root --socket=/usr/local/mysql/data/mysql.sock Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.6.19-debug-log Source distribution
Now we have a DEBUG MySQL , we can try to create a core dump: Add this line under [mysqld] catalog: [mysqld] core-file And also append this to the end of my.cnf file: [mysqld_safe] core_file_size=unlimited And restart MySQL. If some crash happens you will see :
bin/mysqld_safe: line 166: 27247 Segmentation fault (core dumped)
Core Dumped indicates that, core file is created. Default core file will reside in MySQL datadir. Thank You!.