Keeping track of database table size changes in MySQL

I don’t know how common is this problem, but it is good to know from time to time about which tables how many disk space needed at a certain time. For example, you can catch an amok running software part which writes your database full. Or, – as you will see so – you can catch up some code what doesn’t work as excepted.

So, let’s start at the beginning. You wanna to know how big are your tables, and you need to know how many data gets there day-by-day (or minute-by-minute. or whatever).

Continue reading “Keeping track of database table size changes in MySQL”

MySQL connections listing

Currently, at Kinja we are in a middle of big architectural change on database servers, so I have run into a problem regarding this. Sometimes I have to check current connections on database servers, to see what schemas are in use, what servers using a given DB server, or even which users are connected to the database server.

Previously when I had to determine connected hosts, I just used a one-liner script in bash, what parse through the output of netstat and listed the number of connections from given servers like this:

[root@sql-slave1 banyek]# netstat | grep mysql | awk '{print $5}' | awk -F: '{print $1}' | sort | uniq -c
      1 app01.bfc.kinja-ops.c
     83 app05.bfc.kinja-ops.c
     84 app09.bfc.kinja-ops.c
      9 dbcacti.bfc.kinja-ops
      1 nagios.bfc.kinja-ops.
      1 sql-master1.bfc.kinja

This was enough to quickly see the connected hosts, but the output wasn’t too chatty, and there are a lot of information which were hidden.

Continue reading “MySQL connections listing”