Archive for the ‘MySQL’ Category

TokuDB file & table sizes with information_schema

Here’s a few queries using the information_schema.TokuDB_fractal_tree_info to get the on disk size in MB for TokuDB tables. This first one will sum up the on disk size for tables using the TokuDB engine. ?View Code MYSQLSELECT table_schema, table_name, SUM(ROUND(bt_size_allocated / 1024 / 1024, 2)) AS table_size_mb FROM information_schema.`TokuDB_fractal_tree_info` WHERE table_schema = ‘database_name’ GROUP BY […]

The behavior of gtid_strict_mode in MariaDB

GTIDs in MariaDB are a nice improvement to replication and make fail-over a simpler process. I struggled a little with the explanation of gtid_strict_mode and what to expect. So I thought I’d run through a simple scenario to make my own understanding clear. In this scenario I have created two instances of MySQL; primary and secondary. […]

Better “read_only” slaves in MariaDB / MySQL

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 others have made. We now have another option in the form of tx_read_only which was […]

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}