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