Showing entries 31 to 40 of 102
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Tech (reset)
How to drop table in a hacky way

I showed in an earlier post how to drop a whole database in a very safe way (no replication lag at all) and that technique is usable to drop a single table too, but cleaning up a table can take hours if not days to finish, so this is not the most comfortable way to do that. We also don’t want to have even a small spike of replication lag, so we need to find an another solution.

How to remove database in a safe way
When you have to drop a large database, you’ll encounter some problems, mainly replication…
Read more
What happens when you issue a DROP TABLE command? The table has to be removed from the table dictionary – which is a fast, atomic operation – and has to be removed from file system too. If you use older version than 5.5.10 you have to calculate with a huge amount of time if your buffer pool is big, because the server will scan through the pages there, checking if anything is in memory from that …

[Read more]
Including Code Samples With rst2pdf

Every document I create these days is written in rst (ReStructuredText) and transformed into something useful using rst2pdf. This includes worksheets, reports, handouts and slide decks. Along the way I've learned a few tricks, and I try to write them down so I can look up how to do something. If this helps you too, then great :)

Start Simple

Include a code block in rst by adding something like this:

.. code-block:: php

Your code must then be indented, and the style persists until you "undent" again. There is pretty extensive language/syntax support since it is built on pygments, so you can use most language names instead of the php there.

Line Numbers and PHP Tags

In order to make the PHP syntax highlight …

[Read more]
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 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 supressed, and if none then that means, every message will be show …

[Read more]
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 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 …

[Read more]
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 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 …

[Read more]
MySQL documentation writer for MEM and Replication wanted!

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 …

[Read more]
MySQL replication caveats, or why to be cautious.

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 …

[Read more]
MySQL variables module for ansible

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 -m shell -a "mysql -e 'select @@read_only'" | success | rc=0 >>

[root@admin banyek]# ansible -m shell -a "mysql -e 'set global read_only=1'" | 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 …

[Read more]
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 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` (
[Read more]
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 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 …

[Read more]
Showing entries 31 to 40 of 102
« 10 Newer Entries | 10 Older Entries »