Dump MySQL core file

Setup MySQL Core-file

Last year, we experienced a PXC crash where all our nodes just went down in a domino effect. We contacted the percona support team to try and hep us figure out what could have caused this so we can avoid it in the future. Unfortunately, we could not get help from Percona from the log files we provided. 

So we decided to setup MySQL core file to help us troubleshoot future crashes if similar nature. Basically, the core file dumps all MySQL server memory content including buffer pool which can consume so much of your disk space, and hence this effect has to be kept in mind before embarking on enabling core file. Dumping the core file to disk can also takes a lot of time, as in hours, so this should also be put into consideration.

So below are the steps on how to setup MySQL core file

Step1:  first, check if OS has core files enabled

   $ulimit -c     #if 0 or another number is displayed, change this to unlimited by running the following command

   $ulimit -c unlimited

Step2: Restart MySQL

    $sudo /etc/init.d/mysql restart

    

 Step3. Check fs.suid_dumpable value

 Run the following command and make sure fs.suid_dumpable=2

      $sysctl -a | grep dumpable   

if not = 2, then set it to =2 using the following command

       $/sbin/sysctl -w fs.suid_dumpable=2

Step4: Perform following commands as root

    #mkdir /tmp/corefiles

    #chmod 777 /tmp/corefiles

    #echo "/tmp/corefiles/core" > /proc/sys/kernel/core_pattern

    #echo "1" > /proc/sys/kernel/core_uses_pid

*Note - We might want to enable access to both root and mysql to /tmp/corefiles, not sure. So maybe just make the dir accessible by all with chmod 777

Step5: edit my.cnf file

    # my.cnf

    [mysqld]

    core-file

    [mysqld_safe]

    core-file-size = unlimited

Step6: View core file

to view a core-file, enter the the following command using gdb as root:

    #gdb binary-file core-file  #gdb binary-file /tmp/corefiles/core-file

kill mysql with SEGV

    $sudo kill -s SEGV ‘mysql_pid'