mysql

MySQL / MariaDB login audit

by .pQd
once in a while i need to review which database accounts are used, from what IPs connections arrive. MySQL / MariaDB does not have built-in mechanism [ unless you want to allow full query log ], but there’s quite easy way to get the data.

multi-master mysql replication with servers on 3 different continents

by .pQd
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?

by .pQd
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

by .pQd
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

by .pQd
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

by .pQd
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

by .pQd
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

by .pQd
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

by .pQd
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

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