I’ve created and another module for this besides the mysql_replication named mysql_variables.
The purpose of this module to get/set variables from running MySQL servers, and avoid to use shell module for this, because that is a bit frustrating, and there are long commands to run.
For example, when you wanna to set read_only mode via shell, you can do that like this:
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).
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.
Yesterday Dominis mentioned shell-foo and one cool thing came to my mind what one of my ex-colleagues showed me a few years ago. (Hi Pali!)
So let’s pretend you have to locate pid of mysql process. You run the good old ps command, and grep for mysql:
We maintain a lot of servers under Kinja, so we have to use some orchestrator software to perform some tasks on a lot of servers. The Ansible software is used by us because it is cool.
We have also a lot of MySQL servers (and counting!) under Kinja, so we have some tasks to perform on them, such as managing replication. Of course, there are some ways to do this, for example using multiplexed terminals, or run ansible shell commands what performs mysql queries (e.g. ansible mysql-master1 -m shell -a “mysql -e “SOME SQL QUERY HERE”) but it is not too comfy, and needs a lot of manual work.
So, there is a way to make it easier for us, and that’s why I made a mysql_replication module for Ansible. (And I made a pull request for that on GitHub, so I hope it will be merged soon to ‘official’ branch)
The mysql_replication module helps you to
The module can be found in my GitHub repo here.
First, I have to tell you, that the “fragmentation” is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitsev’s 2008 article about Information_schema, I used that query to get that results what I needed.
Some background: a few weeks ago we got some free space related problems (well, they weren’t a real problem, but they could lead onto one, and we had to act fast.) The InnoDB is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use
innodb_file_per_table
configuration option, what will split up the database to a lot of .ibd datafiles, instead of one big ibdata file. If you got your data in different tables, you can run “OPTIMIZE TABLE” from mysql, what will compact the datafiles (creates a new one, copies the table contents onto this new table, deletes the old file, and renames the new) for you. It takes a lot of time to run, and you have to know, that the “optimize” command will also be replicated, so after you did this on master be aware that it will happen on slaves too. So, when you take some maintenance time, you have to calculate with this. It’s easy to determine what tables would be optimized, you can check the table size from the data dictionary, and you can see how big is that table on disk. If they differ, then you can reclaim the space what is the difference between the two sizes.