Posts Tagged ‘MySQL’

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

Aria Storage Engine Primer

I’m looking into HA MySQL at the moment. With these types of technologies you need to have a crash-safe storage engine in use. MyISAM just won’t cut it. While my long-term goal is to move to a fully-transactional storage engine, for example InnoDB, I’m looking at other possibilities. Changing to a fully-transactional storage engine may […]

Move a MySQL / TokuDB database?

I’ve been having a look at TokuDB recently and I’m quite excited about some of its claims. But everything comes with its limitations! If you search Google for “move tokudb database” You’ll be presented with a big page of NO! Aside from moving the entire data directory the advice here is use mysqldump or change to another […]

MySQL Database Maintenance Stored Procedure Update

This is just a quick update of a stored procedure to assist with MySQL Database Maintenance. I originally posted this back in 2012. The Stored Procedure allows you to run OPTIMIZE TABLE or ANALYZE TABLE on all (or most) tables in a MySQL database. In this version I’ve added some simple logging and automatic recognition of partitioned tables to take advantage […]