Archive for the ‘DBA’ Category

MariaDB Compound Statements Outside Stored Procedures

It’s always been a small annoyance that the MySQL / MariaDB flavour of SQL wouldn’t allow you to use if else logic or loops outside of a stored procedure or trigger. There were ways around  this but it’s not as nice if you’re coming from TSQL. This is rectified in MariaDB from 10.1.1. One thing that is […]

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… […]