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:**

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 :

Secure connection? Today we will explore an interesting task about activating and using SSL connection in MySQL. We will provide all necessary scripts and commands to re-play all steps. So assume that we have CentOS 6.5 with OpenSSL and MySQL already installed:

Our test host is a Virtual Machine with static ip address: 192.168.1.77. The rest of contents of this article is quite straight just follow commands:

### Create Environment ###
[root@linuxsrv3 ~]# cd /etc/
[root@linuxsrv3 etc]# mkdir mysql_ssl_certs
[root@linuxsrv3 etc]# ls -ld mysql_ssl_certs/
drwxr-xr-x. 2 root root 4096 2014-05-27 17:33 mysql_ssl_certs/
### Create CA certificates ###
[root@linuxsrv3 etc]# cd mysql_ssl_certs/
# 1
[root@linuxsrv3 mysql_ssl_certs]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus ..............+++ .......................+++ e is 65537 (0x10001)
# 2 [root@linuxsrv3 mysql_ssl_certs]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
# 3 ### Create server certificate #

server-cert.pem = public key, server-key.pem = private key

[root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key ...+++ ..........................+++ writing new private key to 'server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student
# 4 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key
# 5 [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key
# 6 ### Create client certificate ###

client-cert.pem = public key, client-key.pem = private key

[root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key .............................................................+++ ......................+++ writing new private key to 'client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student
# 7 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
# 8
[root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key

That’s all. As you see our client Connection over ssluser is using SSL connection. In Final stage of our article o want to show some coding examples for developers. I use Python 2.7.6 with official mysql-connector package installed. To make article little i paste this codes on gist. So here is the first file is mysql_connection_without_ssl.py: mysql_connection_without_ssl.pyNote: There is an infinite loop inside code just for showing what is unsecured connection like.
Run this Python script(it will select from country table) and on another terminal run ngrep to read packages:

As you see we can see what is retrieved from our select statement. And know lets try The another script which uses SSL: mysql_connection_with_ssl.py Run This Python script. To do this you will have to copy certificates from remote server to local and ofcourse edit my.cnf on local machine to reflect to new certificates. So as you see from Python code we specify paths where certificates reside on our local machine.

Recently i encounter an uprising among PHP Developers that , they must write a script additionally checking the strength of MySQL user password at creation time. So, just for note that there is ready to use plugin in MySQL named: validate_password.so The file location is default in plugin_dir in Linux:

As you see there is a validate_password.so. So what is the defaults of this plugin and how it works? In General if this plugin activated, User will only be able to create passwords with following specifications: 1. Greater or equal to 8 char length. 2. At least 1 number 3. At least 1 non-letter non-number (eg. #, % ,!) 4. At least 1 Upper case letter So the sample password will be: “Sh@rkp45!” or something similar. All weak passwords such as “12345” , “acbsg54” will not allowed to create:

Restart Server. And that’s all. Force + Permanent will guarantee that plugin can not be disabled at run time or while running MySQL. If you want to change default values for this plugin Read documentation: Validate Password Plugin

This topic is not for all situations but in my situation it helps. The problem is with Slave is lagging behind master in very large numbers and the relay logs are continously growing.

Slave is using: Relay_Log_File: mysql-relay-bin.000031.
But in folder where relay log resides, was created up to: mysql-relay-bin.000135 And it is continously growing.
So what i decide to do, again step-by-step i edited my.cnf file as follows:

Auditing maybe the main part of a corporate structure that wants to know everything. Searching for MySQL auditing plugins we come up to 3 known plugins: 1. MySQL Enterprise Audit (Official one from Oracle) – commercial 2. MariaDB audit plugin for MySQL (version 1.1.6) – free 3. McAfee MySQL audit plugin – free
With a great happiness tried to test this plugins. But wait i don’t want to crash my MySQL just want to install and use these plugins. Let’s begin with MariaDB audit plugin for MySQL — at this time the latest version is 1.1.6 With a great effort in SkySQL site they provide us with a tutorial of how to activate this plugin. Check it: SkySQL Tutorial about Plugin Followed all instructions from tutorial and got a crash of MySQL 5.6.17. So there was an error messages with 2 statement: