Copying Tablespaces to Another Server (Transportable Tablespaces)

If you want take several actions on tables? It is really very exciting 😛 Start MySQL 5.6.17(as we have) with:

    [mysqld] 
    innodb_file_per_table = 1 
    lower_case_table_names = 1

Then create a huge table or find one from friend’s database to testing. First off all let’s to rename table which is nearly 2.4 gigs.

[root@linuxsrv3 xxx]# ls -lthr 
    total 2,4G 
    -rw-rw----. 1 mysql mysql 65 2014-05-07 11:05 db.opt
    -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table.frm
    -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table.ibd

We have renamed test_table to test_table2.:

    [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table to xxx.test_table2' 
    Enter password: 
    real 0m1.973s 
    user 0m0.002s 
    sys 0m0.004s 

It takes only 1.9 second. So what if we want to move table from one database to another. There is no need to backup this table and then import. Use Rename.

   [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table2 to test.test_table2' 
   Enter password: 
   real 0m1.876s 
   user 0m0.005s 
   sys 0m0.003s 

And very mysteriously our table is in test database now:

   [root@linuxsrv3 test]# ls -lthr 
   total 2,4G 
   -rw-rw----. 1 mysql mysql 8,4K 2014-05-12 11:32 ff.frm 
   -rw-rw----. 1 mysql mysql 96K 2014-05-12 11:33 ff.ibd 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 11:34 db.opt 
   -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table2.frm 
   -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table2.ibd

So quite fast way to move tables. But what about moving one table from one server to another server? Here is a show of transportable tablespaces begin. If you want to use this features you must have a MySQL 5.6.x a good one the latest GA. and it must be started as innodb_file_per_table = 1. Ofcourse you must have identical database names and table names on both side. We have already test_table2 table in test database on linuxsrv3. Created on linuxsrv4 same database and table. Our source server is: linuxsrv3 Destination server is: linuxsrv4 On destination server discard existing tablespace:

    mysql> use test;  
    mysql> alter table test_table2 discard tablespace; 
    Query OK, 0 rows affected (0,05 sec)

If you check you will see no .ibd file on database directory:

    [root@linuxsrv4 test]# ls db.opt test_table2.frm

On source server:

    mysql> use test; 
    Database changed 
    mysql> FLUSH TABLES test_table2 FOR EXPORT; 
    Query OK, 0 rows affected (0,00 sec)

Copy .ibd file to destination server:

    [root@linuxsrv3 test]# scp test_table2.ibd root@192.168.1.88:/var/lib/mysql/test root@192.168.1.88's password: 
    test_table2.ibd 100% 2444MB 15.4MB/s 02:39 

On source server:

    mysql> use test;  
    Database changed 
    mysql> UNLOCK TABLES; 
    Query OK, 0 rows affected (0,00 sec)

On destination server:

   [root@linuxsrv4 test]# ls 
   db.opt 
   test_table2.frm 
   test_table2.ibd 

   mysql> use test; 
   Database changed 
   mysql> alter table test_table2 IMPORT TABLESPACE; 
   ERROR 1146 (42S02): Table 'test.test_table2' doesn't exist

If you see such error it indicates that there is an owner issue:

   [root@linuxsrv4 test]# ls -l 
   total 2502684 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
   -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
   -rw-r-----. 1 root root 2562719744 2014-05-12 15:16 test_table2.ibd

Change file owner to mysql:

    [root@linuxsrv4 test]# chown mysql:mysql test_table2.ibd 
    [root@linuxsrv4 test]# ls -l total 2502684 
    -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
    -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
    -rw-r-----. 1 mysql mysql 2562719744 2014-05-12 15:16 test_table2.ibd

And now try again:

    mysql> use test;  
    Database changed 
    mysql> alter table test_table2 IMPORT TABLESPACE; 
    Query OK, 0 rows affected, 1 warning (2 min 29,38 sec) 

And checking :

    mysql> select idvisit from test_table2 order by idvisit desc limit 1; 
    +---------+ 
    | idvisit | 
    +---------+ 
    | 7016951 | 
    +---------+ 
    1 row in set (0,04 sec)

That’s all in such manner you can move one or more tables to another server for testing or just for what you need.

Install MySQL from source with debugging option in Linux

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 & [1] 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!.

Activate SSL connection in MySQL

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:

[root@linuxsrv3 ~]# mysql --version 
mysql Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using EditLine wrapper
[root@linuxsrv3 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
[root@linuxsrv3 ~]# openssl version 
OpenSSL 1.0.1e-fips 11 Feb 2013
[root@linuxsrv3 ~]# rpm -qf `which openssl` 
openssl-1.0.1e-16.el6_5.7.x86_64 

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

The last step is to verifying certificates:

[root@linuxsrv3 mysql_ssl_certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK 

So let’s summurize that our certificates are stored in /etc/mysql_ssl_certs:

[root@linuxsrv3 mysql_ssl_certs]# ls -l /etc/mysql_ssl_certs/ 
total 32 
-rw-r--r--. 1 root root 1424 2014-05-27 17:37 ca-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:36 ca-key.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:46 client-cert.pem 
-rw-r--r--. 1 root root 1675 2014-05-27 17:45 client-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:45 client-req.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:41 server-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:41 server-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:40 server-req.pem

Going on we must modify MySQL Configuration file(my.cnf) to reflect our generated certificates. In my case my.cnf located in /etc But Before this check if MySQL installation supports SSL:

mysql> SHOW VARIABLES LIKE 'have_ssl'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| have_ssl | YES | 
+---------------+-------+ 
1 row in set (0,10 sec)

If you get “YES” open my.cnf and type under the [mysqld] catalog following:

[mysqld] 
# SSL 
ssl-ca=/etc/mysql_ssl_certs/ca-cert.pem
ssl-cert=/etc/mysql_ssl_certs/server-cert.pem
ssl-key=/etc/mysql_ssl_certs/server-key.pem 

Just NOTE that, this on server side (our virtual machine). Restart MySQL and create user and force it to only SSL connection:

create user 'ssluser'@'%' IDENTIFIED BY 'Pass@123#'; 
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'Pass@123#' REQUIRE SSL;

And let’s try to connect:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

Now, by logic we have a user that reuquires SSL, but our mysql client which we used to connect does not know about this. So we must again edit my.cnf but now under [client] catalog:

[client] 
ssl-cert=/etc/mysql_ssl_certs/client-cert.pem
ssl-key=/etc/mysql_ssl_certs/client-key.pem

Restart MySQL and test again:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
mysql> SHOW STATUS LIKE 'Ssl_cipher'; 
+---------------+--------------------+ 
| Variable_name | Value | 
+---------------+--------------------+ | 
Ssl_cipher | DHE-RSA-AES256-SHA | 
+---------------+--------------------+ 
1 row in set (0,08 sec)

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.py
Note: 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:

sh@sh-work:~$ sudo ngrep 
649.San Miguel.SLV.San Miguel.127696-....650.Nueva San Salvado r.SLV.La Libertad.98400!....651.Apopa.SLV.San Salvador.88800.....652.Asmara .ERI.Maekel.431000.....653.Madrid.ESP.Madrid.2879052$....654.Barcelona.ESP. Katalonia.1503451!....655.Valencia.ESP.Valencia.739412!....656.Sevilla.ESP. Andalusia.701927!....657.Zaragoza.ESP.Aragonia.603367!....658.M..laga.ESP.A ndalusia.530553.....659.Bilbao.ESP.Baskimaa.3575899....660.Las Palmas de Gr an Canaria.ESP.Canary Islands.354757.....661.Murcia.ESP.Murcia.353504)....6 62.Palma de Mallorca.ESP.Balears.326993-....663.Valladolid.ESP.Castilla and Le..n.319998"....664.C..rdoba.ESP.Andalusia.311708.....665.Vigo.ESP.Galici 

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.

root@sh-work:/home/sh/mysql-ssl# ls -l 
total 32 
-rw-r--r-- 1 root root 1424 May 27 18:39 ca-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 ca-key.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 client-cert.pem 
-rw-r--r-- 1 root root 1675 May 27 18:39 client-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 client-req.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 server-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 server-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 server-req.pem

Run Python script and on another terminal run ngrep again to read packages:

sh@sh-work:~$ sudo ngrep 
.OV.*z.S..~8j6.`.. W..j...f.X......0...S....#..d...E%.R.7..?3...<..d..C..T.....D...T.....=_......V..UI+.0s...- .....-.P...B.N,.&.....EF.t..E.e....im.P.....v.A.H...d.h..T..,.H..h.+..........(....F.....K.^.Y..).R.g.........Lh.Zq.0..........|S.......4z .-X.LWN.......q....N.D....^8r...p..x+si.e..`.,P.t..7...i.{c...>)...~...7..X..EC.E...,..W+..ik.58...%r.K..lwG8t'../..(A.A...u...'.VygP&..o ...(!..)..RH....B.Y.[..nD~S....~.*kUC.1....'.g.........k.......D.......^%4.^u....!U.gY.@.hGK&.-..Z8N."./..........1....5.g..N.........2t ..c.U.....W{.&g'...m...........<5d.x.=..$@..0x#.Y.%.......M.........!.....V,.k.2...,.r..:..1..CU.4.y.._.4#.z=.R..t....F......'z~....Lf.ks5 .tX..I...Z...]....)|Q.(.]$....v.P.U.bS..9..l...*].AY...K..o..* .C.%..q...}[.>..3.[..D....v.s.....$..q_.f...E...P8.Sg.Y..~)N..j#.$A..j.[Gm( ....A..`..._.]+.....S....&.k....v...}.f.....4...H.`..%.....$K.....=.V..^.A..ck......_>.s.[XE....:.O.!.S.k..J<..r....Y%..ZU....A...or.....D |l.^[.......H?.........._.NO.m..i.2*Kl

I think you see the difference 🙂 Thank you for reading if you have any questions feel free to ask.

Activate validate_password Plugin

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:

mysql> select @@plugin_dir; 
+--------------------------+ 
| @@plugin_dir | 
+--------------------------+ 
| /usr/lib64/mysql/plugin/ | 
+--------------------------+ 
1 row in set (0,00 sec) 

List this directory:

[root@linuxsrv3 plugin]# ls 
adt_null.so auth_test_plugin.so innodb_engine.so mypluglib.so qa_auth_server.so validate_password.so auth.so daemon_example.ini libdaemon_example.so qa_auth_client.so semisync_master.so auth_socket.so debug libmemcached.so qa_auth_interface.so semisync_slave.so

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:

mysql> create user "nc"@"%" identified by '12345'; 
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 

Question is, will i be able to connect with already created user before activating plugin? Answer is: YES. So how to activate plugin. Simply add this to my.cnf file under [mysqld] catalog:

    # Password Validation # 

    plugin-load=validate_password.so 
    validate-password=FORCE_PLUS_PERMANENT 

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

How to Solve Replication lag? quick solution

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:

1. I have 13 database so give to slave paralel workers 13: slave-parallel-workers = 13

2. Disabled sync_binlog: sync_binlog = 0

3. Changed at_trx_commit from 1 to 0: innodb_flush_log_at_trx_commit = 0

4. Gave log_at_timeout (only from 5.6.6>) to 10: innodb_flush_log_at_timeout = 10

5. Disabled slow query log( or commented out)

So Slave lag was somewhat: Seconds_Behind_Master: 250350 And it was continously increasing but now it is dicreasing: Seconds_Behind_Master: 178860

Again be carefull with this options. And After Slave catchs up Master undo these changes.

Audit MySQL? no just Crash it :)

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:

    INSTALL PLUGIN server_audit SONAME 'server_audit.so';
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

And

    SET GLOBAL server_audit_logging=ON; 
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

From Web APP side the error message is something more interesting:

    SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet',

This is a same error message that we saw after Errcode: 28 – No space left on device.
Reported but still waiting as open 🙂 — 72437.

So The State of my server is very critical and actually i lost my lovely MySQL 😦 Killed all mysql processes. Restarted MySQL looked at error log and here you are:

Version: '5.6.17-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140430 11:21:52 server_audit: MariaDB Audit Plugin version 1.1.6 STARTED. 06:21:52 UTC - mysqld got signal 11 ; . . . Thread pointer: 0x18428270 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 = 7f63a0d78e18 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x385780f500] /lib64/libc.so.6[0x3a2173259f]
/usr/sbin/mysqld[0xabdd60] /usr/sbin/mysqld[0xabe0a0]
/usr/lib64/mysql/plugin/server_audit.so(logger_open+0x10b)[0x7f639bdf806b]
/usr/lib64/mysql/plugin/server_audit.so(+0x4202)[0x7f639bdf8202]
/usr/lib64/mysql/plugin/server_audit.so(+0x5ccf)[0x7f639bdf9ccf]
/usr/sbin/mysqld(_Z23initialize_audit_pluginP13st_plugin_int+0x4d)[0x68f6ad]
/usr/sbin/mysqld[0x6ea481]
/usr/sbin/mysqld(_Z20mysql_install_pluginP3THDPK19st_mysql_lex_stringS3_+0x43f)[0x6eee7f] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0xf04)[0x6deb14]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x32f)[0x6e2b3f]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x140b)[0x6e403b] 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b0f3f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b1067]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xabd8da]
/lib64/libpthread.so.0[0x3857807851] /lib64/libc.so.6(clone+0x6d)[0x3a216e811d] . . . 
    2014-04-30 11:21:54 19033 [Note] InnoDB: Starting crash recovery.
    2014-04-30 11:21:54 19033 [Note] InnoDB: Reading tablespace information from the .ibd files... 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Restoring possible half-written data pages 2014-04-30 11:21:55 19033 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 69056068973 InnoDB: Transaction 246648984 was in the XA prepared state. InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 0 row operations to undo InnoDB: Trx id counter is 246649344 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed [/code] Result is was: MariaDB Audit Plugin Crashed my MySQL 5.6.17... Will i use it? Last step was: 140430 14:40:53 server_audit: STOPPED

with all necessary steps that stated here: McAfee Audit Plugin So no more words and just pasting error log portion:

2014-04-30 19:00:48 14571 [Note] Audit Plugin: starting up. Version: 1.0.4 , Revision: 459 (64bit). AUDIT plugin interface version: 769 (0x301). 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: hot patch for: open_tables (0x69bd00) complete. Audit func: 0x7fb366fcad30, Trampoline address: 0x7fb366fcc060 size: 15.
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Done initializing sql command names. status_vars_index: [141], com_status_vars: [0x1215400]. 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Init completed successfully. 2014-04-30 19:00:48 14571 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin 
2014-04-30 19:00:48 14571 [Note] Starting crash recovery... 
2014-04-30 19:00:48 14571 [Note] Crash recovery finished. 
2014-04-30 19:00:48 14571 [Note] Server hostname (bind-address): '*'; port: 3306 2014-04-30 19:00:48 14571 [Note] IPv6 is available. 
2014-04-30 19:00:48 14571 [Note] - '::' resolves to '::'; 
2014-04-30 19:00:48 14571 [Note] Server socket created on IP: '::'. 14:00:48 UTC - mysqld got signal 11 ; Thread pointer: 0x26e2b70 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 = 7fff90422a78 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x35b6e0f710]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x0)[0x69bd00] 
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x44)[0x69cb74] /usr/sbin/mysqld(_Z10acl_reloadP3THD+0x28f)[0x67c9cf]
/usr/sbin/mysqld(_Z8acl_initb+0x120)[0x67e9d0]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0xa26)[0x59fc96]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x35b6a1ed1d] /usr/sbin/mysqld[0x591949] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 0 Status: NOT_KILLED

Conclusion: 2 of 3 Audit Plugins crashed MySQL 5.6.17. And now Triggers will make us happy or Purchase a commercial product