multi-master mysql replication with servers on 3 different continents

at work i’m using mysql replication quite extensively. first it was a straightforward one-way replication that has been rock-solid for us since 2009. in 2012, for another type of data, we’ve started using master-master setup. initially the servers were in different European countries, eventually the secondary site was moved to North America while primary one ... Read More

MySQL on BTRFS?

i’ve been running a set of production MySQL databases on BTRFS since April 2016. BTRFS is not exactly known for its stellar performance when hosting databases or images of virtual machines due to its COW nature. why would i do it then? to have data snapshots and be able to ‘go back in time’ quickly ... Read More

pigz –rsyncable, rdiff

i’m backing up in total ~90GB of mysqldumps each night. the more data, the bigger pain it is.

extracting single database from a [large] mysqldump

extracting the whole database: sed -n '/^-- Current Database: `databaseName`/,/^-- Current Database: `/p' /path/to/the/fill/dump.sql > output.sql single table: sed -n '/^CREATE TABLE `tableName`/,/^\-\- Table structure for table /p' /path/to/the/fill/dump.sql > output.sql [via this blog]

excluding some mysql queries from the slow query log

i keep close eye on the mysql-slow.log log of queries that take more than 1s to execute. but at the same time i know about few house-keeping queries that are executed few times per day that by design take longer to execute [eg table checksums]. i don’t want those queries to ‘pollute’ the slow query ... Read More

mysql 5.1 – turning on/off query logging on the fly

i dont run query logging – more to avoid running out of the disk space on /var/log than for the performance reasons. but from time to time it’s good to be able to turn logging on to see what’s going on. in 5.1 and later versions it’s possible without restarting the mysqld.

Linux and H/W optimizations for MySQL

good stuff: http://en.oreilly.com/mysql2011/public/schedule/detail/17111 http://assets.en.oreilly.com/1/event/56/Linux%20and%20H_W%20optimizations%20for%20MySQL%20Presentation.pdf

innodb_stats_sample_pages to save a day

i’ve run into nasty instability in mysql’s query execution plan joining few tables with 10s of millions of rows. mysql 5.1’s innodb_stats_sample_pages seem to solve the problem.

mysql replication – some tips

so.. i have couple of master machines and few slaves. some of slaves are offsite – 60ms away from primary site.