Archive for the ‘DBA’ Category

Moving an InnoDB Database with FLUSH TABLES .. FOR EXPORT

If we wanted to move a large InnoDB database our options were very limited. Essentially we had to mysqldump the single database or move the entire tablespace. I did have an idea for moving a single InnoDB database by copying files but only ever tried it out with TokuDB. This method worked but seemed to […]

A MariaDB Multi-Master setup example

Here’s a very quick example for how to setup Multi-Master replication with MariaDB.  It’s light on detail here to focus only on the steps you actually need to take, Have a good read of the documentation before attempting this. This example also uses GTIDs so you’ll need some understanding of these as well. The example here is […]

Identify Cross Database Foreign Keys

I’ve blogged before about cross-database foreign keys and what I think of them. I had a developer wanting to check for such references between two databases today. Here’s what I came up with to do this… ?View Code MYSQLSELECT t.NAME AS TABLE_NAME, i.NAME AS INDEX_NAME, f.* FROM information_schema.`INNODB_SYS_FOREIGN` f INNER JOIN information_schema.`INNODB_SYS_INDEXES` i ON i.`NAME` […]

mysqldump: backing up specific tables

Here’s a quick example of how to backup specific MySQL / MariaDB tables and piping to xz to compress… ?View Code BASHmysqldump -h 127.0.0.1 -P3306 mysql user tables_priv | xz –compress -9 > /db_dumps/3104/mysqldump_tables_example.sql.xz Here’s how to decompress the resulting file. Note xz will delete the input file unless you specifiy –keep on the command […]

MariaDB’s federatedx engine

I’ve been experimenting a little with the federatedx engine in MariaDB. For those of you coming from MSSQL think linked servers and that’s pretty much it, albeit with a few differences. Here’s a quick primer on the basics. Firstly you might need to enable the engine… ?View Code MYSQL# Try "SHOW ENGINES" if federated isn’t […]