Since yesterday, the new Percona Server (5.5.37-35.0) includes an audit plugin. This is the announcement on mysqlperformance blog. I needed to have this feature for a customer running an older version of Percona Server (5.5.33-31.1), so I back ported the plugin (revision 654) removing some extra info that is not available on previous version of Percona Server. The information above in struct mysql_event_general (include/mysql/plugin_audit.h) were not yet present:
MYSQL_LEX_STRING general_host; MYSQL_LEX_STRING general_sql_command; MYSQL_LEX_STRING general_external_user; MYSQL_LEX_STRING general_ip;Additionally, as I needed to parse the audit log quickly, I added a new format (CSV) to replace both implementation of XML output (OLD & NEW). This is an example of the new output:
AUDIT_RECORD - Audit::35531_2014-05-07T06:15:32::2014-05-07T06:15:36 UTC::5.5.33-31.1::--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/percona1.err --pid-file=/var/lib/mysql/percona1.pid::x86_64-Linux AUDIT_RECORD - Connect,35532_2014-05-07T06:15:32,2014-05-07T06:16:04 UTC,1,0,root,root,,,localhost, AUDIT_RECORD - Query::35533_2014-05-07T06:15:32::2014-05-07T06:16:04 UTC::1::0::select @@version_comment limit 1::root[root] @ localhost  AUDIT_RECORD - Query::35534_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::SELECT DATABASE()::root[root] @ localhost  AUDIT_RECORD - Init DB::35535_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::(null)::root[root] @ localhost  AUDIT_RECORD - Query::35536_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::show databases::root[root] @ localhost  AUDIT_RECORD - Query::35537_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::show tables::root[root] @ localhost  AUDIT_RECORD - Field List::35538_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::::root[root] @ localhost  AUDIT_RECORD - Field List::35539_2014-05-07T06:15:32::2014-05-07T06:16:20 UTC::1::0::::root[root] @ localhost  AUDIT_RECORD - Query::35540_2014-05-07T06:15:32::2014-05-07T06:16:23 UTC::1::0::show databases::root[root] @ localhost  AUDIT_RECORD - Query::35541_2014-05-07T06:15:32::2014-05-07T06:16:27 UTC::1::0::SELECT DATABASE()::root[root] @ localhost  AUDIT_RECORD - Init DB::35542_2014-05-07T06:15:32::2014-05-07T06:16:27 UTC::1::0::(null)::root[root] @ localhost  AUDIT_RECORD - Query::35543_2014-05-07T06:15:32::2014-05-07T06:16:29 UTC::1::0::show tables::root[root] @ localhost  AUDIT_RECORD - Query::35544_2014-05-07T06:15:32::2014-05-07T06:16:34 UTC::1::0::select * from t::root[root] @ localhost The patch is available below and the compiled plugin for Centos 6 x86_64 for Percona Server 5.5.33-rel31. Update: I've now added the code in launchpad and changed the default audit format to CVS, so the first time the plugin is loaded, CVS is used and no need to restart mysqld to enable CVS.
At Percona Live MySQL Conference & Expo (PLMCE), I had the honor to receive a MySQL Community Award for the "Community Contributor of the year 2014". I was so proud and still I am. This is the reason why I received it: Frederic organizes the MySQL & Friends Devroom at FOSDEM every year. He worked towards making a true community driven event participated by all key players. in 2014 the MySQL & Friends devroom also presented a shared booth/stand regrouping Oracle, MariaDB/SkySQL and Percona engineers and developers. But this Award can't be only mine, I need to share it with all people that helped me in this adventure and that makes it possible. FOSDEM MySQL & Friends Devroom is maybe now the second best event in Europe after Percona Live UK (PLUK). I'm sorry, "apologies upfront" (some people will understand this sentence), but I'll now thanks all the people that deserves it and the list is long: - FOSDEM, for the acceptance of the MySQL & Friends Devroom every year since 2009. - Kris sdog Buytaert, who encouraged me to give my first talk in the MySQL Devroom in 2010. - Lenz Grimmer, to have run the devroom at the beginning - Giuseppe @datacharmer Maxia, for having helped me and introduced me to key players in the Community to be able to create every year a strong Committee with people of different companies and opensource projects - Colin @bytebot Charles, for having participate to all these devrooms as visitor, speaker and committee member (and congratulation for the Award too) - Sergey Petrunia who also helped me the first year I was in charge of the devroom - Henrik @h_ingo Ingo, who was a model for me in how he represented the Open Source in the MySQL ecosystem (you can come back whenever you want) - Oracle, Tungsten, SkySQL, MariaDB, Percona to have allowed their engineers to travel, speak and share the booth with each others - All committee members - All speakers (2014, 2013,2012) - All attendees - and last but not least the Percona Belgian Team, Liz @lizztheblizz van Dijk, Dimitri @dim0 Vanoverbeke and Kenny @gryp Gryp who supported me for the logistic and organized a wonderful Community Dinner this year. Again thank you everybody, this award is our award to all of us !
I will be present in Prato (Firenze), Italy Thursday and Friday (September 19th and 20th) to deliver 2 presentations at OSS4B:
My colleague Kenny hit a bug with Innotop and MySQL 5.6. He submitted a bug with a patch. This is a new pre-release rpm of Innotop (1.9.0-3) that includes that patch. Enjoy it ! [UPDATE] New rpms are now available directly on Innotop's download page
For my first participation to THE MySQL event of the year, I have the honour of delivering a full day tutorial on Opetational DBA in a nutshell with my two Belgian colleagues (call us the be-team ;) ) and a lighting talk related to MySQL database schema migration: how to version them and manage those operations. If you don't want to miss THE essential event of the MySQL ecosystem, use code "SpeakMySQL" at checkout for 15% off! Register here: http://bit.ly/14au67Z. See you soon there !
If you test Galera synchronous replication with Percona XtraDB Cluster or MariaDB Galera Cluster you must have tried to use a load balancer like HA Proxy or Galera Load Balancer. On very heavy load, you may have issue with a large amount of TCP in TIME_WAIT like this one:
tcp 0 0 127.0.0.1:59035 127.0.0.1:3306 TIME_WAITThis can lead to a TCP port exhaustion as explained on this post. On HA proxy since version 1.4.19, you can use the nolinger option also on TCP backends. This terminate the connection (TCP RST) as soon as the loadbalancer finished the communication. The counter part is that Aborted_clients status counter in MySQL increases with each connections' end. This counter becomes then useless. This option is not available on glb (with -l parameter) if you apply the patch attached to this post. I provide also rpm package with the patch applied :
Name : glb Version : 0.9.2 Release : 2 Architecture: x86_64 Install Date: (not installed) Group : Productivity/Networking/Routing Size : 208489 License : GNU General Public License version 2 or later (GPL v2 or later) Signature : (none) Source RPM : glb-0.9.2-2.src.rpm Build Date : mer 27 fév 2013 17:15:54 CET Build Host : percona1 Relocations : (not relocatable) URL : http://www.codership.com/products/galera-load-balancer Summary : TCP Connection Balancer Description : glb is a simple user-space TCP connection balancer made with scalability and performance in mind. It was inspired by pen, but unlike pen its functionality is limited only to balancing generic TCP connections. Features: * list of backend servers is configurable in runtime. * supports server "draining", i.e. does not allocate new connections to server, but does not kill existing ones, waiting for them to end gracefully. * on Linux 2.6 and higher glb uses epoll API for ultimate performance. * glb is multithreaded, so it can utilize multiple CPU cores. In fact even on a single core CPU using several threads can significantly improve performance when using poll()-based IO. * connections are distributed proportionally to weights assigned to backend servers. * this is a patched version providing SO_LINGERExample:
[root@macbookair ~]# glbd -K -l --threads 6 --control 127.0.0.1:4444 127.0.0.1:3308 127.0.0.1:3306 glb v0.9.2 (epoll) Incoming address: 127.0.0.1:3308 , control FIFO: /tmp/glbd.fifo Control address: 127.0.0.1:4444 Number of threads: 6, max conn: 493, policy: 'least connected', top: NO, nodelay: ON, keepalive: OFF, defer accept: OFF, verbose: OFF, linger: ON, daemon: NO Destinations: 1 0: 127.0.0.1:3306 , w: 1.000 Router: ------------------------------------------------------ Address : weight usage map conns 127.0.0.1:3306 : 1.000 0.000 N/A 0 ------------------------------------------------------ Destinations: 1, total connections: 0 of 493 max Pool: connections per thread: 0 0 0 0 0 0If you test it please post a comment.
If you followed previous post about init script (here and here) for Percona XtraDB Cluster, I proposed a new solution to standardize the bootstrap of the cluster in debian/ubuntu and redhat/centos instead of using 2 different ways. You can read about this on launchpad bug #1152764. So now to start the first node of a cluster when no other node is already running, you just need to use bootstrap command with the init script like bellow:
# /etc/init.d/mysql bootstrap Starting MySQL (Percona XtraDB Cluster)..... SUCCESS!I attached the patches here too.
Recently I wrote a blog post with a patch for Percona XtraDB Cluster Init script. But this was more a dirty hack then a real debian/ubuntu patch as Jan pointed it to me. So I rewrote that patch to not work as the init script of RedHat/CentOS but more like it should work on Debian, Now to initialize the cluster, on the very first node that will start the cluster, you need to run the following command:
root@percona1:/etc/init.d# /etc/init.d/mysql initialize * Starting MySQL (Percona XtraDB Cluster) database server and initialize the cluster mysqld [ OK ] * Checking for corrupt, not cleanly closed and upgrade needing tables.Please note, the new command initialize. I hope this patch is better for Debian afficionados ;-)
Codership released a new version of the load balancer for Galera. I made new rpms but I forgot to share them ;) Here they are ! This new version provides ] a "single" balancing policy where all connections are directed to a single destination chosen by highest weight, a --top option that forces balancing only between the destinations with the highest weight, and a SO_KEEPALIVE option on destination connections (default: on) for timely detection of the destination failure.
On mysqlperformanceblog, I wrote on what was an easy solution to initialize a PXC cluster without modifying the configuration file (my.cnf) but only by providing a parameter to the init script. (link to that post) Unfortunately, this works only on RedHat systems (including CentOS, Fedora... For Debian/Ubuntu, I wrote a minimal patch allowing that. The patch is attached to this post. Enjoy !
2 days ago was released a new version of glb (Galera Load Balancer) that fixes connection problem (see the release note). If you want to try it on CentOS 6, I built a rpm.