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 have not spend 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?

Check out this scenario: you have two master servers, called db-master1 and db-master2 and two slaves db-slave1 db-slave2. All the writes are happening on db-master1 and this will replicating to the slaves and the failover master too.

So you have your replication set up, and everything works well. But there is a point …

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 have to change because of renaming, deprecating etc. and after all it could be done in a few minutes.

…but not as easy as it seems at the first sight.

But this is not as easy if you want to be sure, that the upgrade won’t break anything. Well, on a big site like Kinja, I can ensure you: you want to check out all of the possible bad things which could happen.

So, you have to check out three different aspects of the upgraded system:

• The slave data consistency

• The query result consistency

• The systems overall performance

The upgrade process Slave data …

As MySQL is thriving and growing, we're looking for an experienced technical writer located in the UK or Ireland to join the MySQL documentation team.

For this job, we need the best and most dedicated people around. You will be part of a geographically distributed documentation team responsible for the technical documentation of all MySQL products. Team members are expected to work independently, requiring discipline and excellent time-management skills as well as the technical facilities and experience to communicate across the Internet.

Candidates should be prepared to work intensively with our engineers and support personnel. The overall team is highly distributed across different geographies and time zones. Our source format is DocBook XML. We're not just writing documentation, but also handling publication. This means you should be familiar with DocBook, and willing to learn our publication …

Recently we had a small hickup in our service; it was caused by our mysql replication configuration and a small mistake which was made 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 affects the database to a binary log file, and slaves will be 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.

Of course we can filter out some statements from replication, for example when there are tables which are only read or write on master, and didn’t needed to have on slaves. On kinja, we have certain tables where this rule applies, for example a sessions table, which contains session related data. We have to ensure that the data will be there asap when it was written, and because of that, there is …

I’ve create 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:

ansible db-secondary.bfc.kinja-ops.com -m shell -a "mysql -e 'select @@read_only'"
db-secondary.bfc.kinja-ops.com | success | rc=0 >>

[root@admin banyek]# ansible db-secondary.bfc.kinja-ops.com -m shell -a "mysql -e 'set global read_only=1'"
db-secondary.bfc.kinja-ops.com | success | rc=0 >>

Not so neat, isn’t it, eh? And I have not speak about the —user and —password variables, to this could be real annoying.

That’s why I made this module, because it is a bit easier to use (and I like to use own toolset with the …

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

So, lets 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).

You can query information_schema.tables for table sizes, this is good, but you won’t be happy just with these results, because you won’t find any time based changes, so you have to store the data.

So first, we have to create a table to store this historical data:

CREATE TABLE `test`.`table_sizes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE …

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

Previously when I had to determine connected hosts, I just used a one-liner script in bash, what parsed 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 …

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:

[banyek@sql-master1.bfc ~]$ ps -ef | grep mysql
root      1751     1  0 Jul01 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/sql-master1.bfc.kinja.com.pid
mysql     2424  1751 88 Jul01 ?        21:59:18 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/var/lib/mysql/sql-master1.bfc.kinja.com.pid --socket=/var/lib/mysql/mysql.sock
banyek    4630  4360  0 11:31 pts/0    00:00:00 grep mysql
[banyek@sql-master1.bfc ~]$

There is one small problem: the grep will find itself too, so if you want count …

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 Ansbile 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 ansibile 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

…

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 Zaitchev’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 …