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