MySQL-AutoXtrabackup command line tool for using Percona Xtrabackup

Want to introduce our MySQL-AutoXtraBackup command line tool, using Percona Xtrabackup in core. Looking for contributor 😉

Project Structure:

XtraBackup is powerfull and open-source hot online backup tool for MySQL from Percona.
This script is using XtraBackup for full and incremental backups, also for preparing and recovering taken backups.
Here is project tree:

* backup_dir — The main folder for storing backups.
* master_backup_script — Full and Incremental backup taker script.
* backup_prepare — Backup prepare and restore script.
* partial_recovery — Partial table recovery script.
* general_conf — All-in-one config file’s and config reader class folder.
* setup.py — Setup file.
* autoxtrabackup.py — Commandline Tool provider script.
* /etc/bck.conf — Config file will be created from general_conf/bck.conf

Now there is a config file located in ‘/etc/bck.conf’ where you should be able to change your settings and use.

Here you can watch Demo Usage Video.

Add Patch to MySQL source code in Linux

Due to reported BUG report(BUG 73365) i am testing full disk error conditions with MySQL 5.6.19-debug-log.

There were some interesting error messages in error log:

2014-07-23 08:09:59 7f44500f0700 InnoDB: Error: Write to file ./xxx/news.ibd failed at offset 218103808.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.

InnoDB: 1048576 bytes should have been written, only -1 were written.
So what is “-1” here?

The answer from:

[24 Jul 16:02] David Bennett

The -1 value is an error return from the os_file_pwrite() function in storage/innobase/os/os0file.c. This only occurs on non-Windows platforms. On Windows the WriteFile functions _Out_opt_ lpNumberOfBytesWritten is used which will return 0 on error. A patch is attached which will correct the error message.

David Bennett attached an patch to my report.
There is no such terrible situation with patches.Patch is simply a text file which describe code difference and etc.
So content of attached patch file:

=== modified file 'storage/innobase/os/os0file.c'
--- storage/innobase/os/os0file.c       2014-02-06 10:25:06 +0000
+++ storage/innobase/os/os0file.c       2014-07-24 15:56:14 +0000
@@ -3045,6 +3045,8 @@

        if (!os_has_said_disk_full) {

+               if (ret == -1) ret++;
+
                ut_print_timestamp(stderr);

                fprintf(stderr,

It is clear that at line 3045 or nearby you must add if (ret == -1) ret++; line to os0file.c file which is in storage/innobase/os/.

Let’s try:

[root@localhost ~]# cd mysql-5.6.19/storage/innobase/os/
[root@localhost os]# ls
os0file.cc  os0proc.cc  os0sync.cc  os0thread.cc

Open this file with option to go to 3045’s line:

[root@localhost os]# nano +3045 os0file.cc
*
*
*
if (!os_has_said_disk_full) {
                // Added New Line 
                if (ret == -1) ret++;

                ut_print_timestamp(stderr);

                fprintf(stderr,
                        " InnoDB: Error: Write to file %s failed"
                        " at offset " UINT64PF ".n"
                        "InnoDB: %lu bytes should have been written,"
                        " only %ld were written.n"
                        "InnoDB: Operating system error number %lu.n"
                        "InnoDB: Check that your OS and file system"
                        " support files of this size.n"
                        "InnoDB: Check also that the disk is not full"
                        " or a disk quota exceeded.n",
                        name, offset, n, (lint) ret,
                        (ulint) errno);
                if (strerror(errno) != NULL) {
                        fprintf(stderr,
                                "InnoDB: Error number %d means '%s'.n",
                                errno, strerror(errno));
                }

                fprintf(stderr,
                        "InnoDB: Some operating system error numbers"
                        " are described atn"
                        "InnoDB: "
                        REFMAN "operating-system-error-codes.htmln");

                os_has_said_disk_full = TRUE;
        }

As you see, error messages is provided by this code lines and we added new line from patch.
Save this file and build/compile MySQL again. (See: Install MySQL from source with debugging option in Linux)

And again i create a full disk error condition but now the “-1” dissappears from error log, it is fixed:

InnoDB: 1048576 bytes should have been written, only 0 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at

Thank you.

Change binary log and relay log location to /home? SELinux issue

Recently in my slave server there was a full disk error because of the huge size of binary logs.Which is lead to critical error and BUG report which is still stays as open: http://bugs.mysql.com/bug.php?id=72437
So the issue was a partition problem which in partitioning stage haven’t been specified for “/” (root) filesystem. But in “/home” there were left enough space. The quick decision was to change binary and relay log location(path) to “/home”. But there was a great challenge to get real working slave again. Now we will explore all steps in our test virtual machine.
Test conditions for me: 1. CentOS 6.5 (x86_64) (both master and slave) 2. MySQL 5.6.19 GA (from official yum repo) (both master and slave)
Master’s my.cnf:

# BINARY LOGGING # 
server_id = 1 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1 
log_slave_updates = 1

Slave’s original my.cnf:

# BINARY LOGGING # 
server_id = 2 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /var/lib/mysql/data/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

So we assume that we have already working master->slave topology. Let’s examine Slave Host for further explanation:

[root@linuxsrv2 home]# df -h 
Filesystem Size Used Avail Use% Mounted on 
/dev/mapper/vg_linuxsrv1-lv_root 48G 2,3G 43G 6% / 
tmpfs 246M 0 246M 0% /dev/shm 
/dev/sda1 485M 57M 403M 13% /boot 
/dev/mapper/new_lv_linuxsrv1-lv_home 7,4G 145M 6,9G 3% /home

So we have seperate free space on /home directory and now we want to change slave’s binary log and reloy log files into this directory. Step-by-step:

1. Create directory:

[root@linuxsrv2 ~]# mkdir /home/binlogs 
[root@linuxsrv2 ~]# ls -ld /home/binlogs/ 
drwxr-xr-x. 2 root root 4096 2014-06-13 15:39 /home/binlogs/

2. Change Slave’s my.cnf to reflect new location. So the new my.cnf of slave must be something like this:

# BINARY LOGGING # 
server_id = 2 
log_bin = /home/binlogs/mysql-bin 
log_bin_index = /home/binlogs/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /home/binlogs/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

3. Stop MySQL slave server:

[root@linuxsrv2 ~]# service mysqld stop 
Stopping mysqld: [ OK ]

4. Move all binary log and relay log files to new directory “/home/binlogs”:

[root@linuxsrv2 ~]# cd /var/lib/mysql/data/
[root@linuxsrv2 data]# ls 
mysql-bin.000001 mysql-bin.index mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-relay-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-slow.log 
[root@linuxsrv2 data]# ls -l /home/binlogs/ 
total 20 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 37 2014-06-13 15:28 mysql-bin.index 
-rw-rw----. 1 mysql mysql 741 2014-06-13 15:31 mysql-relay-bin.000003 
-rw-rw----. 1 mysql mysql 471 2014-06-13 15:46 mysql-relay-bin.000004 
-rw-rw----. 1 mysql mysql 86 2014-06-13 15:31 mysql-relay-bin.index

5. Change owner of /home/binlogs to mysql:mysql:

[root@linuxsrv2 data]# cd /home 
[root@linuxsrv2 home]# ls binlogs lost+found my_changed.cnf my_original.cnf
[root@linuxsrv2 home]# chown -R mysql:mysql binlogs

6. Go to new directory edit *.index file of both binary logs and relay logs. Why? Open these files and you will see that, the old path is still there:

[root@linuxsrv2 binlogs]# cat mysql-bin.index
/var/lib/mysql/data/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/var/lib/mysql/data/mysql-relay-bin.000003
/var/lib/mysql/data/mysql-relay-bin.000004 

So after editing it must be something like:

[root@linuxsrv2 binlogs]# cat mysql-bin.index 
/home/binlogs/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/home/binlogs/mysql-relay-bin.000003 
/home/binlogs/mysql-relay-bin.000004

7. Now try to start MySQL:

[root@linuxsrv2 binlogs]# service mysqld start 
MySQL Daemon failed to start. 
Starting mysqld: [FAILED]

See error log:

140613 15:46:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140613 16:01:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
2014-06-13 16:02:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 
2014-06-13 16:02:16 12358 [Warning] Buffered warning: Performance schema disabled (reason: init failed). ^G/usr/sbin/mysqld: File '/home/binlogs/mysql-bin.index' not found (Errcode: 13 - Permission denied) 
2014-06-13 16:02:16 12358 [ERROR] Aborting 
2014-06-13 16:02:16 12358 [Note] Binlog end 
2014-06-13 16:02:16 12358 [Note] /usr/sbin/mysqld: Shutdown complete

But as you see we changed owner of this directory to mysql user, still error occured. SELinux come ups with it’s blocking functionalty here to prevent mysql to use other directories rather than, /var/lib/mysql. Simply you can disable SELinux at all, but it is not a best practice. First possible way is to run following SElinux related commands:

$ semanage fcontext -a -t mysqld_db_t “/home(/.*)?” 
$ restorecon -Rv /home

But if you are not comfortable with these commands, you can just do it automatically using audit2allow:

8. Install “policycoreutils-python” on Linux:

[root@linuxsrv2 ~]# yum install policycoreutils-python
[root@linuxsrv2 ~]# rpm -qa | grep policy*
selinux-policy-3.7.19-231.el6_5.3.noarch
policycoreutils-python-2.0.83-19.39.el6.x86_64 checkpolicy-2.0.22-1.el6.x86_64
policycoreutils-2.0.83-19.39.el6.x86_64
selinux-policy-targeted-3.7.19-231.el6_5.3.noarch 

9. Configuring SELinux:
##1##

[root@linuxsrv2 home]# audit2allow -w -a 
type=AVC msg=audit(1402659916.941:291): avc: denied { search } for pid=14356 comm="mysqld" name="/" dev=dm-2 ino=2 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:file_t:s0 tclass=dir Was caused by: Missing type enforcement (TE) allow rule. You can use audit2allow to generate a loadable module to allow this access. 


[root@linuxsrv2 home]# audit2allow -a 
#============= mysqld_t ============== #
!!!! The source type 'mysqld_t' can write to a 'dir' of the following types: # var_log_t, mysqld_var_run_t, mysqld_db_t, tmp_t, mysqld_tmp_t, var_lib_t, var_run_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:dir { write search read remove_name open add_name }; 
#!!!! The source type 'mysqld_t' can write to a 'file' of the following types: # mysqld_var_run_t, mysqld_db_t, mysqld_tmp_t, mysqld_log_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:file { rename read create write getattr unlink open append };

##3##

[root@linuxsrv2 home]# audit2allow -a -M mymysqlallow 
******************** IMPORTANT *********************** 
To make this policy package active, execute: semodule -i mymysqlallow.pp 

##4##

[root@linuxsrv2 home]# ls 
binlogs lost+found my_changed.cnf mymysqlallow.pp mymysqlallow.te
my_original.cnf 
[root@linuxsrv2 home]# semodule -i mymysqlallow.pp 

10. Try again to start MySQL:

[root@linuxsrv2 ~]# service mysqld start Starting mysqld: [ OK ]

But actually Slave does not work now:
From slave status: ** Slave_IO_Running: No Slave_SQL_Running: No**
And from error log:

2014-06-13 17:03:36 15360 [ERROR] Failed to open the relay log '/var/lib/mysql/data/mysql-relay-bin.000004' (relay_log_pos 448). 
2014-06-13 17:03:36 15360 [ERROR] Could not find target log file mentioned in relay log info in the index file '/home/binlogs/mysql-relay-bin.index' during relay log initialization. 
2014-06-13 17:03:36 15360 [ERROR] Failed to initialize the master info structure 
2014-06-13 17:03:36 15360 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 

Here is another issue, in fact we defined path correctly in mysql-relay-bin.index file but it shows the old path. So Why? As examining mysql database you will see some tables like:

mysql> show tables like 'slave%'; 
+--------------------------+ 
| Tables_in_mysql (slave%) |
+--------------------------+ 
| slave_master_info | 
| slave_relay_log_info | 
| slave_worker_info | 
+--------------------------+ 
3 rows in set (0,00 sec)

Select from slave_relay_log_info table:

mysql> select * from slave_relay_log_infoG 
*************************** 1. row *************************** 
Number_of_lines: 7 
Relay_log_name: /var/lib/mysql/data/mysql-relay-bin.000004 
Relay_log_pos: 448 
Master_log_name: mysql-bin.000002 
Master_log_pos: 478 
Sql_delay: 0 
Number_of_workers: 0 
Id: 1 
1 row in set (0,00 sec) 

Yes as you see , although, we have defined new path it is not updated in this table. So quite straight option is manually updating this table to new path:

mysql> update slave_relay_log_info set relay_log_name='/home/binlogs/mysql-relay-bin.000004' where id=1; 
Query OK, 1 row affected (0,05 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

11. Restart MySQL again:

[root@linuxsrv2 ~]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

Check Slave if it is working properly now? Slave_IO_Running: Yes Slave_SQL_Running: Yes Yes now the binary logs and relay logs of slave server is located in /home directory:

[root@linuxsrv2 ~]# ls -l /home/binlogs/ 
total 28 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 214 2014-06-13 17:21 mysql-bin.000002 
-rw-rw----. 1 mysql mysql 333 2014-06-13 17:26 mysql-bin.000003 
-rw-rw----. 1 mysql mysql 93 2014-06-13 17:22 mysql-bin.index 
-rw-rw----. 1 mysql mysql 173 2014-06-13 17:22 mysql-relay-bin.000006 
-rw-rw----. 1 mysql mysql 590 2014-06-13 17:26 mysql-relay-bin.000007 
-rw-rw----. 1 mysql mysql 74 2014-06-13 17:22 mysql-relay-bin.index

Thanks for reading 😉

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