MySQL replication module upgrade

Yesterday I’ve put some new features into the ansible’s mysql_replication module because we are planning to move to GTID based replication from the good old binlog position based one, and the module wasn’t aware of.


This parameter can be either 0 or 1, defaults to 0. If set to 1 the replication will be threaded as GTID based replication.


This parameter threats the warnings because of MySQL 5.6 complaints a lot more than the previous versions. (For example, if the replication is not encrypted with SSL/TLS.) This could break our playbooks so you can set it to all, warnings, none (defaults none). Speaks for itself, all means all warnings/errors will be shown, if warnings set, then only the errors will be shown, and the warnings suppressed, and if none then that means, every message will be show up as-is.

If you need it, and you don’t want to wait until it is merged and released, you can download it from my GitHub repository.

GTID based replication showcase

We are in the middle of switching to GTID based replication from the good old logfile & log position based replication.

But what is GTID? GTID is an abbreviation of ‘GLOBAL TRANSACTION ID’ what speaks for itself: each transaction of a mysql cluster got its globally unique transaction ID, and the DBA has not spent time with positioning slaves, as well as we don’t have to ‘freeze’ any of the servers because of a master failover. The only thing we have to care about: to know what server should be used as a replication master.

OK, what was the problem with the old file-based replication?

Continue reading “GTID based replication showcase”

Upgrading MySQL

Upgrading MySQL is easy…

Upgrading mysql to a newer version is very simple: just replace the binaries, and run:

# mysql_upgrade

watch the error log after restarting the service to check if any configuration parameter has to change because of renaming, deprecating etc. and after all, it could be done in a few minutes.

Continue reading “Upgrading MySQL”

MySQL replication caveats, or why to be cautious.

Recently we had a small hiccup in our service; it was caused by our mysql replication configuration and a small mistake which was made a long time before. The lesson was learned, and now I show that to you.

MySQL replication is simple. The replica set’s primary server (aka. replication master) writes the events which affect the database to a binary log file, and slaves will apply that log on their datasets. The binary log can be statement-based, row-based or in mixed mode, the events can be either DML or DDL queries. Simple enough.

Continue reading “MySQL replication caveats, or why to be cautious.”

MySQL variables module for ansible

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:

Continue reading “MySQL variables module for ansible”

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”

MySQL replication module for ansible

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

  • Query slave status
  • Query master status
  • Change replication slave’s parameters (such as master server IP, master_log_pos, etc.)
  • Stop slave thread
  • Start slave thread

The module can be found in my GitHub repo here.

Free up space on mysql servers

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


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.

Continue reading “Free up space on mysql servers”