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
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
i’m backing up in total ~90GB of mysqldumps each night. the more data, the bigger pain it is.
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]
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
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.
i’ve been playing for a while with mysql circular replication; here are some notes from setting it up.
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
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.
so.. i have couple of master machines and few slaves. some of slaves are offsite – 60ms away from primary site.