Archive for the ‘MySQL’ Category

Better “read_only” slaves in MariaDB / MySQL

UPDATE: As of MySQL 5.7.8 there is super_read_only so use this instead of this trick. It’s always been an annoyance that read_only in MySQL actually means “read only apart from those with the SUPER priv”. Now I know it’s best practice not to give this permission out to users but sometimes we are stuck with the choices […]

Table & Tablespace encryption in MariaDB 10.1.3

Here’s just a few notes detailing my investigations into table & tablespace encryption in MariaDB 10.1.3. Table Encryption First Generate encryption keys ?View Code BASHlinux> openssl enc -aes-256-cbc -k secretPassword -P -md sha1 Next build your key file from the above output. This should be in the following format.. 1;<iv>;<key> This will look something like… […]

Grok expression for MariaDB Audit Log

Here’s a grok expression for the MariaDB Audit Plugin Log. This has only been tested against CONNECT/DISCONNECT/FAILED_CONNECT events and will likely need modification for other event types. ^%{YEAR:year}%{MONTHNUM:month}%{MONTHDAY:day} %{TIME:time},%{GREEDYDATA:host},%{GREEDYDATA:username},%{GREEDYDATA:client_hostname},%{INT:connection_id},%{INT:query_id},%{GREEDYDATA:operation},%{GREEDYDATA:schema},%{GREEDYDATA:object},%{INT:return_code}

innodb_buffer_page queries

If you want to get some high level statistics on the buffer pool in MySQL / MariaDB you can use the INNODB_BUFFER_POOL_STATS table in the information_schema database. If you need a little more detail then the INNODB_BUFFER_PAGE table is a good place to start. This view contains details about each page in the buffer pool. […]

Missing InnoDB information_schema Views in MariaDB

While working on a MariaDB 10.0.14 instance today I noticed the INNODB_% tables were missing from information_schema. I could tell the InnoDB plugin was loaded. ?View Code MYSQLSHOW PLUGINS; Name Status Type Library License InnoDB ACTIVE STORAGE ENGINE GPL Checking the documentation I could see that these views are supplied by the innodb plugin itself. […]